带小LIMIT的少结果查询比多结果查询慢1000倍的问题排查

我正在调试一个PostgreSQL查询的异常现象：返回结果越多查询速度越快，但使用小LIMIT（如10）返回少量结果（甚至少于10行）时，性能会严重下降（慢1000倍以上）。

1. 无LIMIT的快速查询（返回5条结果） #

SQL语句 #

SELECT *
FROM transaction_internal_by_addresses
WHERE address = 'foo'
ORDER BY block_number desc;

执行计划 #

Sort  (cost=7733.14..7749.31 rows=6468 width=126) (actual time=0.030..0.031 rows=5 loops=1)
"  Output: address, block_number, log_index, transaction_hash"
  Sort Key: transaction_internal_by_addresses.block_number
  Sort Method: quicksort  Memory: 26kB
  Buffers: shared hit=10
  ->  Index Scan using transaction_internal_by_addresses_pkey on public.transaction_internal_by_addresses  (cost=0.69..7323.75 rows=6468 width=126) (actual time=0.018..0.021 rows=5 loops=1)
"        Output: address, block_number, log_index, transaction_hash"
        Index Cond: (transaction_internal_by_addresses.address = 'foo'::text)
        Buffers: shared hit=10
Query Identifier: -8912211611755432198
Planning Time: 0.051 ms
Execution Time: 0.041 ms

2. 高LIMIT的快速查询（返回5条结果） #

SQL语句 #

SELECT *
FROM transaction_internal_by_addresses
WHERE address = 'foo'
ORDER BY block_number desc
LIMIT 100;

执行计划 #

Limit  (cost=7570.95..7571.20 rows=100 width=126) (actual time=0.024..0.025 rows=5 loops=1)
"  Output: address, block_number, log_index, transaction_hash"
  Buffers: shared hit=10
  ->  Sort  (cost=7570.95..7587.12 rows=6468 width=126) (actual time=0.023..0.024 rows=5 loops=1)
"        Output: address, block_number, log_index, transaction_hash"
        Sort Key: transaction_internal_by_addresses.block_number DESC
        Sort Method: quicksort  Memory: 26kB
        Buffers: shared hit=10
        ->  Index Scan using transaction_internal_by_addresses_pkey on public.transaction_internal_by_addresses  (cost=0.69..7323.75 rows=6468 width=126) (actual time=0.016..0.020 rows=5 loops=1)
"              Output: address, block_number, log_index, transaction_hash"
              Index Cond: (transaction_internal_by_addresses.address = 'foo'::text)
              Buffers: shared hit=10
Query Identifier: 3421253327669991203
Planning Time: 0.042 ms
Execution Time: 0.034 ms

3. 低LIMIT的慢速查询（返回0条结果） #

SQL语句 #

SELECT *
FROM transaction_internal_by_addresses
WHERE address = 'foo'
ORDER BY block_number desc
LIMIT 10;

执行计划 #

Limit  (cost=1000.63..6133.94 rows=10 width=126) (actual time=10277.845..11861.269 rows=0 loops=1)
"  Output: address, block_number, log_index, transaction_hash"
  Buffers: shared hit=56313576
  ->  Gather Merge  (cost=1000.63..3333036.90 rows=6491 width=126) (actual time=10277.844..11861.266 rows=0 loops=1)
"        Output: address, block_number, log_index, transaction_hash"
        Workers Planned: 4
        Workers Launched: 4
        Buffers: shared hit=56313576
        ->  Parallel Index Scan Backward using transaction_internal_by_address_idx_block_number on public.transaction_internal_by_addresses  (cost=0.57..3331263.70 rows=1623 width=126) (actual time=10256.995..10256.995 rows=0 loops=5)
"              Output: address, block_number, log_index, transaction_hash"
              Filter: (transaction_internal_by_addresses.address = 'foo'::text)
              Rows Removed by Filter: 18485480
              Buffers: shared hit=56313576
              Worker 0:  actual time=10251.822..10251.823 rows=0 loops=1
                Buffers: shared hit=11387166
              Worker 1:  actual time=10250.971..10250.972 rows=0 loops=1
                Buffers: shared hit=10215941
              Worker 2:  actual time=10252.269..10252.269 rows=0 loops=1
                Buffers: shared hit=10191990
              Worker 3:  actual time=10252.513..10252.514 rows=0 loops=1
                Buffers: shared hit=10238279
Query Identifier: 2050754902087402293
Planning Time: 0.081 ms
Execution Time: 11861.297 ms

表结构DDL #

create table transaction_internal_by_addresses
(
    address          text   not null,
    block_number     bigint,
    log_index        bigint not null,
    transaction_hash text   not null,
    primary key (address, log_index, transaction_hash)
);

alter table transaction_internal_by_addresses
    owner to "icon-worker";

create index transaction_internal_by_address_idx_block_number
    on transaction_internal_by_addresses (block_number);

问题解答 #

1. 是否应强制查询优化器优先基于address（主键）应用WHERE条件？ #

是的，这是解决当前性能问题的核心手段，有两种可行方案：

• 索引提示：直接在查询中指定使用主键索引，强制优化器走正确路径，示例：

  SELECT *
  FROM transaction_internal_by_addresses INDEX(transaction_internal_by_addresses_pkey)
  WHERE address = 'foo'
  ORDER BY block_number desc
  LIMIT 10;
  

• 创建复合索引：添加(address, block_number DESC)的复合索引，让优化器可以直接通过该索引完成过滤+排序，无需额外操作，这是更持久的解决方案。

2. 慢速查询的执行计划中扫描了block_number，请问原因是什么？ #

PostgreSQL优化器的成本计算出现了偏差：它认为小LIMIT场景下，通过transaction_internal_by_address_idx_block_number索引倒序扫描（从最大的block_number开始），能快速找到10条符合address='foo'的记录。但实际数据分布中，该address对应的记录极少（甚至没有），导致优化器需要扫描数百万条不符合条件的行才能完成过滤，最终引发性能灾难。

这种错误通常源于统计信息不准确，或者优化器对小LIMIT+稀疏数据场景的成本预估偏差。

3. 这种结果越多查询越快的现象是否正常？通常应该是数据越多查询越难。 #

这完全不正常，属于查询优化器执行计划选择错误导致的异常。

在无LIMIT或高LIMIT场景下，优化器选择了最优路径：先通过主键索引过滤出所有address='foo'的行（仅5行），再排序返回，成本极低。但小LIMIT时，优化器错误选择了基于block_number的索引扫描路径，由于目标address的记录稀疏，反而需要扫描大量无效数据，导致性能暴跌。

本质是优化器的成本模型在特定场景下失效，没有正确评估两种执行路径的实际成本。

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