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 能走索引？

1. 表达式索引
   PostgreSQL 支持把任意 immutable 表达式 作为索引键

   CREATE INDEX idx_hourON demo USING btree (date_trunc(\'hour\', ts));

2. 字节级匹配
   查询里的 date_trunc(\'hour\', ts) 与索引键 逐字节一致，优化器即可使用索引。
   任何额外函数（to_char、::text 等）都会破坏一致性。

3. 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 节省远超存储成本。

---

四、落地三步走

1. 建索引（一次即可

   CREATE INDEX CONCURRENTLY idx_hourON your_table USING btree (date_trunc(\'hour\', ts));

2. 写查询（与索引键完全对齐

   SELECT *FROM your_tableWHERE date_trunc(\'hour\', ts) >= \'2025-07-28 15:00:00\';

3. 验证计划

   EXPLAIN (ANALYZE, BUFFERS)SELECT * FROM your_tableWHERE 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 + 索引” 三件套，就能让时间截断查询飞起来。