From: Peter <pmc@citylink.dinoex.sub.org>
To: pgsql-performance@lists.postgresql.org
Subject: Should from_collapse be switched off? (queries 10 times faster)
Date: Fri, 23 Mar 2018 11:03:08 +0100
Message-ID: <20180323100308.GA1188@gate.oper.dinoex.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pmc@citylink.dinoex.sub.org, pgsql-performance@lists.postgresql.org
Subject: Re: Should from_collapse be switched off? (queries 10 times faster)
In-Reply-To: <20180323100308.GA1188@gate.oper.dinoex.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox