Re: How to find entries missing in 2nd table?

On 7/13/06, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
>
> > > SELECT controller_id FROM control
> > > WHERE controller_id NOT IN
> > > (SELECT DISTINCT controller_id FROM datapack);
> > The DISTINCT is not necessary. I have heard with Oracle that DISTINCT
> is a
> > huge performance problem. Is that true on PostgreSQL also?
>
> From my experience, it does not preform as well as the standard group by
> clause. I noticed a ~20%
> increase in query run times.

So in that case this would be better:

SELECT controller_id FROM control
WHERE controller_id NOT IN
(SELECT controller_id FROM datapack);

or

SELECT controller_id FROM control
WHERE controller_id NOT IN
(SELECT controller_id FROM datapack GROUP BY controller_id);

Guess you need to do some explain plans to see which would be best.

Good luck!

==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================