index for inet and >> (contains) function

From: Richard Jones <rich(at)annexia(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: index for inet and >> (contains) function
Date: 2006-03-22 16:35:35
Message-ID: 20060322163535.GA17175@furbychan.cocan.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Jones 2006-03-22 16:40:27 ANNOUNCE: Type-safe interface to PostgreSQL
Previous Message Andrus 2006-03-22 16:11:41 Re: How to release locks