本文摘要两千万行订单表按天分桶的表达式索引建不起来天级慢查询长期无法收敛。不可变性在建索引那一刻即校验绕法只有显式化依赖、冗余列、谎称不可变。一、问题与结论pg_stat_user_tables里n_live_tup与orders实际行数同量级、last_autovacuum正常统计信息过期排除条件写成created_at $1::text会让 btree 因类型不符失效隐式类型转换也排除。剩下这条路径的终点是 DDL 直接失败-- 一、统计信息过期估算行数与实际差一个数量级就先 ANALYZESELECTrelname,n_live_tup,last_autovacuum,last_autoanalyzeFROMpg_stat_user_tablesWHERErelnameorders;-- 二、隐式类型转换created_at $1::text 会让索引失效-- 三、给 date_trunc(day, created_at) 建表达式索引CREATEINDEXONorders((date_trunc(day,created_at)));ERROR: functions in index expression must be immutable不可变性检查发生在建索引那一刻不通过就没有索引可用代价落在 schema 设计、写入链路与结果正确性不在查询期。前两类失效改 SQL 即可修第三类必须改 schema 或写入这就是三种绕法各有代价的根源。二、排查与选择依据判定顺序固定三步先EXPLAIN (ANALYZE, BUFFERS)配合pg_stat_statements确认慢在哪估算行数与实际差一个数量级就ANALYZE并看 autovacuum再确认条件没有隐式类型转换最后查函数 volatility。第三步不能凭记忆查 catalog-- 函数真实 volatilitydate_trunc 常因依赖会话 TimeZone 而不是 IMMUTABLESELECTp.proname,oidvectortypes(p.proargtypes)ASargs,CASEp.provolatileWHENiTHENIMMUTABLEWHENsTHENSTABLEWHENvTHENVOLATILEENDASvolFROMpg_proc pWHEREp.pronameIN(date_trunc,timezone,now,lower);-- 已存在的表达式索引SELECTi.relnameASidx,pg_get_indexdef(x.indexrelid)ASddlFROMpg_index xJOINpg_class iONi.oidx.indexrelidWHEREx.indexprsISNOTNULL;-- 业务 schema 里 volatility 非 IMMUTABLE 的自定义函数SELECTn.nspname,p.proname,p.provolatileFROMpg_proc pJOINpg_namespace nONn.oidp.pronamespaceWHEREp.provolatileiANDn.nspnameNOTIN(pg_catalog,information_schema);两条分界在这里统计信息与类型转换影响“查询怎么执行”改 SQL 即可不可变性影响“索引能不能存在”只能改 schema 与写入链路。而真正谎称 IMMUTABLE 的函数自标不可变却依赖会话状态pg_proc查不出来只能审函数体。替代方案与取舍方案选择条件代价与边界范围条件created_at $d AND created_at $d interval 1 day只要时间切片不要函数语义零 schema 变更可配 BRIN对乱序写入效果差生成列 普通索引PG 12表达式确实 IMMUTABLE只想简化 SQL写入期求值加STORED列可能重写全表同样受不可变约束非确定性 ICU collationPG 12目标是大小写不敏感比较collation 版本升级后需REINDEX算子与函数支持随版本变化冗余列 触发器求值依赖会话状态但业务能固定口径回填产生 WALCOPY、逻辑复制可绕过触发器谎称 IMMUTABLE能证明结果只依赖入参且有兜底校验唯一会静默给错答案默认不采用不该用表达式索引的场景只要时间切片就用范围条件求值依赖TimeZone、collation 等会话状态而业务锁不定口径时改数据模型别把函数标成不可变写入远多于查询的表不适合加冗余列与触发器。三、关键原理闸门范围表达式索引、部分索引谓词、生成列表达式、CHECK约束、分区键表达式都要求 IMMUTABLE措辞略有差别——表达式索引报functions in index expression must be immutable部分索引谓词报functions in index predicate must be immutable。报错只针对函数调用(a b)纯运算不受影响。定义IMMUTABLE 是“只依赖入参”不是“在我环境里稳定”。STABLEnow()同一查询内结果不变但反映其他事务已提交改动VOLATILErandom()同一查询内都可能变。TimeZone、collation、lc_ctype、search_path都是入参以外的依赖官方文档明确警告把依赖它们的函数标成 IMMUTABLE索引会返回过期或错误结果。匹配查询里必须逐字写同样的表达式只有lower、upper等少数内建函数有等价改写自定义包装函数漏写一处就全表扫描且不报错。版本PG 11 起ADD COLUMN ... DEFAULT 常量只改元数据不重写全表PG 12 引入生成列与非确定性 collation。生成列同样受不可变约束它不绕开检查只是把求值挪到写入期。升级不会放宽这条约束。三种绕法的代价落点由此确定绕法 1 把AT TIME ZONE UTC写进表达式索引建得出来代价是语义钉死业务改用Asia/Shanghai分桶就要重建绕法 2 加冗余列与触发器代价在写入与运维绕法 3 用包装函数自行声明 IMMUTABLE代价是正确性风险且是唯一不报错却给错答案的一种。四、可运行示例环境一台可建库的 PostgreSQL 实例版本自行确认下面的 SQL 不依赖 PG 12 特性输入是一张orders表加一行跨日写入的边界数据。按顺序执行。SETtimezoneUTC;DROPTABLEIFEXISTSordersCASCADE;CREATETABLEorders(id bigserialPRIMARYKEY,created_at timestamptzNOTNULL,amountnumeric(12,2));INSERTINTOorders(created_at,amount)VALUES(2024-03-10 23:30:0000,1.00);复现闸门STABLE 情形CREATEINDEXONorders((date_trunc(day,created_at)));预期输出ERROR: functions in index expression must be immutable。date_trunc(text, timestamptz)依赖会话TimeZonevolatility 以你实例的 catalog 查询为准若它被标成 IMMUTABLE这里会直接建成功。绕法 1把时区写进表达式查询逐字一致。CREATEINDEXorders_day_idxONorders(date_trunc(day,created_at ATTIMEZONEUTC));EXPLAIN(ANALYZE,COSTSOFF)SELECTidFROMordersWHEREdate_trunc(day,created_at ATTIMEZONEUTC)2024-03-10 00:00:00::timestamp;预期输出Index Scan using orders_day_idx返回 1 行。实际输出在你的实例执行后把计划与行数填回来对照均未实测差异以实例为准。绕法 2冗余列 触发器代价落在写入与运维。ALTERTABLEordersADDCOLUMNday_utctimestamp;UPDATEordersSETday_utcdate_trunc(day,created_at ATTIMEZONEUTC);CREATEORREPLACEFUNCTIONorders_set_day_utc()RETURNStriggerLANGUAGEplpgsqlAS$$BEGINNEW.day_utc :date_trunc(day,NEW.created_at ATTIMEZONEUTC);RETURNNEW;END$$;CREATETRIGGERorders_day_utc BEFOREINSERTORUPDATEOFcreated_atONordersFOR EACH ROWEXECUTEFUNCTIONorders_set_day_utc();CREATEINDEXONorders(day_utc);预期输出索引建在普通列上查询直接写WHERE day_utc 2024-03-10 00:00:00::timestamp不再受表达式逐字匹配约束。绕法 3 与它的静默失败索引在 UTC 会话下建成CREATEORREPLACEFUNCTIONday_utc(ts timestamptz)RETURNStimestampLANGUAGEplpgsql IMMUTABLEAS$$BEGINRETURNdate_trunc(day,ts);-- 返回 timestamp 的隐式转换同样依赖会话 TimeZoneEND$$;CREATEINDEXorders_day_lie_idxONorders(day_utc(created_at));SETtimezoneAsia/Shanghai;SETenable_seqscanoff;EXPLAIN(ANALYZE,COSTSOFF)SELECTidFROMordersWHEREday_utc(created_at)2024-03-10 00:00:00::timestamp;RESET enable_seqscan;EXPLAIN(ANALYZE,COSTSOFF)SELECTidFROMordersWHEREday_utc(created_at)2024-03-10 00:00:00::timestamp;预期输出第一次Index Scan using orders_day_lie_idx返回 1 行索引冻结的是 UTC 口径的值第二次Seq Scan返回 0 行。同一条 SQL、同一份数据结果随执行计划而变。常见失败与修复有两个LANGUAGE sql IMMUTABLE的包装函数可能被内联内层 STABLE 调用重新暴露给检查CREATE INDEX仍报同样的错——改用plpgsql不内联并先在实例验证绕法 1 的查询漏写AT TIME ZONE UTC索引不命中也无报错以EXPLAIN里是否出现orders_day_idx为准。五、验证结果与边界不给耗时与收益数字文中现象均为依据文档语义的预期除标注外均未验证。上线前需在实例核对date_trunc、timezone的provolatile实际值LANGUAGE sql包装函数是否被内联切换timezone后返回行数是否随计划变化ADD COLUMN ... GENERATED ... STORED是否重写全表非确定 collation 支持的算子范围。边界三条pg_dump恢复后默认timezone不同索引条目口径与查询口径会整体错位触发器可被COPY或逻辑复制绕过冗余列会漂移需要约束或校验脚本兜底绕法 3 的函数在生成列、CHECK约束处另有措辞不同的检查不能统一使用。思考按天分桶口径固定为 UTC 后冗余列与表达式索引哪种更易在团队代码里保持一致谎称 IMMUTABLE 的函数应靠代码评审禁止还是靠pg_proc体检 SQL 定期扫描参考资料CREATE INDEXPostgreSQL current函数 volatility 分类PostgreSQL currentCREATE FUNCTIONPostgreSQL current生成列PostgreSQL currentCollation 支持PostgreSQL currentpg_proc catalogPostgreSQL current
