Re: rewrite in to exists?

From: LN Cisneros <lnsea(at)earthlink(dot)net>
To: Josh Berkus <josh(at)agliodbs(dot)com>, LN Cisneros <chulat(at)mail(dot)com>, LN Cisneros <lnsea(at)earthlink(dot)net>, Manfred Koizar <mkoi-pg(at)aon(dot)at>, 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 19:27:23
Message-ID: 14897881.1063913244318.JavaMail.root@waldorf.psp.pas.earthlink.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)agliodbs(dot)com>
Sent: Sep 18, 2003 10:34 AM
To: LN Cisneros <chulat(at)mail(dot)com>, LN Cisneros <lnsea(at)earthlink(dot)net>,
Manfred Koizar <mkoi-pg(at)aon(dot)at>,
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: [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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jenny Zhang 2003-09-18 22:36:50 osdl-dbt3 run results - puzzled by the execution plans
Previous Message Tom Lane 2003-09-18 18:20:33 Re: Many fields in one table or many tables?