Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vmI3P-00A3rg-1D for pgsql-hackers@arkaria.postgresql.org; Sat, 31 Jan 2026 20:58:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vmI3I-0096rV-2Z for pgsql-hackers@arkaria.postgresql.org; Sat, 31 Jan 2026 20:58:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vmI3I-0096rN-1X for pgsql-hackers@lists.postgresql.org; Sat, 31 Jan 2026 20:58:45 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vmI3C-00000000Phi-01aG for pgsql-hackers@lists.postgresql.org; Sat, 31 Jan 2026 20:58:43 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 60VKwY9M204197; Sat, 31 Jan 2026 15:58:34 -0500 From: Tom Lane To: Pavel Stehule cc: PostgreSQL Hackers Subject: Re: slow SELECT expr INTO var in plpgsql In-reply-to: References: Comments: In-reply-to Pavel Stehule message dated "Sat, 31 Jan 2026 07:52:10 +0100" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <203957.1769893078.0@sss.pgh.pa.us> Date: Sat, 31 Jan 2026 15:58:34 -0500 Message-ID: <204196.1769893114@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <203957.1769893078.1@sss.pgh.pa.us> Pavel Stehule 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="optimize-select-into-v1.patch"; charset="us-ascii" Content-ID: <203957.1769893078.2@sss.pgh.pa.us> Content-Description: optimize-select-into-v1.patch Content-Transfer-Encoding: quoted-printable diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/cont= rib/pg_stat_statements/expected/level_tracking.out index a15d897e59b..832d65e97ca 100644 --- a/contrib/pg_stat_statements/expected/level_tracking.out +++ b/contrib/pg_stat_statements/expected/level_tracking.out @@ -1500,12 +1500,11 @@ SELECT PLUS_ONE(1); SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE = "C"; calls | rows | query = -------+------+---------------------------------------------------- - 2 | 2 | SELECT (i + $2 + $3)::INTEGER 2 | 2 | SELECT (i + $2)::INTEGER LIMIT $3 2 | 2 | SELECT PLUS_ONE($1) 2 | 2 | SELECT PLUS_TWO($1) 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) +(4 rows) = -- immutable SQL function --- can be executed at plan time CREATE FUNCTION PLUS_THREE(i INTEGER) RETURNS INTEGER AS @@ -1525,15 +1524,14 @@ SELECT PLUS_THREE(10); SELECT toplevel, calls, rows, query FROM pg_stat_statements ORDER BY quer= y COLLATE "C"; toplevel | calls | rows | query = = ----------+-------+------+-----------------------------------------------= ------------------------------- - f | 2 | 2 | SELECT (i + $2 + $3)::INTEGER f | 2 | 2 | SELECT (i + $2)::INTEGER LIMIT $3 t | 2 | 2 | SELECT PLUS_ONE($1) t | 2 | 2 | SELECT PLUS_THREE($1) t | 2 | 2 | SELECT PLUS_TWO($1) - t | 1 | 5 | SELECT calls, rows, query FROM pg_stat_stateme= nts ORDER BY query COLLATE "C" + t | 1 | 4 | SELECT calls, rows, query FROM pg_stat_stateme= nts ORDER BY query COLLATE "C" f | 2 | 2 | SELECT i + $2 LIMIT $3 t | 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL = AS t -(8 rows) +(7 rows) = SELECT pg_stat_statements_reset() IS NOT NULL AS t; t = diff --git a/contrib/pg_stat_statements/expected/plancache.out b/contrib/p= g_stat_statements/expected/plancache.out index e152de9f551..32bf913b286 100644 --- a/contrib/pg_stat_statements/expected/plancache.out +++ b/contrib/pg_stat_statements/expected/plancache.out @@ -159,11 +159,10 @@ SELECT calls, generic_plan_calls, custom_plan_calls,= toplevel, query FROM pg_sta calls | generic_plan_calls | custom_plan_calls | toplevel | = query = -------+--------------------+-------------------+----------+-------------= --------------------------------------- 2 | 0 | 0 | t | CALL select_= one_proc($1) - 4 | 2 | 2 | f | SELECT $1 1 | 0 | 0 | t | SELECT pg_st= at_statements_reset() IS NOT NULL AS t 2 | 0 | 0 | t | SELECT selec= t_one_func($1) 2 | 0 | 0 | t | SET plan_cac= he_mode TO $1 -(5 rows) +(4 rows) = -- -- EXPLAIN [ANALYZE] EXECUTE + functions/procedures @@ -211,10 +210,9 @@ SELECT calls, generic_plan_calls, custom_plan_calls, = toplevel, query FROM pg_sta 2 | 0 | 0 | t | EXPLAIN (ANA= LYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT select_one_f= unc($1) 4 | 0 | 0 | f | EXPLAIN (ANA= LYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT select_one_f= unc($1); 2 | 0 | 0 | t | EXPLAIN (COS= TS OFF) SELECT select_one_func($1) - 4 | 2 | 2 | f | SELECT $1 1 | 0 | 0 | t | SELECT pg_st= at_statements_reset() IS NOT NULL AS t 2 | 0 | 0 | t | SET plan_cac= he_mode TO $1 -(7 rows) +(6 rows) = RESET pg_stat_statements.track; -- diff --git a/src/pl/plpgsql/src/expected/plpgsql_cache.out b/src/pl/plpgsq= l/src/expected/plpgsql_cache.out index 9df188ce56b..601e1ff3bc1 100644 --- a/src/pl/plpgsql/src/expected/plpgsql_cache.out +++ b/src/pl/plpgsql/src/expected/plpgsql_cache.out @@ -55,8 +55,7 @@ select show_result_type('select 1 as a'); -- (but if debug_discard_caches is on, it will succeed) select show_result_type('select 2.0 as a'); ERROR: type of parameter 5 (numeric) does not match that when preparing = the plan (integer) -CONTEXT: SQL statement "select pg_typeof(r.a)" -PL/pgSQL function show_result_type(text) line 7 at SQL statement +CONTEXT: PL/pgSQL function show_result_type(text) line 7 at SQL statemen= t -- but it's OK if we force plan rebuilding discard plans; select show_result_type('select 2.0 as a'); diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 75325117ec9..b693d6cc829 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -4267,6 +4267,43 @@ exec_stmt_execsql(PLpgSQL_execstate *estate, stmt->mod_stmt_set =3D true; } = + /* + * Some users write "SELECT expr INTO var" instead of "var :=3D expr". = If + * the expression is simple and the INTO target is a single variable, we + * can bypass SPI and call ExecEvalExpr() directly. (exec_assign_expr + * would actually work for non-simple expressions too, but such an + * expression might return more or less than one row, complicating matte= rs + * greatly. The potential performance win is small if it's non-simple, + * and any errors we might issue would likely look different, so avoid + * using this code path for non-simple cases.) + */ + if (expr->expr_simple_expr && stmt->into) + { + PLpgSQL_datum *target =3D estate->datums[stmt->target->dno]; + + if (target->dtype =3D=3D PLPGSQL_DTYPE_ROW) + { + PLpgSQL_row *row =3D (PLpgSQL_row *) target; + + if (row->nfields =3D=3D 1) + { + /* Evaluate the expression and assign to the INTO target */ + exec_assign_expr(estate, estate->datums[row->varnos[0]], + expr); + + /* + * We must duplicate the other effects of the code below, as + * well. We know that exactly one row was returned, so it + * doesn't matter whether the INTO was STRICT or not. + */ + exec_set_found(estate, true); + estate->eval_processed =3D 1; + + return PLPGSQL_RC_OK; + } + } + } + /* * Set up ParamListInfo to pass to executor */ ------- =_aaaaaaaaaa0--