Performance of count() vs count() in a window function

Question

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 work_mem to 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?

Answer 1

why there is such a performance difference between executing a SELECT + a count() and selecting and counting in the same query?

Because PostgreSQL isn't optimized to do what you want, clearly. The count(*) OVER () is subject to WindowAgg which presumably does more (perhaps even with an exposed accumulator) than the PartialAggregate. And, moreover, as you pointed out one of them is subject to Parallel Seq Scan, presumably Window Functions are not subject to Parallel queries yet.

I'm sure patches are accepted.

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.

That's to be expected, what's surprising? The LIMIT always runs last.

creating an btree index didn't help,