PostgreSQL：实现带窗口函数的有限终止扫描并保证结果正确性

场景与表结构 #

我有一张大表events，希望通过索引扫描执行窗口函数，当聚合条件满足时立即停止扫描并返回结果（窗口函数无法放在WHERE子句中，因此不能用WHERE ... LIMIT 1）。表结构如下：

=> \d events
                      Table "public.events"
   Column   |       Type        | Collation | Nullable | Default 
------------+-------------------+-----------+----------+---------
 block      | character varying |           | not null | 
 chainid    | bigint            |           | not null | 
 height     | bigint            |           | not null | 
 idx        | bigint            |           | not null | 
 module     | character varying |           | not null | 
 modulehash | character varying |           | not null | 
 name       | character varying |           | not null | 
 params     | jsonb             |           | not null | 
 paramtext  | character varying |           | not null | 
 qualname   | character varying |           | not null | 
 requestkey | character varying |           | not null | 
Indexes:
    "events_pkey" PRIMARY KEY, btree (block, idx, requestkey)
    "events_height_chainid_idx" btree (height DESC, chainid, idx)

初始查询（性能符合预期但存在正确性隐患） #

经过测试，我写出了一个能返回目标结果且执行计划符合预期的查询：

=> EXPLAIN ANALYZE SELECT * 
FROM (
  SELECT *
       , ROW_NUMBER() OVER (ORDER BY height DESC, block, requestkey, idx) as scan_num
       , count(*) FILTER (WHERE qualname ILIKE '%transfer%') OVER
           ( ORDER BY height DESC, block, requestkey, idx 
             ROWS BETWEEN unbounded PRECEDING AND CURRENT ROW
           ) AS foundCnt
  FROM events
  ORDER BY height DESC, block, requestkey, idx 
) as scanned_events
WHERE foundCnt = 3 OR scan_num = 100000
LIMIT 1                                             
;

对应的执行计划：

QUERY PLAN                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1065.81..1400.34 rows=1 width=397) (actual time=0.095..0.096 rows=1 loops=1)
   ->  Subquery Scan on scanned_events  (cost=1065.81..165535223.46 rows=494824 width=397) (actual time=0.095..0.095 rows=1 loops=1)
         Filter: ((scanned_events.foundcnt = 3) OR (scanned_events.scan_num = 100000))
         Rows Removed by Filter: 2
         ->  WindowAgg  (cost=1065.81..164791126.56 rows=49606460 width=397) (actual time=0.089..0.094 rows=3 loops=1)
               ->  WindowAgg  (cost=1065.81..163550965.06 rows=49606460 width=389) (actual time=0.081..0.083 rows=4 loops=1)
                     ->  Incremental Sort  (cost=1065.81..162434819.71 rows=49606460 width=381) (actual time=0.076..0.076 rows=5 loops=1)
                           Sort Key: events.height DESC, events.block, events.requestkey, events.idx
                           Presorted Key: events.height
                           Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 56kB  Peak Memory: 56kB
                           ->  Index Scan using events_height_chainid_idx on events  (cost=0.56..158424783.98 rows=49606460 width=381) (actual time=0.015..0.035 rows=53 loops=1)
 Planning Time: 0.112 ms
 Execution Time: 0.128 ms
(13 rows)

这个查询的目标是：扫描events表，统计qualname包含'transfer'的行数，找到第3条匹配行或扫描满100000行时立即返回。从执行计划可以看到，实际仅扫描了53行就返回结果，性能符合预期。

但该查询存在严重缺陷：外层SELECT没有ORDER BY子句，理论上PostgreSQL可能以任意顺序返回满足条件的行，无法保证结果的正确性（比如可能返回不是第3个匹配的行）。

添加外层ORDER BY后的性能问题 #

为修复正确性问题，我尝试在外层添加ORDER BY，查询语句如下：

=> EXPLAIN ANALYZE SELECT * 
FROM (
  SELECT *
       , ROW_NUMBER() OVER (ORDER BY height DESC, block, requestkey, idx) as scan_num
       , count(*) FILTER (WHERE qualname ILIKE '%transfer%') OVER
           ( ORDER BY height DESC, block, requestkey, idx 
             ROWS BETWEEN unbounded PRECEDING AND CURRENT ROW
           ) AS foundCnt
  FROM events
  ORDER BY height DESC, block, requestkey, idx 
) as scanned_events
WHERE foundCnt = 3 OR scan_num = 100000
ORDER BY height DESC, block, requestkey, idx 
LIMIT 1
;

对应的执行计划：

QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=16173553.41..16173571.35 rows=1 width=397) (actual time=86703.480..88314.937 rows=1 loops=1)
   ->  Subquery Scan on scanned_events  (cost=16173553.41..25051383.19 rows=494821 width=397) (actual time=86435.692..88047.148 rows=1 loops=1)
         Filter: ((scanned_events.foundcnt = 3) OR (scanned_events.scan_num = 100000))
         Rows Removed by Filter: 2
         ->  WindowAgg  (cost=16173553.41..24307291.63 rows=49606104 width=397) (actual time=86435.682..88047.143 rows=3 loops=1)
               ->  WindowAgg  (cost=16173553.41..23067139.03 rows=49606104 width=389) (actual time=86435.662..88047.120 rows=4 loops=1)
                     ->  Gather Merge  (cost=16173553.41..21951001.69 rows=49606104 width=381) (actual time=86435.630..88047.085 rows=5 loops=1)
                           Workers Planned: 2
                           Workers Launched: 2
                           ->  Sort  (cost=16172553.39..16224226.41 rows=20669210 width=381) (actual time=86147.622..86147.642 rows=106 loops=3)
                                 Sort Key: events.height DESC, events.block, events.requestkey, events.idx
                                 Sort Method: external merge  Disk: 6535240kB
                                 Worker 0:  Sort Method: external merge  Disk: 6503568kB
                                 Worker 1:  Sort Method: external merge  Disk: 6506736kB
                                 ->  Parallel Seq Scan on events  (cost=0.00..2852191.10 rows=20669210 width=381) (actual time=43.151..4135.334 rows=16430767 loops=3)
 Planning Time: 0.353 ms
 JIT:
   Functions: 16
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 3.412 ms, Inlining 105.447 ms, Optimization 204.392 ms, Emission 87.327 ms, Total 400.578 ms
 Execution Time: 89345.338 ms
(21 rows)

此时查询触发了全表扫描+外部排序，执行时间长达近90秒，完全无法接受。我尝试过多种变体，比如将子查询改为CTE、外层按scan_num排序等，但只要外层添加ORDER BY，就会触发全表扫描。

核心疑问 #

是否存在不依赖脆弱语义的实现方式？即如何编写PostgreSQL查询，实现有限行数扫描，且满足窗口函数相关条件时立即返回，同时保证结果正确性。

评论回复 #

@nbk建议添加height DESC, block, requestkey, idx索引（即查询所需的精确排序索引）。尽管我不想添加该索引（第一个查询性能已符合预期，无需额外索引），但仍进行了尝试，不过并未改变第二个查询的执行计划，它依然不使用任何索引，仅让第一个查询略有提速。

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