From: | Peter <pmc(at)citylink(dot)dinoex(dot)sub(dot)org> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Should from_collapse be switched off? (queries 10 times faster) |
Date: | 2018-03-26 10:36:13 |
Message-ID: | 20180326103612.GA16180@gate.oper.dinoex.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2018-03-26 17:13:21 | Re: Should from_collapse be switched off? (queries 10 times faster) |
Previous Message | bk | 2018-03-25 18:18:44 | Re: Slow planning time for custom function |