Re: Yet another slow join query..

[Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>]

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?