Обсуждение: Yet another slow join query..

Поиск
Список
Период
Сортировка

Yet another slow join query..

От
Rajesh Kumar Mallah
Дата:

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.

Re: Yet another slow join query..

От
Stephan Szabo
Дата:
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?

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/