Re: Yet another slow join query.. [ SOLVED ]
От | |
---|---|
Тема | Re: Yet another slow join query.. [ SOLVED ] |
Дата | |
Msg-id | 1062.219.65.236.165.1058550070.squirrel@mail.trade-india.com обсуждение исходный текст |
Ответ на | Re: Yet another slow join query.. (Stephan Szabo <sszabo@megazone.bigpanda.com>) |
Список | pgsql-performance |
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/
В списке pgsql-performance по дате отправления: