Re: improve 'where not exists' query..

On Wed, Oct 26, 2005 at 12:58:26 -0500,
Noel Whelan <noel(dot)whelan(at)gmail(dot)com> wrote:
> I've done an explain analyze - nothing looks wrong to me. I'm thinking it's
> not exactly an issue with the query itself; it's just an inefficient thing I
> want to do (essentially, for each id in the one table, identify whether or
> not one exists in the other table).
> Current installation is 7.3.4; but I'll look into NOT IN in case that would
> be an improvement. Thanks,
> - Noel

I am pretty sure the speed up for NOT IN was after 7.3 so that isn't likely to
help. You probably should try the outer join suggestion in that case.

You should probably also seriously consider upgrading. There have been a number
of performance enhancing changes since 7.3.

>
> On 10/26/05, Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
> >
> > On Tue, Oct 25, 2005 at 15:46:52 -0500,
> > Noel Whelan <noel(dot)whelan(at)gmail(dot)com> wrote:
> > > I'm wondering if there's an ideal way to improve the efficiency of this
> > > query:
> > >
> > > SELECT i.id <http://i.id> <http://i.id> FROM items i
> > > WHERE (NOT (EXISTS (SELECT c.id <http://c.id> <http://c.id>
> > > FROM contacts c WHERE (c.id <http://c.id> <http://c.id> = i.id<http://i.id><
> > http://i.id>))));
> > >
> > > It takes a while to execute, clearly. Thank you,
> >
> > On recent versions of postgres, NOT IN is potentially faster.
> >
> > Have you looked at EXPLAIN ANALYZE output to see if anything looks amiss?
> >