0

I am running a query like

explain (analyze, buffers) select col1, col2, count(col3) as c from table1 group by 2, 1 order by 2, 1

When the work_mem is set at 4 MB, the plan looks like this:


"GroupAggregate  (cost=0.70..211944.54 rows=573788 width=26) (actual time=5.146..2601.133 rows=1867574 loops=1)"
"  Group Key: col2, col1"
"  Buffers: shared hit=1844356 read=9682"
"  ->  Incremental Sort  (cost=0.70..191999.45 rows=1894295 width=21) (actual time=5.131..1848.190 rows=1894295 loops=1)"
"        Sort Key: col2, col1"
"        Presorted Key: col2"
"        Full-sort Groups: 58831  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB"
"        Buffers: shared hit=1844356 read=9682"
"        ->  Index Scan using table1_pkey on table1  (cost=0.43..121686.41 rows=1894295 width=21) (actual time=5.071..923.512 rows=1894295 loops=1)"
"              Buffers: shared hit=1844356 read=9682"
"Planning:"
"  Buffers: shared hit=2"
"Planning Time: 0.127 ms"
"JIT:"
"  Functions: 7"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.614 ms, Inlining 0.000 ms, Optimization 0.346 ms, Emission 4.648 ms, Total 5.609 ms"
"Execution Time: 2725.164 ms"

When I increase the work_mem to 1GB, it is suddenly very different

"Sort  (cost=107700.32..109134.79 rows=573788 width=26) (actual time=6461.310..6821.930 rows=1867574 loops=1)"
"  Sort Key: col2, col1"
"  Sort Method: quicksort  Memory: 195057kB"
"  Buffers: shared hit=13813 read=116"
"  ->  HashAggregate  (cost=47079.16..52817.04 rows=573788 width=26) (actual time=1194.218..1777.794 rows=1867574 loops=1)"
"        Group Key: col2, col1"
"        Batches: 1  Memory Usage: 303121kB"
"        Buffers: shared hit=13813 read=116"
"        ->  Seq Scan on table1  (cost=0.00..32871.95 rows=1894295 width=21) (actual time=0.016..214.794 rows=1894295 loops=1)"
"              Buffers: shared hit=13813 read=116"
"Planning:"
"  Buffers: shared read=2"
"Planning Time: 0.122 ms"
"JIT:"
"  Functions: 7"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.477 ms, Inlining 0.000 ms, Optimization 0.216 ms, Emission 4.722 ms, Total 5.416 ms"
"Execution Time: 6967.294 ms"

Confusing observations -

  1. It switched to a sequential scan instead of index scan where there's more memory
  2. It ditched the efficient incremental sort (followed by GroupAggregate) and did a HashAggregate followed by Quick sort
  3. Cost of the new plan with 1 GB memory is shown to be lower, but the run time is much higher.

What's going on?

5
  • It is substantially wrong on how many groups there will be. It is not surprising this leads to poor plan choice. It is hard to speculate on why it is so wrong. Could you give a full description of the table, index, and any custom statistics defined on it.
    – jjanes
    Feb 5 at 22:05
  • Its fairly vanilla. The table has 4 cols - A (int), B(char), C (char), D (int). Primary key index on (B, D). Unique constraint on (D, A). Unique constraint on (D, C). Haven't defined any custom statistics on the table.
    – ahron
    Feb 6 at 4:02
  • @jjanes - I tested and saw that when I disable incremental sort, the performance with 4 MB vs 1 GB work_mem is in line with each other. My hunch is the way it assigns costs to one sorting method is not aligned with how it estimates cost of another sorting method. Could it be? If you want to test the same queries on the same DB, please let me know and I can share with you in a chat. I also didn't completely understand your remark "wrong on how many groups there will be"; could you clarify please?
    – ahron
    Feb 6 at 4:55
  • Is col3 nullable?
    – bobflux
    Feb 6 at 10:57
  • @bobflux Nope...
    – ahron
    Feb 6 at 11:01

2 Answers 2

1

It switched to a sequential scan instead of index scan where there's more memory

It switched from GroupAggregate to HashAggregate. The seq scan is an incidental consequence to this change. HashAggregate is penalized for being expected to spill to disk, and increasing work_mem removes that penalty.

It ditched the efficient incremental sort (followed by GroupAggregate) and did a HashAggregate followed by Quick sort

The HashAggregate is also efficient. It is the sort afterwards, of 3 times as many rows as it expected (573788 vs 1867574), that was slow.

Cost of the new plan with 1 GB memory is shown to be lower, but the run time is much higher.

Estimation is hard. There is nothing we can do (in general) about estimation being hard. What are you trying to do? Solve a concrete problem with this exact query? Learn some general principle? Vent?

1
  • Not venting for sure. In my limited experience, cost estimates do correspond (even if not proportionally) to actual execution times and increasing working memory is a good thing. I just came across this observation while playing around with a database and wanted to dig a bit deeper - because it was new / strange to me.
    – ahron
    Feb 7 at 5:14
1

You could try to use extended statistics to improve the estimate:

CREATE STATISTICS mystats (ndistinct) ON col1, col2 FROM table1;
ANALYZE table1;

I am not sure if that is enough to make the fast plan win.

1
  • Tried it just now.. still the same...
    – ahron
    Feb 6 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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