Re: overlaps performance

Grzegorz Jaśkiewicz <gj(at)pointblue(dot)com(dot)pl> writes:

> Tom Lane pisze:
>> 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.
>
> shame, I just work on a thing that would benefit from index that could be used
> in OVERLAPS. I don't know psql internals , except for how GiST works, hence my
> question.

Ah, but the transformation given is actually a bit of a red herring. If you
look at the plan it's doing two bitmap index scans which together are actually
effectively doing a full index scan. The benefit comes from applying the full
overlap condition to the index tuples and only scanning the heap for matching
tuples. Presumably this index is much smaller than the table and/or cached in
memory so the random accesses are outweighed by the lower i/o.

This does raise the possibility that we should check for index scan paths if
we have selective enough columns even if the pathkeys aren't a prefix of the
index pathkeys. We would have to do a full index scan but the cost might still
be lower.

I think the reason we don't (aside from it not being at all useful in he past)
is that it would lead to a lot of possible index scans being considered.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!