Re: Indexed access for INET/ CIDR datatype - Mailing list pgsql-novice

From Tom Lane
Subject Re: Indexed access for INET/ CIDR datatype
Date
Msg-id 10102.1056045477@sss.pgh.pa.us
Whole thread Raw
In response to Re: Indexed access for INET/ CIDR datatype  (Bruno Wolff III <bruno@wolff.to>)
List pgsql-novice
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

pgsql-novice by date:

Previous
From: "Knut P. Lehre"
Date:
Subject: returning recordsets/cursors in plpgsql 7.2.1
Next
From: Bruno Wolff III
Date:
Subject: Re: Indexed access for INET/ CIDR datatype