MIME-Version: 1.0
From: Chris Joysn <joysn71@gmail.com>
Date: Tue, 1 Apr 2025 15:37:51 +0200
Message-ID: 
 <CAKRYhW36jEbPPSPoiPkNcPWdrEXOtdugw9Hyg1poHju7LECHJg@mail.gmail.com>
Subject: Very slow query performance when using CTE
To: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000552b500631b7a5bb"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKRYhW36jEbPPSPoiPkNcPWdrEXOtdugw9Hyg1poHju7LECHJg%40mail.gmail.com>
Precedence: bulk

--000000000000552b500631b7a5bb
Content-Type: text/plain; charset="UTF-8"

Hello,
I have an issue when using CTEs. A query, which consists of multiple CTEs,
runs usually rather fast (~5s on my environment). But it turned out that
using one CTE can lead to execution times of up to one minute.
That CTE is used two times within the query. In the CTE there are 2600
rows, compared to results of the other CTEs its a fraction of the data.
When replacing this CTE and use the original table instead in the jions,
the query performs nicely.
However, it is not always like this. Running the same query on a almost
same set of data, quantity wise, may give indeed good performance when
using that CTE.
This is the slow performing query using CTE:
https://explain.dalibo.com/plan/45ce86d9cfge14c7
And this is the fast performing query without that CTE:
https://explain.dalibo.com/plan/4abgc4773gg349b4

The query runs on the very same environment and data.
What can be the issue here and how can I address it?

--000000000000552b500631b7a5bb
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello,</div><div>I have an issue when using CTEs. A q=
uery, which consists of multiple CTEs, runs usually rather fast (~5s on my =
environment). But it turned out that using one CTE can lead to execution ti=
mes of up to one minute.</div><div>That CTE is used two times within the qu=
ery. In the CTE there are 2600 rows, compared to results of the other CTEs =
its a fraction of the data.<br></div><div>When replacing this CTE and use t=
he original table instead in the jions, the query performs nicely.</div><di=
v>However, it is not always like this. Running the same query on a almost s=
ame set of data, quantity wise, may give indeed good performance when using=
 that CTE.</div><div>This is the slow performing query using CTE:=C2=A0</di=
v><div><a href=3D"https://explain.dalibo.com/plan/45ce86d9cfge14c7">https:/=
/explain.dalibo.com/plan/45ce86d9cfge14c7</a><br></div><div>And this is the=
 fast performing query without that CTE:=C2=A0</div><div><a href=3D"https:/=
/explain.dalibo.com/plan/4abgc4773gg349b4">https://explain.dalibo.com/plan/=
4abgc4773gg349b4</a><br><br></div><div>The query runs on the very same envi=
ronment and data.</div><div></div><div>What can be the issue here and how c=
an I address it?</div></div>

--000000000000552b500631b7a5bb--