Re: slow SELECT expr INTO var in plpgsql

so 31. 1. 2026 v 21:58 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.

Regards

Pavel

>
> regards, tom lane
>
>