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