Re: overlaps performance

Gregory Stark <stark@enterprisedb.com> writes:
> Grzegorz Jaśkiewicz <gj@pointblue.com.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