From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Rajesh Kumar Mallah <mallah(at)trade-india(dot)com> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Yet another slow join query.. |
Date: | 2003-07-18 16:09:31 |
Message-ID: | 20030718090729.Y95696-100000@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Scheit | 2003-07-18 16:20:55 | Re: Sanity check requested |
Previous Message | Vincent van Leeuwen | 2003-07-18 16:00:43 | Re: Sanity check requested |