From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Clemens Eisserer <linuxhippy(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Any disadvantages of using =ANY(ARRAY()) instead of IN? |
Date: | 2012-05-01 14:43:58 |
Message-ID: | 13236.1335883438@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Clemens Eisserer <linuxhippy(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Walker, James Les | 2012-05-01 14:44:15 | Re: Tuning Postgres 9.1 on Windows |
Previous Message | Clemens Eisserer | 2012-05-01 14:34:10 | Any disadvantages of using =ANY(ARRAY()) instead of IN? |