From: Scott Carey <scott@richrelevance.com>
To: Clemens Eisserer <linuxhippy@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Date: Wed, 18 May 2011 10:41:40 -0700
Subject: Re: hash semi join caused by "IN (select ...)"
Thread-Topic: [PERFORM] hash semi join caused by "IN (select ...)"
Thread-Index: AcwVgsnTa/v93gNFTc2dSC+0ss2/pQ==
Message-ID: <C9F95085.373D6%scott@richrelevance.com>
In-Reply-To: <BANLkTiky98A60cJ85mbTk4iuWBQYpyJgLg@mail.gmail.com>
Accept-Language: en-US
Content-Language: en-US
user-agent: Microsoft-MacOutlook/14.10.0.110310
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0


On 5/17/11 12:38 AM, "Clemens Eisserer" <linuxhippy@gmail.com> wrote:

>Hi,
>
>>> select .... from t1 left join t2 .... WHERE id IN (select ....)
>>
>> Does it work as expected with one less join?  If so, try increasing
>> join_collapse_limit ...
>
>That did the trick - thanks a lot. I only had to increase
>join_collapse_limit a bit and now get an almost perfect plan.
>Instead of hash-joining all the data, the planner generates
>nested-loop-joins with index only on the few rows I fetch.
>
>Using =3D ANY(array(select... )) also seems to work, I wonder which one
>works better. Does ANY(ARRAY(...)) force the optimizer to plan the
>subquery seperated from the main query?


I'm not sure exactly what happens with ANY(ARRAY()).

I am fairly confident that the planner simply transforms an IN(select ...)
to a join, since they are equivalent.

Because "foo IN (select ...)" is just a join, it counts towards
join_collapse_limit.


>
>Thanks, Clemens
>
>--=20
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance