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
----------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (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'::character varying) 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/