PostgreSQL无匹配数据时误用慢索引导致查询极慢问题排查

本质是PostgreSQL优化器的代价估算偏差，选错了执行索引：

• 对于where id = ? order by datetime desc limit 1这类查询，有两个可选执行路径：
  1. 走(id, datetime DESC)复合索引：先通过索引快速定位到目标id对应的索引区间，直接取第一条记录就是结果，不管该id的数据量多大、最新数据时间多早，执行时间都是毫秒级，id=10的查询就是走的这个路径，0.3ms完成。
  2. 走datetime单列索引倒序扫描：从全局最新时间点开始往历史方向扫，每读一条就判断id是否匹配，找到第一条符合条件的就返回。这个路径的执行速度完全取决于目标id的最新数据离当前时间的距离：如果设备近期一直在上报数据，扫几条就能命中，速度很快；如果设备很久没有上报，就要扫过这段时间所有其他设备的上千万条数据再过滤，id=11的查询就是这种情况，扫了1030万条不匹配的数据才找到结果，耗时94秒。
• 优化器选错路径的核心原因：默认单列统计信息只会记录每个id的总行数、datetime列的整体分布，不会记录「每个id对应的最新datetime在全局时间轴上的位置」这种关联极值信息。优化器会按平均值估算：按时间倒序扫的话，平均每扫N条（N=总表行数/目标id行数）就能命中，算出来的代价比走复合索引低，但实际id=11这类久未上报的设备，需要扫描的行数远高于估算值，最终选了极差的执行计划。
• 你提到的扩展统计信息不适用这个场景：扩展统计信息主要解决多列值的相关性、函数依赖等问题，没法修正这种跨列极值分布的估算偏差。

按落地成本从低到高选择即可：

• 零成本改SQL，彻底规避选错索引的问题：在order by里加上id字段，和现有复合索引的排序键完全对齐，优化器就没有选错的余地。修改后的SQL和原逻辑完全等价，因为where条件已经固定id为常量，额外加id排序不会改变结果顺序：

select *
from openiot.medidas_analizadores ma
where id = 10
order by id, datetime desc
limit 1;

这个写法下，优化器只会选择走(id, datetime DESC)复合索引，不会再选单列时间索引，不管统计信息怎么算都能稳定在毫秒级返回。

• 如果不方便修改业务SQL，可以安装pg_hint_plan扩展，给这类查询加强制索引提示，指定走medidas_analizadores_id_idx索引，绕开优化器的代价估算。
• 如果你没有「不带id条件、只按datetime过滤/排序」的业务查询，可以直接删掉medidas_analizadores_datetime_idx这个单列索引，从根源上避免优化器选这个错误路径；如果有这类全局时间查询的需求，不要删索引，用前两种方案即可。
• 不建议靠调大统计信息采样率、创建额外索引解决这个问题：你已经有完全适配查询的复合索引，问题出在路径选择，不是索引缺失，调统计信息只能降低选错概率，没法100%避免倾斜数据下的误判。

内容的提问来源于stack exchange，提问作者José D.