Automatic optimization of IN clauses via INNER JOIN

From: Thomas Hamilton <thomashamilton76(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Automatic optimization of IN clauses via INNER JOIN
Date: 2009-12-17 15:23:48
Message-ID: 293252.2824.qm@web45814.mail.sp1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2009-12-17 15:32:53 Re: Automatic optimization of IN clauses via INNER JOIN
Previous Message Pavel Stehule 2009-12-16 08:38:22 Re: Parallel Function calls using multiple processes