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