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