PostgreSQL中OVERLAPS谓词与重写语句是否等价及索引问题

问题背景 #

在PostgreSQL 15.3中，使用OVERLAPS运算符的WHERE子句无法利用(an_id, start_ts, end_ts)多列索引：

WHERE (start_ts, end_ts) OVERLAPS (input_ts_start, input_ts_end)

将语句重写为以下形式后，查询规划器可以正常使用上述多列索引：

WHERE input_ts_start < end_ts AND input_ts_end > start_ts

重写语句与原OVERLAPS谓词的等价性 #

在确保start_ts < end_ts且input_ts_start < input_ts_end的前提下，二者完全等价，依据如下：

根据PostgreSQL 15官方文档对OVERLAPS的定义：

当两个时间段（由端点定义）重叠时，该表达式返回true，否则返回false。端点可以指定为日期、时间或时间戳对；也可以是日期、时间或时间戳后跟间隔。当提供一对值时，起始或结束值可任意顺序书写；OVERLAPS会自动将对中较早的值作为起始点。每个时间段视为半开区间start <= time < end，除非起始和结束值相等，此时表示单个时间点。这意味着仅端点重合的两个时间段不视为重叠。

1. 区间逻辑匹配：
   由于已经保证两个时间段的起始都小于结束，OVERLAPS无需交换端点，直接将两个时间段视为半开区间[start_ts, end_ts)和[input_ts_start, input_ts_end)。而重写后的input_ts_start < end_ts AND input_ts_end > start_ts正是半开区间重叠的充要判断条件——两个区间重叠当且仅当一个区间的起始小于另一个区间的结束，且反之亦然。
2. NULL处理一致：
   PostgreSQL中OVERLAPS遇到NULL端点时返回NULL，重写后的逻辑中只要任意比较涉及NULL，结果也会是NULL，与原运算符的行为完全匹配。

额外注意事项 #

除了input_ts_end设为'infinity'::timestamp时索引仍可用外，还需关注以下几点：

• 单时间点场景兼容：如果某条数据的start_ts = end_ts（表示单个时间点），原OVERLAPS会判断该点是否落在目标区间内；重写后的语句等价于input_ts_start < start_ts AND input_ts_end > start_ts，与原逻辑一致，只要该时间点处于[input_ts_start, input_ts_end)区间内就返回true。
• 统计信息维护：定期执行ANALYZE更新表的统计信息，确保查询规划器能准确评估索引的使用价值，避免因数据分布不均选择低效执行计划。
• 边界判断严格性：严格遵循半开区间规则，不要误将重写语句写成input_ts_start <= end_ts或input_ts_end >= start_ts，否则会错误地将端点重合的情况判定为重叠，违背OVERLAPS的原始逻辑。
• 类型一致性：确保input_ts_start、input_ts_end与start_ts、end_ts的类型完全匹配（例如均为timestamp而非混合timestamp与timestamptz），避免隐式类型转换导致索引失效。

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