PostgreSQL 16中JOIN查询未使用预期索引的问题排查

在PostgreSQL 16.0环境下，存在两张表：

CREATE TABLE fk_table (
  fk_id INT PRIMARY KEY
, fk_name VARCHAR(50)
);
CREATE TABLE main_table (
  id INT PRIMARY KEY
, date TIMESTAMP
, fk INT REFERENCES fk_table(fk_id)
--, tens more columns
);

其中main_table包含数百万行数据、数十列，fk_table仅含少量数据。

尝试优化以下JOIN查询：

SELECT MIN(date) FROM main_table
INNER JOIN fk_table ON fk = fk_id
WHERE fk_name = 'pending';

创建复合索引CREATE INDEX idx__main_table__fk__date ON main_table(fk, date);后，该查询仍未使用此索引，执行计划显示采用并行全表扫描+哈希连接，执行时间约257ms；

而改用嵌套子查询写法：

SELECT MIN(date) FROM main_table
WHERE fk = (SELECT fk_id 
            FROM fk_table 
            WHERE fk_name = 'pending')

则成功使用该索引，执行计划为索引仅扫描，执行时间仅约0.07ms。

疑问：为何第一个JOIN查询未使用预期索引？是查询、索引存在问题，还是有其他遗漏点？

• 统计信息与成本估算偏差：PostgreSQL优化器依赖表统计信息判断执行路径成本。虽然fk_table数据量少，但如果统计信息未精准反映fk_name='pending'仅对应唯一fk_id，优化器会默认认为JOIN后可能需要处理大量数据，判定并行全表扫描+哈希连接的成本低于索引扫描，因此选择了后者。
• 查询逻辑的执行计划判定差异：INNER JOIN的写法会让优化器先考虑两表关联的逻辑，即使实际可以先过滤fk_table，但优化器的成本模型对这种关联场景的判断，和子查询的单值匹配场景不同。子查询明确先拿到唯一的fk_id，优化器能直接识别出可以通过复合索引(fk, date)快速定位到目标数据，触发索引仅扫描；而JOIN写法被优化器归类为多表关联场景，未优先触发索引的精准匹配逻辑。
• 索引适用性的触发条件：复合索引(fk, date)的最优使用场景是先通过fk过滤出子集，再快速获取该子集的date极值。子查询的写法直接限定了单fk值，完美匹配索引的使用条件；而JOIN写法中，优化器未明确感知到fk_table返回的是单值，因此没将索引扫描作为优先选项，反而认为并行全表扫描在处理大表时的并行处理能力更具成本优势。

查询和索引本身不存在语法或结构问题，核心原因是PostgreSQL优化器对两种查询写法的成本估算逻辑差异，加上统计信息可能未精准反映fk_table的单值返回特性，导致JOIN写法未触发预期的索引使用。

可以通过执行ANALYZE fk_table;更新统计信息，让优化器更精准判断数据分布；或者直接将查询改写为子查询/EXISTS的形式，来触发索引优化。

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