PostgreSQL中FOR UPDATE子句为何导致排序方式从top-N堆排序变为外部归并排序？

这个问题其实戳中了PostgreSQL在处理带锁查询时的执行计划选择逻辑，我来一步步拆解背后的原因：

先看两种执行计划的核心差异 #

首先对比你的两个执行计划：

• 不带FOR UPDATE时：PostgreSQL使用了top-N heapsort，这是一种专为ORDER BY + LIMIT N场景优化的排序方式——它不需要对全表数据排序，只需要维护一个大小为100的堆结构，边扫描数据边更新堆，最终直接得到前100条符合排序要求的行，内存占用极低（你这里只用到40kB），效率极高。而且还用到了并行扫描（Gather Merge），多个worker各自处理部分数据并做top-N排序，最后合并结果，进一步提速。

  对应的执行计划：

  EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM outgoing ORDER BY created_at LIMIT 100;
  -- 实际输出：
  Limit (cost=14230.69..14242.36 rows=100 width=18) (actual time=80.188..85.939 rows=100 loops=1)
  Buffers: shared hit=3305 dirtied=1
  ->  Gather Merge (cost=14230.69..62845.12 rows=416666 width=18) (actual time=80.186..85.929 rows=100 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=3305 dirtied=1
        ->  Sort (cost=13230.67..13751.50 rows=208333 width=18) (actual time=25.455..25.459 rows=67 loops=3)
              Sort Key: created_at
              Sort Method: top-N heapsort  Memory: 40kB
              Buffers: shared hit=3305 dirtied=1
              Worker 0:  Sort Method: top-N heapsort  Memory: 40kB
              Worker 1:  Sort Method: quicksort  Memory: 25kB
              ->  Parallel Seq Scan on outgoing (cost=0.00..5268.33 rows=208333 width=18) (actual time=0.007..7.269 rows=166667 loops=3)
                    Buffers: shared hit=3185 dirtied=1
  Planning Time: 0.097 ms
  Execution Time: 85.972 ms
  

• 带FOR UPDATE SKIP LOCKED时：执行计划变成了「全表扫描 → 全量外部归并排序 → LockRows → Limit」，排序方式切换成了external merge sort——这种方式需要把全表数据写到磁盘上做归并排序，内存和IO消耗都极大，所以你的查询耗时飙升到30秒。

  对应的执行计划：

  EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM outgoing ORDER BY created_at LIMIT 100 FOR UPDATE SKIP LOCKED;
  -- 实际输出：
  Limit (cost=27294.64..27295.89 rows=100 width=24) (actual time=163.076..163.107 rows=100 loops=1)
  Buffers: shared hit=3285, temp read=494 written=2441
  ->  LockRows (cost=27294.64..33544.64 rows=500000 width=24) (actual time=163.075..163.101 rows=100 loops=1)
        Buffers: shared hit=3285, temp read=494 written=2441
        ->  Sort (cost=27294.64..28544.64 rows=500000 width=24) (actual time=163.053..163.058 rows=100 loops=1)
              Sort Key: created_at
              Sort Method: external merge  Disk: 19432kB
              Buffers: shared hit=3185, temp read=494 written=2441
              ->  Seq Scan on outgoing (cost=0.00..8185.00 rows=500000 width=24) (actual time=0.010..29.042 rows=500000 loops=1)
                    Buffers: shared hit=3185
  Planning Time: 0.073 ms
  Execution Time: 166.365 ms
  

为什么FOR UPDATE会改变排序策略？ #

核心原因是锁机制与top-N排序的兼容性冲突：

1. top-N heapsort的前提是「提前终止扫描」
   top-N排序的高效性来自于它不需要扫描全表——只要找到足够的前N条数据就可以停止扫描。但这个逻辑在带锁场景下不成立：因为SKIP LOCKED要求我们跳过已经被其他事务锁定的行，如果你用top-N的方式边扫边选前100条，中途遇到被锁定的行就必须跳过，这意味着你之前筛选出的“前100条”可能有缺失，需要继续扫描后面的行来补全。这种情况下，top-N的“提前终止”优势完全消失，甚至可能需要全表扫描，反而不如直接全量排序可靠。

2. LockRows节点的限制
   当你加上FOR UPDATE时，PostgreSQL的执行计划会插入LockRows节点，这个节点的作用是对上游传来的行加锁（并跳过已锁行）。但LockRows要求上游必须传入完整的有序数据集——如果上游是top-N排序的“部分结果”，LockRows无法处理中途补行的逻辑，因为它不知道后面还有没有符合条件的行需要替换被跳过的锁行。

   因此，优化器会选择先对全表数据做完整排序（因为没有created_at索引，只能通过全表扫描+排序得到有序集），再将全量有序数据传入LockRows节点，最后取前100条锁定。当数据量超过work_mem的限制时，就会触发磁盘-based的外部归并排序，这就是你看到的慢查询根源。

为什么加了索引就解决了问题？ #

当你给created_at添加索引后，PostgreSQL可以直接通过索引有序扫描拿到前100条符合排序要求的行，完全不需要排序操作。无论带不带FOR UPDATE，执行计划都会变成：

Limit (cost=0.43..4.35 rows=100 width=24) (actual time=0.021..0.052 rows=100 loops=1)
  ->  LockRows (cost=0.43..19500.43 rows=500000 width=24) (actual time=0.020..0.049 rows=100 loops=1)
        ->  Index Scan using idx_outgoing_created_at on outgoing (cost=0.43..19000.43 rows=500000 width=24) (actual time=0.017..0.035 rows=100 loops=1)
Planning Time: 0.082 ms
Execution Time: 0.068 ms

这时候既不需要全表扫描，也不需要排序，直接通过索引定位到目标行并锁定，自然速度飞快。

总结一下 #

• FOR UPDATE SKIP LOCKED改变排序方式的核心是：锁机制要求完整的有序数据集，导致优化器放弃了高效的top-N heapsort，转而选择全量排序。
• 当没有索引时，全量排序在大数据量下会触发外部归并排序，导致查询变慢；添加索引后，直接通过索引有序扫描获取目标行，绕过了排序环节，解决了性能问题。

内容来源于stack exchange