Clemens Eisserer <linuxhippy@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
