pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedYet another slow join query..
2+ messages / 2 participants
[nested] [flat]
* Yet another slow join query..
@ 2003-07-18 12:51 Rajesh Kumar Mallah <mallah@trade-india.com>
2003-07-18 16:09 ` Re: Yet another slow join query.. Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Rajesh Kumar Mallah @ 2003-07-18 12:51 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Yet another slow join query..
2003-07-18 12:51 Yet another slow join query.. Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2003-07-18 16:09 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2003-07-18 16:09 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: 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?
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-07-18 16:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-18 12:51 Yet another slow join query.. Rajesh Kumar Mallah <mallah@trade-india.com>
2003-07-18 16:09 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox