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...