如何高效从PostgreSQL multirange中提取重叠范围？

问题描述 #

我需要用SQL从PostgreSQL的multirange中提取与另一个范围重叠的子范围。例如，对于multirange {[5,10), [14,20), [26,28), [29,31)} 和范围 {[15,27)}，期望得到结果 {[14,20), [26,28)}。

我目前通过unnest和range_agg实现：

select range_agg(t.v) from (select unnest(:ranges) v) t where t.v && :range;

但这种方法性能不佳，请问有没有更高效的实现方式？另外，是否存在类似&的操作符，能直接实现如下效果：

{[5,10), [14,20), [26,28), [29,31)} & {[15,27)} -> {[14,20), [26,28)}

解决方案 #

1. 官方内置函数（PostgreSQL 16+） #

PostgreSQL 16及以上版本提供了multirange_intersect函数，专门计算两个multirange的交集，性能远优于unnest+range_agg的组合，完全匹配你的需求：

select multirange_intersect(:ranges, int4range(15,27));

执行后直接返回重叠子范围组成的新multirange，效果和你期望的&操作符一致。

2. 自定义&操作符（兼容旧版本） #

如果你的PostgreSQL版本低于16，可以自定义&操作符来简化调用：
首先创建核心函数：

create or replace function multirange_range_intersect(m int4multirange, r int4range) returns int4multirange as $$
select range_agg(v) from unnest(m) v where v && r;
$$ language sql stable;

然后绑定&操作符：

create operator & (
    leftarg = int4multirange,
    rightarg = int4range,
    procedure = multirange_range_intersect,
    commutator = &
);

之后即可直接使用&操作符：

select :ranges & int4range(15,27);

注意：该实现底层仍依赖unnest，但封装后更易用；若版本支持，优先使用官方内置函数获得更好性能。

性能优化建议 #

• 为multirange列创建GiST索引（例如create index idx_ranges on your_table using gist(ranges_column);），索引会大幅加速范围重叠判断的效率。
• 避免在大表中逐行执行unnest展开操作，优先使用索引或内置函数减少计算开销。

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