Re: slow SELECT expr INTO var in plpgsql

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.

regards, tom lane