postgres.email
❮

slow SELECT expr INTO var in plpgsql

  • Jump to comment-1
    Pavel Stehule<pavel.stehule@gmail.com>
    Jan 31, 2026, 6:52 AM UTC
    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
    View in PostgreSQL Archives →
    • Jump to comment-1
      Tom Lane<tgl@sss.pgh.pa.us>
      Jan 31, 2026, 8:58 PM UTC
      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) with
      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 pgstatstatements, assignments aren't.
      * The context report for an error can be different, because
      SPIerror_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 pgstatstatements 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.
      		regards, tom lane
      View in PostgreSQL Archives →