Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group