Re: rewrite in to exists? - Mailing list pgsql-performance

From Manfred Koizar
Subject Re: rewrite in to exists?
Date
Msg-id hbrimvc2ffnivpbclengdl2al9mdnb1l5h@email.aon.at
Whole thread Raw
In response to Re: rewrite in to exists?  ("Christopher Kings-Lynne" <chriskl@familyhealth.com.au>)
List pgsql-performance
On Thu, 18 Sep 2003 13:23:37 +0800, "Christopher Kings-Lynne"
<chriskl@familyhealth.com.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

pgsql-performance by date:

Previous
From: "Christopher Kings-Lynne"
Date:
Subject: Re: Is there a reason _not_ to vacuum continuously?
Next
From: "Oliver Scheit"
Date:
Subject: Re: Is there a reason _not_ to vacuum continuously?