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

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