pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedShould from_collapse be switched off? (queries 10 times faster)
10+ messages / 4 participants
[nested] [flat]
* Should from_collapse be switched off? (queries 10 times faster)
@ 2018-03-23 10:03 Peter <pmc@citylink.dinoex.sub.org>
2018-03-23 11:39 ` Re: Should from_collapse be switched off? (queries 10 times faster) Thomas Kellerer <spam_eater@gmx.net>
2018-03-23 11:41 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 10+ messages in thread
From: Peter @ 2018-03-23 10:03 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Should from_collapse be switched off? (queries 10 times faster)
2018-03-23 10:03 Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
@ 2018-03-23 11:39 ` Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 0 replies; 10+ messages in thread
From: Thomas Kellerer @ 2018-03-23 11:39 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Peter schrieb am 23.03.2018 um 11:03:
> 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.
You should post some example queries together with the slow and fast plans.
Ideally generated using "explain(analyze, buffers)" instead of a simple "explain" to see details on the execution
Thomas
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Should from_collapse be switched off? (queries 10 times faster)
2018-03-23 10:03 Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
@ 2018-03-23 11:41 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-23 14:14 ` Re: Should from_collapse be switched off? (queries 10 times faster) Tom Lane <tgl@sss.pgh.pa.us>
2018-03-23 14:30 ` Re: Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2018-03-23 14:30 ` Re: Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
1 sibling, 3 replies; 10+ messages in thread
From: Laurenz Albe @ 2018-03-23 11:41 UTC (permalink / raw)
To: Peter <pmc@citylink.dinoex.sub.org>; pgsql-performance@lists.postgresql.org
Peter wrote:
> 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.
https://www.postgresql.org/docs/current/static/explicit-joins.html
states towards the end of the page that the search tree grows
exponentially with the number of relations, and from_collapse_limit
can be set to control that.
> 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.
It seems like you are barking up the wrong tree.
Your query does not take long because of the many relations in the
FROM list, but because the optimizer makes a wrong choice.
If you set from_collapse_limit to 1, you force the optimizer to
join the tables in the order in which they appear in the query, and
by accident this yields a better plan than the one generated if the
optimizer is free to do what it thinks is best.
The correct solution is *not* to set from_collapse_limit = 1, but
to find and fix the problem that causes the optimizer to make a
wrong choice.
If you send the query and the output of
EXPLAIN (ANALYZE, BUFFERS) SELECT ...
we have a chance of telling you what's wrong.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Should from_collapse be switched off? (queries 10 times faster)
2018-03-23 10:03 Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2018-03-23 11:41 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
@ 2018-03-23 14:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-03-23 17:36 ` Re: Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2018-03-23 14:14 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Peter <pmc@citylink.dinoex.sub.org>; pgsql-performance@lists.postgresql.org
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Peter wrote:
>> 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.
> https://www.postgresql.org/docs/current/static/explicit-joins.html
> states towards the end of the page that the search tree grows
> exponentially with the number of relations, and from_collapse_limit
> can be set to control that.
It's conceivable that the OP's problem is actually planning time
(if the query joins sufficiently many tables) and that restricting
the cost of the join plan search is really what he needs to do.
Lacking any further information about the problem, we can't say.
We can, however, point to
https://wiki.postgresql.org/wiki/Slow_Query_Questions
concerning how to ask this type of question effectively.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Should from_collapse be switched off? (queries 10 times faster)
2018-03-23 10:03 Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2018-03-23 11:41 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-23 14:14 ` Re: Should from_collapse be switched off? (queries 10 times faster) Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-03-23 17:36 ` Peter <pmc@citylink.dinoex.sub.org>
2018-03-25 05:12 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 10+ messages in thread
From: Peter @ 2018-03-23 17:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance@lists.postgresql.org
On Fri, Mar 23, 2018 at 10:14:19AM -0400, Tom Lane wrote:
! It's conceivable that the OP's problem is actually planning time
! (if the query joins sufficiently many tables) and that restricting
! the cost of the join plan search is really what he needs to do.
Negative. Plnning time 10 to 27 ms. Execution time 600 to 6300 ms.
! Lacking any further information about the problem, we can't say.
! We can, however, point to
! https://wiki.postgresql.org/wiki/Slow_Query_Questions
! concerning how to ask this type of question effectively.
I strongly hope the data that I sent as followup will now
suffice Your expectations.
rgds,
PMc
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Should from_collapse be switched off? (queries 10 times faster)
2018-03-23 10:03 Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2018-03-23 11:41 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-23 14:14 ` Re: Should from_collapse be switched off? (queries 10 times faster) Tom Lane <tgl@sss.pgh.pa.us>
2018-03-23 17:36 ` Re: Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
@ 2018-03-25 05:12 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-26 10:36 ` Re: Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
0 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2018-03-25 05:12 UTC (permalink / raw)
To: Peter <pmc@citylink.dinoex.sub.org>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
Peter wrote:
> On Fri, Mar 23, 2018 at 10:14:19AM -0400, Tom Lane wrote:
>
> ! It's conceivable that the OP's problem is actually planning time
> ! (if the query joins sufficiently many tables) and that restricting
> ! the cost of the join plan search is really what he needs to do.
>
> Negative. Plnning time 10 to 27 ms. Execution time 600 to 6300 ms.
>
> ! Lacking any further information about the problem, we can't say.
> ! We can, however, point to
> ! https://wiki.postgresql.org/wiki/Slow_Query_Questions
> ! concerning how to ask this type of question effectively.
>
> I strongly hope the data that I sent as followup will now
> suffice Your expectations.
Your reported execution times don't match the time reported in the
EXPLAIN output...
The cause of the long execution time is clear:
The row count of the join between "places" (WHERE platz = 'WAEHR'),
"wpnames" and "places AS places_1" is underestimated by a factor of 10
(1 row instead of 10).
The nested loop join that is chosen as a consequence is now executed
10 times instead of the estimated 1 time, which is where almost all the
execution time is spent.
The question how to fix that is more complicated, and I cannot solve
it off-hand with a complicated query like that.
Setting "enable_nestloop = off" is as coarse as forcing "from_collapse = 1"
and will negatively impact other queries - if it helps at all.
You'll probably have to rewrite the query.
Sorry that I cannot be of more help.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Should from_collapse be switched off? (queries 10 times faster)
2018-03-23 10:03 Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2018-03-23 11:41 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-23 14:14 ` Re: Should from_collapse be switched off? (queries 10 times faster) Tom Lane <tgl@sss.pgh.pa.us>
2018-03-23 17:36 ` Re: Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2018-03-25 05:12 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
@ 2018-03-26 10:36 ` Peter <pmc@citylink.dinoex.sub.org>
2018-03-26 17:13 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 10+ messages in thread
From: Peter @ 2018-03-26 10:36 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-performance@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Laurenz,
thank You very much for Your comments!
On Sun, Mar 25, 2018 at 07:12:08AM +0200, Laurenz Albe wrote:
! Your reported execution times don't match the time reported in the
! EXPLAIN output...
Should these match?
It seems the EXPLAIN (ANALYZE, BUFFERS) does additional things, not
just execute the query.
! The cause of the long execution time is clear:
!
! The row count of the join between "places" (WHERE platz = 'WAEHR'),
! "wpnames" and "places AS places_1" is underestimated by a factor of 10
! (1 row instead of 10).
!
! The nested loop join that is chosen as a consequence is now executed
! 10 times instead of the estimated 1 time, which is where almost all the
! execution time is spent.
I've seen this, but do not fully understand it yet.
! Setting "enable_nestloop = off" is as coarse as forcing "from_collapse = 1"
! and will negatively impact other queries - if it helps at all.
Since this query is already put into a function, I found I can easily
set from_collapse=1 only for this function, by means of "ALTER
FUNCTION ... SET ...", so it does only influence this query.
It seems this is the most straight-forward solution here.
rgds,
P.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Should from_collapse be switched off? (queries 10 times faster)
2018-03-23 10:03 Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2018-03-23 11:41 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-23 14:14 ` Re: Should from_collapse be switched off? (queries 10 times faster) Tom Lane <tgl@sss.pgh.pa.us>
2018-03-23 17:36 ` Re: Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2018-03-25 05:12 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-26 10:36 ` Re: Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
@ 2018-03-26 17:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 10+ messages in thread
From: Laurenz Albe @ 2018-03-26 17:13 UTC (permalink / raw)
To: Peter <pmc@citylink.dinoex.sub.org>; +Cc: pgsql-performance@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Peter wrote:
> ! Your reported execution times don't match the time reported in the
> ! EXPLAIN output...
>
> Should these match?
> It seems the EXPLAIN (ANALYZE, BUFFERS) does additional things, not
> just execute the query.
True.
I had assumed you were speaking about the duration of the EXPLAIN (ANALYZE).
> ! Setting "enable_nestloop = off" is as coarse as forcing "from_collapse = 1"
> ! and will negatively impact other queries - if it helps at all.
>
> Since this query is already put into a function, I found I can easily
> set from_collapse=1 only for this function, by means of "ALTER
> FUNCTION ... SET ...", so it does only influence this query.
> It seems this is the most straight-forward solution here.
It is an option, although not one that makes one happy.
You might have to revisit the decision if the data distribution changes
and the chosen query plan becomes inefficient.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Should from_collapse be switched off? (queries 10 times faster)
2018-03-23 10:03 Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2018-03-23 11:41 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
@ 2018-03-23 14:30 ` Peter <pmc@citylink.dinoex.sub.org>
2 siblings, 0 replies; 10+ messages in thread
From: Peter @ 2018-03-23 14:30 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-performance@lists.postgresql.org
The problem appeared when I found the queries suddenly taking longer
than usual. Investigation showed that execution time greatly depends
on the way the queries are invoked.
Consider fn(x) simply a macro containing a plain SQL SELECT statement
returning SETOF (further detail follows below):
# SELECT fn(x);
-> 6.3 sec.
# SELECT a from fn(x) as a;
-> 1.3 sec.
Further investigation with auto_explain shows different plans being
chosen. The slower one uses an Index Only Scan, which seems to perform
bad. Slightly increasing random_page_cost solves this, but this seems
the wrong way, because we are on SSD+ZFS, where random_page_cost
actually should be DEcreased, as there is no difference if random or
sequential.
During this effort I accidentally came upon from_collapse_limit,
and setting it off significantly changed things:
# SET from_collapse_limit = 1;
# SELECT fn(x);
-> 0.6 sec.
# SELECT a from fn(x) as a;
-> 1.2 sec.
The plans look different now (obviousely), and again the difference
between the two invocations comes from an an Index Only Scan, but
this time the Index Only Scan is faster. So now we can reduce
random_page_cost in order to better reflect physical circumstances,
and then both invocations will be fast.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Should from_collapse be switched off? (queries 10 times faster)
2018-03-23 10:03 Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2018-03-23 11:41 ` Re: Should from_collapse be switched off? (queries 10 times faster) Laurenz Albe <laurenz.albe@cybertec.at>
@ 2018-03-23 14:30 ` Peter <pmc@citylink.dinoex.sub.org>
2 siblings, 0 replies; 10+ messages in thread
From: Peter @ 2018-03-23 14:30 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
On Fri, Mar 23, 2018 at 12:41:35PM +0100, Laurenz Albe wrote:
! https://www.postgresql.org/docs/current/static/explicit-joins.html
! states towards the end of the page that the search tree grows
! exponentially with the number of relations, and from_collapse_limit
! can be set to control that.
Yes, I read that page.
! > 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.
!
! It seems like you are barking up the wrong tree.
!
! Your query does not take long because of the many relations in the
! FROM list, but because the optimizer makes a wrong choice.
Exactly!
And I am working hard in order to understand WHY this happens.
! The correct solution is *not* to set from_collapse_limit = 1, but
! to find and fix the problem that causes the optimizer to make a
! wrong choice.
!
! If you send the query and the output of
! EXPLAIN (ANALYZE, BUFFERS) SELECT ...
! we have a chance of telling you what's wrong.
Your viewpoint would be preferrable, only I am lacking any idea on
where there could be such a problem that would make up a root cause.
I will gladly follow Your suggestion; data is underway.
P.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2018-03-26 17:13 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-23 10:03 Should from_collapse be switched off? (queries 10 times faster) Peter <pmc@citylink.dinoex.sub.org>
2018-03-23 11:39 ` Thomas Kellerer <spam_eater@gmx.net>
2018-03-23 11:41 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-23 14:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-03-23 17:36 ` Peter <pmc@citylink.dinoex.sub.org>
2018-03-25 05:12 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-26 10:36 ` Peter <pmc@citylink.dinoex.sub.org>
2018-03-26 17:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-23 14:30 ` Peter <pmc@citylink.dinoex.sub.org>
2018-03-23 14:30 ` Peter <pmc@citylink.dinoex.sub.org>
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