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

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

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

In response to

Responses

pgsql-performance by date

Next:From: Dave JohansenDate: 2011-05-18 14:00:50
Subject: Re: hash semi join caused by "IN (select ...)"
Previous:From: Stefan KellerDate: 2011-05-17 22:07:05
Subject: Re: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)

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