Date: Mon, 27 Apr 2020 21:37:41 +0200 (CEST)
From: Andreas Joseph Krogh <andreas@visena.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Jean-Christophe Boggio <postgresql@thefreecat.org>,
	pgsql-performance@lists.postgresql.org
Message-ID: <VisenaEmail.0.b2f0b36a277bd2fb.171bd2414f7@tc7-visena>
In-Reply-To: <20200427181034.GA28974@telsasoft.com>
Subject: Re: Recursive query slow on strange conditions
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_Part_0_217694631.1588016261980"
Precedence: bulk

------=_Part_0_217694631.1588016261980
Content-Type: multipart/related;
	boundary="----=_Part_1_348687660.1588016262015"

------=_Part_1_348687660.1588016262015
Content-Type: multipart/alternative;
	boundary="----=_Part_2_113349374.1588016262045"

------=_Part_2_113349374.1588016262045
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable


P=C3=A5 mandag 27. april 2020 kl. 20:10:34, skrev Justin Pryzby <
pryzby@telsasoft.com <mailto:pryzby@telsasoft.com>>:=20
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 i=
nto
 > a function) when some tables are empty.

 > I first sent a message to the pgsql-bugs mailing list :
 >=20
https://www.postgresql.org/message-id/16390-e9866af103d5a03a%40postgresql.o=
rg
 =3D> BUG #16390: Regression between 12.2 and 11.6 on a recursive query : v=
ery=20
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=
=20
(Andreas Karlsson, Andrew Gierth, David Fetter, Tom Lane)
 |Specifically, CTEs are automatically inlined if they have no side-effects=
,=20
are not recursive, and are referenced only once in the query. Inlining can =
be=20
prevented by specifying MATERIALIZED, or forced for multiply-referenced CTE=
s by=20
specifying NOT MATERIALIZED. Previously, CTEs were never inlined and were=
=20
always evaluated before the rest of the query.=20

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


--
 Andreas Joseph Krogh=20

------=_Part_2_113349374.1588016262045
Content-Type: text/html;charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div>P=C3=A5 mandag 27. april 2020 kl. 20:10:34, skrev Justin Pryzby &lt;<a=
 href=3D"mailto:pryzby@telsasoft.com">pryzby@telsasoft.com</a>&gt;:</div>

<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style=3D"display:inline; font-family: monospace; font-size: 12px;">On =
Mon, Apr 27, 2020 at 07:49:50PM +0200, Jean-Christophe Boggio wrote:<br>
&gt; I have a performance/regression problem on a complicated query (placed=
 into<br>
&gt; a function) when some tables are empty.<br>
<br>
&gt; I first sent a message to the pgsql-bugs mailing list :<br>
&gt; https://www.postgresql.org/message-id/16390-e9866af103d5a03a%40postgre=
sql.org<br>
=3D&gt; BUG #16390: Regression between 12.2 and 11.6 on a recursive query :=
 very slow and overestimation of rows<br>
<br>
The most obvious explanation is due to this change:<br>
https://www.postgresql.org/docs/12/release-12.html<br>
|Allow common table expressions (CTEs) to be inlined into the outer query (=
Andreas Karlsson, Andrew Gierth, David Fetter, Tom Lane)<br>
|Specifically, CTEs are automatically inlined if they have no side-effects,=
 <span style=3D"background-color:#FFFF00;">are not recursive</span>, and ar=
e referenced only once in the query. Inlining can be prevented by specifyin=
g MATERIALIZED, or forced for multiply-referenced CTEs by specifying NOT MA=
TERIALIZED. Previously, CTEs were never inlined and were always evaluated b=
efore the rest of the query.</div>
</blockquote>

<div>=C2=A0</div>

<div>The OP's query=C2=A0<em>is</em>=C2=A0recursive, sow no inlining will t=
ake place...</div>

<div>=C2=A0</div>

<div class=3D"visena-email-signature" data-visena-message-id=3D"&lt;VisenaE=
mail.0.b2f0b36a277bd2fb.171bd2414f7@tc7-visena>">
<div>--<br>
Andreas Joseph Krogh</div>
</div>

<div>=C2=A0</div>
------=_Part_2_113349374.1588016262045--

------=_Part_1_348687660.1588016262015--

------=_Part_0_217694631.1588016261980--