Hi
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
