1亿行PostgreSQL表含WHERE及ORDER BY的查询性能优化问询

问题场景 #

有一张1亿+行的transactions表，需查询from或to等于指定地址的最新25条数据。原查询语句如下：

SELECT * 
FROM transactions
WHERE transactions.from = '0xfbde4acae6c489197280635f0fa172148c61838b'
   OR transactions.to = '0xfbde4acae6c489197280635f0fa172148c61838b'
ORDER BY transactions.timestamp DESC 
LIMIT 25;

已为from和to单独创建索引，但当符合条件的行数较多时（如20万+），查询耗时达4-8秒。执行计划显示，数据库需先扫描所有符合条件的行再排序，排序环节成为性能瓶颈。

原查询执行计划 #

"QUERY PLAN"
"Limit  (cost=2174753.21..2174756.12 rows=25 width=324) (actual time=5225.725..5242.938 rows=25 loops=1)"
"  Output: hash, block_hash, block_number, ""from"", ""to"", gas, gas_used, gas_price, nonce, transaction_index, value, contract_address, status, ""timestamp"""
"  Buffers: shared hit=17 read=146499 dirtied=6 written=379"
"  ->  Gather Merge  (cost=2174753.21..2214827.74 rows=343472 width=324) (actual time=5225.723..5242.935 rows=25 loops=1)"
"        Output: hash, block_hash, block_number, ""from"", ""to"", gas, gas_used, gas_price, nonce, transaction_index, value, contract_address, status, ""timestamp"""
"        Workers Planned: 2"
"        Workers Launched: 2"
"        Buffers: shared hit=17 read=146499 dirtied=6 written=379"
"        ->  Sort  (cost=2173753.18..2174182.52 rows=171736 width=324) (actual time=5212.330..5212.332 rows=19 loops=3)"
"              Output: hash, block_hash, block_number, ""from"", ""to"", gas, gas_used, gas_price, nonce, transaction_index, value, contract_address, status, ""timestamp"""
"              Sort Key: transactions.""timestamp"" DESC"
"              Sort Method: top-N heapsort  Memory: 40kB"
"              Buffers: shared hit=17 read=146499 dirtied=6 written=379"
"              Worker 0:  actual time=5205.779..5205.781 rows=25 loops=1"
"                Sort Method: top-N heapsort  Memory: 39kB"
"                Buffers: shared hit=5 read=49090 dirtied=2 written=117"
"              Worker 1:  actual time=5205.776..5205.778 rows=25 loops=1"
"                Sort Method: top-N heapsort  Memory: 42kB"
"                Buffers: shared hit=7 read=49181 dirtied=2 written=134"
"              ->  Parallel Bitmap Heap Scan on public.transactions  (cost=6130.59..2168906.92 rows=171736 width=324) (actual time=33.562..5167.871 rows=131846 loops=3)"
"                    Output: hash, block_hash, block_number, ""from"", ""to"", gas, gas_used, gas_price, nonce, transaction_index, value, contract_address, status, ""timestamp"""
"                    Recheck Cond: ((transactions.""from"" = '0xfbde4acae6c489197280635f0fa172148c61838b'::bpchar) OR (transactions.""to"" = '0xfbde4acae6c489197280635f0fa172148c61838b'::bpchar))"
"                    Rows Removed by Index Recheck: 663904"
"                    Heap Blocks: exact=13510 lossy=33729"
"                    Buffers: shared hit=5 read=146497 dirtied=6 written=379"
"                    Worker 0:  actual time=26.980..5162.413 rows=133634 loops=1"
"                      Buffers: shared read=49088 dirtied=2 written=117"
"                    Worker 1:  actual time=27.062..5162.932 rows=132710 loops=1"
"                      Buffers: shared read=49181 dirtied=2 written=134"
"                    ->  BitmapOr  (cost=6130.59..6130.59 rows=412182 width=0) (actual time=40.743..40.744 rows=0 loops=1)"
"                          Buffers: shared hit=5 read=349"
"                          ->  Bitmap Index Scan on from_idx  (cost=0.00..5840.70 rows=406417 width=0) (actual time=40.487..40.487 rows=401210 loops=1)"
"                                Index Cond: (transactions.""from"" = '0xfbde4acae6c489197280635f0fa172148c61838b'::bpchar)"
"                                Buffers: shared hit=3 read=347"
"                          ->  Bitmap Index Scan on to_idx  (cost=0.00..83.81 rows=5765 width=0) (actual time=0.254..0.254 rows=124 loops=1)"
"                                Index Cond: (transactions.""to"" = '0xfbde4acae6c489197280635f0fa172148c61838b'::bpchar)"
"                                Buffers: shared hit=2 read=2"
"Planning Time: 0.108 ms"
"Execution Time: 5243.004 ms"

从执行计划可见，数据库先通过BitmapOr扫描两个索引获取所有符合条件的行，再全量排序后取前25条，当匹配行数较多时，排序和堆扫描的IO开销极大。

优化方案 #

1. 创建复合索引（核心优化） #

单独的timestamp索引无法解决问题，但包含查询条件与排序字段的复合索引，可让数据库直接从索引中获取已排序的结果，避免全量扫描与排序：

• 针对from字段创建复合索引：

  CREATE INDEX idx_from_timestamp ON transactions ("from", timestamp DESC);
  

• 针对to字段创建复合索引：

  CREATE INDEX idx_to_timestamp ON transactions ("to", timestamp DESC);
  

这两个索引可让数据库快速定位from或to等于指定地址的最新数据，再合并结果取前25条。

2. 拆分查询并合并排序 #

将原查询拆分为两个独立子查询，分别获取from和to匹配的最新25条数据，合并后再排序取最终结果。这种方式可利用上述复合索引，每个子查询仅扫描少量数据，合并后排序开销可忽略：

SELECT * FROM (
    SELECT * FROM transactions 
    WHERE "from" = '0xfbde4acae6c489197280635f0fa172148c61838b'
    ORDER BY timestamp DESC LIMIT 25
    UNION ALL
    SELECT * FROM transactions 
    WHERE "to" = '0xfbde4acae6c489197280635f0fa172148c61838b'
    ORDER BY timestamp DESC LIMIT 25
) AS combined
ORDER BY timestamp DESC LIMIT 25;

3. 表分区（长期优化） #

若表数据持续增长，可按timestamp进行分区（如按天/按月），查询时仅扫描最新的几个分区，减少扫描数据量。结合复合索引使用，性能可进一步提升。

4. 临时调整work_mem参数 #

若排序时出现lossy块或内存不足，可临时调大work_mem（如设为64MB），让排序在内存中完成，避免磁盘排序：

SET work_mem = '64MB';

此为临时缓解方案，优先选择索引优化。

效果验证 #

创建复合索引后，重新执行原查询，执行计划应变为直接使用复合索引的Index Scan，而非Bitmap Heap Scan + Sort，执行时间可降至几百毫秒以内。

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