Count and Its Many Guises
Russell Morris
- 5 minutes read - 896 wordsContext
So why am I blogging about the count command today? Its mainly because I keep on seeing this come up on my feed. Normally the posts are under the guise of look at this interview question or junior DBAs should know this.
Which is great but the articles rarely show why one is better then the other, or even how the query planner looks at the query that you are running. The conclusions of this blog may be the same as the quick articles but hopefully this will provide a bit more information as to the WHY. This post relates to the POSTGRES dbengine.
So lets start at the beginning, we can split this into separate sections based on that. But first lets create a table and fill it with some test data - as the proof is always in the data.
Environment setup
Quick environment setup was created courtesy of an AI query, heres the scripts:
-- Create the table
CREATE TABLE test_table (
id SERIAL PRIMARY KEY NOT NULL, -- First column, NOT NULL
col1 TEXT,
col2 INTEGER,
col3 TIMESTAMP,
col4 NUMERIC
);
-- Insert 5 million rows (other columns may have NULLs)
INSERT INTO test_table (col1, col2, col3, col4)
SELECT
CASE WHEN random() > 0.2 THEN md5(random()::text) ELSE NULL END, -- 80% chance of text, else NULL
CASE WHEN random() > 0.3 THEN (random() * 10000)::int ELSE NULL END, -- 70% chance of integer, else NULL
CASE WHEN random() > 0.4 THEN NOW() - (random() * INTERVAL '365 days') ELSE NULL END, -- 60% chance, else NULL
CASE WHEN random() > 0.5 THEN random() * 100 ELSE NULL END -- 50% chance, else NULL
FROM generate_series(1, 5000000);
--Analyze the table to bring all the stats up to date
ANALYZE test_table;
COUNT(*)
So this is the most common query you will see relating to count, what it will do is count all the rows in the table that you are looking at:
EXPLAIN (ANALYZE, COSTS, BUFFERS)
SELECT COUNT(*)
FROM test_table
RESULT SET = 5000000
Finalize Aggregate (cost=75409.88..75409.89 rows=1 width=8) (actual time=359.511..363.447 rows=1 loops=1)
Buffers: shared hit=12469 read=35899
-> Gather (cost=75409.67..75409.88 rows=2 width=8) (actual time=359.318..363.440 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=12469 read=35899
-> Partial Aggregate (cost=74409.67..74409.68 rows=1 width=8) (actual time=341.376..341.376 rows=1 loops=3)
Buffers: shared hit=12469 read=35899
-> Parallel Seq Scan on test_table (cost=0.00..69201.33 rows=2083333 width=0) (actual time=0.075..212.629 rows=1666667 loops=3)
Buffers: shared hit=12469 read=35899
Planning Time: 0.051 ms
Execution Time: 363.474 ms
So a pretty standard execution plan, to be fair.
COUNT(1)
So I have seen this floated as possibly more performant then COUNT(*) as its counting the first column only, but lets have a look:
EXPLAIN (ANALYZE, COSTS, BUFFERS)
SELECT COUNT(*)
FROM test_table
RESULT SET = 5000000
EXECUTION PLAN:
Finalize Aggregate (cost=75409.88..75409.89 rows=1 width=8) (actual time=317.303..319.729 rows=1 loops=1)
Buffers: shared hit=13260 read=35108
-> Gather (cost=75409.67..75409.88 rows=2 width=8) (actual time=317.100..319.720 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=13260 read=35108
-> Partial Aggregate (cost=74409.67..74409.68 rows=1 width=8) (actual time=313.602..313.603 rows=1 loops=3)
Buffers: shared hit=13260 read=35108
-> Parallel Seq Scan on test_table (cost=0.00..69201.33 rows=2083333 width=0) (actual time=0.052..192.281 rows=1666667 loops=3)
Buffers: shared hit=13260 read=35108
Planning:
Buffers: shared hit=49 read=12
Planning Time: 0.805 ms
Execution Time: 319.795 ms
Looking at the plan, it seems that there is no difference really. The buffers are now being looked at as the data has now been brought into the buffers after the first run. Is it better than COUNT(*) - not really.
COUNT(col1)
Well I would like to use COUNT(col1), and see what happens then:
EXPLAIN (ANALYZE, COSTS, BUFFERS)
SELECT COUNT(col1)
FROM test_table;
RESULT SET = 3999633
Finalize Aggregate (cost=75409.88..75409.89 rows=1 width=8) (actual time=378.495..382.558 rows=1 loops=1)
Buffers: shared hit=13356 read=35012
-> Gather (cost=75409.67..75409.88 rows=2 width=8) (actual time=378.383..382.551 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=13356 read=35012
-> Partial Aggregate (cost=74409.67..74409.68 rows=1 width=8) (actual time=373.787..373.788 rows=1 loops=3)
Buffers: shared hit=13356 read=35012
-> Parallel Seq Scan on test_table (cost=0.00..69201.33 rows=2083333 width=33) (actual time=0.065..195.579 rows=1666667 loops=3)
Buffers: shared hit=13356 read=35012
Planning:
Buffers: shared hit=78 read=4
Planning Time: 0.914 ms
Execution Time: 382.629 ms
Well we had alot less Buffers being hit this time (BUFFERS will have an impact on IO), so this is a bit less resource hungry. But oh our result set is not the same, we don’t seem to have counted all the rows. This is because when you choose to count a column it will only count the rows that actually have a value, so no nulls will be counted in this case.
Now we need to understand we are looking at the full table here, as soon as you start filtering the table with where clauses it will have an impact on the plan. But it will not change COUNT(*) and COUNT(1) will have the similar impact on the table.
Ok so lets look at something slightly different
If an almost number is close enough, how about avoiding the count command completely. Now this will be as up to date as your statistics (you should ensure these are up to date as it helps with your query executions):
SELECT relname AS table_name,
reltuples::bigint AS estimated_count
FROM pg_class
WHERE relname = 'test_table';
I hope from reading through this blog you will see that there are a number of ways you can get a count of your data. Also depending on which count you use, will determine what the affect is on database.