Mailbox-Line: From mallah@trade-india.com  Wed Jun 26 11:30:57 2002
Content-Type: text/plain;
  charset="us-ascii"
From: "Rajesh Kumar Mallah." <mallah@trade-india.com>
Organization: Infocom Network Limited.
To: pgsql-sql@postgresql.org
Subject: Efficient Query For Mapping IP Addresses To Country Code.
Date: Wed, 26 Jun 2002 20:30:23 +0530
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200206262030.23395.mallah@trade-india.com>

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=3Dip_country_map.country w=
here host_ip between
 ip_country_map.start_ip and ip_country_map.end_ip;

Nested Loop  (cost=3D0.00..1711037.55 rows=3D5428333 width=3D563)
  ->  Seq Scan on ip_country_map  (cost=3D0.00..1112.55 rows=3D48855 width=
=3D70)
  ->  Seq Scan on access_log_2002_06_25  (cost=3D0.00..20.00 rows=3D1000 wi=
dth=3D493)
---------------

2nd (based on subselect)
---------------
explain UPDATE access_log_2002_06_25 set country=3D(select country from ip_=
country_map where access_log_2002_06_25.host_ip=20
between start_ip and end_ip);
NOTICE:  QUERY PLAN:
Seq Scan on access_log_2002_06_25  (cost=3D0.00..20.00 rows=3D1000 width=3D=
493)
  SubPlan
    ->  Seq Scan on ip_country_map  (cost=3D0.00..1356.83 rows=3D5428 width=
=3D6)

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 j=
oin ip_country_map on
( host_ip between start_ip and end_ip) ;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=3D0.00..1711037.55 rows=3D5428333 width=3D102)
  ->  Seq Scan on ip_country_map  (cost=3D0.00..1112.55 rows=3D48855 width=
=3D70)
  ->  Seq Scan on access_log_2002_06_25  (cost=3D0.00..20.00 rows=3D1000 wi=
dth=3D32)

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

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


also even a simple query like do not use indexes.

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

Seq Scan on ip_country_map  (cost=3D0.00..1356.83 rows=3D5428 width=3D6)

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

Seq Scan on ip_country_map  (cost=3D0.00..1356.83 rows=3D5428 width=3D6)

EXPLAIN
access_log=3D#

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.