From: | Clemens Eisserer <linuxhippy(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: hash semi join caused by "IN (select ...)" |
Date: | 2011-05-18 08:46:00 |
Message-ID: | BANLkTikuKT0AMio56cRrDzp6r8SWSyxTWg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Johansen | 2011-05-18 14:00:50 | Re: hash semi join caused by "IN (select ...)" |
Previous Message | Stefan Keller | 2011-05-17 22:07:05 | Re: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation) |