Re: hash semi join caused by "IN (select ...)"

Hi,

Does anybody know why the planner treats "= 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(at)gmail(dot)com>:
> 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 = 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
>