public inbox for [email protected]  

help / color / mirror / Atom feed

From: Pavel Stehule <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: slow SELECT expr INTO var in plpgsql
Date: Sat, 31 Jan 2026 07:52:10 +0100
Message-ID: <CAFj8pRDieSQOPDHD_svvR75875uRejS9cN87FoAC3iXMXS1saQ@mail.gmail.com> (raw)

Hi

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

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: slow SELECT expr INTO var in plpgsql
  In-Reply-To: <CAFj8pRDieSQOPDHD_svvR75875uRejS9cN87FoAC3iXMXS1saQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox