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

Re: EXISTS vs IN vs OUTER JOINS

From: Joe Conway <mail(at)joeconway(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Tomasz Myrta <jasiek(at)klaster(dot)net>,PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org>
Subject: Re: EXISTS vs IN vs OUTER JOINS
Date: 2002-12-19 17:43:24
Message-ID: 3E02053C.7070305@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-performance
Josh Berkus wrote:
> where I have rarely seen a difference of more than 3:1.  As I
> understand it, this is because NOT EXISTS can use optimized join
> algorithms to locate matching rows, whereas NOT IN must compare each
> row against every possible matching value in the subselect.
> 
> It also makes a difference whether or not the referenced field(s) in
> the subselect is indexed.   EXISTS will often use an index to compare
> the values in the master query to the sub-query.  As far as I know, IN
> can use an index to retrieve the subquery values, but not to sort or
> compare them after they have been retreived into memory.

I wonder if "[NOT] IN (subselect)" could be improved with a hash table in 
similar fashion to the hash aggregate solution Tom recently implemented?

Joe



In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2002-12-19 19:02:39
Subject: Re: EXISTS vs IN vs OUTER JOINS
Previous:From: jasiekDate: 2002-12-19 17:27:33
Subject: Re: EXISTS vs IN vs OUTER JOINS

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