PostgreSQL不同schema下相同查询性能差异问题排查求助

1 数据量与数据分布差异 #

• 先对比两个schema下同表的总行数，执行SELECT COUNT(*) FROM schema_name.table_name;查询，即使表结构完全一致，数据量差数倍的情况下性能表现必然存在差异
• 验证PostgreSQL统计信息是否准确，优化器完全依赖统计信息生成执行计划，执行ANALYZE VERBOSE schema_name.table_name;查看统计信息的最新更新时间，schema B如果长期未更新统计信息会导致优化器生成错误的执行计划
• 检查死元组占比，执行SELECT n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname = 'table_name' AND schemaname = 'schema_name';，死元组占比过高会导致扫描时需要遍历大量无效数据，即使索引生效也会拖慢查询速度

2 执行计划差异 #

• 分别在两个schema下执行带执行分析的查询语句，比如EXPLAIN ANALYZE SELECT * FROM schema_a.table WHERE 过滤条件;和schema B的对应版本，重点对比以下差异：
  • 是否存在schema B走全表扫描、schema A走索引的情况
  • 相同执行步骤的实际扫描行数、执行耗时是否有数量级差异
  • 是否存在执行计划预估行数和实际行数偏差超过10倍的情况，出现该问题基本可以判定是统计信息失效导致

3 物理存储与索引有效性差异 #

• 即使索引配置完全一致，也需要验证索引是否可用、是否存在膨胀：执行SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE relname = 'table_name' AND schemaname IN ('schema_a','schema_b');查看两个schema下同索引的扫描次数、命中情况，执行pg_indexes_size('schema_name.index_name')查看索引大小，相同数据量下索引大小差很多就说明存在索引膨胀
• 检查表膨胀情况：执行SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(pg_relation_size(relid)) AS table_size FROM pg_stat_user_tables WHERE relname = 'table_name' AND schemaname IN ('schema_a','schema_b');，相同数据量下表大小差很多说明存在表膨胀，会大幅提升查询的IO消耗
• 检查两个schema下的表FILLFACTOR配置是否一致，频繁更新的表如果FILLFACTOR配置不合理会大幅提升存储碎片化程度，影响查询性能

4 运行时与配置差异 #

• 检查是否存在schema/表级别的参数配置差异，比如work_mem、random_page_cost等参数会直接影响优化器的执行计划选择，执行SELECT * FROM pg_db_role_setting;查看是否存在针对不同用户、不同表的参数覆盖配置
• 执行查询时检查schema B的对应表是否存在锁阻塞，执行SELECT * FROM pg_locks WHERE relation = 'schema_b.table_name'::regclass;查看是否有长时间持有的排他锁，导致查询排队等待
• 检查是否schema B存在大量未提交的长写入事务，导致查询需要遍历多版本快照，产生额外开销

• 统计信息失效的情况，对schema B全表执行ANALYZE schema_name.*;更新统计信息，更新后重新验证执行计划是否恢复正常
• 存在死元组或者表/索引膨胀的情况，在业务低峰期执行VACUUM FULL ANALYZE schema_name.table_name;，注意VACUUM FULL会锁表，禁止在业务高峰期执行，日常可以调整自动VACUUM的触发阈值，避免膨胀累积
• 索引失效/膨胀的情况，可以删除重建schema B的对应索引：DROP INDEX schema_b.index_name; CREATE INDEX index_name ON schema_b.table_name(索引字段);
• 参数配置差异的情况，把schema B关联的用户参数、表级参数调整为和schema A一致，比如如果work_mem太小导致排序走磁盘，可以适当调大work_mem参数
• 存在锁阻塞或者长事务的情况，终止对应的无用会话：SELECT pg_terminate_backend(pid);，pid为pg_locks中查询到的阻塞会话id

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