MIME-Version: 1.0
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sat, 31 Jan 2026 07:52:10 +0100
Message-ID: <CAFj8pRDieSQOPDHD_svvR75875uRejS9cN87FoAC3iXMXS1saQ@mail.gmail.com>
Subject: slow SELECT expr INTO var in plpgsql
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000999ffb0649a989c7"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRDieSQOPDHD_svvR75875uRejS9cN87FoAC3iXMXS1saQ%40mail.gmail.com>
Precedence: bulk

--000000000000999ffb0649a989c7
Content-Type: text/plain; charset="UTF-8"

Hi

I found a article
https://medium.com/google-cloud/postgresql-performance-the-context-switch-trap-that-slows-down-t-sql-migrations-7b8c6f518fd8

It compare T-SQL and PLpgSQL performance on some simple benchmark

do $$ declare x int; begin for i in 1..10000000 loop x := 0; end loop; end
$$;
do $$ declare x int; begin for i in 1..10000000 loop select 0 into x; end
loop; end $$;

SELECT expr INTO var is syntax used on old sybase and mssql systems. The
positive result in this article is fact, so Postgres in all tests are very
well comparable. More - the assignment is really fast and significantly
faster than on MSSQL.

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) with
interesting results

release, assign time, select into time
9.4, 2900 ms, 20800 ms
11, 2041 ms, 16243 ms
master, 534ms, 15438 ms

Originally, I used gcc with O0, and master is really slow without O2
optimization

9.4, 2600 ms, 20800 ms --<< 9.4 is faster with O0
11, 2177 ms, 19128 ms
master, 1395 ms, 70060 ms -- << master is very slow with O0

Using SELECT expr INTO var is plpgsql's antipattern. plpgsql_check can
detect it now. But it will still be nice if there will not be too big a
difference like now. I didn't check the code yet, and I have no idea if
there are some possibilities on how to execute this case better.

Regards

Pavel

tested on Fedora 43

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

<div dir=3D"ltr"><div>Hi</div><div><br></div><div>I found a article=C2=A0<a=
 href=3D"https://medium.com/google-cloud/postgresql-performance-the-context=
-switch-trap-that-slows-down-t-sql-migrations-7b8c6f518fd8">https://medium.=
com/google-cloud/postgresql-performance-the-context-switch-trap-that-slows-=
down-t-sql-migrations-7b8c6f518fd8</a></div><div><br></div><div>It compare =
T-SQL and PLpgSQL performance on some simple benchmark</div><div><br></div>=
<div>do $$ declare x int; begin for i in 1..10000000 loop x :=3D 0; end loo=
p; end $$;</div><div>do $$ declare x int; begin for i in 1..10000000 loop s=
elect 0 into x; end loop; end $$;</div><div><br></div><div>SELECT expr INTO=
 var is syntax used on old sybase and mssql systems. The positive result in=
 this article is fact, so Postgres in all tests are very well comparable. M=
ore - the assignment is really fast and significantly faster than on MSSQL.=
</div><div><br></div><div>I remember the old discussion about this issue, a=
nd I thought that the performance of SELECT INTO and assignment=C2=A0should=
=C2=A0be almost the same. I repeated these tests on pg 9.4, 11 and master (=
asserts are disabled) with interesting=C2=A0results</div><div><br></div><di=
v>release, assign time, select into time</div><div>9.4, 2900 ms, 20800 ms</=
div><div>11, 2041 ms, 16243 ms</div><div>master, 534ms, 15438 ms</div><div>=
<br></div><div>Originally, I used gcc with O0, and master is really slow wi=
thout O2 optimization</div><div><br></div><div>9.4, 2600 ms, 20800 ms --&lt=
;&lt; 9.4 is faster with O0</div><div>11, 2177 ms, 19128 ms</div><div>maste=
r, 1395 ms, 70060 ms -- &lt;&lt; master is very slow with O0</div><div><br>=
</div><div>Using SELECT expr INTO var is plpgsql&#39;s antipattern. plpgsql=
_check can detect it now. But it will still be nice if there will not be to=
o big a difference like now. I didn&#39;t check the code yet, and I have no=
 idea if there are some possibilities on how to execute this case better.</=
div><div><br></div><div>Regards</div><div><br></div><div>Pavel</div><div><b=
r></div><div>tested on Fedora 43</div><div><br></div><div><br></div></div>

--000000000000999ffb0649a989c7--