My queries get up to 10 times faster when I disable from_collapse (setting from_collapse_limit=1). After this finding, The pramatic solution is easy: it needs to be switched off. BUT: I found this perchance, accidentally (after the queries had been running for years). And this gives me some questions about documentation and best practices. I could not find any documentation or evaluation that would say that from_collapse can have detrimental effects. Even less, which type of queries may suffer from that. Since we cannot experimentally for all of our queries try out all kinds of options, if they might have significant (negative) effects, my understanding now is that, as a best practice, from_collapse should be switched off by default. And only after development it should be tested if activating it gives a positive improvement. Sadly, my knowledge does not reach into the internals. I can understand which *logical* result I should expect from an SQL statement. But I do not know how this is achieved internally. So, I have a very hard time when trying to understand output from EXPLAIN, or to make an educated guess on how the design of a query may influence execution strategy. I am usually happy when I found some SQL that would correctly produce the results I need. In short: I lack the experience to do manual optimization, or to see where manual optimization might be feasible. The manual section "Controlling the Planner with Explicit JOIN Clauses" gives a little discussion on the issue. But it seems only concerned about an increasing amount of cycles used for the planning activity, not about bad results from the optimization. Worse, it creates the impression that giving the planner maximum freedom is usually a good thing (at least until it takes too much cycles for the planner to evaluate all possibilities). In my case, planning uses 1 or 2% of the cycles needed for execution; that seems alright to me. And, as said above, I cannot see why my queries might be an atypical case (I don't think they are). If somebody would like to get a hands-on look onto the actual case, I'd be happy to put it online. rgds, PMc
