Re: Query tuning

On Fri, 2008-05-16 at 09:21 -0600, Scott Marlowe wrote:
> On Thu, May 15, 2008 at 12:00 AM,  <kapil.munish@wipro.com> wrote:
> > Hi,
> >
> > select count(distinct(j.JOBID)) as jobCount
> > from  JOB_TYPE_FIRST a, JOB_TYPE_SECOND b, JOB_ALLOCATION_WORKLIST j
> > where (( a.JOBID = j.JOBID)
> > and (a.BOOK_ID = :bookId))
> > or ((b.JOBID = j.JOBID)
> > and (b.BOOK_ID = :bookId));
> 
> Have you tried joining a with j and b with j separately, and then
> doing a union on those two sets?

That seems the only way to get a sane answer to this query, which is
otherwise an unconstrained join on both sides of the OR. Great example
of a query which runs slow because the question is phrased incorrectly.
Count(distinct) is pretty much the only function that will give the same
answer as a correctly phrased query.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support