Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Header And Logo
|
Site Navigation
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: Thu, 19 Dec 2002 09:43:24 -0800
- Message-id: <3E02053C.7070305@joeconway.com> <text/plain>
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
- Follow-Ups:
- Re: EXISTS vs IN vs OUTER JOINS
- From: Tom Lane
- Re: EXISTS vs IN vs OUTER JOINS
- References:
- Re: EXISTS vs IN vs OUTER JOINS
- From: Josh Berkus
- Re: EXISTS vs IN vs OUTER JOINS
- Prev by Date: Re: EXISTS vs IN vs OUTER JOINS
- Next by Date: 4G row table?
- Previous by thread: Re: EXISTS vs IN vs OUTER JOINS
- Next by thread: Re: EXISTS vs IN vs OUTER JOINS
- Indexes: