Efficient Query For Mapping IP Addresses To Country Code.

Hi folks,

the problem is to update one table by querying another.

i have a table where i store apache access logs where one of the fields is the host ip address.
i need to find corresponding country for all the ip addrresses.

for this i have another table that contains apnic,arin and ripe databases
in the form of:
      Table "ip_country_map" Column  |     Type     | Modifiers
----------+--------------+-----------start_ip | inet         |end_ip   | inet         |country  | character(2) |
Indexes: end_ip_idx,        start_ip_idx

I  need to update the accees log's country field by
searching the ip in ip_country_map for country.
i have follwing three alternatives , all seems to be slow.

1 st. (based on implicit join)
-------------
explain UPDATE access_log_2002_06_25 set country=ip_country_map.country where host_ip betweenip_country_map.start_ip
andip_country_map.end_ip; 

Nested Loop  (cost=0.00..1711037.55 rows=5428333 width=563) ->  Seq Scan on ip_country_map  (cost=0.00..1112.55
rows=48855width=70) ->  Seq Scan on access_log_2002_06_25  (cost=0.00..20.00 rows=1000 width=493) 
---------------

2nd (based on subselect)
---------------
explain UPDATE access_log_2002_06_25 set country=(select country from ip_country_map where
access_log_2002_06_25.host_ip 
between start_ip and end_ip);
NOTICE:  QUERY PLAN:
Seq Scan on access_log_2002_06_25  (cost=0.00..20.00 rows=1000 width=493) SubPlan   ->  Seq Scan on ip_country_map
(cost=0.00..1356.83rows=5428 width=6) 

EXPLAIN
----------------

3 rd (do not update country field at all just join both the table)
--------------------------------------------------------------------
explain SELECT  host_ip,ip_country_map.country from access_log_2002_06_25 join ip_country_map on
( host_ip between start_ip and end_ip) ;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..1711037.55 rows=5428333 width=102) ->  Seq Scan on ip_country_map  (cost=0.00..1112.55
rows=48855width=70) ->  Seq Scan on access_log_2002_06_25  (cost=0.00..20.00 rows=1000 width=32) 

EXPLAIN
--------------------------------------------------------------------

Yet Another option
----------------------------------------------------------------------
while loading access_log from file into database i do a select on ip_country_map.


also even a simple query like do not use indexes.

access_log=# explain  SELECT  country from ip_country_map where start_ip <= '203.196.129.1' and end_ip >=
'203.196.129.1';
NOTICE:  QUERY PLAN:

Seq Scan on ip_country_map  (cost=0.00..1356.83 rows=5428 width=6)

EXPLAIN
access_log=# explain SELECT  country from ip_country_map where '203.196.129.1' between start_ip and end_ip;
NOTICE:  QUERY PLAN:

Seq Scan on ip_country_map  (cost=0.00..1356.83 rows=5428 width=6)

EXPLAIN
access_log=#

IS THERE ANYTHING woring with my database schema?
how shud i be storing the the data of ipranges and
country for efficient utilization in this problem.




regds

Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.