Skip to content

Commit 5983a1a

Browse files
tglsfdctglsfdc
committed
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.
1 parent 389870b commit 5983a1a

File tree

10 files changed

+122
-66
lines changed

10 files changed

+122
-66
lines changed

‎doc/src/sgml/protocol.sgml

Lines changed: 10 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.65 2006/06/18 15:38:36 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.66 2006/09/06 20:40:47 tgl Exp $ -->
22

33
<chapter id="protocol">
44
<title>Frontend/Backend Protocol</title>
@@ -126,8 +126,9 @@
126126
into multiple steps. The state retained between steps is represented
127127
by two types of objects: <firstterm>prepared statements</> and
128128
<firstterm>portals</>. A prepared statement represents the result of
129-
parsing, semantic analysis, and planning of a textual query string. A
130-
prepared statement is not necessarily ready to execute, because it may
129+
parsing, semantic analysis, and (optionally) planning of a textual query
130+
string.
131+
A prepared statement is not necessarily ready to execute, because it may
131132
lack specific values for <firstterm>parameters</>. A portal represents
132133
a ready-to-execute or already-partially-executed statement, with any
133134
missing parameter values filled in. (For <command>SELECT</> statements,
@@ -693,7 +694,7 @@
693694

694695
<para>
695696
Query planning for named prepared-statement objects occurs when the Parse
696-
message is received. If a query will be repeatedly executed with
697+
message is processed. If a query will be repeatedly executed with
697698
different parameters, it may be beneficial to send a single Parse message
698699
containing a parameterized query, followed by multiple Bind
699700
and Execute messages. This will avoid replanning the query on each
@@ -703,9 +704,9 @@
703704
<para>
704705
The unnamed prepared statement is likewise planned during Parse processing
705706
if the Parse message defines no parameters. But if there are parameters,
706-
query planning is delayed until the first Bind message for the statement
707-
is received. The planner will consider the actual values of the parameters
708-
provided in the Bind message when planning the query.
707+
query planning occurs during Bind processing instead. This allows the
708+
planner to make use of the actual values of the parameters provided in
709+
the Bind message when planning the query.
709710
</para>
710711

711712
<note>
@@ -717,17 +718,8 @@
717718
planning a parameterized query assigned to a named prepared-statement
718719
object. This possible penalty is avoided when using the unnamed
719720
statement, since it is not planned until actual parameter values are
720-
available.
721-
</para>
722-
723-
<para>
724-
If a second or subsequent Bind referencing the unnamed prepared-statement
725-
object is received without an intervening Parse, the query is
726-
not replanned. The parameter values used in the first Bind message may
727-
produce a query plan that is only efficient for a subset of possible
728-
parameter values. To force replanning of the query for a fresh set of
729-
parameters, send another Parse message to replace the unnamed
730-
prepared-statement object.
721+
available. The cost is that planning must occur afresh for each Bind,
722+
even if the query stays the same.
731723
</para>
732724
</note>
733725

‎src/backend/commands/explain.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994-5, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.150 2006/08/02 01:59:45 joe Exp $
10+
* $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.151 2006/09/06 20:40:47 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -191,7 +191,7 @@ ExplainOneQuery(Query *query, ExplainStmt *stmt, ParamListInfo params,
191191
}
192192

193193
/* plan the query */
194-
plan = planner(query, isCursor, cursorOptions, NULL);
194+
plan = planner(query, isCursor, cursorOptions, params);
195195

196196
/*
197197
* Update snapshot command ID to ensure this query sees results of any

‎src/backend/commands/portalcmds.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
*
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.53 2006/09/03 03:19:44 momjian Exp $
17+
* $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.54 2006/09/06 20:40:47 tgl Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -95,7 +95,7 @@ PerformCursorOpen(DeclareCursorStmt *stmt, ParamListInfo params)
9595
errmsg("DECLARE CURSOR ... FOR UPDATE/SHARE is not supported"),
9696
errdetail("Cursors must be READ ONLY.")));
9797

98-
plan = planner(query, true, stmt->options, NULL);
98+
plan = planner(query, true, stmt->options, params);
9999

100100
/*
101101
* Create a portal and copy the query and plan into its memory context.

‎src/backend/commands/prepare.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Copyright (c) 2002-2006, PostgreSQL Global Development Group
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.62 2006/08/29 02:11:29 momjian Exp $
13+
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.63 2006/09/06 20:40:47 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -261,6 +261,7 @@ EvaluateParams(EState *estate, List *params, List *argtypes)
261261
ParamExternData *prm = &paramLI->params[i];
262262

263263
prm->ptype = lfirst_oid(la);
264+
prm->pflags = 0;
264265
prm->value = ExecEvalExprSwitchContext(n,
265266
GetPerTupleExprContext(estate),
266267
&prm->isnull,

‎src/backend/executor/functions.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/functions.c,v 1.105 2006/08/12 20:05:55 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/functions.c,v 1.106 2006/09/06 20:40:47 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -442,6 +442,7 @@ postquel_sub_params(SQLFunctionCachePtr fcache,
442442

443443
prm->value = fcinfo->arg[i];
444444
prm->isnull = fcinfo->argnull[i];
445+
prm->pflags = 0;
445446
prm->ptype = fcache->argtypes[i];
446447
}
447448
}

‎src/backend/executor/spi.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.161 2006/09/03 03:19:44 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.162 2006/09/06 20:40:47 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -893,6 +893,7 @@ SPI_cursor_open(const char *name, void *plan,
893893
ParamExternData *prm = &paramLI->params[k];
894894

895895
prm->ptype = spiplan->argtypes[k];
896+
prm->pflags = 0;
896897
prm->isnull = (Nulls && Nulls[k] == 'n');
897898
if (prm->isnull)
898899
{
@@ -1357,6 +1358,7 @@ _SPI_execute_plan(_SPI_plan *plan, Datum *Values, const char *Nulls,
13571358

13581359
prm->value = Values[k];
13591360
prm->isnull = (Nulls && Nulls[k] == 'n');
1361+
prm->pflags = 0;
13601362
prm->ptype = plan->argtypes[k];
13611363
}
13621364
}

‎src/backend/optimizer/util/clauses.c

Lines changed: 59 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.219 2006/08/12 20:05:55 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.220 2006/09/06 20:40:47 tgl Exp $
1212
*
1313
* HISTORY
1414
* AUTHOR DATE MAJOR EVENT
@@ -1462,7 +1462,9 @@ eval_const_expressions(Node *node)
14621462
*
14631463
* Currently the extra steps that are taken in this mode are:
14641464
* 1. Substitute values for Params, where a bound Param value has been made
1465-
* available by the caller of planner().
1465+
* available by the caller of planner(), even if the Param isn't marked
1466+
* constant. This effectively means that we plan using the first supplied
1467+
* value of the Param.
14661468
* 2. Fold stable, as well as immutable, functions to constants.
14671469
*--------------------
14681470
*/
@@ -1487,33 +1489,38 @@ eval_const_expressions_mutator(Node *node,
14871489
{
14881490
Param *param = (Param *) node;
14891491

1490-
/* OK to try to substitute value? */
1491-
if (context->estimate && param->paramkind == PARAM_EXTERN &&
1492-
PlannerBoundParamList != NULL)
1492+
/* Look to see if we've been given a value for this Param */
1493+
if (param->paramkind == PARAM_EXTERN &&
1494+
PlannerBoundParamList != NULL &&
1495+
param->paramid > 0 &&
1496+
param->paramid <= PlannerBoundParamList->numParams)
14931497
{
1494-
/* Look to see if we've been given a value for this Param */
1495-
if (param->paramid > 0 &&
1496-
param->paramid <= PlannerBoundParamList->numParams)
1497-
{
1498-
ParamExternData *prm = &PlannerBoundParamList->params[param->paramid - 1];
1498+
ParamExternData *prm = &PlannerBoundParamList->params[param->paramid - 1];
14991499

1500-
if (OidIsValid(prm->ptype))
1500+
if (OidIsValid(prm->ptype))
1501+
{
1502+
/* OK to substitute parameter value? */
1503+
if (context->estimate || (prm->pflags & PARAM_FLAG_CONST))
15011504
{
15021505
/*
1503-
* Found it, so return a Const representing the param
1504-
* value. Note that we don't copy pass-by-ref datatypes,
1505-
* so the Const will only be valid as long as the bound
1506-
* parameter list exists. This is okay for intended uses
1507-
* of estimate_expression_value().
1506+
* Return a Const representing the param value. Must copy
1507+
* pass-by-ref datatypes, since the Param might be in a
1508+
* memory context shorter-lived than our output plan
1509+
* should be.
15081510
*/
15091511
int16 typLen;
15101512
bool typByVal;
1513+
Datum pval;
15111514

15121515
Assert(prm->ptype == param->paramtype);
15131516
get_typlenbyval(param->paramtype, &typLen, &typByVal);
1517+
if (prm->isnull || typByVal)
1518+
pval = prm->value;
1519+
else
1520+
pval = datumCopy(prm->value, typByVal, typLen);
15141521
return (Node *) makeConst(param->paramtype,
15151522
(int) typLen,
1516-
prm->value,
1523+
pval,
15171524
prm->isnull,
15181525
typByVal);
15191526
}
@@ -3016,10 +3023,9 @@ evaluate_expr(Expr *expr, Oid result_type)
30163023
* stage. In particular, it handles List nodes since a cnf-ified qual clause
30173024
* will have List structure at the top level, and it handles TargetEntry nodes
30183025
* so that a scan of a target list can be handled without additional code.
3019-
* (But only the "expr" part of a TargetEntry is examined, unless the walker
3020-
* chooses to process TargetEntry nodes specially.) Also, RangeTblRef,
3021-
* FromExpr, JoinExpr, and SetOperationStmt nodes are handled, so that query
3022-
* jointrees and setOperation trees can be processed without additional code.
3026+
* Also, RangeTblRef, FromExpr, JoinExpr, and SetOperationStmt nodes are
3027+
* handled, so that query jointrees and setOperation trees can be processed
3028+
* without additional code.
30233029
*
30243030
* expression_tree_walker will handle SubLink nodes by recursing normally
30253031
* into the "testexpr" subtree (which is an expression belonging to the outer
@@ -3364,6 +3370,38 @@ query_tree_walker(Query *query,
33643370
return true;
33653371
if (range_table_walker(query->rtable, walker, context, flags))
33663372
return true;
3373+
if (query->utilityStmt)
3374+
{
3375+
/*
3376+
* Certain utility commands contain general-purpose Querys embedded
3377+
* in them --- if this is one, invoke the walker on the sub-Query.
3378+
*/
3379+
if (IsA(query->utilityStmt, CopyStmt))
3380+
{
3381+
if (walker(((CopyStmt *) query->utilityStmt)->query, context))
3382+
return true;
3383+
}
3384+
if (IsA(query->utilityStmt, DeclareCursorStmt))
3385+
{
3386+
if (walker(((DeclareCursorStmt *) query->utilityStmt)->query, context))
3387+
return true;
3388+
}
3389+
if (IsA(query->utilityStmt, ExplainStmt))
3390+
{
3391+
if (walker(((ExplainStmt *) query->utilityStmt)->query, context))
3392+
return true;
3393+
}
3394+
if (IsA(query->utilityStmt, PrepareStmt))
3395+
{
3396+
if (walker(((PrepareStmt *) query->utilityStmt)->query, context))
3397+
return true;
3398+
}
3399+
if (IsA(query->utilityStmt, ViewStmt))
3400+
{
3401+
if (walker(((ViewStmt *) query->utilityStmt)->query, context))
3402+
return true;
3403+
}
3404+
}
33673405
return false;
33683406
}
33693407

0 commit comments

Comments
 (0)