| 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 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Josh Berkus | 2002-12-19 19:02:39 | Re: EXISTS vs IN vs OUTER JOINS |
| Previous Message | jasiek | 2002-12-19 17:27:33 | Re: EXISTS vs IN vs OUTER JOINS |