PostgreSQL同版本同数据实例性能差异悬殊，已执行Reindex/Vacuum仍无效

• PostgreSQL版本：v12.11
• 数据表：400万行的my_table
• 慢查询：SELECT COUNT(*) FROM my_table WHERE host='www.example.com';（host字段已创建哈希索引）
• 性能差异：
  • 旧实例：清缓存后首次查询90秒，重复10次（不清缓存）后仍需40秒
  • 新实例：清缓存后首次查询3秒，重复10次后仅1.32毫秒
• 已尝试操作：对表执行REINDEX和VACUUM，无性能提升

1. 对比新旧实例的执行计划 #

在两个实例上分别执行：

EXPLAIN ANALYZE SELECT COUNT(*) FROM my_table WHERE host='www.example.com';

重点确认旧实例是否真的使用了哈希索引——统计信息过时会导致优化器错误选择全表扫描而非索引扫描。如果旧实例显示Seq Scan，这就是核心问题。

2. 更新表的统计信息 #

旧实例的统计信息可能过期，导致优化器判断失误，执行：

ANALYZE VERBOSE my_table;

更新后重新查看执行计划，确认是否切换为索引扫描。

3. 彻底清理表物理碎片（锁表操作，需低峰执行） #

普通VACUUM不会整理表的物理碎片，尝试：

VACUUM FULL my_table;

该操作会重建表的物理存储，消除碎片，之后再测试查询性能。

4. 替换哈希索引为B-tree索引 #

PostgreSQL的哈希索引在多数场景下性能不如B-tree，尤其是当host字段重复值较多时。创建B-tree索引测试：

CREATE INDEX idx_my_table_host_btree ON my_table(host);

可先禁用原哈希索引强制使用新索引：

ALTER INDEX idx_my_table_host_hash DISABLE;

测试确认性能提升后，可删除原哈希索引。

5. 核对数据库核心配置参数 #

虽然硬件配置相同，但PostgreSQL的内存参数可能不一致，直接影响缓存和查询效率。在两个实例上分别执行：

SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

将旧实例的参数调整为与新实例一致，再测试性能。

6. 检查表和索引的膨胀率 #

用以下SQL查看表和索引的膨胀情况：

-- 检查表膨胀
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS data_size,
       round(100 * (pg_total_relation_size(relid) - pg_relation_size(relid)) / pg_total_relation_size(relid)) AS bloat_percent
FROM pg_stat_user_tables WHERE relname = 'my_table';

-- 检查索引膨胀
SELECT idx.relname AS index_name,
       pg_size_pretty(pg_relation_size(idx.oid)) AS index_size,
       round(100 * (pg_relation_size(idx.oid) - pg_indexes_size(idx.relid)) / pg_relation_size(idx.oid)) AS bloat_percent
FROM pg_class idx
JOIN pg_index i ON idx.oid = i.indexrelid
JOIN pg_class tbl ON i.indrelid = tbl.oid
WHERE tbl.relname = 'my_table';

如果膨胀率过高，单独对膨胀严重的索引执行REINDEX INDEX <index_name>，或配合VACUUM FULL解决。

7. 排查磁盘IO瓶颈 #

旧实例的磁盘可能存在IO性能问题，执行pg_test_fsync测试磁盘同步速度，对比新旧实例结果。也可通过系统工具iostat、vmstat查看旧实例的磁盘使用率和IO等待时间，确认是否是磁盘拖慢查询。

8. 检查表的触发器和约束 #

旧表可能存在额外的触发器或约束，导致查询产生额外开销。查看旧表的触发器和约束：

-- 查看触发器
SELECT tgname FROM pg_trigger WHERE tgrelid = 'my_table'::regclass AND NOT tgisinternal;

-- 查看约束
SELECT conname FROM pg_constraint WHERE conrelid = 'my_table'::regclass;

对比新实例的情况，若旧实例有多余的触发器或约束，评估是否可移除或优化。

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