Re: Recursive query slow on strange conditions

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
oncein the query. Inlining can be prevented by specifying MATERIALIZED, or forced for multiply-referenced CTEs by
specifyingNOT 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