From 5983a1aaa9137367c834e0ff84cd8d4d48b326b2 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 6 Sep 2006 20:40:48 +0000 Subject: [PATCH] Change processing of extended-Query mode so that an unnamed statement that has parameters is always planned afresh for each Bind command, treating the parameter values as constants in the planner. This removes the performance penalty formerly often paid for using out-of-line parameters --- with this definition, the planner can do constant folding, LIKE optimization, etc. After a suggestion by Andrew@supernews. --- doc/src/sgml/protocol.sgml | 28 ++++------ src/backend/commands/explain.c | 4 +- src/backend/commands/portalcmds.c | 4 +- src/backend/commands/prepare.c | 3 +- src/backend/executor/functions.c | 3 +- src/backend/executor/spi.c | 4 +- src/backend/optimizer/util/clauses.c | 80 ++++++++++++++++++++-------- src/backend/tcop/postgres.c | 50 ++++++++++------- src/include/nodes/params.h | 9 +++- src/pl/plpgsql/src/pl_exec.c | 3 +- 10 files changed, 122 insertions(+), 66 deletions(-) diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 009cedd868..ef2ece938f 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -1,4 +1,4 @@ - + Frontend/Backend Protocol @@ -126,8 +126,9 @@ into multiple steps. The state retained between steps is represented by two types of objects: prepared statements and portals. A prepared statement represents the result of - parsing, semantic analysis, and planning of a textual query string. A - prepared statement is not necessarily ready to execute, because it may + parsing, semantic analysis, and (optionally) planning of a textual query + string. + A prepared statement is not necessarily ready to execute, because it may lack specific values for parameters. A portal represents a ready-to-execute or already-partially-executed statement, with any missing parameter values filled in. (For SELECT statements, @@ -693,7 +694,7 @@ Query planning for named prepared-statement objects occurs when the Parse - message is received. If a query will be repeatedly executed with + message is processed. If a query will be repeatedly executed with different parameters, it may be beneficial to send a single Parse message containing a parameterized query, followed by multiple Bind and Execute messages. This will avoid replanning the query on each @@ -703,9 +704,9 @@ The unnamed prepared statement is likewise planned during Parse processing if the Parse message defines no parameters. But if there are parameters, - query planning is delayed until the first Bind message for the statement - is received. The planner will consider the actual values of the parameters - provided in the Bind message when planning the query. + query planning occurs during Bind processing instead. This allows the + planner to make use of the actual values of the parameters provided in + the Bind message when planning the query. @@ -717,17 +718,8 @@ planning a parameterized query assigned to a named prepared-statement object. This possible penalty is avoided when using the unnamed statement, since it is not planned until actual parameter values are - available. - - - - If a second or subsequent Bind referencing the unnamed prepared-statement - object is received without an intervening Parse, the query is - not replanned. The parameter values used in the first Bind message may - produce a query plan that is only efficient for a subset of possible - parameter values. To force replanning of the query for a fresh set of - parameters, send another Parse message to replace the unnamed - prepared-statement object. + available. The cost is that planning must occur afresh for each Bind, + even if the query stays the same. diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 5787aa413e..48db000ea9 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -7,7 +7,7 @@ * Portions Copyright (c) 1994-5, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.150 2006/08/02 01:59:45 joe Exp $ + * $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.151 2006/09/06 20:40:47 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -191,7 +191,7 @@ ExplainOneQuery(Query *query, ExplainStmt *stmt, ParamListInfo params, } /* plan the query */ - plan = planner(query, isCursor, cursorOptions, NULL); + plan = planner(query, isCursor, cursorOptions, params); /* * Update snapshot command ID to ensure this query sees results of any diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c index 00ad5444ca..e418fabfc5 100644 --- a/src/backend/commands/portalcmds.c +++ b/src/backend/commands/portalcmds.c @@ -14,7 +14,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.53 2006/09/03 03:19:44 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.54 2006/09/06 20:40:47 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -95,7 +95,7 @@ PerformCursorOpen(DeclareCursorStmt *stmt, ParamListInfo params) errmsg("DECLARE CURSOR ... FOR UPDATE/SHARE is not supported"), errdetail("Cursors must be READ ONLY."))); - plan = planner(query, true, stmt->options, NULL); + plan = planner(query, true, stmt->options, params); /* * Create a portal and copy the query and plan into its memory context. diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c index 04445cd33e..dc26430671 100644 --- a/src/backend/commands/prepare.c +++ b/src/backend/commands/prepare.c @@ -10,7 +10,7 @@ * Copyright (c) 2002-2006, PostgreSQL Global Development Group * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.62 2006/08/29 02:11:29 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.63 2006/09/06 20:40:47 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -261,6 +261,7 @@ EvaluateParams(EState *estate, List *params, List *argtypes) ParamExternData *prm = ¶mLI->params[i]; prm->ptype = lfirst_oid(la); + prm->pflags = 0; prm->value = ExecEvalExprSwitchContext(n, GetPerTupleExprContext(estate), &prm->isnull, diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c index 28462ba8b8..e2e5654383 100644 --- a/src/backend/executor/functions.c +++ b/src/backend/executor/functions.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/functions.c,v 1.105 2006/08/12 20:05:55 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/executor/functions.c,v 1.106 2006/09/06 20:40:47 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -442,6 +442,7 @@ postquel_sub_params(SQLFunctionCachePtr fcache, prm->value = fcinfo->arg[i]; prm->isnull = fcinfo->argnull[i]; + prm->pflags = 0; prm->ptype = fcache->argtypes[i]; } } diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c index e6fcae7f0b..dc3ab9e7c3 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.161 2006/09/03 03:19:44 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.162 2006/09/06 20:40:47 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -893,6 +893,7 @@ SPI_cursor_open(const char *name, void *plan, ParamExternData *prm = ¶mLI->params[k]; prm->ptype = spiplan->argtypes[k]; + prm->pflags = 0; prm->isnull = (Nulls && Nulls[k] == 'n'); if (prm->isnull) { @@ -1357,6 +1358,7 @@ _SPI_execute_plan(_SPI_plan *plan, Datum *Values, const char *Nulls, prm->value = Values[k]; prm->isnull = (Nulls && Nulls[k] == 'n'); + prm->pflags = 0; prm->ptype = plan->argtypes[k]; } } diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 71e727a7b4..48b125a774 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.219 2006/08/12 20:05:55 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.220 2006/09/06 20:40:47 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -1462,7 +1462,9 @@ eval_const_expressions(Node *node) * * Currently the extra steps that are taken in this mode are: * 1. Substitute values for Params, where a bound Param value has been made - * available by the caller of planner(). + * available by the caller of planner(), even if the Param isn't marked + * constant. This effectively means that we plan using the first supplied + * value of the Param. * 2. Fold stable, as well as immutable, functions to constants. *-------------------- */ @@ -1487,33 +1489,38 @@ eval_const_expressions_mutator(Node *node, { Param *param = (Param *) node; - /* OK to try to substitute value? */ - if (context->estimate && param->paramkind == PARAM_EXTERN && - PlannerBoundParamList != NULL) + /* Look to see if we've been given a value for this Param */ + if (param->paramkind == PARAM_EXTERN && + PlannerBoundParamList != NULL && + param->paramid > 0 && + param->paramid <= PlannerBoundParamList->numParams) { - /* Look to see if we've been given a value for this Param */ - if (param->paramid > 0 && - param->paramid <= PlannerBoundParamList->numParams) - { - ParamExternData *prm = &PlannerBoundParamList->params[param->paramid - 1]; + ParamExternData *prm = &PlannerBoundParamList->params[param->paramid - 1]; - if (OidIsValid(prm->ptype)) + if (OidIsValid(prm->ptype)) + { + /* OK to substitute parameter value? */ + if (context->estimate || (prm->pflags & PARAM_FLAG_CONST)) { /* - * Found it, so return a Const representing the param - * value. Note that we don't copy pass-by-ref datatypes, - * so the Const will only be valid as long as the bound - * parameter list exists. This is okay for intended uses - * of estimate_expression_value(). + * Return a Const representing the param value. Must copy + * pass-by-ref datatypes, since the Param might be in a + * memory context shorter-lived than our output plan + * should be. */ int16 typLen; bool typByVal; + Datum pval; Assert(prm->ptype == param->paramtype); get_typlenbyval(param->paramtype, &typLen, &typByVal); + if (prm->isnull || typByVal) + pval = prm->value; + else + pval = datumCopy(prm->value, typByVal, typLen); return (Node *) makeConst(param->paramtype, (int) typLen, - prm->value, + pval, prm->isnull, typByVal); } @@ -3016,10 +3023,9 @@ evaluate_expr(Expr *expr, Oid result_type) * stage. In particular, it handles List nodes since a cnf-ified qual clause * will have List structure at the top level, and it handles TargetEntry nodes * so that a scan of a target list can be handled without additional code. - * (But only the "expr" part of a TargetEntry is examined, unless the walker - * chooses to process TargetEntry nodes specially.) Also, RangeTblRef, - * FromExpr, JoinExpr, and SetOperationStmt nodes are handled, so that query - * jointrees and setOperation trees can be processed without additional code. + * Also, RangeTblRef, FromExpr, JoinExpr, and SetOperationStmt nodes are + * handled, so that query jointrees and setOperation trees can be processed + * without additional code. * * expression_tree_walker will handle SubLink nodes by recursing normally * into the "testexpr" subtree (which is an expression belonging to the outer @@ -3364,6 +3370,38 @@ query_tree_walker(Query *query, return true; if (range_table_walker(query->rtable, walker, context, flags)) return true; + if (query->utilityStmt) + { + /* + * Certain utility commands contain general-purpose Querys embedded + * in them --- if this is one, invoke the walker on the sub-Query. + */ + if (IsA(query->utilityStmt, CopyStmt)) + { + if (walker(((CopyStmt *) query->utilityStmt)->query, context)) + return true; + } + if (IsA(query->utilityStmt, DeclareCursorStmt)) + { + if (walker(((DeclareCursorStmt *) query->utilityStmt)->query, context)) + return true; + } + if (IsA(query->utilityStmt, ExplainStmt)) + { + if (walker(((ExplainStmt *) query->utilityStmt)->query, context)) + return true; + } + if (IsA(query->utilityStmt, PrepareStmt)) + { + if (walker(((PrepareStmt *) query->utilityStmt)->query, context)) + return true; + } + if (IsA(query->utilityStmt, ViewStmt)) + { + if (walker(((ViewStmt *) query->utilityStmt)->query, context)) + return true; + } + } return false; } diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 6496b981cf..2e128ece13 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.505 2006/09/03 03:19:44 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.506 2006/09/06 20:40:48 tgl Exp $ * * NOTES * this is the "main" module of the postgres backend and @@ -1168,7 +1168,7 @@ exec_parse_message(const char *query_string, /* string to execute */ * statement, we assume the statement isn't going to hang around long, so * getting rid of temp space quickly is probably not worth the costs of * copying parse/plan trees. So in this case, we set up a special context - * for the unnamed statement, and do all the parsing/planning therein. + * for the unnamed statement, and do all the parsing work therein. */ is_named = (stmt_name[0] != '\0'); if (is_named) @@ -1367,6 +1367,7 @@ exec_bind_message(StringInfo input_message) PreparedStatement *pstmt; Portal portal; ParamListInfo params; + List *plan_list; StringInfoData bind_values_str; pgstat_report_activity(""); @@ -1474,6 +1475,7 @@ exec_bind_message(StringInfo input_message) { Oid ptype = lfirst_oid(l); int32 plength; + Datum pval; bool isNull; StringInfoData pbuf; char csave; @@ -1532,11 +1534,7 @@ exec_bind_message(StringInfo input_message) else pstring = pg_client_to_server(pbuf.data, plength); - params->params[paramno].value = - OidInputFunctionCall(typinput, - pstring, - typioparam, - -1); + pval = OidInputFunctionCall(typinput, pstring, typioparam, -1); /* Save the parameter values */ appendStringInfo(&bind_values_str, "%s$%d = ", @@ -1576,10 +1574,7 @@ exec_bind_message(StringInfo input_message) else bufptr = &pbuf; - params->params[paramno].value = OidReceiveFunctionCall(typreceive, - bufptr, - typioparam, - -1); + pval = OidReceiveFunctionCall(typreceive, bufptr, typioparam, -1); /* Trouble if it didn't eat the whole buffer */ if (!isNull && pbuf.cursor != pbuf.len) @@ -1596,13 +1591,22 @@ exec_bind_message(StringInfo input_message) (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("unsupported format code: %d", pformat))); + pval = 0; /* keep compiler quiet */ } /* Restore message buffer contents */ if (!isNull) pbuf.data[plength] = csave; + params->params[paramno].value = pval; params->params[paramno].isnull = isNull; + /* + * We mark the params as CONST. This has no effect if we already + * did planning, but if we didn't, it licenses the planner to + * substitute the parameters directly into the one-shot plan we + * will generate below. + */ + params->params[paramno].pflags = PARAM_FLAG_CONST; params->params[paramno].ptype = ptype; paramno++; @@ -1641,19 +1645,29 @@ exec_bind_message(StringInfo input_message) /* * If we didn't plan the query before, do it now. This allows the planner - * to make use of the concrete parameter values we now have. + * to make use of the concrete parameter values we now have. Because we + * use PARAM_FLAG_CONST, the plan is good only for this set of param + * values, and so we generate the plan in the portal's own memory context + * where it will be thrown away after use. As in exec_parse_message, + * we make no attempt to recover planner temporary memory until the end + * of the operation. * - * This happens only for unnamed statements, and so switching into the - * statement context for planning is correct (see notes in - * exec_parse_message). + * XXX because the planner has a bad habit of scribbling on its input, + * we have to make a copy of the parse trees, just in case someone binds + * and executes an unnamed statement multiple times. FIXME someday */ if (pstmt->plan_list == NIL && pstmt->query_list != NIL) { - MemoryContext oldContext = MemoryContextSwitchTo(pstmt->context); + MemoryContext oldContext; - pstmt->plan_list = pg_plan_queries(pstmt->query_list, params, true); + oldContext = MemoryContextSwitchTo(PortalGetHeapMemory(portal)); + plan_list = pg_plan_queries(copyObject(pstmt->query_list), + params, + true); MemoryContextSwitchTo(oldContext); } + else + plan_list = pstmt->plan_list; /* * Define portal and start execution. @@ -1664,7 +1678,7 @@ exec_bind_message(StringInfo input_message) bind_values_str.len ? pstrdup(bind_values_str.data) : NULL, pstmt->commandTag, pstmt->query_list, - pstmt->plan_list, + plan_list, pstmt->context); PortalStart(portal, params, InvalidSnapshot); diff --git a/src/include/nodes/params.h b/src/include/nodes/params.h index 13eb25876d..3a93d75f30 100644 --- a/src/include/nodes/params.h +++ b/src/include/nodes/params.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/params.h,v 1.31 2006/04/22 01:26:01 tgl Exp $ + * $PostgreSQL: pgsql/src/include/nodes/params.h,v 1.32 2006/09/06 20:40:48 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -26,15 +26,22 @@ * Although parameter numbers are normally consecutive, we allow * ptype == InvalidOid to signal an unused array entry. * + * PARAM_FLAG_CONST signals the planner that it may treat this parameter + * as a constant (i.e., generate a plan that works only for this value + * of the parameter). + * * Although the data structure is really an array, not a list, we keep * the old typedef name to avoid unnecessary code changes. * ---------------- */ +#define PARAM_FLAG_CONST 0x0001 /* parameter is constant */ + typedef struct ParamExternData { Datum value; /* parameter value */ bool isnull; /* is it NULL? */ + uint16 pflags; /* flag bits, see above */ Oid ptype; /* parameter's datatype, or 0 */ } ParamExternData; diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index dedba15117..7b350caf15 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.177 2006/08/27 23:47:58 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.178 2006/09/06 20:40:48 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -3958,6 +3958,7 @@ exec_eval_simple_expr(PLpgSQL_execstate *estate, ParamExternData *prm = ¶mLI->params[i]; PLpgSQL_datum *datum = estate->datums[expr->params[i]]; + prm->pflags = 0; exec_eval_datum(estate, datum, expr->plan_argtypes[i], &prm->ptype, &prm->value, &prm->isnull); -- 2.40.0