Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 8F770475E5B
	for <pgsql-sql@postgresql.org>; Wed, 29 May 2002 12:08:04 -0400 (EDT)
Received: from mta5.snet.net (mta5.snet.net [204.60.203.77])
	by postgresql.org (Postfix) with ESMTP id 48C18475C21
	for <pgsql-sql@postgresql.org>; Wed, 29 May 2002 12:06:10 -0400 (EDT)
Received: from pop.snet.net (pop.snet.net [204.60.203.72])
	by mta5.snet.net (8.12.3/8.12.3/SNET-mx-1.2/D-1.1.1.1/O-1.1.1.1) with
	ESMTP id g4TG3bW7017531
	for <pgsql-sql@postgresql.org>; Wed, 29 May 2002 12:03:37 -0400 (EDT)
X-Originating-IP: [64.252.95.167]
X-SBCIS-MTA: [pop.snet.net]
Received: from [192.168.1.83] (167.95.252.64.snet.net [64.252.95.167])
	by pop.snet.net (8.12.3/8.12.3/SNET-pop-1.2/D-1.1.1.1/O-1.1.1.1) with
	ESMTP id g4TG5qUQ016154
	for <pgsql-sql@postgresql.org>; Wed, 29 May 2002 12:05:52 -0400 (EDT)
Subject: Can you help with this JOIN?
From: Wei Weng <wweng@kencast.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Ximian Evolution 1.0.3 
Date: 29 May 2002 12:06:14 -0400
Message-Id: <1022688374.26733.5.camel@Monet>
Mime-Version: 1.0
X-Archive-Number: 200205/303
X-Sequence-Number: 7938

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.