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.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
