DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:content-type:content-transfer-encoding;
	b=Mm2C2sF6S1tZih5h2SLbBn6wj2uwTFbB9oM9IPuaw/zQR5L2CS5ja/pepSH4TErX2d
	qsGDzZSrL2VAaAiEdFajr/+2skt/2ya9q7MzoG1c2z7I6yPvNiqDRGvwgcgeGPCGUSfx
	fAWqHxDpCKIfSK2Yv3eYXQ09DQBgaXQ7cRNKo=
MIME-Version: 1.0
In-Reply-To: <BANLkTiky98A60cJ85mbTk4iuWBQYpyJgLg@mail.gmail.com>
References: <BANLkTikR6ngJS9=e1e1yA+E0FyxuJY_i3A@mail.gmail.com>
	<23922.1305588168@sss.pgh.pa.us>
	<BANLkTiky98A60cJ85mbTk4iuWBQYpyJgLg@mail.gmail.com>
Date: Wed, 18 May 2011 10:46:00 +0200
Message-ID: <BANLkTikuKT0AMio56cRrDzp6r8SWSyxTWg@mail.gmail.com>
Subject: Re: hash semi join caused by "IN (select ...)"
From: Clemens Eisserer <linuxhippy@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi,

Does anybody know why the planner treats "=3D ANY(ARRAY(select ...))"
differently than "IN(select ...)"?
Which one is preferable, when I already have a lot of joins?

Thanks, Clemens

2011/5/17 Clemens Eisserer <linuxhippy@gmail.com>:
> Hi,
>
>>> select .... from t1 left join t2 .... WHERE id IN (select ....)
>>
>> Does it work as expected with one less join? =C2=A0If 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?
>
> Thanks, Clemens
>