PostgreSQL带Where子句的Join查询索引优化问题

需要优化的查询语句如下：

SELECT f.* 
FROM first f 
JOIN second s on f.attributex_id = s.id 
WHERE f.attributex_id IS NOT NULL AND f.attributey_id IS NULL
ORDER BY s.month ASC LIMIT 100;

已知约束和表特性：

• attributex_id 是指向 second.id 的外键
• attributey_id 是指向本次查询未使用的第三方表的外键
• 不允许修改原有查询语句
• first表中98%的记录满足f.attributex_id IS NOT NULL条件，98%的记录满足f.attributey_id IS NULL条件
• 已尝试创建如下部分索引，但Explain Analyze验证显示索引未被调用：

CREATE INDEX index_for_first
    ON first (attributex_id, attributey_id)
    WHERE attributex_id IS NOT NULL AND (attributey_id IS NULL)

核心待解答问题：

• 上述自建索引未生效的原因
• 可落地的有效索引优化方案
• s表的唯一字段month上创建索引是否能起到优化作用

自建索引未被调用，本质是优化器计算执行成本后，判定走该索引的开销比全表扫描更高，具体原因有两点：

1. 索引过滤性极差：两个WHERE条件各自仅过滤掉2%的first表数据，最终这个部分索引覆盖了first表约96%的记录。如果走这个索引，数据库需要扫描几乎整个索引树，再通过大量随机IO回表获取f.*的所有字段，随机IO的成本远高于直接顺序全表扫描first表，优化器自然不会选择这个低效路径。
2. 完全无法消除排序开销：查询的排序字段s.month属于second表，first表上的任何索引都无法提前完成排序。如果从first表驱动执行，数据库依然要把所有符合join条件的结果全量拉取、排序、再截取前100条，这个最大的开销点完全没有被降低，索引的实际收益可以忽略。

这个查询的核心优化逻辑是利用LIMIT 100的特性，避免全量两表join和全局排序，不需要扫描全量数据，凑够100条结果即可终止查询。

second表的索引优化 #

在s.month上建索引有非常明显的优化效果，是整个优化的核心：

• 因为s.month是唯一字段，索引本身按升序排列，优化器会选择second表作为驱动表，沿着month索引从小到大依次扫描s表记录，每拿到一条s记录的id，就去first表查找匹配的行，一旦凑够100条符合WHERE条件的结果就直接终止查询，完全跳过全量join和文件排序的开销。实际执行时可能只需要扫描几百条s表记录就能返回结果，成本比从first表驱动低几个数量级。
• 最优的s表索引是(month, id)的联合索引，可以直接从索引上拿到关联需要的s.id，不需要回表访问s表的主键索引，开销更低。如果是InnoDB引擎，二级索引叶子节点本身就存储主键值，单独建(month)的索引也能达到接近的效果。

first表的索引优化 #

不要建之前那种覆盖96%数据的宽索引，只需要建适配关联等值查询的索引即可：
如果是PostgreSQL等支持部分索引的数据库，建如下索引：

CREATE INDEX idx_first_attributex_lookup ON first (attributex_id)
WHERE attributey_id IS NULL;

如果是MySQL等不支持部分索引的数据库，建如下联合索引：

CREATE INDEX idx_first_attributex_lookup ON first (attributex_id, attributey_id);

这个索引的作用是：当驱动表s传过来一个id做等值匹配时，数据库可以直接通过索引定位到所有attributex_id = s.id且attributey_id IS NULL的f记录，不需要全表扫描f表，单次匹配的成本是常数级。这时候哪怕索引覆盖了first表大部分数据也不会被优化器拒绝——因为这个索引是用来做高频等值点查的，不是用来全量扫描过滤的，和之前自建索引的使用场景完全不同。

内容的提问来源于stack exchange，提问作者christopher.online