PostgreSQL daterange类型 查询指定日期前记录的最高效方法

原写法的效率问题 #

你当前的写法如果没有专门针对upper(duration)建表达式索引，确实会触发全表扫描，需要逐行计算duration的上界再做比对，效率很低，不属于该场景的最优实现。

更优的查询方案 #

PostgreSQL针对范围类型内置了专门的操作符和索引支持，你的需求是查询所有duration范围完全早于指定日期的记录，可以用范围类型原生的「左对齐」操作符<<实现，改写后的SQL如下：

SELECT * FROM mytable WHERE duration << '2021-11-01'::date;

<<操作符的语义为左操作数（范围）完全位于右操作数（可以是范围也可以是单个元素）的左侧，和你原SQL的upper(duration) < 指定日期逻辑完全等价。

索引优化 #

要让上述查询走索引，只需要给duration字段建GIST索引即可：

CREATE INDEX idx_mytable_duration ON mytable USING GIST (duration);

这个索引的通用性远高于单独给upper(duration)建的B树索引：除了支持当前的早于查询外，后续你要查范围重叠、包含、晚于等所有范围类型相关的操作，都可以复用这个索引。

补充说明 #

如果你确认业务场景只会用到duration上界的比较，不会用到其他范围查询逻辑，也可以选择给upper(duration)建B树表达式索引，这种情况下你原来的SQL也可以走索引，查询效率和用<<+GIST索引的差异很小，但索引的适用场景会窄很多。

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