PostgreSQL带WHERE子句查询极慢的排查与调试方法

• 统计信息缺失/失真：数据库还原完成后默认不会自动收集全量统计信息，优化器没有准确的行分布、页占比、数据离散度数据时，大概率不会选择索引扫描，甚至会生成完全错误的执行计划。你提到库内有大量自定义数据类型，这类类型的统计信息收集规则如果未正确加载，失真问题会比内置类型更严重。
• 自定义类型依赖缺失/版本不匹配：Hedera主节点的库依赖大量自定义类型、对应比较操作符和操作符类，如果还原时漏装对应扩展、自定义类型的动态链接库版本和转储源版本不一致，会出现两种问题：一是where条件比较时触发隐式类型转换，导致索引完全失效；二是索引本身绑定的操作符不可用，数据库直接跳过索引选择全表扫描。
• 索引有效性标记异常：还原过程中如果出现过磁盘空间不足、约束冲突、事务中断，索引对象会留在系统表里但被标记为无效（pg_index.indisvalid = false），这类索引看起来存在，但数据库不会在查询时调用，不属于物理损坏，不需要全库重建。
• 可见性映射与空间配置问题：如果还原用的是非一致性快照、还原过程异常中断，可能导致表的可见性映射(VM)损坏、存在大量未清理的死元组，即便查询走了索引，回表时也要逐行校验元组可见性，耗时会陡增。另外如果还原后用的是数据库默认配置，shared_buffers、work_mem参数过小，索引扫描需要的随机IO无法命中内存，会反复刷盘，而无过滤的limit 100只需要顺序扫描表的前几个数据页，不需要加载索引也能快速返回，和你目前观察到的现象完全吻合。
• 事务ID冻结状态异常：如果转储的源库事务年龄接近冻结阈值，还原后autovacuum会自动触发全表冻结扫描，会占用大量IO资源，导致带过滤的查询响应变慢。

• 第一步先抓取最基础的执行计划定位问题，不要直接做全库级操作。选一个主键列的等值查询，执行explain (analyze, buffers, verbose) select * from <问题表名> where <主键列> = <表内实际存在的一个值>;

  重点关注三个信息：一是执行计划有没有选择索引扫描，有没有在索引列上出现隐式类型转换；二是shared buffer的命中率，如果低于99%基本是内存配置或者存储IO问题；三是actual time里IO等待的占比，如果等待时间占总时间90%以上，先排查存储性能。

• 第二步先补全统计信息，这是还原后数据库性能异常的最高频原因。执行vacuumdb -az --analyze-in-stages，该命令会分三个阶段从粗粒度到细粒度收集全库统计信息，对刚还原的库执行速度很快，绝大多数场景下跑完这一步查询性能就会恢复正常，不需要做重建索引操作。
• 第三步校验所有数据库对象的有效性。通过\dT+列出所有自定义类型，检查对应依赖的比较函数、操作符、操作符类是否存在且有效，没有被标记为invalid；查询pg_index系统表确认所有常用索引的indisvalid字段为true。
• 第四步做小范围的完整性校验，不要上来就全库reindex。先对问题表的主键索引执行单索引重建测试，观察重建后查询速度是否恢复；如果实例开启了数据校验，可对问题所在的表空间跑pg_verify_checksums排查物理页损坏。
• 第五步排查存储性能瓶颈：索引查询以随机IO为主，无过滤的limit查询以顺序读前几页为主，两者对存储性能的要求差异极大。可以用iostat等工具观测存储的随机读延迟，如果延迟超过10ms，说明是存储性能不足导致的慢查询，和数据库索引本身无关。

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