Thread: Indexed access for INET/ CIDR datatype
Is there a way to index the >>= or <<= operators for CIDR/ INET datatypes? I built a btree index, which works for the = clause, but not the others.
Ananth Kaitharam
On Tue, Jun 17, 2003 at 08:25:04 -0400, "Kaitharam, Ananth" <AKaitharam@gomez.com> wrote: > Is there a way to index the >>= or <<= operators for CIDR/ INET datatypes? I > built a btree index, which works for the = clause, but not the others. I don't think so. Those operators aren't total orders and I think btree will only work with total orders. I expect that it should be possible to develop rtree or gist indexes though.
Bruno Wolff III <bruno@wolff.to> writes: > On Tue, Jun 17, 2003 at 08:25:04 -0400, > "Kaitharam, Ananth" <AKaitharam@gomez.com> wrote: >> Is there a way to index the >>= or <<= operators for CIDR/ INET datatypes? I >> built a btree index, which works for the = clause, but not the others. > I don't think so. There are some provisions for turning <<= tests into range scans, for example regression=# create table foo (f1 inet unique); NOTICE: CREATE TABLE / UNIQUE will create implicit index 'foo_f1_key' for table 'foo' CREATE TABLE regression=# explain select * from foo where f1 <<= '127.0/16'; QUERY PLAN -------------------------------------------------------------------------------- Index Scan using foo_f1_key on foo (cost=0.00..17.07 rows=500 width=32) Index Cond: ((f1 >= '127.0.0.0/16'::inet) AND (f1 <= '127.0.255.255'::inet)) Filter: (f1 <<= '127.0.0.0/16'::inet) (3 rows) I believe you have to write "field <<= constant" to get this to happen. regards, tom lane
On Thu, Jun 19, 2003 at 14:27:46 -0400, "Kaitharam, Ananth" <AKaitharam@gomez.com> wrote: > > I was hoping that, by using the INET datatype, I could do lookups of > individual IPs against a range efficiently, but that doesn't seem to be the > case. This just came up today in another thread and the answer is that if you use a btree index a range scan over the index will be used.
Thanks Mark, so creating a unique constraint, as opposed to a unique index makes a difference to the query plan. I noticed one thing though :
My table has an entry like '24.128.168.0/23' in the INET datatype field. I'm trying to do a lookup for '24.128.168.61' which is contained in the above:
explain select * from iptest2 where ip >>= '24.128.168.61'
yields
Seq Scan on iptest2 (cost=0.00..22.50 rows-500 width=32)
Filter (ip >>='24.128.168.61'::inet)
I was hoping that, by using the INET datatype, I could do lookups of individual IPs against a range efficiently, but that doesn't seem to be the case.
If I try to create a rtree index, I get the error :
Number: -2147467259
Description: ERROR: data type inet has no default operator class for access method "rtree". You must specify an operator class for the index or define a default operator clas for the data type
Any idea how to get around that?
Thanks and regards
Ananth
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, June 19, 2003 1:58 PM
To: Bruno Wolff III
Cc: Kaitharam, Ananth; 'pgsql-novice@postgresql.org'
Subject: Re: [NOVICE] Indexed access for INET/ CIDR datatype
Bruno Wolff III <bruno@wolff.to> writes:
> On Tue, Jun 17, 2003 at 08:25:04 -0400,
> "Kaitharam, Ananth" <AKaitharam@gomez.com> wrote:
>> Is there a way to index the >>= or <<= operators for CIDR/ INET datatypes? I
>> built a btree index, which works for the = clause, but not the others.
> I don't think so.
There are some provisions for turning <<= tests into range scans,
for example
regression=# create table foo (f1 inet unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'foo_f1_key' for table 'foo'
CREATE TABLE
regression=# explain select * from foo where f1 <<= '127.0/16';
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using foo_f1_key on foo (cost=0.00..17.07 rows=500 width=32)
Index Cond: ((f1 >= '127.0.0.0/16'::inet) AND (f1 <= '127.0.255.255'::inet))
Filter: (f1 <<= '127.0.0.0/16'::inet)
(3 rows)
I believe you have to write "field <<= constant" to get this to happen.
regards, tom lane