明明有索引，limit 1 反而慢了两万倍

NickYoung

修改于 2026-07-16 21:33:03

1230

又回归手搓文章了。本篇回顾一个 limit 算子的经典问题——数据均匀分布假设导致 cost 预估偏差，进而让 SQL 执行效率断崖式下跌。之前写过一篇类似案例，最近又踩到，正好借这次机会做一些更深入的补充。

一、问题现象：颠覆认知的 limit

limit 1 走了性能更差的 SeqScan，而不加 limit 或 limit 更大时反而走 IndexScan，两者性能差距巨大。

limit 1 → SeqScan，耗时 1515.691 ms

postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
postgres-# SELECT c1 FROM t_demo WHERE c2 = 0 AND c3 = false AND c4 = 0limit 1;
                                     QUERY PLAN
----------------------------------------------------------------------------------
 Limit  (cost=0.00..0.26 rows=1 width=21) (actual time=1515.663..1515.665 rows=0.00 loops=1)
   Output: c1
   Buffers: shared hit=16031 read=79558
   ->  Seq Scan on public.t_demo  (cost=0.00..290590.56 rows=1099337 width=21) (actual time=1515.660..1515.661 rows=0.00 loops=1)
         Output: c1
         Filter: ((NOT t_demo.c3) AND (t_demo.c2 = 0) AND (t_demo.c4 = 0))
 Rows Removed by Filter: 13000000
         Buffers: shared hit=16031 read=79558
 Planning Time: 0.170 ms
 Execution Time: 1515.691 ms
(10 rows)

limit 3 或不加 limit → IndexScan，耗时 0.1 ms

postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
postgres-# SELECT c1 FROM t_demo WHERE c2 = 0 AND c3 = false AND c4 = 0limit 3;
                                     QUERY PLAN
----------------------------------------------------------------------------------
Limit  (cost=0.56..0.65 rows=3 width=21) (actual time=0.041..0.042 rows=0.00 loops=1)
   Output: c1
   Buffers: shared hit=4
   ->  Index Only Scan using idx_demo on public.t_demo  (cost=0.56..32078.14 rows=1099337 width=21) (actual time=0.039..0.040 rows=0.00 loops=1)
         Output: c1
         Index Cond: ((t_demo.c2 = 0) AND (t_demo.c3 = false) AND (t_demo.c4 = 0))
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=4
 Planning Time: 0.157 ms
 Execution Time: 0.084 ms
(11 rows)

postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
postgres-# SELECT c1 FROM t_demo WHERE c2 = 0 AND c3 = false AND c4 = 0 ;
                                 QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using idx_demo on public.t_demo  (cost=0.56..32078.14 rows=1099337 width=21) (actual time=0.036..0.037 rows=0.00 loops=1)
   Output: c1
   Index Cond: ((t_demo.c2 = 0) AND (t_demo.c3 = false) AND (t_demo.c4 = 0))
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=4
 Planning Time: 0.139 ms
 Execution Time: 0.061 ms
(8 rows)

当 set enable_seqscan to off 后，limit 1 也能走回索引，耗时同样是 0.1ms 级：

postgres=# set enable_seqscan to off;
SET
postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
postgres-# SELECT c1 FROM t_demo WHERE c2 = 0 AND c3 = false AND c4 = 0limit 1;
                                     QUERY PLAN
----------------------------------------------------------------------------------
Limit  (cost=0.56..0.59 rows=1 width=21) (actual time=0.038..0.039 rows=0.00 loops=1)
   Output: c1
   Buffers: shared hit=4
   ->  Index Only Scan using idx_demo on public.t_demo  (cost=0.56..32078.14 rows=1099337 width=21) (actual time=0.037..0.037 rows=0.00 loops=1)
         Output: c1
         Index Cond: ((t_demo.c2 = 0) AND (t_demo.c3 = false) AND (t_demo.c4 = 0))
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=4
 Planning Time: 0.146 ms
 Execution Time: 0.062 ms
(11 rows)

why？ 默认放着索引不走，是 BUG 吗？看过之前文章的朋友，对背后的逻辑应该已经很熟悉了。

二、问题分析：为什么 limit 1 选了 SeqScan

在之前的一系列文章里，我给大家介绍过 PG 优化器进行路径比较的逻辑，核心就是比较 startup_cost 和 total_cost。整个选路分三个阶段：

① add_path 时的路径剪枝 生成每个节点路径时调用 compare_path_costs_fuzzily 做模糊比较：仅当某条路径的 startup 和 total 都比另一条小 1% 及以上，才直接淘汰另一条。

② set_cheapest 时的精确比较 add_path 之后，用 compare_path_costs 做精确比较，角逐出 cheapest_startup_cost 和 cheapest_total_cost。

③ get_cheapest_fractional_path 取 best_path 非游标查询 tuple_fraction 恒为 0，直接返回 cheapest_total_path 作为 best_path；游标查询则遍历 pathlist 调用 compare_fractional_path_costs 插值比较。

这条 SQL 很简单，执行计划也只有两个节点：基表的 ScanMethod 和 Limit。我们把两个阶段拆开看。

先比较基表 ScanMethod

SeqScan：startup_cost = 0，total_cost = 290590.56 IndexScan：startup_cost = 0.56，total_cost = 32078.14

add_path 模糊比较：0 < 0.56×1.01，但 290590.56 > 32078.14×1.01，两条路径互相淘汰不掉，都保留。 set_cheapest 精确比较：cheapest_startup = 0（SeqScan），cheapest_total = 32078.14（IndexScan）。

再比较 Limit 节点

SeqScan 对应的 Limit：startup = 0，total = 0.26 IndexScan 对应的 Limit：startup = 0.56，total = 0.59

add_path 模糊比较：0 < 0.56×1.01 且 0.26 < 0.59，SeqScan 这条路径全面更优，于是在 add_path 环节 IndexScan 的 Limit 路径就被淘汰下线了。此时 cheapest_startup = 0，cheapest_total = 0.26。

由于是非游标查询，tuple_fraction = 0，因此在 get_cheapest_fractional_path 中直接取 best_path = cheapest_total_path = 0.26，也就是 SeqScan 对应的路径。谜底揭开：不是优化器犯傻，是它算出来 SeqScan 的 Limit 成本更低。

这个 0.26 是怎么来的？

Limit 节点的 total_cost 计算公式（adjust_limit_rows_costs）：

*total_cost = *startup_cost +
    (input_total_cost - input_startup_cost)
        * count_rows / input_rows;
SeqScan 子路径 startup=0、total=290590.56、input_rows=1099337，limit 1 即 count_rows=1：
total_cost = 0 + (290590.56 − 0) × 1 / 1099337
          = 290590.56 / 1099337
          ≈ 0.2643  → EXPLAIN 显示 0.26 ✓

还是老问题——符合条件的 rows 被预估成 1099337，实际却是 0 行。是统计信息不准吗？

三、1099337 究竟是怎么算出来的

先看表结构与统计信息。索引是 idx_demo btree (c2, c3, c4, c1)：

postgres=# \d+ t_demo
                              Table "public.t_demo"
 Column |        Type         | Nullable | Storage  | Stats target
--------+-----------------------+----------+----------+--------------
 c1     | character varying(20) | not null | extended |
 c2     | integer            | not null | plain    |
 c3     | boolean            | not null | plain    |
 c4     | smallint          | not null | plain    |
Indexes:
    "idx_demo" btree (c2, c3, c4, c1)
Not-null constraints:
    "t_demo_c1_not_null" NOT NULL "c1"
    "t_demo_c2_not_null" NOT NULL "c2"
    "t_demo_c3_not_null" NOT NULL "c3"
    "t_demo_c4_not_null" NOT NULL "c4"
Access method: heap
postgres=# SELECT attname, avg_width, n_distinct, null_frac, most_common_vals, most_common_freqs
postgres-#  FROM pg_stats WHERE tablename='t_demo' AND attname IN ('c1','c2','c3','c4');
 attname | avg_width | n_distinct | null_frac | most_common_vals |       most_common_freqs
---------+-----------+------------+-----------+------------------+--------------------------------
 c2     |        4 |          2 |         0 | {1,0}         | {0.50296664,0.49703333}
 c3     |        1 |          2 |         0 | {f,t}         | {0.50296664,0.49703333}
 c4     |        2 |          3 |         0 | {0,1,2}      | {0.33826667,0.3329,0.32883334}
 c1     |      21 |        -1 |         0 |               |
(4 rows)

postgres=# SELECT relname, relpages, reltuples FROM pg_class WHERE relname IN ('t_demo','idx_demo');
 relname  | relpages |   reltuples
----------+----------+---------------
 t_demo   |    95589 | 1.3000104e+07
 idx_demo |    78929 | 1.3000104e+07
(2 rows)
走顺序扫描时，行数估算在 clauselist_selectivity() 里完成。无扩展统计时，它按独立性假设把各子句选择性直接连乘。每个等值子句走 eqsel，命中 MCV 时选择性就等于对应的 most_common_freqs：

/* clauselist_selectivity_ext 核心循环 */
Selectivity s1 = 1.0;
foreach(l, clauses)
{
    s2 = clause_selectivity_ext(root, clause, ...);
    s1 = s1 * s2;   /* ← 就是这行：直接相乘 */
}
return s1;
s(c2=0)    = 0.49703333
s(c3=false) = 0.50296664
s(c4=0)    = 0.33826667

S = 0.49703333 × 0.50296664 × 0.33826667 = 0.0845641 rows = clamp_row_est(S * tuples)= rint(0.0845641 × 13000104) = 1099337

问题的根子就在 s1 = s1 * s2 这一行乘法。而这行乘法在概率论上成立的唯一前提，就是各条件相互独立。所以 0.0845641 隐含了一个假设：c2、c3、c4 的取值互不相关。

但实际上这三列高度相关——组合 (c2=0, c3=false, c4=0) 实际匹配 0 行（Rows Removed by Filter: 13000000，actual rows=0.00）。连乘的 8.4% 与真实的 0%，偏差达百万倍。

所以结论是：统计信息不是不准，而是缺失了几列之间的相关性。那么，加个多列扩展统计信息是不是就能解决？除了 set enable_seqscan to off 之外还有别的手段吗？既然执行计划里明确写着 Rows Removed by Filter: 13000000, actual rows=0.00，那创建一个条件索引来消除 Filter 应该也可行。

四、解决方案

方案一：创建多列扩展统计信息

让优化器识别列间相关性，预估行数不再离谱，直接走回现有索引，执行降到 ms 级：

postgres=# CREATE STATISTICS stat_demo_c234 (mcv, ndistinct) ON c2, c3, c4 FROM t_demo;
CREATE STATISTICS
postgres=# analyze t_demo;
ANALYZE
postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
postgres-# SELECT c1 FROM t_demo WHERE c2 = 0 AND c3 = false AND c4 = 0limit 1;
                                     QUERY PLAN
----------------------------------------------------------------------------------
Limit  (cost=0.56..4.58 rows=1 width=21) (actual time=0.017..0.017 rows=0.00 loops=1)
   Output: c1
   Buffers: shared hit=4
   ->  Index Only Scan using idx_demo on public.t_demo  (cost=0.56..4.58 rows=1 width=21) (actual time=0.015..0.016 rows=0.00 loops=1)
         Output: c1
         Index Cond: ((t_demo.c2 = 0) AND (t_demo.c3 = false) AND (t_demo.c4 = 0))
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=4
 Planning:
   Buffers: shared hit=19
 Planning Time: 0.297 ms
 Execution Time: 0.037 ms
(13 rows)

方案二：创建条件索引

直接把过滤条件固化进索引定义，走该索引消除 Filter，执行同样降到 ms 级：

postgres=# CREATE INDEX idx_demo_partial ON t_demo (c1) WHERE c2=0 AND c3=false AND c4=0;
CREATE INDEX
postgres=# analyze t_demo;
ANALYZE
postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
postgres-# SELECT c1 FROM t_demo WHERE c2 = 0 AND c3 = false AND c4 = 0limit 1;
                                     QUERY PLAN
----------------------------------------------------------------------------------
Limit  (cost=0.12..0.14 rows=1 width=21) (actual time=0.004..0.005 rows=0.00 loops=1)
   Output: c1
   Buffers: shared hit=1
   ->  Index Only Scan using idx_demo_partial on public.t_demo  (cost=0.12..10867.80 rows=1086367 width=21) (actual time=0.003..0.004 rows=0.00 loops=1)
         Output: c1
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=1
 Planning:
   Buffers: shared hit=9
 Planning Time: 0.235 ms
 Execution Time: 0.020 ms
(12 rows)