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>
Subject: Re: Should from_collapse be switched off? (queries 10 times faster)
Date: Mon, 26 Mar 2018 19:13:21 +0200
Message-ID: <1522084401.3179.25.camel@cybertec.at> (raw)
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>
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
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: laurenz.albe@cybertec.at, pmc@citylink.dinoex.sub.org, tgl@sss.pgh.pa.us
Subject: Re: Should from_collapse be switched off? (queries 10 times faster)
In-Reply-To: <1522084401.3179.25.camel@cybertec.at>
* 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