Hi ne 1. 2. 2026 v 6:09 odesÃlatel Pavel Stehule <[email protected]> napsal:
> > > 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. > 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 >> >>
From bf5874b9c9bf273935dbc61604c6767572d0e8b1 Mon Sep 17 00:00:00 2001 From: "[email protected]" <[email protected]> Date: Tue, 10 Mar 2026 08:37:38 +0100 Subject: [PATCH] optimze select into --- .../expected/level_tracking.out | 8 +-- .../pg_stat_statements/expected/plancache.out | 6 +- src/pl/plpgsql/src/expected/plpgsql_cache.out | 3 +- src/pl/plpgsql/src/pl_exec.c | 66 +++++++++++++++++++ 4 files changed, 72 insertions(+), 11 deletions(-) diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/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 query 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_statements ORDER BY query COLLATE "C" + t | 1 | 4 | SELECT calls, rows, query FROM pg_stat_statements 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/pg_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_stat_statements_reset() IS NOT NULL AS t 2 | 0 | 0 | t | SELECT select_one_func($1) 2 | 0 | 0 | t | SET plan_cache_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 (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT select_one_func($1) 4 | 0 | 0 | f | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) SELECT select_one_func($1); 2 | 0 | 0 | t | EXPLAIN (COSTS OFF) SELECT select_one_func($1) - 4 | 2 | 2 | f | SELECT $1 1 | 0 | 0 | t | SELECT pg_stat_statements_reset() IS NOT NULL AS t 2 | 0 | 0 | t | SET plan_cache_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/plpgsql/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 statement -- 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 84552e32c87..b14dbc77147 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -1301,6 +1301,19 @@ plpgsql_exec_error_callback(void *arg) estate->func->fn_signature); } +/* + * This error callback is used when case SELECT expr INTO var + */ +static void +plpgsql_execsql_error_callback(void *arg) +{ + PLpgSQL_expr *expr = (PLpgSQL_expr *) arg; + + Assert(expr); + + errcontext("SQL statement \"%s\"", expr->query); +} + /* ---------- * Support function for initializing local execution variables @@ -4253,6 +4266,59 @@ exec_stmt_execsql(PLpgSQL_execstate *estate, stmt->mod_stmt_set = true; } + /* + * Some users write "SELECT expr INTO var" instead of "var := 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 matters + * 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 = estate->datums[stmt->target->dno]; + + if (target->dtype == PLPGSQL_DTYPE_ROW) + { + PLpgSQL_row *row = (PLpgSQL_row *) target; + + if (row->nfields == 1) + { + ErrorContextCallback plerrcontext; + + /* + * Setup error traceback support for reporting SQL statement + * in context. + */ + plerrcontext.callback = plpgsql_execsql_error_callback; + plerrcontext.arg = expr; + plerrcontext.previous = error_context_stack; + error_context_stack = &plerrcontext; + + /* 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 = 1; + + /* + * Pop the error context stack + */ + error_context_stack = plerrcontext.previous; + + return PLPGSQL_RC_OK; + } + } + } + /* * Set up ParamListInfo to pass to executor */ -- 2.53.0
