Hi, I am using postgresql as database for a hibernate based java oltp project and as in previous projects am totally impressed by postgresql's robustness, performance and feature-richness. Thanks for this excellent piece of software. 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. 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". However, I am a bit uncertain: - Is it safe to use ANY(ARRAY(select ...)) when I know the sub-query will only return a small amount (0-100s) of rows? - Shouldn't the optimizer be a bit smarter avoiding optimizing this case in the first place, instead of bailing out later? Should I file a bug-report about this problem? Thank you in advance, Clemens -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
