X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id F15FAD1B503 for ; Thu, 18 Sep 2003 19:29:24 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 30503-05 for ; Thu, 18 Sep 2003 16:29:15 -0300 (ADT) Received: from conure.mail.pas.earthlink.net (conure.mail.pas.earthlink.net [207.217.120.54]) by svr1.postgresql.org (Postfix) with ESMTP id 3690DD1B4F0 for ; Thu, 18 Sep 2003 16:29:14 -0300 (ADT) Received: from waldorf.psp.pas.earthlink.net ([207.217.78.20]) by conure.mail.pas.earthlink.net with esmtp (Exim 3.33 #1) id 1A04R2-000091-00; Thu, 18 Sep 2003 12:27:24 -0700 Message-ID: <14897881.1063913244318.JavaMail.root@waldorf.psp.pas.earthlink.net> Date: Thu, 18 Sep 2003 12:27:23 -0700 (GMT-07:00) From: LN Cisneros Reply-To: LN Cisneros To: Josh Berkus , LN Cisneros , LN Cisneros , Manfred Koizar , Christopher Kings-Lynne Subject: Re: rewrite in to exists? Cc: LN Cisneros , pgsql-performance@postgresql.org Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Mailer: Earthlink Zoo Mail 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/277 X-Sequence-Number: 3643 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 Sent: Sep 18, 2003 10:34 AM To: LN Cisneros , LN Cisneros , Manfred Koizar , Christopher Kings-Lynne Cc: LN Cisneros , 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