pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Yet 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>
  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 16:09  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Rajesh Kumar Mallah <mallah@trade-india.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