MIME-Version: 1.0
References: 
 <CAKRYhW36jEbPPSPoiPkNcPWdrEXOtdugw9Hyg1poHju7LECHJg@mail.gmail.com>
 <348393.1743517738@sss.pgh.pa.us>
 <CAKRYhW2MShr6zcFFQN5ZBMSb5Uo36Gr-MRBh2G-1VcomHY7MSQ@mail.gmail.com>
In-Reply-To: 
 <CAKRYhW2MShr6zcFFQN5ZBMSb5Uo36Gr-MRBh2G-1VcomHY7MSQ@mail.gmail.com>
From: Chris Joysn <joysn71@gmail.com>
Date: Thu, 3 Apr 2025 09:20:40 +0200
Message-ID: 
 <CAKRYhW1RwatKHzsYFXMjLUOcTONi1ZcLy6NAz12O6HwZQ7Rn7w@mail.gmail.com>
Subject: Fwd: Very slow query performance when using CTE
To: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000033ac950631da9cd1"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKRYhW1RwatKHzsYFXMjLUOcTONi1ZcLy6NAz12O6HwZQ7Rn7w%40mail.gmail.com>
Precedence: bulk

--00000000000033ac950631da9cd1
Content-Type: text/plain; charset="UTF-8"

unfortunately that increased the query execution time by a factor of 8:
https://explain.dalibo.com/plan/a6d2443d87fea1ee


On Tue, 1 Apr 2025 at 16:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Chris Joysn <joysn71@gmail.com> writes:
> > 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.
>
> Try labeling that CTE as NOT MATERIALIZED.
>
>                         regards, tom lane
>

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

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><div dir=
=3D"ltr"><div>unfortunately that increased the query execution time by a fa=
ctor of 8:</div><div><a href=3D"https://explain.dalibo.com/plan/a6d2443d87f=
ea1ee" target=3D"_blank">https://explain.dalibo.com/plan/a6d2443d87fea1ee</=
a></div><div><br></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr=
" class=3D"gmail_attr">On Tue, 1 Apr 2025 at 16:28, Tom Lane &lt;<a href=3D=
"mailto:tgl@sss.pgh.pa.us" target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; wrot=
e:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Chris Joysn &=
lt;<a href=3D"mailto:joysn71@gmail.com" target=3D"_blank">joysn71@gmail.com=
</a>&gt; writes:<br>
&gt; I have an issue when using CTEs. A query, which consists of multiple C=
TEs,<br>
&gt; runs usually rather fast (~5s on my environment). But it turned out th=
at<br>
&gt; using one CTE can lead to execution times of up to one minute.<br>
&gt; That CTE is used two times within the query.<br>
<br>
Try labeling that CTE as NOT MATERIALIZED.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>
</div></div>

--00000000000033ac950631da9cd1--