Обсуждение: index for inet and >> (contains) function

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

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