Re: overlaps performance

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> Grzegorz Jakiewicz <gj(at)pointblue(dot)com(dot)pl> writes:
>> So my question would be, why isn't postgresql using indexes for OVERLAPS,
>> and why optimizer doesn't substitute it with something like:
>>
>> (c <= a AND d > a) OR ( c >= a AND c < b)

> How would you use an index for that?

I believe you can index overlaps-like tests using GIST on an
interval-like data type --- look at contrib/seg for an example.

The reason we don't automatically translate OVERLAPS is that the spec's
definition of OVERLAPS is too weird for that to work; in particular
it demands a true result for some cases in which one of the four
endpoints is NULL, which'd be pretty hard to do with an interval-style
index.

regards, tom lane