MIME-Version: 1.0
References: 
 <CAFj8pRDieSQOPDHD_svvR75875uRejS9cN87FoAC3iXMXS1saQ@mail.gmail.com>
 <204196.1769893114@sss.pgh.pa.us>
 <CAFj8pRDhF0ZwOgK=hMoALZkuyOgjtqHgR6CkVyq86ByERpUYYA@mail.gmail.com>
 <CAFj8pRDDtcErg0HFtDJ-9YutnqdkofBfO45sdjc_VoNAzsU-dw@mail.gmail.com>
 <2034619.1774045988@sss.pgh.pa.us>
In-Reply-To: <2034619.1774045988@sss.pgh.pa.us>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sat, 21 Mar 2026 06:10:52 +0100
Message-ID: 
 <CAFj8pRAbfvHA7kduB=yyhyjPdQsLunLU_2_9T+xBpUvgY2go7A@mail.gmail.com>
Subject: Re: slow SELECT expr INTO var in plpgsql
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a5bae6064d81d512"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFj8pRAbfvHA7kduB%3DyyhyjPdQsLunLU_2_9T%2BxBpUvgY2go7A%40mail.gmail.com>
Precedence: bulk

--000000000000a5bae6064d81d512
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

p=C3=A1 20. 3. 2026 v 23:33 odes=C3=ADlatel Tom Lane <tgl@sss.pgh.pa.us> na=
psal:

> Pavel Stehule <pavel.stehule@gmail.com> writes:
> >> so 31. 1. 2026 v 21:58 odes=C3=ADlatel Tom Lane <tgl@sss.pgh.pa.us> na=
psal:
> >>> I experimented a little bit with converting simple-expression
> >>> SELECT INTO into an assignment, as attached.  It does reclaim
> >>> nearly all of the performance difference: for me, these two
> >>> test cases now take about 276 vs 337 ms.  However, I'm concerned
> >>> about the side-effects of substituting this other code path;
> >>> there's a lot of potential minor differences in behavior.
> >>> Two that you can see in the regression test changes are:
> >>>
> >>> * SELECT INTO is tracked by pg_stat_statements, assignments aren't.
> >>>
> >>> * The context report for an error can be different, because
> >>> _SPI_error_callback() doesn't get used.
> >>>
> >>> We could probably eliminate the context-report difference by setting
> >>> up a custom error context callback in this new code path, but the
> >>> difference in pg_stat_statements output would be hard to mask.
> >>> There may be other discrepancies as well, such as variations in
> >>> error message wording.
>
> > Do you plan to push this patch? Unfortunately there is not any discussi=
on
> > about side effects.
>
> Yeah, general radio silence out there.  After thinking about it for
> awhile, I've decided to go ahead with the patch.  It'll be easy enough
> to revert if people are unhappy.
>
> > I wrote a version with dedicated error context callback,
>
> Thanks for doing that.  I found though that it wasn't quite enough,
> because the existing code path applies _SPI_error_callback() during
> evaluation of the expression but not during assignment to the target
> variable.  So for example, errors during type conversion to match
> the target variable don't get a context line claiming they happened
> during evaluation of the expression, which seems correct to me.
> I was able to fix it by not using exec_assign_expr() but instead
> copying that code in-line, so that we can pop the error context stack
> at the right point.  (See added tests in the committed patch,
> ce8d5fe0e2802158b65699aeae1551d489948167.)
>
> > ... so there will be
> > differences only in pg_stat_statements. It is true, so this should be
> hard
> > to mask. Maybe this difference can be just documented - like "`SELECT
> expr
> > INTO variable` can be optimized and executed by a direct expression
> > executor, and then this query will not be visible in pg_stat_statement.=
"
>
> We don't document that "var :=3D expression" isn't captured, so I don't
> think this needs to be either.  Possibly Bruce will pick up the change
> as a release-note item.
>

Thank you very much

Regards

Pavel


>
>                         regards, tom lane
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">p=C3=A1 20. 3. =
2026 v=C2=A023:33 odes=C3=ADlatel Tom Lane &lt;<a href=3D"mailto:tgl@sss.pg=
h.pa.us">tgl@sss.pgh.pa.us</a>&gt; napsal:<br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex">Pavel Stehule &lt;<a href=3D"mailto:pavel.stehul=
e@gmail.com" target=3D"_blank">pavel.stehule@gmail.com</a>&gt; writes:<br>
&gt;&gt; so 31. 1. 2026 v 21:58 odes=C3=ADlatel Tom Lane &lt;<a href=3D"mai=
lto:tgl@sss.pgh.pa.us" target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; napsal:<=
br>
&gt;&gt;&gt; I experimented a little bit with converting simple-expression<=
br>
&gt;&gt;&gt; SELECT INTO into an assignment, as attached.=C2=A0 It does rec=
laim<br>
&gt;&gt;&gt; nearly all of the performance difference: for me, these two<br=
>
&gt;&gt;&gt; test cases now take about 276 vs 337 ms.=C2=A0 However, I&#39;=
m concerned<br>
&gt;&gt;&gt; about the side-effects of substituting this other code path;<b=
r>
&gt;&gt;&gt; there&#39;s a lot of potential minor differences in behavior.<=
br>
&gt;&gt;&gt; Two that you can see in the regression test changes are:<br>
&gt;&gt;&gt; <br>
&gt;&gt;&gt; * SELECT INTO is tracked by pg_stat_statements, assignments ar=
en&#39;t.<br>
&gt;&gt;&gt; <br>
&gt;&gt;&gt; * The context report for an error can be different, because<br=
>
&gt;&gt;&gt; _SPI_error_callback() doesn&#39;t get used.<br>
&gt;&gt;&gt; <br>
&gt;&gt;&gt; We could probably eliminate the context-report difference by s=
etting<br>
&gt;&gt;&gt; up a custom error context callback in this new code path, but =
the<br>
&gt;&gt;&gt; difference in pg_stat_statements output would be hard to mask.=
<br>
&gt;&gt;&gt; There may be other discrepancies as well, such as variations i=
n<br>
&gt;&gt;&gt; error message wording.<br>
<br>
&gt; Do you plan to push this patch? Unfortunately there is not any discuss=
ion<br>
&gt; about side effects.<br>
<br>
Yeah, general radio silence out there.=C2=A0 After thinking about it for<br=
>
awhile, I&#39;ve decided to go ahead with the patch.=C2=A0 It&#39;ll be eas=
y enough<br>
to revert if people are unhappy.<br>
<br>
&gt; I wrote a version with dedicated error context callback,<br>
<br>
Thanks for doing that.=C2=A0 I found though that it wasn&#39;t quite enough=
,<br>
because the existing code path applies _SPI_error_callback() during<br>
evaluation of the expression but not during assignment to the target<br>
variable.=C2=A0 So for example, errors during type conversion to match<br>
the target variable don&#39;t get a context line claiming they happened<br>
during evaluation of the expression, which seems correct to me.<br>
I was able to fix it by not using exec_assign_expr() but instead<br>
copying that code in-line, so that we can pop the error context stack<br>
at the right point.=C2=A0 (See added tests in the committed patch,<br>
ce8d5fe0e2802158b65699aeae1551d489948167.)<br>
<br>
&gt; ... so there will be<br>
&gt; differences only in pg_stat_statements. It is true, so this should be =
hard<br>
&gt; to mask. Maybe this difference can be just documented - like &quot;`SE=
LECT expr<br>
&gt; INTO variable` can be optimized and executed by a direct expression<br=
>
&gt; executor, and then this query will not be visible in pg_stat_statement=
.&quot;<br>
<br>
We don&#39;t document that &quot;var :=3D expression&quot; isn&#39;t captur=
ed, so I don&#39;t<br>
think this needs to be either.=C2=A0 Possibly Bruce will pick up the change=
<br>
as a release-note item.<br></blockquote><div><br></div><div>Thank you very =
much</div><div><br></div><div>Regards</div><div><br></div><div>Pavel</div><=
div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<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>

--000000000000a5bae6064d81d512--