Skip to content

Commit 604ffd2

Browse files
tglsfdctglsfdc
committed
Create hooks to let a loadable plugin monitor (or even replace) the planner
and/or create plans for hypothetical situations; in particular, investigate plans that would be generated using hypothetical indexes. This is a heavily-rewritten version of the hooks proposed by Gurjeet Singh for his Index Advisor project. In this formulation, the index advisor can be entirely a loadable module instead of requiring a significant part to be in the core backend, and plans can be generated for hypothetical indexes without requiring the creation and rolling-back of system catalog entries. The index advisor patch as-submitted is not compatible with these hooks, but it needs significant work anyway due to other 8.2-to-8.3 planner changes. With these hooks in the core backend, development of the advisor can proceed as a pgfoundry project.
1 parent ce5b24a commit 604ffd2

File tree

9 files changed

+168
-62
lines changed

9 files changed

+168
-62
lines changed

‎src/backend/commands/explain.c

+68-28
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.163 2007/05/04 21:29:52 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.164 2007/05/25 17:54:24 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -33,6 +33,12 @@
3333
#include "utils/tuplesort.h"
3434

3535

36+
/* Hook for plugins to get control in ExplainOneQuery() */
37+
ExplainOneQuery_hook_type ExplainOneQuery_hook = NULL;
38+
/* Hook for plugins to get control in explain_get_index_name() */
39+
explain_get_index_name_hook_type explain_get_index_name_hook = NULL;
40+
41+
3642
typedef struct ExplainState
3743
{
3844
/* options */
@@ -61,6 +67,8 @@ static void show_sort_keys(Plan *sortplan, int nkeys, AttrNumber *keycols,
6167
StringInfo str, int indent, ExplainState *es);
6268
static void show_sort_info(SortState *sortstate,
6369
StringInfo str, int indent, ExplainState *es);
70+
static const char *explain_get_index_name(Oid indexId);
71+
6472

6573
/*
6674
* ExplainQuery -
@@ -140,9 +148,6 @@ static void
140148
ExplainOneQuery(Query *query, ExplainStmt *stmt, const char *queryString,
141149
ParamListInfo params, TupOutputState *tstate)
142150
{
143-
PlannedStmt *plan;
144-
QueryDesc *queryDesc;
145-
146151
/* planner will not cope with utility statements */
147152
if (query->commandType == CMD_UTILITY)
148153
{
@@ -151,25 +156,19 @@ ExplainOneQuery(Query *query, ExplainStmt *stmt, const char *queryString,
151156
return;
152157
}
153158

154-
/* plan the query */
155-
plan = planner(query, 0, params);
156-
157-
/*
158-
* Update snapshot command ID to ensure this query sees results of any
159-
* previously executed queries. (It's a bit cheesy to modify
160-
* ActiveSnapshot without making a copy, but for the limited ways in which
161-
* EXPLAIN can be invoked, I think it's OK, because the active snapshot
162-
* shouldn't be shared with anything else anyway.)
163-
*/
164-
ActiveSnapshot->curcid = GetCurrentCommandId();
159+
/* if an advisor plugin is present, let it manage things */
160+
if (ExplainOneQuery_hook)
161+
(*ExplainOneQuery_hook) (query, stmt, queryString, params, tstate);
162+
else
163+
{
164+
PlannedStmt *plan;
165165

166-
/* Create a QueryDesc requesting no output */
167-
queryDesc = CreateQueryDesc(plan,
168-
ActiveSnapshot, InvalidSnapshot,
169-
None_Receiver, params,
170-
stmt->analyze);
166+
/* plan the query */
167+
plan = planner(query, 0, params);
171168

172-
ExplainOnePlan(queryDesc, stmt, tstate);
169+
/* run it (if needed) and produce output */
170+
ExplainOnePlan(plan, params, stmt, tstate);
171+
}
173172
}
174173

175174
/*
@@ -210,20 +209,35 @@ ExplainOneUtility(Node *utilityStmt, ExplainStmt *stmt,
210209
* not running the query. No cursor will be created, however.
211210
*
212211
* This is exported because it's called back from prepare.c in the
213-
* EXPLAIN EXECUTE case
214-
*
215-
* Note: the passed-in QueryDesc is freed when we're done with it
212+
* EXPLAIN EXECUTE case, and because an index advisor plugin would need
213+
* to call it.
216214
*/
217215
void
218-
ExplainOnePlan(QueryDesc *queryDesc, ExplainStmt *stmt,
219-
TupOutputState *tstate)
216+
ExplainOnePlan(PlannedStmt *plannedstmt, ParamListInfo params,
217+
ExplainStmt *stmt, TupOutputState *tstate)
220218
{
219+
QueryDesc *queryDesc;
221220
instr_time starttime;
222221
double totaltime = 0;
223222
ExplainState *es;
224223
StringInfoData buf;
225224
int eflags;
226225

226+
/*
227+
* Update snapshot command ID to ensure this query sees results of any
228+
* previously executed queries. (It's a bit cheesy to modify
229+
* ActiveSnapshot without making a copy, but for the limited ways in which
230+
* EXPLAIN can be invoked, I think it's OK, because the active snapshot
231+
* shouldn't be shared with anything else anyway.)
232+
*/
233+
ActiveSnapshot->curcid = GetCurrentCommandId();
234+
235+
/* Create a QueryDesc requesting no output */
236+
queryDesc = CreateQueryDesc(plannedstmt,
237+
ActiveSnapshot, InvalidSnapshot,
238+
None_Receiver, params,
239+
stmt->analyze);
240+
227241
INSTR_TIME_SET_CURRENT(starttime);
228242

229243
/* If analyzing, we need to cope with queued triggers */
@@ -592,7 +606,7 @@ explain_outNode(StringInfo str,
592606
if (ScanDirectionIsBackward(((IndexScan *) plan)->indexorderdir))
593607
appendStringInfoString(str, " Backward");
594608
appendStringInfo(str, " using %s",
595-
quote_identifier(get_rel_name(((IndexScan *) plan)->indexid)));
609+
explain_get_index_name(((IndexScan *) plan)->indexid));
596610
/* FALL THRU */
597611
case T_SeqScan:
598612
case T_BitmapHeapScan:
@@ -618,7 +632,7 @@ explain_outNode(StringInfo str,
618632
break;
619633
case T_BitmapIndexScan:
620634
appendStringInfo(str, " on %s",
621-
quote_identifier(get_rel_name(((BitmapIndexScan *) plan)->indexid)));
635+
explain_get_index_name(((BitmapIndexScan *) plan)->indexid));
622636
break;
623637
case T_SubqueryScan:
624638
if (((Scan *) plan)->scanrelid > 0)
@@ -1150,3 +1164,29 @@ show_sort_info(SortState *sortstate,
11501164
pfree(sortinfo);
11511165
}
11521166
}
1167+
1168+
/*
1169+
* Fetch the name of an index in an EXPLAIN
1170+
*
1171+
* We allow plugins to get control here so that plans involving hypothetical
1172+
* indexes can be explained.
1173+
*/
1174+
static const char *
1175+
explain_get_index_name(Oid indexId)
1176+
{
1177+
const char *result;
1178+
1179+
if (explain_get_index_name_hook)
1180+
result = (*explain_get_index_name_hook) (indexId);
1181+
else
1182+
result = NULL;
1183+
if (result == NULL)
1184+
{
1185+
/* default behavior: look in the catalogs and quote it */
1186+
result = get_rel_name(indexId);
1187+
if (result == NULL)
1188+
elog(ERROR, "cache lookup failed for index %u", indexId);
1189+
result = quote_identifier(result);
1190+
}
1191+
return result;
1192+
}

‎src/backend/commands/prepare.c

+2-19
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Copyright (c) 2002-2007, PostgreSQL Global Development Group
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.75 2007/04/27 22:05:47 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.76 2007/05/25 17:54:25 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -678,8 +678,6 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, ExplainStmt *stmt,
678678

679679
if (IsA(pstmt, PlannedStmt))
680680
{
681-
QueryDesc *qdesc;
682-
683681
if (execstmt->into)
684682
{
685683
if (pstmt->commandType != CMD_SELECT ||
@@ -694,22 +692,7 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, ExplainStmt *stmt,
694692
pstmt->intoClause = execstmt->into;
695693
}
696694

697-
/*
698-
* Update snapshot command ID to ensure this query sees results of
699-
* any previously executed queries. (It's a bit cheesy to modify
700-
* ActiveSnapshot without making a copy, but for the limited ways
701-
* in which EXPLAIN can be invoked, I think it's OK, because the
702-
* active snapshot shouldn't be shared with anything else anyway.)
703-
*/
704-
ActiveSnapshot->curcid = GetCurrentCommandId();
705-
706-
/* Create a QueryDesc requesting no output */
707-
qdesc = CreateQueryDesc(pstmt,
708-
ActiveSnapshot, InvalidSnapshot,
709-
None_Receiver,
710-
paramLI, stmt->analyze);
711-
712-
ExplainOnePlan(qdesc, stmt, tstate);
695+
ExplainOnePlan(pstmt, paramLI, stmt, tstate);
713696
}
714697
else
715698
{

‎src/backend/executor/nodeBitmapIndexscan.c

+14-4
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/nodeBitmapIndexscan.c,v 1.22 2007/01/05 22:19:28 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/nodeBitmapIndexscan.c,v 1.23 2007/05/25 17:54:25 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -198,10 +198,12 @@ ExecEndBitmapIndexScan(BitmapIndexScanState *node)
198198
#endif
199199

200200
/*
201-
* close the index relation
201+
* close the index relation (no-op if we didn't open it)
202202
*/
203-
index_endscan(indexScanDesc);
204-
index_close(indexRelationDesc, NoLock);
203+
if (indexScanDesc)
204+
index_endscan(indexScanDesc);
205+
if (indexRelationDesc)
206+
index_close(indexRelationDesc, NoLock);
205207
}
206208

207209
/* ----------------------------------------------------------------
@@ -256,6 +258,14 @@ ExecInitBitmapIndexScan(BitmapIndexScan *node, EState *estate, int eflags)
256258
indexstate->ss.ss_currentRelation = NULL;
257259
indexstate->ss.ss_currentScanDesc = NULL;
258260

261+
/*
262+
* If we are just doing EXPLAIN (ie, aren't going to run the plan),
263+
* stop here. This allows an index-advisor plugin to EXPLAIN a plan
264+
* containing references to nonexistent indexes.
265+
*/
266+
if (eflags & EXEC_FLAG_EXPLAIN_ONLY)
267+
return indexstate;
268+
259269
/*
260270
* Open the index relation.
261271
*

‎src/backend/executor/nodeIndexscan.c

+14-4
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/nodeIndexscan.c,v 1.121 2007/04/06 22:33:42 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/nodeIndexscan.c,v 1.122 2007/05/25 17:54:25 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -415,10 +415,12 @@ ExecEndIndexScan(IndexScanState *node)
415415
ExecClearTuple(node->ss.ss_ScanTupleSlot);
416416

417417
/*
418-
* close the index relation
418+
* close the index relation (no-op if we didn't open it)
419419
*/
420-
index_endscan(indexScanDesc);
421-
index_close(indexRelationDesc, NoLock);
420+
if (indexScanDesc)
421+
index_endscan(indexScanDesc);
422+
if (indexRelationDesc)
423+
index_close(indexRelationDesc, NoLock);
422424

423425
/*
424426
* close the heap relation.
@@ -520,6 +522,14 @@ ExecInitIndexScan(IndexScan *node, EState *estate, int eflags)
520522
*/
521523
ExecAssignScanType(&indexstate->ss, RelationGetDescr(currentRelation));
522524

525+
/*
526+
* If we are just doing EXPLAIN (ie, aren't going to run the plan),
527+
* stop here. This allows an index-advisor plugin to EXPLAIN a plan
528+
* containing references to nonexistent indexes.
529+
*/
530+
if (eflags & EXEC_FLAG_EXPLAIN_ONLY)
531+
return indexstate;
532+
523533
/*
524534
* Open the index relation.
525535
*

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

+25-1
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.219 2007/05/04 01:13:44 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.220 2007/05/25 17:54:25 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -42,6 +42,10 @@
4242
#include "utils/syscache.h"
4343

4444

45+
/* Hook for plugins to get control in planner() */
46+
planner_hook_type planner_hook = NULL;
47+
48+
4549
/* Expression kind codes for preprocess_expression */
4650
#define EXPRKIND_QUAL 0
4751
#define EXPRKIND_TARGET 1
@@ -79,9 +83,29 @@ static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
7983
*
8084
* Query optimizer entry point
8185
*
86+
* To support loadable plugins that monitor or modify planner behavior,
87+
* we provide a hook variable that lets a plugin get control before and
88+
* after the standard planning process. The plugin would normally call
89+
* standard_planner().
90+
*
91+
* Note to plugin authors: standard_planner() scribbles on its Query input,
92+
* so you'd better copy that data structure if you want to plan more than once.
93+
*
8294
*****************************************************************************/
8395
PlannedStmt *
8496
planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
97+
{
98+
PlannedStmt *result;
99+
100+
if (planner_hook)
101+
result = (*planner_hook) (parse, cursorOptions, boundParams);
102+
else
103+
result = standard_planner(parse, cursorOptions, boundParams);
104+
return result;
105+
}
106+
107+
PlannedStmt *
108+
standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
85109
{
86110
PlannedStmt *result;
87111
PlannerGlobal *glob;

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

+12-1
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.134 2007/04/21 21:01:45 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.135 2007/05/25 17:54:25 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -40,6 +40,9 @@
4040
/* GUC parameter */
4141
bool constraint_exclusion = false;
4242

43+
/* Hook for plugins to get control in get_relation_info() */
44+
get_relation_info_hook_type get_relation_info_hook = NULL;
45+
4346

4447
static void estimate_rel_size(Relation rel, int32 *attr_widths,
4548
BlockNumber *pages, double *tuples);
@@ -279,6 +282,14 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
279282
rel->indexlist = indexinfos;
280283

281284
heap_close(relation, NoLock);
285+
286+
/*
287+
* Allow a plugin to editorialize on the info we obtained from the
288+
* catalogs. Actions might include altering the assumed relation size,
289+
* removing an index, or adding a hypothetical index to the indexlist.
290+
*/
291+
if (get_relation_info_hook)
292+
(*get_relation_info_hook) (root, relationObjectId, inhparent, rel);
282293
}
283294

284295
/*

0 commit comments

Comments
 (0)