pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedrewrite 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