Re: GiST for range types (was Re: Range Types - typo + NULL string constructor)

On Mon, Jan 30, 2012 at 1:39 AM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> Thank you for the updates. I have a small patch attached.
>
> The only code change I made was very minor: I changed the constants used
> in the penalty function because your version used INFINITE_BOUND_PENALTY
> when adding an empty range, and that didn't quite make sense to me. If
> I'm mistaken you can leave it as-is.
>
> I also attached range-gist-test.sql, which I used for a performance
> test. I mix various types of ranges together in a larger table of 1.1M
> tuples. And then I create a smaller table that only contains normal
> ranges and empty ranges. There are two tests:
> 1. Create an index on the big table
> 2. Do a "range join" (using "overlaps" rather than "equals") where the
> smaller table is on the outer side of a nested loop join and an index
> scan over the larger table on the inner.
>
> The index creation time reduces by a small amount with the patch, from
> around 16s without the patch to around 13s with the patch. The query
> time, however, dropped from around 26s to around 14s! Almost 2x speedup
> with the patch!
>
> Moreover, looking at the loop timing in the explain analyze output, it
> goes from about "7..24" ms per loop down to about "1.5..13" ms per loop.
> That seems to indicate that the index distribution is better, with more
> queries returning quickly.
>
> So, great work Alexander! Very convincing results.
>
Great! Thank you for reviewing this patch!

> Marking "ready for committer", but please apply my comment fixes at your
> discretion.
>
Patch with your comment fixes is attached.

-----
With best regards,
Alexander Korotkov.