2

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?

3
  • We need to see the actual results of explain analyze. – Evan Carroll Oct 15 '18 at 19:03
  • 3
    What about SELECT id, (select count(*) from main) as total count FROM main limit 10? – a_horse_with_no_name Oct 15 '18 at 19:24
  • Yes in that case the query is as fast as a count, but I wanted to use Window functions to avoid the overhead to generate complex queries in the backend, as I have to set a bunch of dynamic filters. But I guess I’ll have to stop being lazy and pour another coffee :) – Creaforge Oct 15 '18 at 19:39
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,

3
  • Sorry Evan english isn’t my mother tongue, what I ment wasn’t that Postgres is the problem of course, but that my knowledge is too limited to understand what is happening. What I don’t get is why the planner did choose a costly WindowAgg over 1M rows. There is probably a reason: either my query is not right, either window functions is not designed for such queries, in that case I have to think differently. I tried to sort the OVER() but in that case I got actual ids returned instead of the count :) – Creaforge Oct 15 '18 at 19:35
  • @a_horse_with_no_name totally true, I deleted that part.. THOUGH IT WOULD PROBABLY FORCE THE USE OF THE INDEX. =) – Evan Carroll Oct 15 '18 at 19:47
  • @Creaforge it's not your query, it's just what you want the planner isn't smart enough to plan in the smartest fashion, yet. That's all there is to it. Sometimes the planner is smart enough so that you can relax. Sometimes it's not. For the time being you'll have to put it in a subselect. explain analyze SELECT id, (SELECT count(*) FROM main) FROM main LIMIT 10; – Evan Carroll Oct 15 '18 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.