Re: rewrite in to exists?

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
Cc: "LN Cisneros" <chulat(at)mail(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: rewrite in to exists?
Date: 2003-09-18 09:16:03
Message-ID: hbrimvc2ffnivpbclengdl2al9mdnb1l5h@email.aon.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Thu, 18 Sep 2003 13:23:37 +0800, "Christopher Kings-Lynne"
<chriskl(at)familyhealth(dot)com(dot)au> wrote:
>Why can't you just go:
>
>select code, id, name, date_of_service from tbl where xxx <= 29800 and xx >=
>29909 and code='XX' and client_code='XX' order by id, date_of_service;

Because (ignoring conditions on code and client_code for a moment) if
for a given date there is at least one row satisfying the condition on
xxx, the original query returns *all* rows having this date,
regardless of their xxx value. For example:

id | date | xxx
----+------------+-------
1 | 2003-01-01 | 10000 *
2 | 2003-01-01 | 29800 * *
3 | 2003-01-01 | 30000 *
4 | 2003-02-02 | 20000
5 | 2003-03-03 | 29900 * *

>> select code, id, name, date_of_service
>> from tbl
>> where date_of_service in
>> (select date_of_service
>> from tbl
>> where xxx >= '29800'
>> and xxx <= '29909'
>> and code = 'XX')
>> and client_code = 'XX'
>> order by id, date_of_service;

To the original poster: You did not provide a lot of information, but
the following suggestions might give you an idea ...

SELECT code, id, date_of_service
FROM tbl
WHERE EXISTS (SELECT *
FROM tbl t2
WHERE t2.xxx >= '29800' AND t2.xxx <= '29909'
AND t2.code = 'XX'
AND tbl.date_of_service = t2.date_of_service)
AND client_code = 'XX'
ORDER BY id, date_of_service;

SELECT t1.code, t1.id, t1.date_of_service
FROM tbl t1 INNER JOIN
(SELECT DISTINCT date_of_service
FROM tbl
WHERE xxx >= '29800' AND xxx <= '29909'
AND code = 'XX'
) AS t2 ON (t1.date_of_service = t2.date_of_service)
WHERE t1.client_code = 'XX'
ORDER BY id, date_of_service;

SELECT DISTINCT t1.code, t1.id, t1.date_of_service
FROM tbl AS t1 INNER JOIN tbl AS t2
ON (t1.date_of_service = t2.date_of_service
AND t2.xxx >= '29800' AND t2.xxx <= '29909'
AND t2.code = 'XX')
WHERE t1.client_code = 'XX' -- might as well put this
-- condition into the ON clause
ORDER BY id, date_of_service;

The last one assumes that there are no duplicates on code, id,
date_of_service in the desired result.

Servus
Manfred

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Oliver Scheit 2003-09-18 09:29:23 Re: Is there a reason _not_ to vacuum continuously?
Previous Message Christopher Kings-Lynne 2003-09-18 08:48:42 Re: Is there a reason _not_ to vacuum continuously?