PostgreSQL仅索引查询中共享缓冲区读取与堆扫描异常排查
2026-7-6
PostgreSQL索引仅扫描异常问题
环境信息
- PostgreSQL版本：13.10（AWS RDS）
仅索引查询语句
explain (analyse, verbose, buffers) SELECT block_timestamp FROM archive_tokentransfer WHERE ( from_addr = 72387 AND block_timestamp >= 0 ) order by block_timestamp LIMIT 10000
查询执行计划
"QUERY PLAN" "Limit (cost=0.71..4279.42 rows=10000 width=8) (actual time=0.483..60.586 rows=10000 loops=1)" " Output: block_timestamp" " Buffers: shared hit=4909 read=1659 written=200" " I/O Timings: read=37.730 write=13.824" " -> Index Only Scan Backward using token_transfer_from_addr on public.archive_tokentransfer (cost=0.71..245647.39 rows=574114 width=8) (actual time=0.482..59.544 rows=10000 loops=1)" " Output: block_timestamp" " Index Cond: ((archive_tokentransfer.from_addr = 72387) AND (archive_tokentransfer.block_timestamp >= 0))" " Heap Fetches: 1145" " Buffers: shared hit=4909 read=1659 written=200" " I/O Timings: read=37.730 write=13.824" "Planning:" " Buffers: shared hit=235 read=7" " I/O Timings: read=0.036" "Planning Time: 29.771 ms" "Execution Time: 61.209 ms"
最近VACUUM信息
[ { "schemaname": "public", "relname": "archive_tokentransfer", "last_vacuum": null, "last_autovacuum": "2023-10-19 02:51:55.087541+00", "last_analyze": null, "last_autoanalyze": "2023-11-06 18:53:41.891353+00" } ]
问题描述
针对from_addr = 72387的仅索引查询，获取10000行数据却需要遍历约6000个缓冲区，远超估算值：
- 索引结构包含3个4字节整数、2个8字节大整数，单行长约28字节加少量开销，按索引总大小计算平均每行约50字节；PostgreSQL每页8KB，半满情况下每页可存约100行，10000行仅需约100页。
- 表更新操作极少，但堆扫描（Heap Fetches）数约1000，数值偏高。
已尝试操作
- 执行
reindex concurrently重建索引：索引大小从1200GB降至850GB，但缓冲区读取量和堆扫描数无变化。 - 测试其他
from_addr值：即使对应行数超过10000，缓冲区读取仅约100，堆扫描数为0，无异常。 - 删除并重新插入相关行：缓冲区命中数从6000降至3500，但堆扫描数从975升至10001。
表结构
devapp=> \d+ archive_tokentransfer; Table "public.archive_tokentransfer" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------------+------------------+-----------+----------+---------------------------------------------------+----------+--------------+------------- id | bigint | | not null | nextval('archive_tokentransfer_id_seq'::regclass) | plain | | block_timestamp | bigint | | not null | | plain | | value | double precision | | not null | | plain | | value_usd | double precision | | | | plain | | block_number | integer | | not null | | plain | | tx_from_address | integer | | not null | | plain | | tx_to_address | integer | | not null | | plain | | emitting_contract | integer | | not null | | plain | | from_addr | integer | | not null | | plain | | to_addr | integer | | not null | | plain | | chain_id | smallint | | not null | | plain | | tx_idx | smallint | | not null | | plain | | type | smallint | | not null | | plain | | pricing_strategy | smallint | | not null | | plain | | token_id | bytea | | | | extended | | Indexes: "archive_tokentransfer_pkey" PRIMARY KEY, btree (id) "block_data_idx" btree (chain_id, block_number, tx_idx) "token_transfer_from_addr" btree (from_addr, block_timestamp DESC) INCLUDE (to_addr, emitting_contract) "token_transfer_nft_transfers" btree (emitting_contract, token_id, block_timestamp DESC) INCLUDE (from_addr, to_addr, value) WHERE token_id IS NOT NULL "token_transfer_participants" btree (emitting_contract, block_timestamp DESC) INCLUDE (from_addr, to_addr) "token_transfer_to_addr" btree (to_addr, block_timestamp DESC) INCLUDE (from_addr, emitting_contract) "token_transfer_value_usd" btree (block_timestamp, value_usd DESC) INCLUDE (from_addr, to_addr, emitting_contract) WHERE value_usd IS NOT NULL Check constraints: "archive_tokentransfer_chain_id_check" CHECK (chain_id >= 0) "archive_tokentransfer_pricing_strategy_check" CHECK (pricing_strategy >= 0) "archive_tokentransfer_tx_idx_check" CHECK (tx_idx >= 0) "archive_tokentransfer_type_check" CHECK (type >= 0) "no_cross_chain_data_bt" CHECK (NOT chain_id = 0) "one_fungibletoken_only_bt" CHECK (NOT (token_id IS NOT NULL AND type = 4)) "only_allow_token_address_types_bt" CHECK (type = ANY (ARRAY[4, 5, 6])) Access method: heap
问题分析与解决方案
异常原因
索引数据分布极度分散
重建索引虽降低了总大小，但from_addr=72387的索引条目可能因初始插入顺序混乱、多次删除/插入操作，导致分散在大量索引页中，远超出正常的页密度，因此需要读取更多缓冲区。可见性映射未及时更新
仅索引扫描依赖visibility map（可见性映射）判断数据是否可见，若映射未标记对应堆页为全可见，PostgreSQL必须去堆中验证数据可见性，从而产生Heap Fetches：- 自动VACUUM距今时间过久，该范围堆页的可见性映射未更新；
- 删除重插操作产生大量新堆元组，旧元组未被清理，导致可见性映射无法标记为全可见，反而加剧堆扫描。
解决步骤
手动执行VACUUM ANALYZE
强制更新可见性映射并收集最新统计信息，建议在低峰期操作：VACUUM ANALYZE public.archive_tokentransfer;创建部分索引优化目标查询
针对from_addr=72387创建专属部分索引，大幅提升查询效率：CREATE INDEX token_transfer_from_addr_72387 ON public.archive_tokentransfer (block_timestamp DESC) INCLUDE (to_addr, emitting_contract) WHERE from_addr = 72387;检查索引分布与空闲空间
验证索引碎片化情况：-- 查看索引扫描统计 SELECT idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE indexname = 'token_transfer_from_addr'; -- 查看索引页空闲空间 SELECT * FROM pg_freespace('token_transfer_from_addr') LIMIT 20;调整自动VACUUM参数
降低自动VACUUM的触发阈值，确保可见性映射及时更新：ALTER TABLE public.archive_tokentransfer SET (autovacuum_vacuum_scale_factor = 0.01);重新验证执行计划
完成上述操作后，重新运行EXPLAIN ANALYZE查询，观察缓冲区读取量和Heap Fetches是否下降。
内容的提问来源于stack exchange，提问作者bharath
相关产品推荐
相关产品推荐
