降低PostgreSQL的work_mem后查询反而变快，原因何在？

核心逻辑：work_mem影响优化器的执行计划成本估算 #

work_mem的作用是控制单个排序、哈希等操作的内存上限，官方文档提到要尽量避免写临时文件没错，但这不代表work_mem越大就一定最优——优化器会根据work_mem的大小，估算排序操作的成本，进而选择不同的执行路径。

结合你的执行计划看差异： #

1. work_mem=64MB时：
   优化器判断内存足够完成排序（实际仅用了1447kB，远低于64MB），因此选择了「全表扫描（Seq Scan）+ 内存排序」的方案。但实际执行中，全表扫描加排序的总耗时（约6ms）反而更高。

   对应的执行计划：

   QUERY PLAN                                                                                                                        |
   ----------------------------------------------------------------------------------------------------------------------------------+
   Unique  (cost=325.67..333.67 rows=472 width=1046) (actual time=5.574..5.938 rows=493 loops=1)                                     |
     ->  Sort  (cost=325.67..329.67 rows=1599 width=1046) (actual time=5.572..5.681 rows=1773 loops=1)                               |
           Sort Key: id, revision_date DESC, revision_id DESC                                                                        |
           Sort Method: quicksort  Memory: 1447kB                                                                                    |
           ->  Seq Scan on indicator_revisions  (cost=0.00..240.58 rows=1599 width=1046) (actual time=0.015..2.501 rows=1773 loops=1)|
                 Filter: (NOT draft)                                                                                                 |
                 Rows Removed by Filter: 269                                                                                         |
   Planning Time: 0.151 ms                                                                                                           |
   Execution Time: 6.025 ms                                                                                                          |
   

2. work_mem=64kB时：
   优化器估算内存不足以完成排序，磁盘排序的成本远高于内存排序，因此转而选择使用key_risk_indicators_index的索引扫描方案——该索引的排序规则恰好匹配Unique所需的键（id, revision_date DESC, revision_id DESC），无需额外排序，过滤后直接得到结果，实际耗时更低（约3.9ms）。

   对应的执行计划：

   QUERY PLAN                                                                                                                                                     |
   ---------------------------------------------------------------------------------------------------------------------------------------------------------------+
   Unique  (cost=0.28..1015.05 rows=472 width=1046) (actual time=0.026..3.834 rows=493 loops=1)                                                                   |
     ->  Index Scan using key_risk_indicators_index on indicator_revisions  (cost=0.28..1011.05 rows=1599 width=1046) (actual time=0.022..3.138 rows=1773 loops=1)|
           Filter: (NOT draft)                                                                                                                                    |
           Rows Removed by Filter: 269                                                                                                                            |
   Planning Time: 0.158 ms                                                                                                                                        |
   Execution Time: 3.904 ms                                                                                                                                       |
   

为什么优化器一开始没选Index Scan？ #

优化器的选择完全基于成本估算，而估算结果依赖统计信息和配置参数，出现偏差的原因通常有这些：

• 当work_mem较大时，优化器认为内存排序成本极低，因此估算「Seq Scan + 内存排序」的总成本（333.67）远低于「Index Scan」的估算成本（1015.05），所以优先选择前者。
• 但实际执行中Index Scan更快，说明估算和真实场景有偏差，可能的诱因包括：
  • 统计信息不准确：表的总行数、过滤后返回行数的统计值和实际不符，导致优化器误判成本。
  • 成本参数设置不合理：cpu_tuple_cost、cpu_index_tuple_cost等参数定义了不同操作的CPU成本权重，若参数和硬件实际性能不匹配，会影响估算结果。
  • 索引实际效率被低估：比如索引过滤后的回表开销比估算的低，或者磁盘I/O的真实速度和优化器预设值有差异。

内容的提问来源于stack exchange，提问作者Caio Salgado