如何使PostgreSQL优化器生成确定性执行计划，稳定采用高效方案

问题根源 #

PostgreSQL优化器会根据统计信息估算返回数据量，选择它认为成本最低的执行计划。当scans.createdAt的时间范围从26天扩展到27天时，优化器可能错误估算了匹配的scanId数量，认为哈希连接+全表扫描的成本更低，但实际数据分布下嵌套循环+索引扫描才是更优选择。即使添加了联合索引，过时或不准确的统计信息仍会导致优化器做出错误判断。

解决方案 #

1. 更新并优化统计信息 #

优化器依赖表的统计信息做决策，先确保统计信息是最新且精准的：

• 手动更新表统计：

ANALYZE scans;
ANALYZE scan_exchanges;

• 提高createdAt字段的统计精度（针对时间字段分布不均匀的情况）：

ALTER TABLE scans ALTER COLUMN "createdAt" SET STATISTICS 1000;
ANALYZE scans;

（1000是统计样本量，可根据表大小调整，默认值通常是100）

2. 调整查询写法，引导优化器选择嵌套循环 #

将原有的CTE+IN子查询结构改为JOIN写法，让优化器更清晰地判断关联顺序：

SELECT se."scanId", percentile_disc(0.5) WITHIN GROUP (ORDER BY se.duration) AS p50
FROM scans s
JOIN scan_exchanges se ON s.id = se."scanId"
WHERE s."applicationId" = '2ce67bbf-d740-4f4e-aaf8-33552d54e482'
  AND s."createdAt" BETWEEN NOW() - INTERVAL '27 day' AND NOW()
GROUP BY se."scanId";

这种写法直接将两个表关联，优化器更容易优先扫描scans的索引获取匹配ID，再通过scan_exchanges_scanId_idx索引拉取对应数据，避免全表扫描。

3. 临时强制禁用哈希连接（测试用） #

如果统计信息更新后仍无改善，可以临时局部禁用哈希连接，强制优化器选择嵌套循环：

BEGIN;
SET LOCAL enable_hashjoin = off;
-- 执行目标查询
WITH filtered_scans AS (
  SELECT id FROM scans
  WHERE scans."createdAt" BETWEEN NOW() - INTERVAL '27 day' AND NOW() AND
    scans."applicationId" = '2ce67bbf-d740-4f4e-aaf8-33552d54e482'
)
SELECT "scanId", percentile_disc(0.5) WITHIN GROUP (ORDER BY duration) AS p50
FROM scan_exchanges
WHERE "scanId" IN (SELECT id FROM filtered_scans)
GROUP BY "scanId";
COMMIT;

注意：此方法仅用于临时验证，不建议全局或长期启用，否则会影响其他查询的执行计划。

4. 验证索引有效性 #

确认你添加的(applicationId, createdAt, id)联合索引被正确识别：

• 查看索引是否存在：

\di scans_applicationId_createdAt_id_idx

（替换为你实际的索引名称）

• 强制使用索引扫描：在scans的查询中添加索引扫描引导：

SELECT id FROM scans
WHERE scans."applicationId" = '2ce67bbf-d740-4f4e-aaf8-33552d54e482'
  AND scans."createdAt" BETWEEN NOW() - INTERVAL '27 day' AND NOW()
INDEX SCAN USING scans_applicationId_createdAt_id_idx ON scans;

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