I'm having a hard time trying to figure the performance gap between a raw
count() query vs a
count() OVER() window function.
Here is the test setup. Let's create a dummy table:
CREATE TABLE main (id int); INSERT INTO main VALUES (generate_series(1,1000000)); VACUUM ANALYZE main;
So far so good, counting the whole set takes around 60ms using a parallel sequential scan:
SELECT count(*) FROM main;
Finalize Aggregate (cost=10633.55..10633.56 rows=1 width=8) (actual time=65.260..65.260 rows=1 loops=1) -> Gather (cost=10633.33..10633.54 rows=2 width=8) (actual time=65.229..65.256 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=9633.33..9633.34 rows=1 width=8) (actual time=45.728..45.729 rows=1 loops=3) -> Parallel Seq Scan on main (cost=0.00..8591.67 rows=416667 width=0) (actual time=0.008..24.864 rows=333333 loops=3) Planning time: 0.783 ms Execution time: 67.769 ms
Now let's try to return a few rows along with the count :
explain analyze SELECT id, count(*) OVER() FROM main LIMIT 10;
Limit (cost=0.00..0.27 rows=10 width=12) (actual time=1077.092..1077.100 rows=10 loops=1) -> WindowAgg (cost=0.00..26925.00 rows=1000000 width=12) (actual time=1077.091..1077.097 rows=10 loops=1) -> Seq Scan on main (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.520..60.036 rows=1000000 loops=1) Planning time: 0.671 ms Execution time: 1094.541 ms
This query takes 1050ms to complete. What surprises me is that the planner chooses to do a sequential scan, then a window aggregation, and finally a limit over the whole set.
Creating an btree index didn't help, while raising the
128MB did (dropped to 300ms). But I'm not a big fan of "if it too slow, order more servers". I'd rather understand what causes this behaviour and optimize the code.
I am aware that you can get estimates by querying the reltuples, but my question is more about understanding why there is such a performance difference between executing a
SELECT + a
count() and selecting and counting in the same query?