Skip site navigation (1) Skip section navigation (2)

Re: rewrite in to exists?

From: Josh Berkus <josh(at)agliodbs(dot)com>
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: rewrite in to exists?
Date: 2003-09-18 17:34:13
Message-ID: (view raw or whole thread)
Lists: pgsql-performance

> >SELECT t1.code,, 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 

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

Josh Berkus
Aglio Database Solutions
San Francisco

In response to


pgsql-performance by date

Next:From: Josh BerkusDate: 2003-09-18 17:50:34
Subject: Re: [PERFORM] How to force an Index ?
Previous:From: Josh BerkusDate: 2003-09-18 17:30:38
Subject: Re: Is there a reason _not_ to vacuum continuously?

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group