PostgreSQL事务内基于主键的SELECT查询异常缓慢原因排查求助

遇到这种情况确实挺闹心的——明明是基于主键的查询，按说应该瞬间返回，结果在更新另一张表后的同事务里突然变慢，还是两张数据量都很大的表。结合PostgreSQL的特性，我整理了几个最可能的方向，你可以逐一排查：

• 外键约束的隐式操作：先检查下table2和table1之间有没有外键关联，尤其是带ON UPDATE CASCADE/ON UPDATE SET NULL这类触发动作的。当你更新table1的行时，PostgreSQL会自动去table2中匹配关联行执行相应操作，如果table2数据量极大，这个隐式操作可能会产生大量锁或者后台扫描，进而影响同事务后续对table2的查询。可以用\d table2命令查看外键定义。

• 表膨胀与版本链遍历：如果table2存在频繁更新/删除的情况，会产生大量死元组，导致表膨胀。PostgreSQL在查询时需要遍历行的版本链来确定当前事务可见的数据，即使是主键查询，版本链过长也会拖慢速度。你可以用这条语句查看table2的死元组情况：

  SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='table2';
  

  如果死元组数量远大于活元组，建议手动执行VACUUM ANALYZE table2;清理。

• 事务内的查询计划异常：有时候事务内的上下文会让PostgreSQL选择不合适的执行计划，比如之前执行过涉及table2的其他查询，导致统计信息过时或者缓存了错误计划。你可以在事务内执行EXPLAIN ANALYZE select * from table2 where id = 10;，看看是否真的走了主键索引（正常应该显示Index Scan using table2_pkey on table2）。如果走了全表扫描，那大概率是统计信息的问题，执行ANALYZE table2;更新统计信息后再试试。

• 跨事务的锁等待：虽然是同事务内的操作，但如果在你启动事务前，已有其他事务持有了table2中id=10行的排他锁，当前事务的SELECT（默认READ COMMITTED隔离级别）会等待锁释放。可以用这条语句查看table2的锁状态：

  SELECT * FROM pg_locks WHERE relation = 'table2'::regclass;
  

  看看有没有处于等待状态的锁条目。

备注：内容来源于stack exchange，提问作者Sreejith A G