PostgreSQL仅800行表简单SELECT查询耗时超60秒问题求助

问题原因分析 #

EXPLAIN ANALYZE 结果显示数据库本身执行该查询仅耗时0.18ms，说明数据库内核执行逻辑无性能问题，耗时全部产生在查询执行后的后续环节，常见原因如下：

• 大字段传输/客户端处理耗时：该表包含xml、response_xml、final_xml三个text类型的大字段，即便只有800行数据，如果单条记录的XML内容体积达到数MB级别，总返回数据量可能达到GB级。EXPLAIN ANALYZE统计的仅为数据库端执行查询的耗时，不会统计数据网络传输、客户端接收渲染（比如部分数据库工具会自动格式化XML、展开大字段内容）的时间，这是最常见的原因。
  验证方式：执行SELECT sum(length(xml) + length(coalesce(response_xml,'')) + length(coalesce(final_xml,''))) total_size FROM eis_transactions; 计算总返回数据体积，若体积超过100MB，传输和处理耗时会显著上升；也可以直接只查询非大字段SELECT id, operation_type, created FROM eis_transactions LIMIT 1000，如果该查询耗时极短即可确认是大字段导致的问题。
• 锁等待：存在未提交的长事务持有该表的排他锁、或大量行锁，导致你的查询需要等待锁释放才能返回结果。EXPLAIN ANALYZE测试时可能刚好锁被释放，所以执行速度快。
  验证方式：执行以下SQL查看是否有针对该表的未释放锁：

  SELECT * FROM pg_locks WHERE relation = 'public.eis_transactions'::regclass;
  

  同时可查询pg_stat_activity视图查看是否存在运行时间超过60秒、操作该表的事务。
• 表异常膨胀：如果该表历史上有过频繁的删除、更新操作，且未及时执行VACUUM，会导致存在大量死元组，表实际占用的磁盘空间远大于800行数据应有的体积。不过从EXPLAIN ANALYZE结果看仅扫描了15个共享缓存页（总大小120KB），该可能性较低。
  修复方式：执行VACUUM FULL ANALYZE public.eis_transactions; 清理死元组并更新表统计信息。
• 冗余索引导致的隐性开销：该表在主键id外额外创建了3个重复的id字段索引，属于完全无用的冗余索引，虽然不会直接影响SELECT查询的执行速度，但如果存在未完成的索引维护操作可能会产生锁等待。建议删除冗余索引，仅保留主键约束自带的id索引即可。
• 客户端额外逻辑开销：部分数据库可视化工具会自动开启外键关联查询、大字段异步加载、结果自动格式化等特性，拿到查询结果后会额外执行其他关联查询、数据格式化操作，导致感知到的耗时变长。可以切换到psql命令行工具执行相同查询，若耗时恢复正常即可确认是客户端问题。

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