index for inet and >> (contains) function

Richard Jones <rich@annexia.org> writes:

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?

Not at the moment :-(. I think it might be possible to write a GIST
opclass that could handle this, but there's not one there today.

regards, tom lane

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@annexia.org> To: pgsql-general@postgresql.org
Sent by: cc:
Subject: [GENERAL] index for inet and >> (contains) function

pgsql-general-owner@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
----------------------------------------------------------------------------------------------

On Mar 23, 2006, at 1:35 , Richard Jones wrote:

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?

Have you taken a look at the ip4r pgfoundry project?

http://pgfoundry.org/projects/ip4r/

From the README:

Firstly and most importantly, the builtin types have no support for
index lookups of the form (column >>= parameter), i.e. where you have
a table of IP address ranges and wish to find which ones include a
given IP address. This requires an rtree or gist index to do
efficiently, and also requires a way to represent IP address ranges
that do not fall precisely on CIDR boundaries.

ip4r is intended for IPv4 addresses (rather than both IPv4 and IPv6),
so if you're don't need IPv6, it might work well for you.

Michael Glaesemann
grzm myrealbox com