我正在运行一个查询,例如
explain (analyze, buffers) select col1, col2, count(col3) as c from table1 group by 2, 1 order by 2, 1
当work_mem
设置为 4 MB 时,计划如下所示:
"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"
当我将work_mem增加到1GB时,它突然变得非常不同
"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"
令人困惑的观察结果——
- 它切换到顺序扫描而不是索引扫描,因为内存更多
- 它放弃了高效的增量排序(随后是 GroupAggregate),并进行了 HashAggregate 和快速排序
- 具有 1 GB 内存的新计划的成本较低,但运行时间要长得多。
这是怎么回事?
您可以尝试使用扩展统计数据来改进估计:
我不确定这是否足以使快速计划获胜。
它从 GroupAggregate 切换到 HashAggregate。seq 扫描是这一变化的偶然结果。HashAggregate 因预期溢出到磁盘而受到惩罚,而增加 work_mem 可以消除该惩罚。
HashAggregate 也很高效。后来排序的行数是预期的 3 倍(573788 vs 1867574),速度很慢。
估计很难。(一般来说)我们对于估算的困难无能为力。你想做什么?用这个精确的查询解决一个具体的问题?了解一些一般原则?发泄?