Re: index for inet and >> (contains) function

Depending on the types of queries you need to do, maybe you could kludge it
up for speed by doing something like adding another column (or two) that
can be easily indexed, and whose values can be derived from the existing
data. You could then use the indexed column to narrow down the result set.

For example:

create table iptocountry (
network inet not null,
countryid int not null references countries (id),
firstpart varchar(20) not null
);

create index inx_fp on iptocountry (firstpart);

INSERT into iptocountry (network, countryid, firstpart ) values
('192.2.3.4', 1, '192.2');
INSERT into iptocountry (network, countryid, firstpart ) values
('192.2.3.5', 1, '192.2');
etc.

explain analyze
select countryid from iptocountry where network >> '192.2.3.4';

Seq Scan on iptocountry (cost=0.00..22.50 rows=500 width=4) (actual
time=6.977..6.977 rows=0 loops=1)
Filter: (network >> '192.2.3.4'::inet)
Total runtime: 7.020 ms

explain analyze
select countryid from iptocountry where firstpart = '192.2' and network >>
'192.2.3.4';
Index Scan using inx_fp on iptocountry (cost=0.00..17.08 rows=3 width=4)
(actual time=0.065..0.065 rows=0 loops=1)
Index Cond: ((firstpart)::text = '192.2'::text)
Filter: (network >> '192.2.3.4'::inet)
Total runtime: 0.116 ms

I was using a fairly small sample data set (11398 rows), but you get the
idea.

Susan

Richard Jones
<rich(at)annexia(dot)org> To: pgsql-general(at)postgresql(dot)org
Sent by: cc:
Subject: [GENERAL] index for inet and >> (contains) function

pgsql-general-owner(at)pos |-------------------|
tgresql.org | [ ] Expand Groups |
|-------------------|

03/22/2006 08:35
AM

I've got a table like this:

create table iptocountry (
network inet not null,
countryid int not null references countries (id)
);

The idea is that it contains mappings from IP address ranges to
countries, something like this:

insert into iptocountry values ('1.2.3.0/24', 33);

It contains a lot of rows (some 8 million, taken from hostip.info).
Unfortunately when I use the "contains" function (>>) I get a
sequential scan, as in:

select countryid from iptocountry where network >> '1.2.3.4';

Is there a suitable index that I can put on the network field to fix
this?

Rich.

--
Richard Jones, CTO Merjis Ltd.
Merjis - web marketing and technology - http://merjis.com
Team Notepad - intranets and extranets for business -
http://team-notepad.com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------