Re: Automatic optimization of IN clauses via INNER JOIN

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx

Thomas Hamilton <thomashamilton76@xxxxxxxxx> 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





-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux