Message-ID: <1522084401.3179.25.camel@cybertec.at>
Subject: Re: Should from_collapse be switched off? (queries 10 times faster)
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Peter <pmc@citylink.dinoex.sub.org>
Cc: pgsql-performance@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon, 26 Mar 2018 19:13:21 +0200
In-Reply-To: <20180326103612.GA16180@gate.oper.dinoex.org>
References: <20180323100308.GA1188@gate.oper.dinoex.org>
	 <1521805295.2471.40.camel@cybertec.at> <18405.1521814459@sss.pgh.pa.us>
	 <20180323173658.GA58069@gate.oper.dinoex.org>
	 <1521954728.2350.24.camel@cybertec.at>
	 <20180326103612.GA16180@gate.oper.dinoex.org>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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