带有where子句的联接查询的索引PostgreSQL

Question

我必须在索引的帮助下优化以下查询。

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是指向查询中未使用的另一个表的外键
• 更改查询不是一个选项
• 以下第一项中的大多数条目(98%)将是真正的f.attributex_id IS NOT NULL。与第二个条件f.attributey_id IS NULL相同

我尝试添加如下索引。

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

但是在执行查询时不使用索引(通过Explain Analyze进行检查)。我需要什么样的索引来优化查询？我对上面的索引做了什么错误？s.month上的索引也有意义吗(month是唯一的)？

Answer 1

基于查询文本以及first中几乎所有记录都满足where子句的事实，您实际上要做的是

1. 确定具有最低month值的100条second记录
2. 输出CCD_ 12表中的相关记录的内容

为了实现这一点，您可以在上创建索引

1. second.month
2. first.attributex_id

注意事项

由于必须优化此查询，所以可以肯定地说，两个表中都有许多行。由于一年中只有12个月，因此查询的输出可能不具有确定性(，即，每次运行时，它可能会返回不同的行集，即使运行之间的表中没有活动)，因为许多记录可能共享相同的month值。添加"；联络断路器"；second上索引的列可能会有所帮助，尽管您的order by只包括month，因此不能保证。此外，如果second.month可以有null值，那么您需要决定这些null值应该在值中排序第一个还是最后一个。

此外，这个特定的查询并不是针对您的数据运行的唯一查询。这些索引将占用磁盘空间，并逐渐降低对表的写入速度。如果你有十几个查询执行不佳，你可能会陷入创建几个索引来单独帮助每个查询的陷阱，而这不是一个可以很好扩展的解决方案。

最后，你说

更改查询不是的选项

这是否意味着不允许更改查询的文本或查询的输出？

我个人觉得重新编写查询以选择from second，然后选择join first使查询的目标更加明显。事实上，您最初的本能是向first添加索引，这为这个想法提供了证据。如果查询是按如下方式编写的，那么更明显的是，要做的事情是促进对second中您感兴趣的微小行集的有效访问：

...
from second s
join first f ...
where ...
order by s.month asc limit 100;