From: Peter <pmc@citylink.dinoex.sub.org>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Should from_collapse be switched off? (queries 10 times faster)
Date: Fri, 23 Mar 2018 15:30:21 +0100
Message-ID: <20180323143021.GA31620@gate.oper.dinoex.org> (raw)
In-Reply-To: <1521805295.2471.40.camel@cybertec.at>
References: <20180323100308.GA1188@gate.oper.dinoex.org>
<1521805295.2471.40.camel@cybertec.at>
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.
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, laurenz.albe@cybertec.at, pgsql-performance@lists.postgresql.org
Subject: Re: Should from_collapse be switched off? (queries 10 times faster)
In-Reply-To: <20180323143021.GA31620@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