PostgreSQL含ORDER BY与LIMIT的无WHERE查询未使用索引问题排查

我完全理解你的困惑——按道理这个场景下索引应该是最优选择，但优化器的决策有时候确实会让人摸不着头脑。给你几个具体的排查方向，一步步来拆解问题：

• 先确认统计信息是否准确
  PostgreSQL的查询优化器严重依赖表的统计信息，如果统计数据过时，它可能会做出错误的成本估算。先执行ANALYZE your_table_name;更新统计信息，然后再跑一次EXPLAIN ANALYZE看看执行计划有没有变化。

• 验证索引的定义是否完全匹配
  虽然你提到建了降序BTREE唯一索引，但最好再确认一下索引的具体结构。在psql里执行\d your_table_name，查看索引的定义是不是CREATE UNIQUE INDEX ... ON your_table USING btree (id DESC);。如果索引是升序的（id ASC），虽然理论上也能反向扫描，但优化器有时候会因为成本估算问题不选择它；另外也要确认索引没有被禁用或者失效。

• 仔细分析执行计划的细节
  跑EXPLAIN ANALYZE SELECT * FROM your_table ORDER BY id DESC LIMIT 10;，重点看这几个点：

  • 扫描类型是Seq Scan还是Index Scan/Index Only Scan？
  • 优化器给出的成本估算（cost=后面的数值），对比索引扫描和全表扫描的成本差异。如果它认为全表扫描的总成本更低，那肯定会优先选全表。

• 考虑回表成本的影响
  你的查询是SELECT *，而索引里只有id列，所以用索引扫描的话，需要通过id回表去获取其他列的数据（也就是"书签查找"）。如果你的表每行数据很大，回表10次的IO成本加上索引扫描的成本，可能被优化器估算得比全表扫描+排序更高。可以先测试只查id：SELECT id FROM your_table ORDER BY id DESC LIMIT 10;，如果这个查询用了索引，那问题就出在回表成本上。解决办法是建覆盖索引，把你需要的列都包含进去，比如：

  CREATE INDEX idx_table_id_desc_covering ON your_table USING btree (id DESC) INCLUDE (column1, column2, ...);
  

  这样查询可以直接从索引里拿到所有需要的数据，不需要回表，优化器大概率会选择索引扫描。

• 检查配置参数是否影响了优化器选择
  有几个参数会影响优化器的成本判断：

  • enable_indexscan：如果这个参数被设为off，那索引扫描会被禁用，执行SHOW enable_indexscan;确认一下，正常应该是on。
  • random_page_cost和seq_page_cost：这两个参数分别控制随机IO和顺序IO的成本权重。默认random_page_cost=4，seq_page_cost=1，如果你的存储是SSD（随机IO性能很好），可以把random_page_cost降到2甚至1.1，这样优化器会更倾向于选择索引扫描。

• 排查表的膨胀或死元组问题
  如果表存在大量死元组（比如频繁删除/更新但没做VACUUM），表的实际大小会远大于有效数据的大小，这时候全表扫描的成本可能被低估。执行以下查询查看死元组情况：

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

  如果死元组很多，先执行VACUUM ANALYZE your_table_name;清理一下，再测试查询。

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