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: 200309181034.13414.josh@agliodbs.com (view raw or flat)
Thread:
Lists: 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

In response to

Responses

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-2014 The PostgreSQL Global Development Group