Subject: Re: Can you help with this JOIN?
From: Wei Weng <wweng@kencast.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20020529100354.M33333-100000@megazone23.bigpanda.com>
References: <20020529100354.M33333-100000@megazone23.bigpanda.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 30 May 2002 13:16:58 -0400
Message-Id: <1022779024.32670.7.camel@Monet>
Mime-Version: 1.0

But how do I write the LEFT OUTER JOIN if I don't do explicit JOINs on
the other tables? Can I just do

SELECT x FROM a, b, c, LEFT OUTER JOIN d ON (d.id = a.id)?


On Wed, 2002-05-29 at 13:18, Stephan Szabo wrote:
> 
> On 29 May 2002, Wei Weng wrote:
> 
> > 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
> 
> Not necessarily.  If the values in the table are in random pages (ie, no
> real coordination between index key value and position in table) after a
> point you end up reading most of the pages of the table anyway, plus
> you're reading the index (and the smaller the individual row, the more
> likely this is to occur as well I'd guess)
> 
> You might want to consider trying other join orders since you're using
> explicit join syntax since postgres will take the order you're doing your
> joins as the order you want the joins to be done in.  And are you sure
> you need the distinct, that's probably resulting in the sort and unique
> step.
> 
> For other things, what are your postgresql.conf settings for shared memory
> and sort memory?
> 
> 
-- 
Wei Weng
Network Software Engineer
KenCast Inc.