Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group