排序大量数据时PostgreSQL查询缓慢的优化方案咨询

当执行以下查询（筛选processed=false）时，速度仅需6毫秒：

select h.id
from history h
where
    h.processed = false
order by coalesce (h.last_process_attempt_time, h.delivery_time)
limit 5

但将条件改为processed=true后，执行时间超过1.5秒（是前者的260倍）：

explain analyze 
select h.id
from pgz75008.history h
where
    h.processed = true -- 唯一修改的地方
ORDER BY coalesce (h.last_process_attempt_time, h.delivery_time)
limit 5

当前使用的索引：

CREATE INDEX test_idx ON history USING btree (processed, last_process_attempt_time, delivery_time);

两次查询的执行计划 #

第一次（processed=false） #

Limit  (cost=18242.80..18242.81 rows=5 width=45) (actual time=6.866..6.869 rows=5 loops=1)
  ->  Sort  (cost=18242.80..18468.04 rows=90095 width=45) (actual time=6.863..6.864 rows=5 loops=1)
        Sort Key: (COALESCE(last_process_attempt_time, delivery_time))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Index Scan using test_idx on history h  (cost=0.43..16746.36 rows=90095 width=45) (actual time=0.034..6.601 rows=1149 loops=1)
              Index Cond: (processed = false)
Planning Time: 0.309 ms
Execution Time: 6.904 ms

第二次（processed=true） #

Limit  (cost=331050.32..331050.92 rows=5 width=45) (actual time=1497.691..1631.789 rows=5 loops=1)
  ->  Gather Merge  (cost=331050.32..1127312.50 rows=6650224 width=45) (actual time=1484.761..1618.855 rows=5 loops=1)
        Workers Planned: 4
        Workers Launched: 4
        ->  Sort  (cost=330050.26..334206.65 rows=1662556 width=45) (actual time=1420.135..1420.138 rows=4 loops=5)
              Sort Key: (COALESCE(last_process_attempt_time, delivery_time))
              Sort Method: top-N heapsort  Memory: 25kB
              Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
              Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
              Worker 2:  Sort Method: top-N heapsort  Memory: 25kB
              Worker 3:  Sort Method: top-N heapsort  Memory: 25kB
              ->  Parallel Seq Scan on history h  (cost=0.00..302435.80 rows=1662556 width=45) (actual time=11.190..1000.568 rows=1347834 loops=5)
                    Filter: processed
                    Rows Removed by Filter: 230
Planning Time: 0.233 ms
JIT:
  Functions: 21
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 8.992 ms, Inlining 0.000 ms, Optimization 5.794 ms, Emission 61.213 ms, Total 75.999 ms
Execution Time: 1634.044 ms

1. 创建表达式索引（最优解） #

当前索引无法直接支持coalesce(last_process_attempt_time, delivery_time)的排序需求，当processed=true的数据量极大时，PostgreSQL会选择并行全表扫描而非索引扫描。创建包含排序表达式的索引，让数据库可以直接从索引中按顺序获取前5条数据，无需排序：

CREATE INDEX idx_processed_coalesced_time ON history 
USING btree (processed, COALESCE(last_process_attempt_time, delivery_time))
INCLUDE (id); -- 包含需要返回的id字段，避免回表查询

这个索引结构中，先按processed过滤，再按合并后的时间字段排序，同时直接包含查询需要的id，查询时可以直接走索引仅扫描，无需访问表数据，速度会大幅提升。

2. 为什么当前索引效率低 #

• 当processed=false时，符合条件的行数极少（仅1149行），数据库走索引扫描后用top-N堆排序就能快速得到结果。
• 当processed=true时，符合条件的数据占绝大多数（约665万行），优化器认为走现有索引需要扫描大量索引条目再排序，成本高于全表扫描后排序，因此选择了并行全表扫描。

3. 临时方案（不推荐） #

如果不想创建新索引，可以尝试强制数据库使用现有索引，但这种方法依赖统计信息，长期来看不稳定：

explain analyze 
select h.id
from pgz75008.history h
where
    h.processed = true
ORDER BY coalesce (h.last_process_attempt_time, h.delivery_time)
limit 5
-- 强制使用索引
INDEX test_idx;

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