如何在索引中使用date_trunc()与timestamptz以支持表连接？

报错原因 #

PostgreSQL要求索引表达式中的函数必须是IMMUTABLE（不可变），即输入相同值时输出永远一致。而date_trunc('hour', event_at)中的event_at是带时区的时间戳（timestamptz），该函数的结果会受数据库时区或会话时区影响，因此属于STABLE（稳定）而非IMMUTABLE，无法直接用于索引表达式。

可行解决办法 #

方法1：优化现有复合索引（无需修改表结构） #

针对你的查询逻辑，创建适配过滤、分组和关联需求的复合索引，无需依赖date_trunc函数：

CREATE INDEX metric_events_query_opt_idx 
ON metric_events (metric_id, test, code, event_at) 
INCLUDE (success);

• 索引列顺序：将查询中用于关联、分组的metric_id、test、code放在最前面，接着是用于时间范围过滤的event_at，最后用INCLUDE添加success避免回表查询。
• 该索引可以快速定位时间范围内的目标数据，同时高效支持分组和与hourly_metric_event_counters的关联逻辑。

方法2：新增生成列（Generated Column）存储小时级时间戳 #

通过生成列固定时区，让截断后的时间戳成为不可变值，再基于该列创建索引：

1. 添加生成列（固定为UTC时区，确保结果不受时区变化影响）：

ALTER TABLE metric_events 
ADD COLUMN event_hour timestamptz 
GENERATED ALWAYS AS (date_trunc('hour', event_at AT TIME ZONE 'UTC') AT TIME ZONE 'UTC') STORED;

2. 创建复合索引：

CREATE INDEX metric_events_event_hour_idx 
ON metric_events (metric_id, test, code, event_hour) 
INCLUDE (success);

3. 调整查询语句，直接使用生成列关联：

SELECT b.event_hour,
       b.metric_id,
       b.code,
       b.test,
       COUNT(b.*) - MAX(bec.total_count) as total_diff,
       SUM(CASE WHEN b.success THEN 1 ELSE 0 END) - MAX(bec.success_count) as success_diff
FROM metric_events b
JOIN hourly_metric_event_counters bec
ON bec.metric_id = b.metric_id AND
   bec.test = b.test AND
   bec.code = b.code AND
   bec.start_at = b.event_hour
WHERE b.event_at >= date_trunc('hour', NOW() - interval '24 hours') AND b.event_at < date_trunc('hour', NOW() - interval '1 hour')
GROUP BY 1, 2, 3, 4
HAVING COUNT(b.*) - MAX(bec.total_count) != 0 OR
       SUM(CASE WHEN b.success THEN 1 ELSE 0 END) - MAX(bec.success_count) != 0

此方法让索引逻辑更直观，查询性能也更稳定。

方法3：自定义IMMUTABLE截断函数 #

如果不想修改表结构，可以自定义一个固定时区的不可变函数来实现小时截断：

1. 创建函数：

CREATE OR REPLACE FUNCTION trunc_hour_utc(t timestamptz) 
RETURNS timestamptz AS $$
SELECT date_trunc('hour', t AT TIME ZONE 'UTC') AT TIME ZONE 'UTC';
$$ LANGUAGE sql IMMUTABLE;

2. 基于自定义函数创建索引：

CREATE INDEX metric_events_composite_index 
ON metric_events (metric_id, code, test, success, trunc_hour_utc(event_at));

3. 查询时替换date_trunc为自定义函数：

SELECT trunc_hour_utc(b.event_at),
       -- 其余查询逻辑保持不变...

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