PostgreSQL timestamptz字段使用AT TIME ZONE时索引不生效问题

PostgreSQL普通B树索引无法命中的本质原因和timestamptz字段类型本身没有直接关系，核心是两个机制限制：

• 索引范围过滤要求扫描对应表时就拿到确定的比较阈值，你的查询不满足这个前提。
  你创建的shifts_start_at_idx是直接基于shifts.start_at原字段值构建的有序B树结构，要靠它做范围剪枝，必须在扫描shifts表的时候就明确知道比较边界值。但你写的过滤条件里，比较阈值是'2022-05-06 03:00:00'::timestamp AT TIME ZONE (EXTRACT(timezone FROM cities.time_zone) * INTERVAL '1 second')，这个值依赖cities表的time_zone字段计算，必须等shifts关联stores、再关联到cities之后才能算出来。
  从第一个执行计划也能直接验证：这个时间比较条件被放在了最外层嵌套循环的Join Filter（连接过滤）阶段，而不是shifts表扫描的Index Cond（索引条件）阶段——扫描shifts的时候根本不知道阈值是多少，自然没法用shifts.start_at上的索引提前过滤数据。
• 不要混淆「索引字段做函数转换」和「比较右值动态计算」两种完全不同的场景。
  很多人会误以为是AT TIME ZONE函数导致索引用不上，实际上就算你不用这个函数，只要比较的右值是依赖后续关联表才能算出的动态值，都没法在单表扫描阶段下推到索引。反过来如果你是在shifts.start_at字段上套AT TIME ZONE做转换（比如shifts.start_at AT TIME ZONE 'UTC' >= xxx），那普通索引也用不了，因为索引存的是原字段值，不是函数计算后的结果，这种场景需要单独建表达式索引才能命中，但你当前遇到的问题不属于这类。

第二个查询能命中索引的逻辑非常清晰：比较右值是固定常量'2022-05-06 03:00:00'，PostgreSQL会在查询规划阶段直接把它解析成确定的timestamptz值，扫描shifts表的时候就能直接拿这个固定值走索引做范围剪枝，所以执行计划里这个条件直接出现在Index Cond中，执行耗时自然低很多。

补充说明：从两个执行计划可以看到，实际扫描shifts表时用的都不是单字段的shifts_start_at_idx，而是shifts_admin_order_by_idx联合索引。本质是这个联合索引的排序顺序和你ORDER BY shifts.start_at DESC, shifts.end_at DESC, shifts.id DESC的排序要求完全匹配，优化器选它可以避免额外排序开销，比单独的shifts_start_at_idx执行效率更高。

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