PostgreSQL查询为何未在Index Condition中使用incident_id却用于Filter

为什么incident_id未被纳入Index Condition？ #

当前查询采用的是Hash Join执行计划，执行逻辑如下：

• 先将relevant_incidents_per_inventory_item表（仅数百行）中的所有incident_id提取出来，构建Hash映射表；
• 再扫描incident_tracker_details的索引，仅用company_id和division_id过滤出符合条件的所有行，之后逐一将这些行的incident_id与Hash表中的值做匹配。

在这个流程里，incident_id的匹配是在Hash Join的关联阶段完成的，而非索引扫描的过滤阶段。数据库在扫描索引时，还不知道需要匹配哪些具体的incident_id值，因此无法将incident_id作为索引条件来缩小扫描范围，只能先取出所有符合company_id+division_id的行，再做后续过滤。

优化方案 #

1. 改用Nested Loop Join（推荐） #

由于relevant_incidents_per_inventory_item数据量极小，Nested Loop Join会更高效：数据库会遍历小表的每一行，将该行的incident_id与company_id、division_id组合成精准条件，直接在索引中查找匹配行。此时索引的三个列都会被纳入Index Condition，避免扫描大量无关数据。

可以用LATERAL JOIN来实现这种逻辑：

SELECT
    r.inventory_id,
    itd.tracker_id
FROM
    relevant_incidents_per_inventory_item r
JOIN LATERAL
    (SELECT tracker_id FROM incident_tracker_details itd
     WHERE itd.company_id = :companyId
       AND itd.division_id = :divisionId
       AND itd.incident_id = r.incident_id
       AND itd.value > 0) itd ON true;

也可以临时关闭Hash Join来测试效果（不建议长期使用）：

SET enable_hashjoin = off;
-- 执行原查询
SET enable_hashjoin = on;

2. 确认索引的合理性 #

你创建的部分索引companyid_divisionId_incidentId_idx是完全合理的：

• 前缀列company_id+division_id是等值过滤条件，incident_id作为后续匹配列，顺序符合索引使用规则；
• 索引包含tracker_id，满足Index Only Scan的覆盖需求；
• 部分索引条件where (value > 0)提前过滤了无效行，减少了索引数据量。

3. 给小表添加辅助索引（可选） #

给relevant_incidents_per_inventory_item的incident_id列创建索引，虽然对核心问题影响不大，但能让数据库在准备Join数据时更高效：

CREATE INDEX idx_incident_id ON relevant_incidents_per_inventory_item(incident_id);

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