To: Clemens Eisserer <linuxhippy@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Any disadvantages of using =ANY(ARRAY()) instead of IN? 
In-reply-to: 
 <CAFvQSYTNtqmd5F9ZWbmxGwm2A4UGoanSuD3p=0f+yzFwpKcq6g@mail.gmail.com>
References: 
 <CAFvQSYTNtqmd5F9ZWbmxGwm2A4UGoanSuD3p=0f+yzFwpKcq6g@mail.gmail.com>
Comments: In-reply-to Clemens Eisserer <linuxhippy@gmail.com>
	message dated "Tue, 01 May 2012 16:34:10 +0200"
Date: Tue, 01 May 2012 10:43:58 -0400
Message-ID: <13236.1335883438@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Clemens Eisserer <linuxhippy@gmail.com> writes:
> Quite often Hibernate ends up generating queries with a lot of joins
> which usually works well, except for queries which load some
> additional data based on a previous query (SUBSELECT collections),
> which look like:

> select ..... from table1 ... left outer join table 15 .... WHERE
> table1.id IN (select id .... join table16 ... join table20 WHERE
> table20.somevalue=?)

> Starting with some amount of joins, the optimizer starts to do quite
> suboptimal things like hash-joining huge tables where selctivity would
> very low.
> I already raised join_collapse_limit and from_collapse_limit, but
> after a certain point query planning starts to become very expensive.

What PG version are we talking about here?

> However, when using " =ANY(ARRAY(select ...))" instead of "IN" the
> planner seems to do a lot better, most likely because it treats the
> subquery as a black-box that needs to be executed independently. I've
> hacked hibernate a bit to use ANY+ARRAY, and it seems to work a lot
> better than using "IN".

That doesn't sound like a tremendously good idea to me.  But with
so few details, it's hard to comment intelligently.  Can you provide
a concrete test case?

			regards, tom lane