Обсуждение: 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