Re: slow SELECT expr INTO var in plpgsql

Hi

ne 1. 2. 2026 v 6:09 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

>
>
> 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.
>

Do you plan to push this patch? Unfortunately there is not any discussion
about side effects.

I wrote a version with dedicated error context callback, so there will be
differences only in pg_stat_statements. It is true, so this should be hard
to mask. Maybe this difference can be just documented - like "`SELECT expr
INTO variable` can be optimized and executed by a direct expression
executor, and then this query will not be visible in pg_stat_statement."

Regards

Pavel

> Regards
>
> Pavel
>
>
>
>>
>> regards, tom lane
>>
>>