query optimization - Mailing list 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)

 

 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.

 

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