Date: Fri, 23 Mar 2018 15:30:21 +0100
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)
Message-ID: <20180323143021.GA31620@gate.oper.dinoex.org>
References: <20180323100308.GA1188@gate.oper.dinoex.org>
 <1521805295.2471.40.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1521805295.2471.40.camel@cybertec.at>
User-Agent: Mutt/1.8.3 (2017-05-23)
Precedence: bulk


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.