Date: Mon, 27 Apr 2020 13:10:34 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Jean-Christophe Boggio <postgresql@thefreecat.org>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Recursive query slow on strange conditions
Message-ID: <20200427181034.GA28974@telsasoft.com>
References: <4a3b0246-f3ef-0767-62d1-6ceb4e2fc09c@thefreecat.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4a3b0246-f3ef-0767-62d1-6ceb4e2fc09c@thefreecat.org>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Mon, Apr 27, 2020 at 07:49:50PM +0200, Jean-Christophe Boggio wrote:
> I have a performance/regression problem on a complicated query (placed into
> a function) when some tables are empty.

> I first sent a message to the pgsql-bugs mailing list :
> https://www.postgresql.org/message-id/16390-e9866af103d5a03a%40postgresql.org
=> BUG #16390: Regression between 12.2 and 11.6 on a recursive query : very slow and overestimation of rows

The most obvious explanation is due to this change:
https://www.postgresql.org/docs/12/release-12.html
|Allow common table expressions (CTEs) to be inlined into the outer query (Andreas Karlsson, Andrew Gierth, David Fetter, Tom Lane)
|Specifically, CTEs are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query. Inlining can be prevented by specifying MATERIALIZED, or forced for multiply-referenced CTEs by specifying NOT MATERIALIZED. Previously, CTEs were never inlined and were always evaluated before the rest of the query.

So you could try the query with ".. AS MATERIALIZED".

> On Pg 11.6 the query takes 121ms
> On Pg 12.2 it takes 11450ms
> 
> Since the query plan is more than 560 lines and the query itself ~400 lines,
> I'm not sure it's efficient to post everything in an email.

You can also send a link to the plan on https://explain.depesz.com/
Which maybe more people will look at than if it requires downloading and
restoring a DB.

FYI, I had a similar issue:
https://www.postgresql.org/message-id/flat/20171110204043.GS8563%40telsasoft.com

And my solution was to 1) create an child table: CREATE TABLE x_child() INHERITS(x)
and, 2) change the query to use select from ONLY.  (1) allows the planner to
believe that the table really is empty, a conclusion it otherwise avoids and
(2) avoids decending into the child (for which the planner would likewise avoid
the conclusion that it's actually empty).

-- 
Justin