PostgreSQL含日期函数的复合索引无法用仅索引扫描？如何优化？

问题根源 #

你当前的查询在WHERE子句中对received_at使用DATE()函数，这会让PostgreSQL无法直接利用索引的有序性进行范围匹配；同时如果索引未覆盖所有查询所需列，或者表的可见性映射（VM）未更新，都会导致无法触发仅索引扫描，只能走回表的Bitmap Heap Scan。

具体解决方案 #

1. 改写WHERE条件，避免函数包裹字段

把基于DATE(received_at)的范围条件，转换成直接对received_at的时间范围匹配，这样PostgreSQL可以高效命中你的复合索引：

SELECT DATE(received_at), location_id, purpose_id, COUNT(DISTINCT identity_id)
FROM inquiries
WHERE received_at >= $1::timestamp 
  AND received_at < ($2::date + INTERVAL '1 day')
GROUP BY DATE(received_at), location_id, purpose_id;

• 逻辑等价性：DATE(received_at) >= '2024-01-01'完全等价于received_at >= '2024-01-01 00:00:00'；DATE(received_at) <= '2024-01-07'等价于received_at < '2024-01-08 00:00:00'，改写后不会改变查询结果。
• 优势：避免了函数对字段的包裹，让优化器能直接使用索引中的date(received_at)列进行范围过滤。

2. 确保复合索引是覆盖索引

你的复合索引必须包含查询中所有用到的列，才能支持仅索引扫描。检查索引定义，需包含date(received_at)、location_id、purpose_id，还要包含identity_id（因为需要用它做COUNT(DISTINCT)）。如果索引缺少identity_id，需要修改索引：

-- 方式1：用INCLUDE添加非键列（PostgreSQL 11+支持）
CREATE INDEX idx_inquiries_daily_stats ON inquiries (date(received_at), location_id, purpose_id) INCLUDE (identity_id);

-- 方式2：把identity_id加入索引键列
CREATE INDEX idx_inquiries_daily_stats ON inquiries (date(received_at), location_id, purpose_id, identity_id);

3. 更新表的可见性映射

仅索引扫描依赖PostgreSQL的可见性映射（VM）来确认数据行是否可见，无需回表检查。如果表有大量更新/删除操作，VM可能过时，执行以下命令更新：

VACUUM ANALYZE inquiries;

效果验证 #

完成上述操作后，重新执行查询并查看执行计划，应该会触发Index Only Scan，大幅降低查询耗时。

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