Re: Recursive query slow on strange conditions

På mandag 27. april 2020 kl. 20:10:34, skrev Justin Pryzby <
pryzby(at)telsasoft(dot)com <mailto:pryzby(at)telsasoft(dot)com>>:
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.

The OP's query is recursive, sow no inlining will take place...

--
Andreas Joseph Krogh