Age Owner Branch data TLA Line data Source code
1 : : /*-------------------------------------------------------------------------
2 : : *
3 : : * pg_plan_advice.c
4 : : * main entrypoints for generating and applying planner advice
5 : : *
6 : : * Copyright (c) 2016-2026, PostgreSQL Global Development Group
7 : : *
8 : : * contrib/pg_plan_advice/pg_plan_advice.c
9 : : *
10 : : *-------------------------------------------------------------------------
11 : : */
12 : : #include "postgres.h"
13 : :
14 : : #include "pg_plan_advice.h"
15 : : #include "pgpa_ast.h"
16 : : #include "pgpa_identifier.h"
17 : : #include "pgpa_output.h"
18 : : #include "pgpa_planner.h"
19 : : #include "pgpa_trove.h"
20 : : #include "pgpa_walker.h"
21 : :
22 : : #include "commands/defrem.h"
23 : : #include "commands/explain.h"
24 : : #include "commands/explain_format.h"
25 : : #include "commands/explain_state.h"
26 : : #include "funcapi.h"
27 : : #include "optimizer/planner.h"
28 : : #include "storage/dsm_registry.h"
29 : : #include "utils/guc.h"
30 : :
3 rhaas@postgresql.org 31 :GNC 11 : PG_MODULE_MAGIC;
32 : :
33 : : /* GUC variables */
34 : : char *pg_plan_advice_advice = NULL;
35 : : bool pg_plan_advice_always_store_advice_details = false;
36 : : static bool pg_plan_advice_always_explain_supplied_advice = true;
37 : : bool pg_plan_advice_feedback_warnings = false;
38 : : bool pg_plan_advice_trace_mask = false;
39 : :
40 : : /* Saved hook value */
41 : : static explain_per_plan_hook_type prev_explain_per_plan = NULL;
42 : :
43 : : /* Other file-level globals */
44 : : static int es_extension_id;
45 : : static MemoryContext pgpa_memory_context = NULL;
46 : : static List *advisor_hook_list = NIL;
47 : :
48 : : static void pg_plan_advice_explain_option_handler(ExplainState *es,
49 : : DefElem *opt,
50 : : ParseState *pstate);
51 : : static void pg_plan_advice_explain_per_plan_hook(PlannedStmt *plannedstmt,
52 : : IntoClause *into,
53 : : ExplainState *es,
54 : : const char *queryString,
55 : : ParamListInfo params,
56 : : QueryEnvironment *queryEnv);
57 : : static bool pg_plan_advice_advice_check_hook(char **newval, void **extra,
58 : : GucSource source);
59 : : static DefElem *find_defelem_by_defname(List *deflist, char *defname);
60 : :
61 : : /*
62 : : * Initialize this module.
63 : : */
64 : : void
65 : 11 : _PG_init(void)
66 : : {
67 : 11 : DefineCustomStringVariable("pg_plan_advice.advice",
68 : : "advice to apply during query planning",
69 : : NULL,
70 : : &pg_plan_advice_advice,
71 : : NULL,
72 : : PGC_USERSET,
73 : : 0,
74 : : pg_plan_advice_advice_check_hook,
75 : : NULL,
76 : : NULL);
77 : :
78 : 11 : DefineCustomBoolVariable("pg_plan_advice.always_explain_supplied_advice",
79 : : "EXPLAIN output includes supplied advice even without EXPLAIN (PLAN_ADVICE)",
80 : : NULL,
81 : : &pg_plan_advice_always_explain_supplied_advice,
82 : : true,
83 : : PGC_USERSET,
84 : : 0,
85 : : NULL,
86 : : NULL,
87 : : NULL);
88 : :
89 : 11 : DefineCustomBoolVariable("pg_plan_advice.always_store_advice_details",
90 : : "Generate advice strings even when seemingly not required",
91 : : "Use this option to see generated advice for prepared queries.",
92 : : &pg_plan_advice_always_store_advice_details,
93 : : false,
94 : : PGC_USERSET,
95 : : 0,
96 : : NULL,
97 : : NULL,
98 : : NULL);
99 : :
100 : 11 : DefineCustomBoolVariable("pg_plan_advice.feedback_warnings",
101 : : "Warn when supplied advice does not apply cleanly",
102 : : NULL,
103 : : &pg_plan_advice_feedback_warnings,
104 : : false,
105 : : PGC_USERSET,
106 : : 0,
107 : : NULL,
108 : : NULL,
109 : : NULL);
110 : :
111 : 11 : DefineCustomBoolVariable("pg_plan_advice.trace_mask",
112 : : "Emit debugging messages showing the computed strategy mask for each relation",
113 : : NULL,
114 : : &pg_plan_advice_trace_mask,
115 : : false,
116 : : PGC_USERSET,
117 : : 0,
118 : : NULL,
119 : : NULL,
120 : : NULL);
121 : :
122 : 11 : MarkGUCPrefixReserved("pg_plan_advice");
123 : :
124 : : /* Get an ID that we can use to cache data in an ExplainState. */
125 : 11 : es_extension_id = GetExplainExtensionId("pg_plan_advice");
126 : :
127 : : /* Register the new EXPLAIN options implemented by this module. */
128 : 11 : RegisterExtensionExplainOption("plan_advice",
129 : : pg_plan_advice_explain_option_handler);
130 : :
131 : : /* Install hooks */
132 : 11 : pgpa_planner_install_hooks();
133 : 11 : prev_explain_per_plan = explain_per_plan_hook;
134 : 11 : explain_per_plan_hook = pg_plan_advice_explain_per_plan_hook;
135 : 11 : }
136 : :
137 : : /*
138 : : * Return a pointer to a memory context where long-lived data managed by this
139 : : * module can be stored.
140 : : */
141 : : MemoryContext
3 rhaas@postgresql.org 142 :UNC 0 : pg_plan_advice_get_mcxt(void)
143 : : {
144 [ # # ]: 0 : if (pgpa_memory_context == NULL)
145 : 0 : pgpa_memory_context = AllocSetContextCreate(TopMemoryContext,
146 : : "pg_plan_advice",
147 : : ALLOCSET_DEFAULT_SIZES);
148 : :
149 : 0 : return pgpa_memory_context;
150 : : }
151 : :
152 : : /*
153 : : * Was the PLAN_ADVICE option specified and not set to false?
154 : : */
155 : : bool
3 rhaas@postgresql.org 156 :GNC 311 : pg_plan_advice_should_explain(ExplainState *es)
157 : : {
158 : 311 : bool *plan_advice = NULL;
159 : :
160 [ + + ]: 311 : if (es != NULL)
161 : 272 : plan_advice = GetExplainExtensionState(es, es_extension_id);
162 [ + + + - ]: 311 : return plan_advice != NULL && *plan_advice;
163 : : }
164 : :
165 : : /*
166 : : * Get the advice that should be used while planning a particular query.
167 : : */
168 : : char *
169 : 174 : pg_plan_advice_get_supplied_query_advice(PlannerGlobal *glob,
170 : : Query *parse,
171 : : const char *query_string,
172 : : int cursorOptions,
173 : : ExplainState *es)
174 : : {
175 : : ListCell *lc;
176 : :
177 : : /*
178 : : * If any advisors are loaded, consult them. The first one that produces a
179 : : * non-NULL string wins.
180 : : */
181 [ - + - - : 174 : foreach(lc, advisor_hook_list)
- + ]
182 : : {
3 rhaas@postgresql.org 183 :UNC 0 : pg_plan_advice_advisor_hook hook = lfirst(lc);
184 : : char *advice_string;
185 : :
186 : 0 : advice_string = (*hook) (glob, parse, query_string, cursorOptions, es);
187 [ # # ]: 0 : if (advice_string != NULL)
188 : 0 : return advice_string;
189 : : }
190 : :
191 : : /* Otherwise, just use the value of the GUC. */
3 rhaas@postgresql.org 192 :GNC 174 : return pg_plan_advice_advice;
193 : : }
194 : :
195 : : /*
196 : : * Add an advisor, which can supply advice strings to be used during future
197 : : * query planning operations.
198 : : *
199 : : * The advisor should return NULL if it has no advice string to offer for a
200 : : * given query. If multiple advisors are added, they will be consulted in the
201 : : * order added until one of them returns a non-NULL value.
202 : : */
203 : : void
3 rhaas@postgresql.org 204 :UNC 0 : pg_plan_advice_add_advisor(pg_plan_advice_advisor_hook hook)
205 : : {
206 : : MemoryContext oldcontext;
207 : :
208 : 0 : oldcontext = MemoryContextSwitchTo(pg_plan_advice_get_mcxt());
209 : 0 : advisor_hook_list = lappend(advisor_hook_list, hook);
210 : 0 : MemoryContextSwitchTo(oldcontext);
211 : 0 : }
212 : :
213 : : /*
214 : : * Remove an advisor.
215 : : */
216 : : void
217 : 0 : pg_plan_advice_remove_advisor(pg_plan_advice_advisor_hook hook)
218 : : {
219 : : MemoryContext oldcontext;
220 : :
221 : 0 : oldcontext = MemoryContextSwitchTo(pg_plan_advice_get_mcxt());
222 : 0 : advisor_hook_list = list_delete_ptr(advisor_hook_list, hook);
223 : 0 : MemoryContextSwitchTo(oldcontext);
224 : 0 : }
225 : :
226 : : /*
227 : : * Other loadable modules can use this function to trigger advice generation.
228 : : *
229 : : * Calling this function with activate = true requests that any queries
230 : : * planned afterwards should generate plan advice, which will be stored in the
231 : : * PlannedStmt. Calling this function with activate = false revokes that
232 : : * request. Multiple loadable modules could be using this simultaneously, so
233 : : * make sure to only revoke your own requests.
234 : : *
235 : : * Note that you can't use this function to *suppress* advice generation,
236 : : * which can occur for other reasons, such as the use of EXPLAIN (PLAN_ADVICE),
237 : : * regardless. It's a way of turning advice generation on, not a way of turning
238 : : * it off.
239 : : */
240 : : void
241 : 0 : pg_plan_advice_request_advice_generation(bool activate)
242 : : {
243 [ # # ]: 0 : if (activate)
244 : 0 : pgpa_planner_generate_advice++;
245 : : else
246 : : {
247 [ # # ]: 0 : Assert(pgpa_planner_generate_advice > 0);
248 : 0 : pgpa_planner_generate_advice--;
249 : : }
250 : 0 : }
251 : :
252 : : /*
253 : : * Handler for EXPLAIN (PLAN_ADVICE).
254 : : */
255 : : static void
3 rhaas@postgresql.org 256 :GNC 122 : pg_plan_advice_explain_option_handler(ExplainState *es, DefElem *opt,
257 : : ParseState *pstate)
258 : : {
259 : : bool *plan_advice;
260 : :
261 : 122 : plan_advice = GetExplainExtensionState(es, es_extension_id);
262 : :
263 [ + - ]: 122 : if (plan_advice == NULL)
264 : : {
265 : 122 : plan_advice = palloc0_object(bool);
266 : 122 : SetExplainExtensionState(es, es_extension_id, plan_advice);
267 : : }
268 : :
269 : 122 : *plan_advice = defGetBoolean(opt);
270 : 122 : }
271 : :
272 : : /*
273 : : * Display a string that is likely to consist of multiple lines in EXPLAIN
274 : : * output.
275 : : */
276 : : static void
277 : 234 : pg_plan_advice_explain_text_multiline(ExplainState *es, char *qlabel,
278 : : char *value)
279 : : {
280 : : char *s;
281 : :
282 : : /* For non-text formats, it's best not to add any special handling. */
283 [ + + ]: 234 : if (es->format != EXPLAIN_FORMAT_TEXT)
284 : : {
285 : 1 : ExplainPropertyText(qlabel, value, es);
286 : 1 : return;
287 : : }
288 : :
289 : : /* In text format, if there is no data, display nothing. */
290 [ + + ]: 233 : if (*value == '\0')
291 : 1 : return;
292 : :
293 : : /*
294 : : * It looks nicest to indent each line of the advice separately, beginning
295 : : * on the line below the label.
296 : : */
297 : 232 : ExplainIndentText(es);
298 : 232 : appendStringInfo(es->str, "%s:\n", qlabel);
299 : 232 : es->indent++;
300 [ + + ]: 723 : while ((s = strchr(value, '\n')) != NULL)
301 : : {
302 : 491 : ExplainIndentText(es);
303 : 491 : appendBinaryStringInfo(es->str, value, (s - value) + 1);
304 : 491 : value = s + 1;
305 : : }
306 : :
307 : : /* Don't interpret a terminal newline as a request for an empty line. */
308 [ + + ]: 232 : if (*value != '\0')
309 : : {
310 : 121 : ExplainIndentText(es);
311 : 121 : appendStringInfo(es->str, "%s\n", value);
312 : : }
313 : :
314 : 232 : es->indent--;
315 : : }
316 : :
317 : : /*
318 : : * Add advice feedback to the EXPLAIN output.
319 : : */
320 : : static void
321 : 113 : pg_plan_advice_explain_feedback(ExplainState *es, List *feedback)
322 : : {
323 : : StringInfoData buf;
324 : :
325 : 113 : initStringInfo(&buf);
326 [ + + + + : 359 : foreach_node(DefElem, item, feedback)
+ + ]
327 : : {
328 : 133 : int flags = defGetInt32(item);
329 : :
330 : 133 : appendStringInfo(&buf, "%s /* ", item->defname);
331 : 133 : pgpa_trove_append_flags(&buf, flags);
332 : 133 : appendStringInfo(&buf, " */\n");
333 : : }
334 : :
335 : 113 : pg_plan_advice_explain_text_multiline(es, "Supplied Plan Advice",
336 : : buf.data);
337 : 113 : }
338 : :
339 : : /*
340 : : * Add relevant details, if any, to the EXPLAIN output for a single plan.
341 : : */
342 : : static void
343 : 138 : pg_plan_advice_explain_per_plan_hook(PlannedStmt *plannedstmt,
344 : : IntoClause *into,
345 : : ExplainState *es,
346 : : const char *queryString,
347 : : ParamListInfo params,
348 : : QueryEnvironment *queryEnv)
349 : : {
350 : : bool should_explain;
351 : : DefElem *pgpa_item;
352 : : List *pgpa_list;
353 : :
354 [ - + ]: 138 : if (prev_explain_per_plan)
3 rhaas@postgresql.org 355 :UNC 0 : prev_explain_per_plan(plannedstmt, into, es, queryString, params,
356 : : queryEnv);
357 : :
358 : : /* Should an advice string be part of the EXPLAIN output? */
3 rhaas@postgresql.org 359 :GNC 138 : should_explain = pg_plan_advice_should_explain(es);
360 : :
361 : : /* Find any data pgpa_planner_shutdown stashed in the PlannedStmt. */
362 : 138 : pgpa_item = find_defelem_by_defname(plannedstmt->extension_state,
363 : : "pg_plan_advice");
364 [ + + ]: 138 : pgpa_list = pgpa_item == NULL ? NULL : (List *) pgpa_item->arg;
365 : :
366 : : /*
367 : : * By default, if there is a record of attempting to apply advice during
368 : : * query planning, we always output that information, but the user can set
369 : : * pg_plan_advice.always_explain_supplied_advice = false to suppress that
370 : : * behavior. If they do, we'll only display it when the PLAN_ADVICE option
371 : : * was specified and not set to false.
372 : : *
373 : : * NB: If we're explaining a query planned beforehand -- i.e. a prepared
374 : : * statement -- the application of query advice may not have been
375 : : * recorded, and therefore this won't be able to show anything. Use
376 : : * pg_plan_advice.always_store_advice_details = true to work around this.
377 : : */
378 [ + + - + : 138 : if (pgpa_list != NULL && (pg_plan_advice_always_explain_supplied_advice ||
- - ]
379 : : should_explain))
380 : : {
381 : : DefElem *feedback;
382 : :
383 : 133 : feedback = find_defelem_by_defname(pgpa_list, "feedback");
384 [ + + ]: 133 : if (feedback != NULL)
385 : 113 : pg_plan_advice_explain_feedback(es, (List *) feedback->arg);
386 : : }
387 : :
388 : : /*
389 : : * If the PLAN_ADVICE option was specified -- and not set to FALSE -- show
390 : : * generated advice.
391 : : */
392 [ + + ]: 138 : if (should_explain)
393 : : {
394 : : DefElem *advice_string_item;
395 : 122 : char *advice_string = NULL;
396 : :
397 : : advice_string_item =
398 : 122 : find_defelem_by_defname(pgpa_list, "advice_string");
399 [ + + ]: 122 : if (advice_string_item != NULL)
400 : : {
401 : 121 : advice_string = strVal(advice_string_item->arg);
402 : 121 : pg_plan_advice_explain_text_multiline(es, "Generated Plan Advice",
403 : : advice_string);
404 : : }
405 : : }
406 : 138 : }
407 : :
408 : : /*
409 : : * Check hook for pg_plan_advice.advice
410 : : */
411 : : static bool
412 : 139 : pg_plan_advice_advice_check_hook(char **newval, void **extra, GucSource source)
413 : : {
414 : : MemoryContext oldcontext;
415 : : MemoryContext tmpcontext;
416 : : char *error;
417 : :
418 [ + + ]: 139 : if (*newval == NULL)
419 : 11 : return true;
420 : :
421 : 128 : tmpcontext = AllocSetContextCreate(CurrentMemoryContext,
422 : : "pg_plan_advice.advice",
423 : : ALLOCSET_DEFAULT_SIZES);
424 : 128 : oldcontext = MemoryContextSwitchTo(tmpcontext);
425 : :
426 : : /*
427 : : * It would be nice to save the parse tree that we construct here for
428 : : * eventual use when planning with this advice, but *extra can only point
429 : : * to a single guc_malloc'd chunk, and our parse tree involves an
430 : : * arbitrary number of memory allocations.
431 : : */
432 : 128 : (void) pgpa_parse(*newval, &error);
433 : :
434 [ + + ]: 128 : if (error != NULL)
435 : 17 : GUC_check_errdetail("Could not parse advice: %s", error);
436 : :
437 : 128 : MemoryContextSwitchTo(oldcontext);
438 : 128 : MemoryContextDelete(tmpcontext);
439 : :
440 : 128 : return (error == NULL);
441 : : }
442 : :
443 : : /*
444 : : * Search a list of DefElem objects for a given defname.
445 : : */
446 : : static DefElem *
447 : 393 : find_defelem_by_defname(List *deflist, char *defname)
448 : : {
449 [ + + + + : 540 : foreach_node(DefElem, item, deflist)
+ + ]
450 : : {
451 [ + + ]: 488 : if (strcmp(item->defname, defname) == 0)
452 : 367 : return item;
453 : : }
454 : :
455 : 26 : return NULL;
456 : : }
|
