MIME-Version: 1.0
References: <CAFj8pRDieSQOPDHD_svvR75875uRejS9cN87FoAC3iXMXS1saQ@mail.gmail.com>
 <204196.1769893114@sss.pgh.pa.us>
In-Reply-To: <204196.1769893114@sss.pgh.pa.us>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sun, 1 Feb 2026 06:09:25 +0100
Message-ID: <CAFj8pRDhF0ZwOgK=hMoALZkuyOgjtqHgR6CkVyq86ByERpUYYA@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="000000000000f830b60649bc370a"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRDhF0ZwOgK%3DhMoALZkuyOgjtqHgR6CkVyq86ByERpUYYA%40mail.gmail.com>
Precedence: bulk

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

so 31. 1. 2026 v 21:58 odes=C3=ADlatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

> Pavel Stehule <pavel.stehule@gmail.com> writes:
> > I remember the old discussion about this issue, and I thought that the
> > performance of SELECT INTO and assignment should be almost the same. I
> > repeated these tests on pg 9.4, 11 and master (asserts are disabled) wi=
th
> > interesting results
>
> > release, assign time, select into time
> > 9.4, 2900 ms, 20800 ms
> > 11, 2041 ms, 16243 ms
> > master, 534ms, 15438 ms
>
> Yeah, we've sweated a good deal about optimizing plpgsql assignment,
> but SELECT INTO is always done the hard way.
>
> 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.
>
> Probably no one would notice such details if it had been like that
> all along, but would they complain about a change?  I dunno.
>

This patch looks well. I can confirm massive speedup.

I don't remember any report related to change of implementation of assign
statement before, and I think it can be similar with this patch.

In this specific case, I think so users suppose SELECT INTO is translated
to assignment by default. And there are a lot of documents on the net that
describe the transformation of the assignment statement to SELECT - so I
think there is some grey zone where optimization can do some magic. More -
the statistics for function execution can be covered by track_functions.

Regards

Pavel


>
>                         regards, tom lane
>
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">so 31. 1. 2026 v=C2=
=A021:58 odes=C3=ADlatel Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us" =
target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; napsal:<br></div><blockquote cl=
ass=3D"gmail_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:pav=
el.stehule@gmail.com" target=3D"_blank">pavel.stehule@gmail.com</a>&gt; wri=
tes:<br>
&gt; I remember the old discussion about this issue, and I thought that the=
<br>
&gt; performance of SELECT INTO and assignment should be almost the same. I=
<br>
&gt; repeated these tests on pg 9.4, 11 and master (asserts are disabled) w=
ith<br>
&gt; interesting results<br>
<br>
&gt; release, assign time, select into time<br>
&gt; 9.4, 2900 ms, 20800 ms<br>
&gt; 11, 2041 ms, 16243 ms<br>
&gt; master, 534ms, 15438 ms<br>
<br>
Yeah, we&#39;ve sweated a good deal about optimizing plpgsql assignment,<br=
>
but SELECT INTO is always done the hard way.<br>
<br>
I experimented a little bit with converting simple-expression<br>
SELECT INTO into an assignment, as attached.=C2=A0 It does reclaim<br>
nearly all of the performance difference: for me, these two<br>
test cases now take about 276 vs 337 ms.=C2=A0 However, I&#39;m concerned<b=
r>
about the side-effects of substituting this other code path;<br>
there&#39;s a lot of potential minor differences in behavior.<br>
Two that you can see in the regression test changes are:<br>
<br>
* SELECT INTO is tracked by pg_stat_statements, assignments aren&#39;t.<br>
<br>
* The context report for an error can be different, because<br>
_SPI_error_callback() doesn&#39;t get used.<br>
<br>
We could probably eliminate the context-report difference by setting<br>
up a custom error context callback in this new code path, but the<br>
difference in pg_stat_statements output would be hard to mask.<br>
There may be other discrepancies as well, such as variations in<br>
error message wording.<br>
<br>
Probably no one would notice such details if it had been like that<br>
all along, but would they complain about a change?=C2=A0 I dunno.<br></bloc=
kquote><div><br></div><div>This patch looks well. I can confirm massive spe=
edup.=C2=A0</div><div><br></div><div>I don&#39;t remember any report relate=
d to change of implementation of assign statement before, and I think it ca=
n be similar with this patch.=C2=A0</div><div><br></div><div>In this specif=
ic case, I think so users suppose SELECT INTO is translated to assignment=
=C2=A0by default. And there are a lot of documents on the net that describe=
 the transformation of the assignment statement to SELECT - so I think ther=
e is some grey zone where optimization can do some magic. More - the statis=
tics for function execution can be covered=C2=A0by track_functions.</div><d=
iv><br></div>Regards<div><br></div><div>Pavel</div><div><br></div><div>=C2=
=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;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>
<br>
</blockquote></div></div>
</div>

--000000000000f830b60649bc370a--