PostgreSQL 中 date_trunc 为什么能走索引？【待验证】
一次把「优劣、走索引、索引方案」说透
同事问：把
date_trunc('hour', ts)放到 WHERE 里，Explain 里竟出现了Index Scan，真的假的？
答：真的，但前提是你得先给它“铺路”。
一、两种写法的直观差异
| 写法 | 过滤条件示例 | 能否走索引 | 结果类型 |
|---|---|---|---|
| A | WHERE date_trunc('hour', ts) >= '2025-07-28 15:00:00' | ✅ 可以 | timestamp |
| B | WHERE to_char(date_trunc('hour', ts), 'YYYY-MM-DD HH24:MI:SS') >= '2025-07-28 15:00:00' | ❌ 不能 | text |
A 直接比较 timestamp，只要建 表达式索引即可利用 B-tree。
B 先转成 text，再做字符串比较，与索引键字节不匹配 → 只能 Seq Scan。
二、为什么 date_trunc 能走索引？
表达式索引
PostgreSQL 支持把任意 immutable 表达式 作为索引键CREATE INDEX idx_hour ON demo USING btree (date_trunc('hour', ts));字节级匹配
查询里的date_trunc('hour', ts)与索引键 逐字节一致，优化器即可使用索引。
任何额外函数（to_char、::text等）都会破坏一致性。immutable 保证
date_trunc被标记为IMMUTABLE：同输入必同输出，索引键可复用；
若函数不是 immutable，即使建了索引也会报ERROR: functions in index expression must be marked IMMUTABLE。
三、优劣全对比
| 维度 | 表达式索引 | 无索引实时计算 |
|---|---|---|
| 性能 | 范围查询 O(log n)，极快 | 全表/全分区扫描 O(n) |
| CPU | 建索引时一次性计算 | 每行实时计算 |
| 存储 | 额外索引页，通常 < 1% 表大小 | 0 |
| 维护 | REINDEX / VACUUM 即可 | 0 |
| 灵活性 | 改函数规则需重建索引 | 改函数即可生效 |
| 并发 | 读无锁，写仅轻量级锁 | 无锁 |
结论：
只要查询量大，表达式索引带来的 IO/CPU 节省远超存储成本。
四、落地三步走
建索引（一次即可
CREATE INDEX CONCURRENTLY idx_hour ON your_table USING btree (date_trunc('hour', ts));写查询（与索引键完全对齐
SELECT * FROM your_table WHERE date_trunc('hour', ts) >= '2025-07-28 15:00:00';验证计划
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_table WHERE date_trunc('hour', ts) >= '2025-07-28 15:00:00';期望看到：
Index Scan using idx_hour ...
五、常见坑 & FAQ
| 问题 | 原因 & 解决 |
|---|---|
| 不走索引 | 查询条件加了 ::text、to_char、+ interval 等 → 移除额外函数 |
| 时区敏感 | 用 timestamptz 时，date_trunc('hour', ts AT TIME ZONE 'UTC') 保持一致 |
| 分区表 | 在分区键表达式上建 全局表达式索引，或每个分区分别建 |
六、一句话总结
date_trunc 能走索引，不是因为黑科技，而是因为你提前把「表达式结果」存进了索引。
记住：“表达式一致 + IMMUTABLE + 索引” 三件套，就能让时间截断查询飞起来。