Message-ID: <1521805295.2471.40.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>,
	pgsql-performance@lists.postgresql.org
Date: Fri, 23 Mar 2018 12:41:35 +0100
In-Reply-To: <20180323100308.GA1188@gate.oper.dinoex.org>
References: <20180323100308.GA1188@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:
> My queries get up to 10 times faster when I disable from_collapse
> (setting from_collapse_limit=1).
> 
> After this finding, The pramatic solution is easy: it needs to be
> switched off.
> 
> BUT:
> I found this perchance, accidentally (after the queries had been
> running for years). And this gives me some questions about
> documentation and best practices.
> 
> I could not find any documentation or evaluation that would say
> that from_collapse can have detrimental effects. Even less, which
> type of queries may suffer from that.

https://www.postgresql.org/docs/current/static/explicit-joins.html
states towards the end of the page that the search tree grows
exponentially with the number of relations, and from_collapse_limit
can be set to control that.

> In my case, planning uses 1 or 2% of the cycles needed for
> execution; that seems alright to me. 
> And, as said above, I cannot see why my queries might be an
> atypical case (I don't think they are).
> 
> If somebody would like to get a hands-on look onto the actual
> case, I'd be happy to put it online.

It seems like you are barking up the wrong tree.

Your query does not take long because of the many relations in the
FROM list, but because the optimizer makes a wrong choice.

If you set from_collapse_limit to 1, you force the optimizer to
join the tables in the order in which they appear in the query, and
by accident this yields a better plan than the one generated if the
optimizer is free to do what it thinks is best.

The correct solution is *not* to set from_collapse_limit = 1, but
to find and fix the problem that causes the optimizer to make a
wrong choice.

If you send the query and the output of
EXPLAIN (ANALYZE, BUFFERS) SELECT ...
we have a chance of telling you what's wrong.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com