Skip site navigation (1) Skip section navigation (2)

Re: Indexed access for INET/ CIDR datatype

From: "Kaitharam, Ananth" <AKaitharam(at)gomez(dot)com>
To: 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: "'pgsql-novice(at)postgresql(dot)org'" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Indexed access for INET/ CIDR datatype
Date: 2003-06-19 18:27:46
Message-ID: CC17A70CE630D7119C520090278444C81248B7@mail1.gomez.com (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)sss(dot)pgh(dot)pa(dot)us] 
Sent: Thursday, June 19, 2003 1:58 PM
To: Bruno Wolff III
Cc: Kaitharam, Ananth; 'pgsql-novice(at)postgresql(dot)org'
Subject: Re: [NOVICE] Indexed access for INET/ CIDR datatype 

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> On Tue, Jun 17, 2003 at 08:25:04 -0400,
>   "Kaitharam, Ananth" <AKaitharam(at)gomez(dot)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

Responses

pgsql-novice by date

Next:From: Bruno Wolff IIIDate: 2003-06-19 18:49:42
Subject: Re: Indexed access for INET/ CIDR datatype
Previous:From: Tom LaneDate: 2003-06-19 17:57:57
Subject: Re: Indexed access for INET/ CIDR datatype

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group