PostgreSQL中TIMESTAMP转DATE查询能否使用普通索引及优化方案？

首先直接给结论：默认情况下，你的普通TIMESTAMP索引不会被用于dt::DATE = NOW()::DATE这类查询——你看到的全表扫描结果就是证明。但我们有办法让普通索引生效，或者选择创建函数索引来适配原查询。

为什么普通索引没被用到？ #

普通索引是基于dt列的原始TIMESTAMP值排序存储的。当你对dt应用::DATE转换函数后，实际上是在查询“所有落在当天的TIMESTAMP值”，但这个转换破坏了索引的有序性：同一个DATE对应的TIMESTAMP是一个连续的时间范围（比如2024-05-20 00:00:00到2024-05-20 23:59:59.999999），但PostgreSQL的优化器默认不会自动把dt::DATE = X重写成对应的TIMESTAMP范围查询，因此它认为无法利用现有索引，只能走全表扫描。

如何让普通索引生效？ #

你可以手动改写查询，把DATE转换的条件转换成对原始TIMESTAMP列的范围查询，这样就能直接利用已有的普通索引。比如把原查询：

SELECT * FROM tbl WHERE dt::DATE = NOW()::DATE;

改写成：

SELECT * FROM tbl 
WHERE dt >= DATE_TRUNC('day', NOW()) 
  AND dt < DATE_TRUNC('day', NOW()) + INTERVAL '1 day';

这个改写和原查询逻辑完全等价：DATE_TRUNC('day', NOW())会得到当天的零点时间，加上1天的区间后，就能精准匹配所有属于当天的TIMESTAMP值。此时再执行EXPLAIN ANALYZE，你应该会看到优化器选择走Index Scan而不是全表扫描。

什么时候必须创建函数索引？ #

如果你不想修改现有查询语句，那创建函数索引是唯一的选择。针对dt::DATE创建索引：

CREATE INDEX idx_tbl_dt_date ON tbl ((dt::DATE));

创建完成后，原查询SELECT * FROM tbl WHERE dt::DATE = NOW()::DATE就会直接使用这个函数索引，避免全表扫描。

补充：为什么优化器没自动重写查询？ #

理论上，dt::DATE = CURRENT_DATE和对应的TIMESTAMP范围查询是完全等价的（对于不带时区的TIMESTAMP类型），但PostgreSQL的优化器并不总是会自动做这个转换——可能是因为统计信息过时，或者优化器误判了成本（但你的案例中符合条件的只有4928条，显然索引扫描更高效，大概率是优化器没识别到可以重写）。手动改写查询是最直接可靠的方式。

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