PostgreSQL查询未命中预期复合索引的解决方法

你遇到的是典型的PostgreSQL优化器成本估算偏差问题：

• 优化器当前基于平均数据分布估算，认为符合order_type_id条件的记录占比很高，走ix_orders_created_at单列索引从最新时间倒序扫描，很快就能凑够50条符合条件的记录，估算成本低于走复合索引
• 但这个估算完全没有考虑order_type_id的数据倾斜：对于订单量极少的低频类型，走单列索引需要扫描大量无关的新订单才能找到足够的匹配记录，甚至扫完全表才能发现没有足够数据，实际成本远高于优化器的估算值
• 首先排查低级错误：你贴出的复合索引创建语句中，首字段写的是orders_type_id（比表中实际字段order_type_id多了一个s），如果不是粘贴时的笔误，请先修正索引字段定义，确保索引建在正确的字段上。

• 优先修正统计信息准确度
  你之前执行的默认ANALYZE采样率不足，无法准确捕捉order_type_id的倾斜分布，尤其是长尾低频类型的数量。执行以下语句调高该字段的统计目标，再重新收集统计信息即可：

  -- 将该字段的统计目标从默认的100调高到1000，采样覆盖更多长尾值
  ALTER TABLE orders ALTER COLUMN order_type_id SET STATISTICS 1000;
  ANALYZE orders;
  

  统计信息准确后，优化器会正确识别低频类型的选择率极低，走单列索引的实际成本远高于复合索引，会自动选择正确的索引，不需要修改SQL或参数，是最治本的方案。

• 调整过时的成本参数
  PostgreSQL默认的random_page_cost=4.0是机械硬盘时代的经验值，在当前SSD存储场景下这个值严重偏高，会导致优化器过度偏好顺序扫描路径、低估索引随机扫描的成本。建议在全局或会话级别将该参数调整为和seq_page_cost接近的值：

  -- 全局修改可编辑postgresql.conf后重载配置，会话级修改可直接执行该语句
  SET random_page_cost = 1.1;
  

  调整后重新执行查询，大部分场景下优化器会自动选择正确的复合索引。

• 无副作用SQL改写引导索引选择
  如果你不方便修改数据库参数，可以改写查询的ORDER BY逻辑，在不改变返回结果顺序的前提下，让排序逻辑完全匹配复合索引的字段顺序：

  SELECT * FROM orders
  WHERE order_type_id=(SELECT id FROM order_types WHERE name='order_type_name')
  -- 增加order_type_id排序，因WHERE中该字段是固定值，返回顺序和原SQL完全一致
  ORDER by order_type_id, created_at DESC
  LIMIT 50;
  

  改写后优化器会直接识别到该排序可以完全命中(order_type_id, created_at desc)复合索引的有序性，会优先选择该索引执行，不需要任何插件或参数修改。

• 使用扩展强制指定索引（快速止血方案）
  如果以上方案都不生效，且生产环境需要快速解决问题，可以安装pg_hint_plan扩展，通过索引提示直接强制查询使用指定复合索引：

  -- 先创建扩展（需要超级用户权限）
  CREATE EXTENSION pg_hint_plan;
  -- 查询中加hint指定索引
  /*+ IndexScan(orders ix_orders_order_type_id_created_at) */
  SELECT * FROM orders
  WHERE order_type_id=(SELECT id FROM order_types WHERE name='order_type_name')
  ORDER by created_at DESC
  LIMIT 50;
  

  注意：不要通过SET enable_indexscan = off、SET enable_sort = off这类全局禁用执行路径的方式强制改执行计划，这类操作会影响库内所有查询，副作用极大。

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