Re: GiST support for inet datatypes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: emre(at)hasegeli(dot)com
Cc: Andreas Karlsson <andreas(at)proxel(dot)se>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Florian Pflug <fgp(at)phlo(dot)org>, Andres Freund <andres(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: Re: GiST support for inet datatypes
Date: 2014-04-08 04:52:14
Message-ID: 12597.1396932734@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Emre Hasegeli <emre(at)hasegeli(dot)com> writes:
> 2014-04-04 18:34, Andreas Karlsson <andreas(at)proxel(dot)se>:
>> The patch looks good but it does not apply anymore against master. If you
>> could fix that and the duplicate OIDs I think this is ready for a committer.

> Rebased version attached. I am marking it as Ready for Committer.

I've been hacking on this patch over the weekend. I wasn't that thrilled
with the design of the index representation: it seemed to me that by
allowing both the minimum netmask width and the number of common address
bits to limit what you store in union values, you were giving up a lot.
I tried recoding things to separate netmask width from number of common
bits. Initially I'd thought that this would allow for smarter
tree-descent logic in the consistent function, but after several failures
I realized that that was more easily said than done. (I've not totally
lost hope about it, but it's not easy given the existing inet comparison
rules.) Nonetheless, I found that doing it like this led to substantially
faster index searches --- better than two-to-one in many cases, on random
data as per the attached test script. I believe the reason is that
decoupling netmask and common address bits makes the picksplit function
more effective at choosing good splits.

I also thought that we should at least put in dummy selectivity functions
for the now-indexable inet operators. This will allow real selectivity
functions to be patched in without forcing initdb. It's probably unlikely
that we'd back-patch your selectivity-function patch into 9.4, but why
foreclose the option?

So attached is an updated patch with these things taken care of. It's
still not quite ready to commit (in particular I've not looked at the
documentation changes yet), but if anyone wants to try to break it or
do their own performance testing, now's the time.

I also attach the script I was using for testing. It runs too long
to be a plausible candidate for the regression tests, but perhaps
someone else would like to use it anyway. There's a test that makes
sure that index searches get the same answers as the base operators
over a bunch of random data, and then a bunch of straight queries that
can be timed for performance testing. I thought that maybe I was
overemphasizing the problem of trimming common address bits to the
minimum netmask width, so the performance part of the script also tests
probes into a table that has a uniform netmask width of 32. (It still
wins to do it like this.)

Comments?

regards, tom lane

Attachment Content-Type Size
inet-gist-v6.patch text/x-diff 58.2 KB
inet-index-test.sql text/plain 5.1 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stefan Keller 2014-04-08 06:28:09 Re: PostgreSQL Columnar Store for Analytic Workloads
Previous Message Amit Kapila 2014-04-08 04:27:00 Re: four minor proposals for 9.5