Skip site navigation (1) Skip section navigation (2)

query optimization

From: Saranya Sivakumar <sarlavk(at)yahoo(dot)com>
To: pgsqlnovice <pgsql-novice(at)postgresql(dot)org>
Subject: query optimization
Date: 2005-08-25 20:25:01
Message-ID: 20050825202502.72805.qmail@web51308.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi all,
 
I have this query that I am trying to optimize.
 
SELECT c.countryid, r.regionid FROM ip_g ip LEFT OUTER JOIN country c ON (CASE WHEN ip.country='rom' THEN 'ROU' ELSE upper(ip.country) END)=c.iso3 LEFT OUTER JOIN region r ON r.countryid=c.countryid AND r.code= substring (upper(ip.region) from 1 for 2) WHERE '68.224.117.161' BETWEEN start_ip AND end_ip AND c.countryid IS NOT NULL LIMIT 1;
 
Limit  (cost=0.00..166.40 rows=1 width=36)
   ->  Nested Loop  (cost=0.00..99944595.79 rows=600623 width=36)
         Join Filter: (("inner".countryid = "outer".countryid) AND (("inner".code)::text = "substring"(upper(("outer".region)::text), 1, 2)))
         ->  Nested Loop  (cost=0.00..7658814.22 rows=600623 width=24)
               Join Filter: (CASE WHEN ("outer".country = 'rom'::character varying) THEN 'ROU'::text ELSE upper(("outer".country)::text) END = ("inner".iso3)::text)
               Filter: ("inner".countryid IS NOT NULL)
               ->  Index Scan using ip_g_start_idx on ip_g ip  (cost=0.00..25437.14 rows=524180 width=13)
                     Index Cond: ('83.228.96.46'::inet >= start_ip)
                     Filter: ('83.228.96.46'::inet <= end_ip)
               ->  Seq Scan on country c  (cost=0.00..9.75 rows=275 width=11)
         ->  Seq Scan on region r  (cost=0.00..74.55 rows=3955 width=12)

 
ip_g table has about 2200000 entries. This table was vacuumed before running the query. The table structure is as below.
 start_ip     | inet                  | 
 end_ip       | inet                  | 
 country      | character varying(3)  | 
 region       | character varying(20) | 
 
 Indexes: ip_g_start_end_idx unique btree (start_ip, end_ip),
         ip_g_countries_idx btree (upper(country)),
         ip_g_start_idx btree (start_ip)

region table has 4000 entries. Structure as follows
 
 regionid  | integer
 countryid | smallint 
 region    | character varying(45) 
 code      | character varying(8)
 adm1code  | character(4)
 adjacent  | character varying(40) 

Indexes: region_pkey primary key btree (regionid),
         region_code_idx btree (code),
         region_countryid_idx btree (countryid)

Even though I have these indices, the query doesnt use any of them.
 
Country table has 270-280 entries. Structure is as follows:
countryid           | integer                 
country             | character varying(128)
fips104             | character varying(2)
iso2                | character varying(2)
iso3                | character varying(3)
ison                | character varying(3)     
internet            | character varying(2)    
capital             | character varying(25)   
mapreference        | character varying(50)   
nationalitysingular | character varying(35)    
nationalityplural   | character varying(35)    
currency            | character varying(30)    
currencycode        | character varying(3)    
population          | integer                
title               | character varying(50)   
comment             | character varying(2048)  

Indexes: countries_pkey primary key btree (countryid),
         countries_iso3_idx btree (iso3)

 
We use this query very frequently in our application. Hence we need to keep it as fast as possible. Even though I have the indices, the query is not using it. When I force index scan to on, the query uses the indices as shown below
 
 Limit  (cost=0.00..243.10 rows=1 width=36)
   ->  Nested Loop  (cost=0.00..146011051.91 rows=600623 width=36)
         Join Filter: (("inner".countryid = "outer".countryid) AND (("inner".code)::text = "substring"(upper(("outer".region)::text), 1, 2)))
         ->  Nested Loop  (cost=0.00..8894512.31 rows=600623 width=24)
               Join Filter: (CASE WHEN ("outer".country = 'rom'::character varying) THEN 'ROU'::text ELSE upper(("outer".country)::text) END = ("inner".iso3)::text)
               Filter: ("inner".countryid IS NOT NULL)
               ->  Index Scan using ip_g_start_idx on ip_g ip  (cost=0.00..25437.14 rows=524180 width=13)
                     Index Cond: ('83.228.96.46'::inet >= start_ip)
                     Filter: ('83.228.96.46'::inet <= end_ip)
               ->  Index Scan using countries_pkey on country c  (cost=0.00..12.11 rows=275 width=11)
         ->  Index Scan using regions_countryid_idx on region r  (cost=0.00..149.19 rows=3955 width=12)

Is there any other way to optimize the query other than forcing index scan?
I appreciate any input on this.
 
Thanks,
Saranya

		
---------------------------------
 Start your day with Yahoo! - make it your home page 

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2005-08-25 21:08:55
Subject: Re: query optimization
Previous:From: Mike G.Date: 2005-08-25 20:11:03
Subject: Superuser can execute but not view function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group