Yet another slow join query..

[Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>]

Hi All,

data_bank.updated_profiles and public.city_master are small tables
with 21790 and 49303 records repectively. both have indexes on the join 
column. in first one on (city,source) and in second one on (city)

The query below does not return for long durations > 10 mins.

explain analyze  select b.state,a.city from data_bank.updated_profiles a join 
public.city_master b using(city)  where source='BRANDING' and a.state is NULL 
and b.country='India' ;


simple explain returns below.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Nested Loop  (cost=0.00..83506.31 rows=14 width=35)
  Join Filter: ("outer".city = ("inner".city)::text)
  ->  Seq Scan on updated_profiles a  (cost=0.00..1376.39 rows=89 width=11)
        Filter: ((source = 'BRANDING'::character varying) AND (state IS NULL))
  ->  Index Scan using city_master_temp1 on city_master b  (cost=0.00..854.87 
rows=5603 width=24)
        Filter: (country = 'India'::character varying)
(6 rows)

-----------------------------------------


Any help is appreciated.


Regds
mallah.