[GENERAL] slow inet within cidr query

[Edwin Grubbs]

Under postgres 8.1, the "<<=" comparison yields very slow queries with large
tables. I can rewrite the query without the "<<=" operator by generating all
33 possible netmasks (0 through 32) for a given IP. This ugly rewrite runs
about 12 times faster (6 seconds versus 0.5 seconds). Be aware that EXPLAIN
ANALYZE seems to be run a different query plan since the first query runs
even slower with EXPLAIN ANALYZE. Setting enable_seqscan did not improve the
query speed.

-Edwin

Here is the original query:
------------------------------------------------------------------------
SELECT ip_address, ip_block
FROM ip_inventory
   JOIN route ON ip_address  <<= ip_block
WHERE ip_address < '1.5.0.0'
ORDER BY ip_address;
------------------------------------------------------------------------

Here is the rewritten query:
------------------------------------------------------------------------
SELECT ip_address, ip_block
from ip_inventory
   JOIN route
       ON ip_block IN (
               network(ip_address),
               network((host(ip_address) || '/31')::inet),
               network((host(ip_address) || '/30')::inet),
               network((host(ip_address) || '/29')::inet),
               network((host(ip_address) || '/28')::inet),
               network((host(ip_address) || '/27')::inet),
               network((host(ip_address) || '/26')::inet),
               network((host(ip_address) || '/25')::inet),
               network((host(ip_address) || '/24')::inet),
               network((host(ip_address) || '/23')::inet),
               network((host(ip_address) || '/22')::inet),
               network((host(ip_address) || '/21')::inet),
               network((host(ip_address) || '/20')::inet),
               network((host(ip_address) || '/19')::inet),
               network((host(ip_address) || '/18')::inet),
               network((host(ip_address) || '/17')::inet),
               network((host(ip_address) || '/16')::inet),
               network((host(ip_address) || '/15')::inet),
               network((host(ip_address) || '/14')::inet),
               network((host(ip_address) || '/13')::inet),
               network((host(ip_address) || '/12')::inet),
               network((host(ip_address) || '/11')::inet),
               network((host(ip_address) || '/10')::inet),
               network((host(ip_address) || '/9')::inet),
               network((host(ip_address) || '/8')::inet),
               network((host(ip_address) || '/7')::inet),
               network((host(ip_address) || '/6')::inet),
               network((host(ip_address) || '/5')::inet),
               network((host(ip_address) || '/4')::inet),
               network((host(ip_address) || '/3')::inet),
               network((host(ip_address) || '/2')::inet),
               network((host(ip_address) || '/1')::inet),
               '0.0.0.0'::cidr
               )
WHERE ip_address < '1.5.0.0'
ORDER BY ip_address;
------------------------------------------------------------------------

Here is the SQL for creating and populating the test tables:
------------------------------------------------------------------------
BEGIN;

CREATE TABLE range (value integer);
COPY range FROM STDIN;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
\.

CREATE TABLE ip_inventory (ip_address inet unique);

-- add 279841 ips
INSERT INTO ip_inventory
SELECT (a || '.' || b || '.' || c || '.' || d)::inet
FROM range AS w(a)
   JOIN range AS x(b) ON TRUE
   JOIN range AS y(c) ON TRUE
   JOIN range AS z(d) ON TRUE;

CREATE TABLE route (ip_block cidr unique);

-- add 12167 routes
INSERT INTO route
SELECT (a || '.' || b || '.' || c || '.0/24')::cidr
FROM range AS w(a)
   JOIN range AS x(b) ON TRUE
   JOIN range AS y(c) ON TRUE;

COMMIT;
------------------------------------------------------------------------