so 31. 1. 2026 v 21:58 odesílatel 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) 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 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.
