pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
rewrite in to exists?
7+ messages / 4 participants
[nested] [flat]

* rewrite in to exists?
@ 2003-09-18 04:59 LN Cisneros <lnsea@earthlink.net>
  2003-09-18 05:23 ` Re: rewrite in to exists? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 7+ messages in thread

From: LN Cisneros @ 2003-09-18 04:59 UTC (permalink / raw)
  To: pgsql-performance


I'm on 7.3.4 and this query gets horrible performance.  Is there a way to rewrite it with an exists or some way to get better performance?

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;

Thanks!






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: rewrite in to exists?
  2003-09-18 04:59 rewrite in to exists? LN Cisneros <lnsea@earthlink.net>
@ 2003-09-18 05:23 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2003-09-18 09:16   ` Re: rewrite in to exists? Manfred Koizar <mkoi-pg@aon.at>
  0 siblings, 1 reply; 7+ messages in thread

From: Christopher Kings-Lynne @ 2003-09-18 05:23 UTC (permalink / raw)
  To: LN Cisneros <chulat@mail.com>; pgsql-performance

> I'm on 7.3.4 and this query gets horrible performance.  Is there a way to
rewrite it with an exists or some way to get better performance?
>
> 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;

????

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;

Or use a between clause is nice:

select code, id, name, date_of_service from tbl where xxx between 29800 and
29909 and code='XX' and client_code='XX' order by id, date_of_service;

But seriously - your query above is referencing 'tbl' twice - is that
correct, or is the tbl in the subselect supposed to be something different?

Chris




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: rewrite in to exists?
  2003-09-18 04:59 rewrite in to exists? LN Cisneros <lnsea@earthlink.net>
  2003-09-18 05:23 ` Re: rewrite in to exists? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-09-18 09:16   ` Manfred Koizar <mkoi-pg@aon.at>
  0 siblings, 0 replies; 7+ messages in thread

From: Manfred Koizar @ 2003-09-18 09:16 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: LN Cisneros <chulat@mail.com>; 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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: rewrite in to exists?
@ 2003-09-18 14:59 LN Cisneros <lnsea@earthlink.net>
  2003-09-18 17:34 ` Re: rewrite in to exists? Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: LN Cisneros @ 2003-09-18 14:59 UTC (permalink / raw)
  To: Manfred Koizar <mkoi-pg@aon.at>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: LN Cisneros <chulat@mail.com>; pgsql-performance


On Thu, 18 Sep 2003 13:23:37 +0800, "Christopher Kings-Lynne"
<snip>

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

Yes, sorry about that. But in my query  for a set of dates returned from the subquery I would then like to get all records that match this set of dates (ordered).

I believe this query will work and hopefully speed it up (the "IN" query is extremely slow)...I give this one a try:

>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;

A question I have is is the "DISTINCT" really going to help or is it just going to throw another sort into the mix making it slower?

Thanks for the help!

Laurette





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: rewrite in to exists?
  2003-09-18 14:59 Re: rewrite in to exists? LN Cisneros <lnsea@earthlink.net>
@ 2003-09-18 17:34 ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2003-09-18 17:34 UTC (permalink / raw)
  To: LN Cisneros <chulat@mail.com>; LN Cisneros <lnsea@earthlink.net>; Manfred Koizar <mkoi-pg@aon.at>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: LN Cisneros <chulat@mail.com>; pgsql-performance

Laurette,

> >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;
>
> A question I have is is the "DISTINCT" really going to help or is it just
> going to throw another sort into the mix making it slower?

It's required if you expect the subquery to return multiple rows for each 
date_of_service match.  Of course, you can also put the DISTINCT in the main 
query instead; it depends on how many results you expect the subquery to 
have.

Still, I'd suggest trying the EXISTS version first .... under most 
circumstances, DISTINCT is pretty slow.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: rewrite in to exists?
@ 2003-09-18 19:27 LN Cisneros <lnsea@earthlink.net>
  2003-09-19 08:57 ` Re: rewrite in to exists? Manfred Koizar <mkoi-pg@aon.at>
  0 siblings, 1 reply; 7+ messages in thread

From: LN Cisneros @ 2003-09-18 19:27 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; LN Cisneros <chulat@mail.com>; LN Cisneros <lnsea@earthlink.net>; Manfred Koizar <mkoi-pg@aon.at>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: LN Cisneros <chulat@mail.com>; pgsql-performance


Thanks Josh!

But, the EXISTS version doesn't really give me what I want...all rows in tbl that match the date of the subquery.

But, using the DISTINCT does make sense.

Thanks again to all who helped!

-----Original Message-----
From: Josh Berkus <josh@agliodbs.com>
Sent: Sep 18, 2003 10:34 AM
To: LN Cisneros <chulat@mail.com>, LN Cisneros <lnsea@earthlink.net>, 
	Manfred Koizar <mkoi-pg@aon.at>, 
	Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: LN Cisneros <chulat@mail.com>, pgsql-performance@postgresql.org
Subject: Re: [PERFORM] rewrite in to exists?

Laurette,

> >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;
>
> A question I have is is the "DISTINCT" really going to help or is it just
> going to throw another sort into the mix making it slower?

It's required if you expect the subquery to return multiple rows for each 
date_of_service match.  Of course, you can also put the DISTINCT in the main 
query instead; it depends on how many results you expect the subquery to 
have.

Still, I'd suggest trying the EXISTS version first .... under most 
circumstances, DISTINCT is pretty slow.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: rewrite in to exists?
  2003-09-18 19:27 Re: rewrite in to exists? LN Cisneros <lnsea@earthlink.net>
@ 2003-09-19 08:57 ` Manfred Koizar <mkoi-pg@aon.at>
  0 siblings, 0 replies; 7+ messages in thread

From: Manfred Koizar @ 2003-09-19 08:57 UTC (permalink / raw)
  To: LN Cisneros <chulat@mail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; LN Cisneros <chulat@mail.com>; LN Cisneros <lnsea@earthlink.net>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; LN Cisneros <chulat@mail.com>; pgsql-performance

On Thu, 18 Sep 2003 12:27:23 -0700 (GMT-07:00), LN Cisneros
<lnsea@earthlink.net> wrote:
>But, the EXISTS version doesn't

Laurette,
looking at that SELECT statement again I can't see what's wrong with
it.  One of us is missing something ;-)

> really give me what I want...

Can you elaborate?

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;

>all rows in tbl that
 ^^^
Well, all that have client_code = 'XX', as in your original query.

> match the date of the subquery.

The matching is done by the line with the (!) comment.

Servus
 Manfred



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2003-09-19 08:57 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-18 04:59 rewrite in to exists? LN Cisneros <lnsea@earthlink.net>
2003-09-18 05:23 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-09-18 09:16   ` Manfred Koizar <mkoi-pg@aon.at>
2003-09-18 14:59 Re: rewrite in to exists? LN Cisneros <lnsea@earthlink.net>
2003-09-18 17:34 ` Josh Berkus <josh@agliodbs.com>
2003-09-18 19:27 Re: rewrite in to exists? LN Cisneros <lnsea@earthlink.net>
2003-09-19 08:57 ` Manfred Koizar <mkoi-pg@aon.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox