我正在调试一个查询，它返回的记录越多，运行速度就越快，但使用小LIMIT（ie 10）时，返回越少（即〈10行），性能就会严重下降（慢10倍以上）。
示例：
快速查询，100万行中有5个结果-无限制

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

快速查询，100万行中有5个结果：- 上限
一个二个一个一个
查询速度慢：-下限

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);

所以我的问题

• 我应该只考虑强制查询计划器对address（主键）应用WHERE的方法吗？
• 正如您在解释中所看到的，行block_number在慢速查询中被扫描，但我不知道为什么。有人能解释一下吗？
• 这正常吗？似乎数据越多，查询就越困难，而不是像本例中那样相反。

更新

• 抱歉，A，答复的延迟，B，这个问题中的一些不一致之处。
• 我更新了解释，清楚地显示了1000倍的性能下降