From: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | kapil(dot)munish(at)wipro(dot)com, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Query tuning |
Date: | 2008-05-16 16:13:09 |
Message-ID: | 1210954389.18922.56.camel@ebony.site |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Fri, 2008-05-16 at 09:21 -0600, Scott Marlowe wrote:
> On Thu, May 15, 2008 at 12:00 AM, <kapil(dot)munish(at)wipro(dot)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.com
PostgreSQL Training, Services and Support
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Browne | 2008-05-16 16:21:08 | Re: Find all instances of a column in the entire database. |
Previous Message | Scott Marlowe | 2008-05-16 15:21:38 | Re: Query tuning |