Обсуждение: inet <<= and indexes

I have a table with about 60,000 ips, and postgres will use the index to
find the ips in a subnet if I type in a literal cidr block
(e.g. address <<= '99.88.5.0'), but it will not use the index if the cidr
block is in a variable (e.g. address <<= block). I have tried setting
ENABLE_SEQSCAN to false and using different values for ALTER TABLE...SET
STATISTICS, but it still uses sequential scans.

Here are several example EXPLAIN results. At the bottom, I have included a
simple python script which I used to set up the test environment.

-Edwin Grubbs

# psql egrubbs
egrubbs=> \d ip
         Table "ip"
 Column  | Type | Modifiers
---------+------+-----------
 address | inet |
Unique keys: ip_address_index

egrubbs=> \d network
      Table "network"
 Column | Type | Modifiers
--------+------+-----------
 block  | cidr |

egrubbs=> explain select * from ip where address <<= '99.88.5.0/24';
NOTICE:  QUERY PLAN:

Index Scan using ip_address_index on ip  (cost=0.00..150.39 rows=32768
width=12)
EXPLAIN
egrubbs=> explain select * from ip where address <<= (select inet
'99.88.5.0/24');
NOTICE:  QUERY PLAN:

Seq Scan on ip  (cost=0.00..1205.20 rows=32768 width=12)
  InitPlan
    ->  Result  (cost=0.00..0.01 rows=1 width=0)

EXPLAIN
egrubbs=> explain select * from ip where address <<= (select cidr
'99.88.5.0/24');
NOTICE:  QUERY PLAN:

Seq Scan on ip  (cost=0.00..1205.20 rows=32768 width=12)
  InitPlan
    ->  Result  (cost=0.00..0.01 rows=1 width=0)

EXPLAIN
egrubbs=> explain select * from ip join network on address <<= block;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..3722.14 rows=65536 width=24)
  ->  Seq Scan on network  (cost=0.00..1.02 rows=2 width=12)
  ->  Seq Scan on ip  (cost=0.00..1041.36 rows=65536 width=12)

EXPLAIN
egrubbs=> explain select * from ip, network where address <<= block;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..3722.14 rows=65536 width=24)
  ->  Seq Scan on network  (cost=0.00..1.02 rows=2 width=12)
  ->  Seq Scan on ip  (cost=0.00..1041.36 rows=65536 width=12)

EXPLAIN
egrubbs=> \q

------------- python script for setting up test data ---------------
#!/usr/bin/python2.2

import psycopg

db = psycopg.connect('dbname=egrubbs')
db.autocommit()
cursor = db.cursor()

cursor.execute('''CREATE TABLE ip (address inet)''')

for i in range(0, 256):
    print i
    for j in range(0, 256):
        cursor.execute('''
            INSERT INTO ip (address)
            VALUES ('99.88.%d.%d')
            ''' % (i, j))

cursor.execute('''CREATE UNIQUE INDEX ip_address_index ON ip (address)''')

cursor.execute('''CREATE TABLE network (block cidr)''')
cursor.execute('''
    INSERT INTO network (block)
    VALUES ('99.88.5.0/24')
    ''')
cursor.execute('''
    INSERT INTO network (block)
    VALUES ('99.88.12.0/24')
    ''')

cursor.execute('''VACUUM ANALYZE ip''')
cursor.execute('''VACUUM ANALYZE network''')
------------------- end of script ----------------------------

Edwin Grubbs <egrubbs@rackspace.com> writes:
> I have a table with about 60,000 ips, and postgres will use the index to
> find the ips in a subnet if I type in a literal cidr block
> (e.g. address <<= '99.88.5.0'), but it will not use the index if the cidr
> block is in a variable (e.g. address <<= block).

Yup, the present implementation of that optimization depends on having
a constant to compare to.  Sorry ...

            regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) [020806 22:47]:
> Edwin Grubbs <egrubbs@rackspace.com> writes:
> > I have a table with about 60,000 ips, and postgres will use the index to
> > find the ips in a subnet if I type in a literal cidr block
> > (e.g. address <<= '99.88.5.0'), but it will not use the index if the cidr
> > block is in a variable (e.g. address <<= block).
>
> Yup, the present implementation of that optimization depends on having
> a constant to compare to.  Sorry ...

Is there a todo list someplace, or a bug system I can put this in,
so it'll get fixed?

Ciao!

--
Tuco: (reading a letter) See you soon idi... idi
Blondie: Idiots. It's for you.
        (The Good, The Bad, and The Ugly)

The Doctor What: <fill in the blank>             http://docwhat.gerf.org/
docwhat@gerf.org                                                   KF6VNC

Yes, we have a TODO list.  I will add this:

  * Allow INET subnet tests with non-constants

---------------------------------------------------------------------------

The Doctor What wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) [020806 22:47]:
> > Edwin Grubbs <egrubbs@rackspace.com> writes:
> > > I have a table with about 60,000 ips, and postgres will use the index to
> > > find the ips in a subnet if I type in a literal cidr block
> > > (e.g. address <<= '99.88.5.0'), but it will not use the index if the cidr
> > > block is in a variable (e.g. address <<= block).
> >
> > Yup, the present implementation of that optimization depends on having
> > a constant to compare to.  Sorry ...
>
> Is there a todo list someplace, or a bug system I can put this in,
> so it'll get fixed?
>
> Ciao!
>
> --
> Tuco: (reading a letter) See you soon idi... idi
> Blondie: Idiots. It's for you.
>         (The Good, The Bad, and The Ugly)
>
> The Doctor What: <fill in the blank>             http://docwhat.gerf.org/
> docwhat@gerf.org                                                   KF6VNC
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026