From: | Thomas Hamilton <thomashamilton76(at)yahoo(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Automatic optimization of IN clauses via INNER JOIN |
Date: | 2009-12-17 15:45:30 |
Message-ID: | 42380.53932.qm@web45816.mail.sp1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Yes, I see the one note that running Analyze can improve the performance.
But in our testing under the same optimization and conditions INNER JOIN is significantly outperforming IN.
----- Original Message ----
From: Tom Lane tgl(at)sss(dot)pgh(dot)pa(dot)us
Thomas Hamilton <thomashamilton76(at)yahoo(dot)com> writes:
> Apparently the latest version of MySQL has solved this problem: http://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/
> But I am running PostgreSQL v8.3 and am observing generally that SELECT ... WHERE ... IN (a, b, c, ...) is much slower than SELECT ... INNER JOIN (SELECT a UNION ALL SELECT b UNION ALL SELECT c ...)
> Why doesn't the optimizer automatically transform IN clauses to INNER JOINs in this fashion?
Did you read all the comments on that three-year-old article?
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-12-17 15:57:15 | Re: Automatic optimization of IN clauses via INNER JOIN |
Previous Message | Tom Lane | 2009-12-17 15:32:53 | Re: Automatic optimization of IN clauses via INNER JOIN |