public inbox for [email protected]  

help / color / mirror / Atom feed

From: Pavel Stehule <[email protected]>
To: Tom Lane <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: slow SELECT expr INTO var in plpgsql
Date: Sun, 1 Feb 2026 06:09:25 +0100
Message-ID: <CAFj8pRDhF0ZwOgK=hMoALZkuyOgjtqHgR6CkVyq86ByERpUYYA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAFj8pRDieSQOPDHD_svvR75875uRejS9cN87FoAC3iXMXS1saQ@mail.gmail.com>
	<[email protected]>

so 31. 1. 2026 v 21:58 odesílatel Tom Lane <[email protected]> napsal:

> Pavel Stehule <[email protected]> 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
>
>

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: slow SELECT expr INTO var in plpgsql
  In-Reply-To: <CAFj8pRDhF0ZwOgK=hMoALZkuyOgjtqHgR6CkVyq86ByERpUYYA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox