Skip to content

Commit 7643bed

Browse files
tglsfdctglsfdc
committed
When using extended-query protocol, postpone planning of unnamed statements
until Bind is received, so that actual parameter values are visible to the planner. Make use of the parameter values for estimation purposes (but don't fold them into the actual plan). This buys back most of the potential loss of plan quality that ensues from using out-of-line parameters instead of putting literal values right into the query text. This patch creates a notion of constant-folding expressions 'for estimation purposes only', in which case we can be more aggressive than the normal eval_const_expressions() logic can be. Right now the only difference in behavior is inserting bound values for Params, but it will be interesting to look at other possibilities. One that we've seen come up repeatedly is reducing now() and related functions to current values, so that queries like ... WHERE timestampcol > now() - '1 day' have some chance of being planned effectively. Oliver Jowett, with some kibitzing from Tom Lane.
1 parent 5fe8c7d commit 7643bed

File tree

14 files changed

+270
-79
lines changed

14 files changed

+270
-79
lines changed

‎doc/src/sgml/protocol.sgml

Lines changed: 41 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.51 2004/03/21 22:29:10 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/protocol.sgml,v 1.52 2004/06/11 01:08:33 tgl Exp $ -->
22

33
<chapter id="protocol">
44
<title>Frontend/Backend Protocol</title>
@@ -684,6 +684,46 @@
684684
</para>
685685
</note>
686686

687+
<para>
688+
Query planning for named prepared-statement objects occurs when the Parse
689+
message is received. If a query will be repeatedly executed with
690+
different parameters, it may be beneficial to send a single Parse message
691+
containing a parameterized query, followed by multiple Bind
692+
and Execute messages. This will avoid replanning the query on each
693+
execution.
694+
</para>
695+
696+
<para>
697+
The unnamed prepared statement is likewise planned during Parse processing
698+
if the Parse message defines no parameters. But if there are parameters,
699+
query planning is delayed until the first Bind message for the statement
700+
is received. The planner will consider the actual values of the parameters
701+
provided in the Bind message when planning the query.
702+
</para>
703+
704+
<note>
705+
<para>
706+
Query plans generated from a parameterized query may be less
707+
efficient than query plans generated from an equivalent query with actual
708+
parameter values substituted. The query planner cannot make decisions
709+
based on actual parameter values (for example, index selectivity) when
710+
planning a parameterized query assigned to a named prepared-statement
711+
object. This possible penalty is avoided when using the unnamed
712+
statement, since it is not planned until actual parameter values are
713+
available.
714+
</para>
715+
716+
<para>
717+
If a second or subsequent Bind referencing the unnamed prepared-statement
718+
object is received without an intervening Parse, the query is
719+
not replanned. The parameter values used in the first Bind message may
720+
produce a query plan that is only efficient for a subset of possible
721+
parameter values. To force replanning of the query for a fresh set of
722+
parameters, send another Parse message to replace the unnamed
723+
prepared-statement object.
724+
</para>
725+
</note>
726+
687727
<para>
688728
If successfully created, a named portal object lasts till the end of the
689729
current transaction, unless explicitly destroyed. An unnamed portal is

‎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.121 2004/05/26 04:41:10 neilc Exp $
10+
* $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.122 2004/06/11 01:08:35 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -176,7 +176,7 @@ ExplainOneQuery(Query *query, ExplainStmt *stmt, TupOutputState *tstate)
176176
}
177177

178178
/* plan the query */
179-
plan = planner(query, isCursor, cursorOptions);
179+
plan = planner(query, isCursor, cursorOptions, NULL);
180180

181181
/* Create a QueryDesc requesting no output */
182182
queryDesc = CreateQueryDesc(query, plan, None_Receiver, NULL,

‎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.27 2004/05/26 04:41:11 neilc Exp $
17+
* $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.28 2004/06/11 01:08:37 tgl Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -84,7 +84,7 @@ PerformCursorOpen(DeclareCursorStmt *stmt)
8484
errmsg("DECLARE CURSOR ... FOR UPDATE is not supported"),
8585
errdetail("Cursors must be READ ONLY.")));
8686

87-
plan = planner(query, true, stmt->options);
87+
plan = planner(query, true, stmt->options, NULL);
8888

8989
/*
9090
* Create a portal and copy the query and plan into its memory

‎src/backend/commands/prepare.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Copyright (c) 2002-2003, PostgreSQL Global Development Group
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.27 2004/05/26 04:41:11 neilc Exp $
13+
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.28 2004/06/11 01:08:38 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -91,7 +91,7 @@ PrepareQuery(PrepareStmt *stmt)
9191
query_list = QueryRewrite(stmt->query);
9292

9393
/* Generate plans for queries. Snapshot is already set. */
94-
plan_list = pg_plan_queries(query_list, false);
94+
plan_list = pg_plan_queries(query_list, NULL, false);
9595

9696
/* Save the results. */
9797
StorePreparedStatement(stmt->name,

‎src/backend/executor/functions.c

Lines changed: 2 additions & 2 deletions
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.81 2004/05/26 04:41:15 neilc Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/functions.c,v 1.82 2004/06/11 01:08:42 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -100,7 +100,7 @@ init_execution_state(List *queryTree_list)
100100
Plan *planTree;
101101
execution_state *newes;
102102

103-
planTree = pg_plan_query(queryTree);
103+
planTree = pg_plan_query(queryTree, NULL);
104104

105105
newes = (execution_state *) palloc(sizeof(execution_state));
106106
if (preves)

‎src/backend/executor/spi.c

Lines changed: 2 additions & 2 deletions
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.117 2004/06/06 00:41:26 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.118 2004/06/11 01:08:43 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1125,7 +1125,7 @@ _SPI_execute(const char *src, int tcount, _SPI_plan *plan)
11251125
QueryDesc *qdesc;
11261126
DestReceiver *dest;
11271127

1128-
planTree = pg_plan_query(queryTree);
1128+
planTree = pg_plan_query(queryTree, NULL);
11291129
plan_list = lappend(plan_list, planTree);
11301130

11311131
dest = CreateDestReceiver(queryTree->canSetTag ? SPI : None, NULL);

‎src/backend/optimizer/path/clausesel.c

Lines changed: 17 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/clausesel.c,v 1.67 2004/05/30 23:40:28 neilc Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/clausesel.c,v 1.68 2004/06/11 01:08:49 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -487,16 +487,27 @@ clause_selectivity(Query *root,
487487
}
488488
}
489489
}
490-
else if (IsA(clause, Param))
491-
{
492-
/* XXX any way to do better? */
493-
s1 = 1.0;
494-
}
495490
else if (IsA(clause, Const))
496491
{
497492
/* bool constant is pretty easy... */
498493
s1 = ((bool) ((Const *) clause)->constvalue) ? 1.0 : 0.0;
499494
}
495+
else if (IsA(clause, Param))
496+
{
497+
/* see if we can replace the Param */
498+
Node *subst = estimate_expression_value(clause);
499+
500+
if (IsA(subst, Const))
501+
{
502+
/* bool constant is pretty easy... */
503+
s1 = ((bool) ((Const *) subst)->constvalue) ? 1.0 : 0.0;
504+
}
505+
else
506+
{
507+
/* XXX any way to do better? */
508+
s1 = (Selectivity) 0.5;
509+
}
510+
}
500511
else if (not_clause(clause))
501512
{
502513
/* inverse of the selectivity of the underlying clause */

‎src/backend/optimizer/plan/planner.c

Lines changed: 14 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.171 2004/05/30 23:40:29 neilc Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.172 2004/06/11 01:08:52 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -43,6 +43,9 @@
4343
#include "utils/syscache.h"
4444

4545

46+
ParamListInfo PlannerBoundParamList = NULL; /* current boundParams */
47+
48+
4649
/* Expression kind codes for preprocess_expression */
4750
#define EXPRKIND_QUAL 0
4851
#define EXPRKIND_TARGET 1
@@ -71,20 +74,24 @@ static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
7174
*
7275
*****************************************************************************/
7376
Plan *
74-
planner(Query *parse, bool isCursor, int cursorOptions)
77+
planner(Query *parse, bool isCursor, int cursorOptions,
78+
ParamListInfo boundParams)
7579
{
7680
double tuple_fraction;
7781
Plan *result_plan;
7882
Index save_PlannerQueryLevel;
7983
List *save_PlannerParamList;
84+
ParamListInfo save_PlannerBoundParamList;
8085

8186
/*
8287
* The planner can be called recursively (an example is when
8388
* eval_const_expressions tries to pre-evaluate an SQL function). So,
8489
* these global state variables must be saved and restored.
8590
*
86-
* These vars cannot be moved into the Query structure since their whole
87-
* purpose is communication across multiple sub-Queries.
91+
* Query level and the param list cannot be moved into the Query structure
92+
* since their whole purpose is communication across multiple sub-Queries.
93+
* Also, boundParams is explicitly info from outside the Query, and so
94+
* is likewise better handled as a global variable.
8895
*
8996
* Note we do NOT save and restore PlannerPlanId: it exists to assign
9097
* unique IDs to SubPlan nodes, and we want those IDs to be unique for
@@ -93,10 +100,12 @@ planner(Query *parse, bool isCursor, int cursorOptions)
93100
*/
94101
save_PlannerQueryLevel = PlannerQueryLevel;
95102
save_PlannerParamList = PlannerParamList;
103+
save_PlannerBoundParamList = PlannerBoundParamList;
96104

97105
/* Initialize state for handling outer-level references and params */
98106
PlannerQueryLevel = 0; /* will be 1 in top-level subquery_planner */
99107
PlannerParamList = NIL;
108+
PlannerBoundParamList = boundParams;
100109

101110
/* Determine what fraction of the plan is likely to be scanned */
102111
if (isCursor)
@@ -139,6 +148,7 @@ planner(Query *parse, bool isCursor, int cursorOptions)
139148
/* restore state for outer planner, if any */
140149
PlannerQueryLevel = save_PlannerQueryLevel;
141150
PlannerParamList = save_PlannerParamList;
151+
PlannerBoundParamList = save_PlannerBoundParamList;
142152

143153
return result_plan;
144154
}

0 commit comments

Comments
 (0)