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