Re: Yet another slow join query.. [ SOLVED ]

The Types of the join columns were different text vs varchar(100),
now its working fine and using a Hash Join

Thanks once again.
regds
mallah.



 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' ;                                                                  QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------
HashJoin  (cost=2806.09..3949.37 rows=28 width=92) (actual 
 time=183.05..326.52 rows=18285 loops=1)   Hash Cond: ("outer".city = "inner".city)
   ->  Index Scan using city_master_temp1 on city_master b
   (cost=0.00..854.87 rows=5603 width=24) (actual time=0.17..45.70
   rows=5603 loops=1)         Filter: (country = 'India'::character varying)
   ->  Hash  (cost=2805.65..2805.65 rows=178 width=68) (actual
   time=181.74..181.74 rows=0 loops=1)         ->  Seq Scan on updated_profiles a  (cost=0.00..2805.65 rows=178
         width=68) (actual time=20.53..149.66 rows=17537 loops=1)               Filter: ((source =
'BRANDING'::charactervarying) AND 
               (state IS NULL)) Total runtime: 348.50 msec
(8 rows)






> On Fri, 18 Jul 2003, Rajesh Kumar Mallah wrote:
>
>> 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)
>
> How many rows actually meet the filter conditions on updated_profiles
> and city_master?  Are the two city columns of the same type?



-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/