From: Rajesh Kumar Mallah. <mallah@trade-india.com>
To: pgsql-sql@postgresql.org
Subject: Efficient Query For Mapping IP Addresses To Country Code.
Date: Wed, 26 Jun 2002 20:30:23 +0530
Message-ID: <200206262030.23395.mallah@trade-india.com> (raw)
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 between
ip_country_map.start_ip and ip_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=48855 width=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.83 rows=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=48855 width=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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah@trade-india.com
Subject: Re: Efficient Query For Mapping IP Addresses To Country Code.
In-Reply-To: <200206262030.23395.mallah@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox