pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: LN Cisneros <lnsea@earthlink.net>
To: Josh Berkus <josh@agliodbs.com>
To: LN Cisneros <chulat@mail.com>
To: LN Cisneros <lnsea@earthlink.net>
To: Manfred Koizar <mkoi-pg@aon.at>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: LN Cisneros <chulat@mail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: rewrite in to exists?
Date: Thu, 18 Sep 2003 12:27:23 -0700 (GMT-07:00)
Message-ID: <14897881.1063913244318.JavaMail.root@waldorf.psp.pas.earthlink.net> (raw)


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






view thread (7+ messages)  latest in thread

Message-ID: <14897881.1063913244318.JavaMail.root@waldorf.psp.pas.earthlink.net>
Permalink:  ../14897881.1063913244318.JavaMail.root@waldorf.psp.pas.earthlink.net/
Also on:    postgresql.org/message-id/14897881.1063913244318.JavaMail.root@waldorf.psp.pas.earthlink.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: lnsea@earthlink.net, josh@agliodbs.com, chulat@mail.com, mkoi-pg@aon.at, chriskl@familyhealth.com.au
  Subject: Re: rewrite in to exists?
  In-Reply-To: <14897881.1063913244318.JavaMail.root@waldorf.psp.pas.earthlink.net>
  References: <14897881.1063913244318.JavaMail.root@waldorf.psp.pas.earthlink.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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