On Mon, Jan 30, 2012 at 1:39 AM, Jeff Davis <pgsql@j-davis.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.
