FAQ
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

Search Discussions

  • Bruno Wolff III at Jun 19, 2003 at 4:57 pm

    On Tue, Jun 17, 2003 at 08:25:04 -0400, "Kaitharam, Ananth" 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.
  • Tom Lane at Jun 19, 2003 at 5:58 pm

    Bruno Wolff III writes:
    On Tue, Jun 17, 2003 at 08:25:04 -0400,
    "Kaitharam, Ananth" 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
  • Kaitharam, Ananth at Jun 19, 2003 at 6:27 pm
    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
    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" 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
  • Bruno Wolff III at Jun 19, 2003 at 6:46 pm

    On Thu, Jun 19, 2003 at 14:27:46 -0400, "Kaitharam, Ananth" 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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 17, '03 at 12:25p
activeJun 19, '03 at 6:46p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase