Subject: Re: Can you help with this JOIN?
From: Wei Weng <wweng@kencast.com>
To: pgsql-sql@postgresql.org
In-Reply-To: <1022688374.26733.5.camel@Monet>
References: <1022688374.26733.5.camel@Monet>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 29 May 2002 12:21:22 -0400
Message-Id: <1022689283.26718.9.camel@Monet>
Mime-Version: 1.0

One question is: It appears to be using Sequential scan on tables no
matter how big it is. I tried to set enable_seqscan='false' and then
vacuum analyze, but the result is even worse.

Isn't index scan supposed to be faster/better than sequential scan for
large tables? One table (contentsummaries) has 11000 entries and another
one (contentattributes) has 33005 entries.

Thanks


On Wed, 2002-05-29 at 12:06, Wei Weng wrote:
> I have a massive join that takes something like 10 seconds to execute in
> Postgresql 7.2. Can any of gurus here help me improve it?
> 
> It is as follows:
> 
> SELECT DISTINCT cs.ContentID AS cscid, ct.Name AS ctname, pb.Name AS
> pbname, cs.Author AS author, cs.CreationDate AS fromtime, cs.ExpireDate
> AS totime, ct.Name AS media 
> FROM 
> ContentSummaries AS cs JOIN ContentTypes AS ct ON (ct.ContentTypeID =
> cs.ContentTypeID) 
> JOIN ContentAttributes AS cab ON (cab.ContentID = cs.ContentID) 
> JOIN Attributes AS ab ON (ab.AttributeID = cab.AttributeID) 
> JOIN Categories AS cat ON (cat.CategoryID = cs.CategoryID) 
> LEFT OUTER JOIN Publishers AS pb ON (pb.PublisherID = cs.PublisherID)
> WHERE cs.CreationDate IS NOT NULL
> 
> I already created indexes on all possible fields in those tables.
> 
> the query plan:
> 
> 
> Limit  (cost=3170.75..3173.00 rows=15 width=172)
>   ->  Unique  (cost=3170.75..3203.77 rows=220 width=172)
>         ->  Sort  (cost=3170.75..3170.75 rows=2201 width=172)
>               ->  Nested Loop  (cost=611.64..3048.54 rows=2201
> width=172)
>                     ->  Hash Join  (cost=611.64..3021.02 rows=2201
> width=148)
>                           ->  Hash Join  (cost=610.61..2931.93 rows=2201
> width=136)
>                                 ->  Hash Join  (cost=609.44..2842.53
> rows=2201 width=124)
>                                       ->  Seq Scan on contentattributes
> cab  (cost=0.00..867.05 rows=33005 width=24)
>                                       ->  Hash  (cost=600.27..600.27
> rows=3667 width=100)
>                                             ->  Hash Join 
> (cost=1.02..600.27 rows=3667 width=100)
>                                                   ->  Seq Scan on
> contentsummaries cs  (cost=0.00..452.52 rows=3667 width=76)
>                                                   ->  Hash 
> (cost=1.02..1.02 rows=2 width=24)
>                                                         ->  Seq Scan on
> contenttypes ct  (cost=0.00..1.02 rows=2 width=24)
>                                 ->  Hash  (cost=1.14..1.14 rows=14
> width=12)
>                                       ->  Seq Scan on attributes ab 
> (cost=0.00..1.14 rows=14 width=12)
>                           ->  Hash  (cost=1.02..1.02 rows=2 width=12)
>                                 ->  Seq Scan on categories cat 
> (cost=0.00..1.02 rows=2 width=12)
>                     ->  Seq Scan on publishers pb  (cost=0.00..0.00
> rows=1 width=24)
> 
> 
> Thanks in advance. I had been really scratching my head for this one.
> 
> 
> -- 
> Wei Weng
> Network Software Engineer
> KenCast Inc.
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 
-- 
Wei Weng
Network Software Engineer
KenCast Inc.