PostgreSQL 9.6.12中现有索引能否加速指定条件的max(viewed_at)查询？

现有索引能否高效执行？ #

现有INDEX on conversation_views (conversation_id, user_id)可以让这个查询正常运行，但并非最优的高效方案。

原因在于：该索引能帮PostgreSQL快速定位到符合conversation_id=%L和user_id=%L条件的行，但索引里不包含viewed_at字段，数据库需要从找到的索引条目回表读取实际的viewed_at值，再计算最大值。如果这两个条件匹配的行数较多，回表的IO开销会明显拉低查询效率。

优化策略 #

1. 创建覆盖索引 #

最有效的优化是把viewed_at加入现有索引，创建一个包含所有查询所需字段的复合索引：

CREATE INDEX idx_conversation_user_viewed ON conversation_views (conversation_id, user_id, viewed_at);

这个索引属于覆盖索引，查询需要的过滤字段（conversation_id、user_id）和计算字段（viewed_at）都包含在索引里，数据库完全不需要回表，直接从索引中就能获取数据。而且PostgreSQL的B-tree索引是有序的，对于相同的conversation_id和user_id，viewed_at会按顺序排列，数据库可以直接定位到最大值，不需要扫描所有匹配的索引条目，效率会大幅提升。

2. 验证执行计划 #

可以用EXPLAIN ANALYZE查看实际执行计划，确认索引是否被正确使用：

EXPLAIN ANALYZE
select max(viewed_at)
from conversation_views
where conversation_id='xxx' and user_id='xxx';

如果执行计划显示使用了我们创建的覆盖索引（比如Index Only Scan），就说明优化已经生效。

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