瀚高基础软件股份有限公司-国产基础软件先行者

在数据库系统中，查询往往受限于 I/O 性能，因此优化工作通常聚焦于减少页面读取次数。索引是典型手段之一，但无法解决所有问题。

Postgres 通过在表主存储（heap）及索引中维护多版本行数据，以支持并发查询的一致性。旧版本行在不再需要前仍占用空间，并可被后续复用。这部分额外空间通常称为“膨胀（bloat）”。本文将分析堆表膨胀与索引膨胀对查询性能的影响，以及对应的预防与处理方案。

在 pgMustard 中，相关提示最初称为“膨胀可能性（Bloat Likelihood）”，但实践表明，查询读取数据过多不仅源于膨胀，还与数据局部性有关。例如，若查询所需的多行数据位于同一页面，其读取效率显著高于分散在多个页面的情况。因此，这类问题被统一归纳为“读取效率（Read Efficiency）”。

此类问题较难识别，通常需结合 EXPLAIN ANALYZE 与 pg_stat_statements 中的 buffer 指标进行分析，因此相关讨论相对较少。但在慢查询执行计划中，该问题较为常见。

膨胀（Bloat）

为演示膨胀问题，构建示例表并写入数据：

CREATE TABLE read_efficiency_demo (
   id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   text1 text NOT NULL,
   text2 text NOT NULL,
   text3 text NOT NULL);

INSERT INTO read_efficiency_demo (text1, text2, text3)
   SELECT
      md5(random()::text),
      md5(random()::text),
      md5(random()::text)
   FROM generate_series(1, 1_000_000);

VACUUM ANALYZE read_efficiency_demo;

为避免 autovacuum^[1] 在演示过程中自动清理数据，临时关闭该功能（仅用于实验环境），实例流程图如下：

ALTER TABLE read_efficiency_demo SET (autovacuum_enabled = off);

初始状态下，100 万行数据的空间占用如下：

SELECT pg_size_pretty(pg_relation_size('read_efficiency_demo')) heap_space,
       pg_size_pretty(pg_relation_size('read_efficiency_demo_pkey')) index_space;

heap_space  | 135 MB
index_space | 21 MB

执行全表扫描的基准性能：

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE)
SELECT * FROM read_efficiency_demo;

                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on read_efficiency_demo  (cost=0.00..27242.00 rows=1000000 width=107) (actual time=0.037..47.737 rows=1000000.00 loops=1)
   Buffers: shared hit=17242
 Planning Time: 0.121 ms
 Serialization: time=134.561 ms  output=118165kB  format=text
 Execution Time: 233.598 ms

结果显示：读取约 17242 个 buffer（约 135 MB），总执行时间约 230 ms。

逐行更新数据后，堆表与索引均新增 100 万行数据版本。重复执行更新操作 9 次，堆表与索引空间均扩大约 10 倍：

UPDATE read_efficiency_demo
   SET id = id + 1_000_000;

-- Run the above 9 times

SELECT pg_size_pretty(pg_relation_size('read_efficiency_demo')) heap_space,
       pg_size_pretty(pg_relation_size('read_efficiency_demo_pkey')) index_space;

heap_space  | 1347 MB
index_space | 255 MB

进一步观察索引扫描：

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE)
SELECT * FROM read_efficiency_demo;

                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on read_efficiency_demo  (cost=0.00..267356.17 rows=9814117 width=107) (actual time=78.955..967.435 rows=1000000.00 loops=1)
   Buffers: shared hit=119782 read=49433
   I/O Timings: shared read=643.876
 Planning Time: 5.525 ms
 Serialization: time=106.633 ms  output=118165kB  format=text
 Execution Time: 1116.107 ms

总缓冲区读取量提升近 10 倍，执行时间提升近 5 倍。耗时增加部分源于磁盘或操作系统缓存数据读取，属于数据膨胀后的正常现象。

上述示例仅对表执行顺序扫描，仅读取堆表数据。在分析问题解决方案前，先查看索引扫描示例：

-- Gather stats to help the planner pick an index scan
ANALYZE read_efficiency_demo;

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE)
SELECT text1 FROM read_efficiency_demo where id < 9_001_000;

                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using read_efficiency_demo_pkey on read_efficiency_demo  (cost=0.42..221.08 rows=723 width=33) (actual time=29.460..29.547 rows=999.00 loops=1)
   Index Cond: (id < 9001000)
   Index Searches: 1
   Buffers: shared hit=24623
 Planning:
   Buffers: shared hit=24595
 Planning Time: 74.871 ms
 Serialization: time=0.032 ms  output=38kB  format=text
 Execution Time: 29.657 ms

结果显示：读取 999 行数据需扫描 24623 个页面。

通过并发重建索引可修复索引膨胀，且不锁定表：

REINDEX INDEX CONCURRENTLY read_efficiency_demo_pkey;

再次查看空间占用，索引空间恢复初始值，堆表空间无变化：

SELECT pg_size_pretty(pg_relation_size('read_efficiency_demo')) heap_space,
       pg_size_pretty(pg_relation_size('read_efficiency_demo_pkey')) index_space;

heap_space  | 1347 MB
index_space | 21 MB

再次执行查询：

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE)
SELECT text1 FROM read_efficiency_demo where id < 9_001_000;

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using read_efficiency_demo_pkey on read_efficiency_demo  (cost=0.42..149.08 rows=723 width=33) (actual time=0.023..0.343 rows=999.00 loops=1)
   Index Cond: (id < 9001000)
   Index Searches: 1
   Buffers: shared hit=33
 Planning:
   Buffers: shared hit=5
 Planning Time: 0.216 ms
 Serialization: time=0.114 ms  output=38kB  format=text
 Execution Time: 0.590 ms

索引扫描仅需读取 33 个数据页即可获取相同数据，执行时间显著下降，说明索引膨胀对查询性能影响显著。

完全消除膨胀可使用 VACUUM FULL 或 CLUSTER，但这类操作会施加重锁，甚至阻塞读取。因此，常用扩展（如 pg_repack^[2]、pg_squeeze^[3]）提供在线重组能力。

需要注意：

一定程度的膨胀属于正常现象。
系统在约 2 倍膨胀下仍可能保持健康。
实际环境中常出现严重膨胀，尤其在频繁更新或删除的索引中。

常见原因包括：

长事务阻塞清理进程。
autovacuum 无法及时跟上负载（需调优）。
autovacuum 被关闭（全局或表级）。

数据局部性（Data Locality）

若查询读取页面数异常，并不一定由膨胀引起，也可能源于数据分布不连续，即数据局部性较差。

示例：

CREATE INDEX text1_idx ON read_efficiency_demo (text1);

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE)
SELECT id, text1 FROM read_efficiency_demo
ORDER BY text1 LIMIT 100;

                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..73.97 rows=100 width=41) (actual time=0.031..0.248 rows=100.00 loops=1)
   Buffers: shared hit=103
   ->  Index Scan using text1_idx on read_efficiency_demo  (cost=0.42..733404.53 rows=997277 width=41) (actual time=0.029..0.226 rows=100.00 loops=1)
         Index Searches: 1
         Buffers: shared hit=103
 Planning Time: 0.120 ms
 Serialization: time=0.045 ms  output=5kB  format=text
 Execution Time: 0.340 ms

索引扫描需读取 103 个数据页才能返回 100 行数据，效率低于每页一行的理想状态。原因包括索引与堆表的两步查询流程，以及数据在堆表中的随机分布。

通过 CLUSTER 命令按照 text1 顺序重建全表（生产环境不建议使用），再次执行查询：

CLUSTER read_efficiency_demo USING text1_idx;

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE)
SELECT id, text1 FROM read_efficiency_demo
ORDER BY text1 LIMIT 100;

                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..11.71 rows=100 width=41) (actual time=0.031..0.098 rows=100.00 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using text1_idx on read_efficiency_demo  (cost=0.42..112807.32 rows=1000000 width=41) (actual time=0.029..0.075 rows=100.00 loops=1)
         Index Searches: 1
         Buffers: shared hit=5
 Planning Time: 0.121 ms
 Serialization: time=0.039 ms  output=5kB  format=text
 Execution Time: 0.183 ms

相同查询仅需读取 5 个数据页，执行时间降低约 2 倍。这一结果本质上来源于数据局部性的改善。

数据局部性问题通常表现为读查询性能随时间逐步下降，尤其在高写入负载场景下更为明显。数据初始按插入顺序具备良好的物理聚集性，但随着更新发生，新版本行可能被写入距离原位置较远的页面，导致访问路径变长、I/O 成本增加。

为缓解该问题，PostgreSQL 提供 HOT（Heap-Only Tuple）^[4]更新机制：在未修改索引列且页面存在可用空间的前提下，新版本行可以保留在原页面内，从而维持局部性。基于此，fillfactor^[5] 参数成为关键调优手段，通过控制页面预留空间，提高 HOT 更新命中率。

由于 PostgreSQL 不会自动维护数据物理顺序，CLUSTER 操作又会带来较重锁开销，实践中通常借助 pg_repack 或 pg_squeeze 在低影响下完成数据重组。

在具体优化策略上，可通过批量写入时预排序数据、利用分区限制数据分布范围，以及构建覆盖索引以支持 Index Only Scan，从而减少访问的数据页数量并稳定查询性能。

结论

当某个查询性能随时间逐步下降，且执行计划中的 buffer 数量明显偏高时，通常意味着存在膨胀（bloat）或数据局部性下降的问题。

问题识别：

通过 EXPLAIN ANALYZE 关注 buffer 使用情况。
持续监控堆表与索引的膨胀程度。

问题修复：

使用 pg_repack / pg_squeeze 重组表。
对严重膨胀索引执行并发重建。

预防措施：

确保 autovacuum 正常启用。
避免长事务等阻塞 autovacuum。
调优 autovacuum 执行频率。
定期重建膨胀索引。
维持关键查询的数据局部性。
构建覆盖索引并定期维护。

原文链接：

https://www.pgmustard.com/blog/read-efficiency-issues-in-postgres-queries

作者：Michael Christofides

引用链接

[1]

autovacuum: https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM

[2]

pg_repack: https://github.com/reorg/pg_repack

[3]

pg_squeeze: https://github.com/cybertec-postgresql/pg_squeeze

[4]

HOT（Heap-Only Tuple）: https://www.postgresql.org/docs/current/storage-hot.html

[5]

fillfactor: https://www.postgresql.org/docs/current/sql-createtable.html#RELOPTION-FILLFACTOR

揭开 PostgreSQL 读取效率问题的真相

膨胀（Bloat）

数据局部性（Data Locality）

结论

引用链接