From: Andreas Joseph Krogh <andreas@visena.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Jean-Christophe Boggio <postgresql@thefreecat.org>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Recursive query slow on strange conditions
Date: Mon, 27 Apr 2020 21:37:41 +0200 (CEST)
Message-ID: <VisenaEmail.0.b2f0b36a277bd2fb.171bd2414f7@tc7-visena> (raw)
In-Reply-To: <20200427181034.GA28974@telsasoft.com>
På mandag 27. april 2020 kl. 20:10:34, skrev Justin Pryzby <
pryzby@telsasoft.com <mailto:pryzby@telsasoft.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas@visena.com, pryzby@telsasoft.com, postgresql@thefreecat.org, pgsql-performance@lists.postgresql.org
Subject: Re: Recursive query slow on strange conditions
In-Reply-To: <VisenaEmail.0.b2f0b36a277bd2fb.171bd2414f7@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox