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 14 : 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 14 : _PG_init(void)
66 : {
67 14 : 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 14 : 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 14 : 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 14 : 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 14 : 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 14 : MarkGUCPrefixReserved("pg_plan_advice");
123 :
124 : /* Get an ID that we can use to cache data in an ExplainState. */
125 14 : es_extension_id = GetExplainExtensionId("pg_plan_advice");
126 :
127 : /* Register the new EXPLAIN options implemented by this module. */
128 14 : RegisterExtensionExplainOption("plan_advice",
129 : pg_plan_advice_explain_option_handler,
130 : GUCCheckBooleanExplainOption);
131 :
132 : /* Install hooks */
133 14 : pgpa_planner_install_hooks();
134 14 : prev_explain_per_plan = explain_per_plan_hook;
135 14 : explain_per_plan_hook = pg_plan_advice_explain_per_plan_hook;
136 14 : }
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 3 : pg_plan_advice_get_mcxt(void)
144 : {
145 3 : if (pgpa_memory_context == NULL)
146 3 : pgpa_memory_context = AllocSetContextCreate(TopMemoryContext,
147 : "pg_plan_advice",
148 : ALLOCSET_DEFAULT_SIZES);
149 :
150 3 : return pgpa_memory_context;
151 : }
152 :
153 : /*
154 : * Was the PLAN_ADVICE option specified and not set to false?
155 : */
156 : bool
157 48047 : pg_plan_advice_should_explain(ExplainState *es)
158 : {
159 48047 : bool *plan_advice = NULL;
160 :
161 48047 : if (es != NULL)
162 7331 : plan_advice = GetExplainExtensionState(es, es_extension_id);
163 48047 : 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 88590 : 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 132857 : foreach(lc, advisor_hook_list)
183 : {
184 88433 : pg_plan_advice_advisor_hook hook = lfirst(lc);
185 : char *advice_string;
186 :
187 88433 : advice_string = (*hook) (glob, parse, query_string, cursorOptions, es);
188 87665 : if (advice_string != NULL)
189 43398 : return advice_string;
190 : }
191 :
192 : /* Otherwise, just use the value of the GUC. */
193 44424 : 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 3 : pg_plan_advice_add_advisor(pg_plan_advice_advisor_hook hook)
206 : {
207 : MemoryContext oldcontext;
208 :
209 3 : oldcontext = MemoryContextSwitchTo(pg_plan_advice_get_mcxt());
210 3 : advisor_hook_list = lappend(advisor_hook_list, hook);
211 3 : MemoryContextSwitchTo(oldcontext);
212 3 : }
213 :
214 : /*
215 : * Remove an advisor.
216 : */
217 : void
218 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 : 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
257 123 : pg_plan_advice_explain_option_handler(ExplainState *es, DefElem *opt,
258 : ParseState *pstate)
259 : {
260 : bool *plan_advice;
261 :
262 123 : plan_advice = GetExplainExtensionState(es, es_extension_id);
263 :
264 123 : if (plan_advice == NULL)
265 : {
266 123 : plan_advice = palloc0_object(bool);
267 123 : SetExplainExtensionState(es, es_extension_id, plan_advice);
268 : }
269 :
270 123 : *plan_advice = defGetBoolean(opt);
271 123 : }
272 :
273 : /*
274 : * Display a string that is likely to consist of multiple lines in EXPLAIN
275 : * output.
276 : */
277 : static void
278 245 : 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 245 : 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 244 : 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 243 : ExplainIndentText(es);
299 243 : appendStringInfo(es->str, "%s:\n", qlabel);
300 243 : es->indent++;
301 745 : while ((s = strchr(value, '\n')) != NULL)
302 : {
303 502 : ExplainIndentText(es);
304 502 : appendBinaryStringInfo(es->str, value, (s - value) + 1);
305 502 : value = s + 1;
306 : }
307 :
308 : /* Don't interpret a terminal newline as a request for an empty line. */
309 243 : if (*value != '\0')
310 : {
311 122 : ExplainIndentText(es);
312 122 : appendStringInfo(es->str, "%s\n", value);
313 : }
314 :
315 243 : es->indent--;
316 : }
317 :
318 : /*
319 : * Add advice feedback to the EXPLAIN output.
320 : */
321 : static void
322 123 : pg_plan_advice_explain_feedback(ExplainState *es, List *feedback)
323 : {
324 : StringInfoData buf;
325 :
326 123 : initStringInfo(&buf);
327 389 : foreach_node(DefElem, item, feedback)
328 : {
329 143 : int flags = defGetInt32(item);
330 :
331 143 : appendStringInfo(&buf, "%s /* ", item->defname);
332 143 : pgpa_trove_append_flags(&buf, flags);
333 143 : appendStringInfo(&buf, " */\n");
334 : }
335 :
336 123 : pg_plan_advice_explain_text_multiline(es, "Supplied Plan Advice",
337 : buf.data);
338 123 : }
339 :
340 : /*
341 : * Add relevant details, if any, to the EXPLAIN output for a single plan.
342 : */
343 : static void
344 3691 : 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 3691 : if (prev_explain_per_plan)
356 0 : prev_explain_per_plan(plannedstmt, into, es, queryString, params,
357 : queryEnv);
358 :
359 : /* Should an advice string be part of the EXPLAIN output? */
360 3691 : should_explain = pg_plan_advice_should_explain(es);
361 :
362 : /* Find any data pgpa_planner_shutdown stashed in the PlannedStmt. */
363 3691 : pgpa_item = find_defelem_by_defname(plannedstmt->extension_state,
364 : "pg_plan_advice");
365 3691 : 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 3691 : if (pgpa_list != NULL && (pg_plan_advice_always_explain_supplied_advice ||
380 : should_explain))
381 : {
382 : DefElem *feedback;
383 :
384 142 : feedback = find_defelem_by_defname(pgpa_list, "feedback");
385 142 : if (feedback != NULL)
386 123 : 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 3691 : if (should_explain)
394 : {
395 : DefElem *advice_string_item;
396 123 : char *advice_string = NULL;
397 :
398 : advice_string_item =
399 123 : find_defelem_by_defname(pgpa_list, "advice_string");
400 123 : if (advice_string_item != NULL)
401 : {
402 122 : advice_string = strVal(advice_string_item->arg);
403 122 : pg_plan_advice_explain_text_multiline(es, "Generated Plan Advice",
404 : advice_string);
405 : }
406 : }
407 3691 : }
408 :
409 : /*
410 : * Check hook for pg_plan_advice.advice
411 : */
412 : static bool
413 151 : pg_plan_advice_advice_check_hook(char **newval, void **extra, GucSource source)
414 : {
415 : MemoryContext oldcontext;
416 : MemoryContext tmpcontext;
417 : char *error;
418 :
419 151 : if (*newval == NULL)
420 14 : return true;
421 :
422 137 : tmpcontext = AllocSetContextCreate(CurrentMemoryContext,
423 : "pg_plan_advice.advice",
424 : ALLOCSET_DEFAULT_SIZES);
425 137 : 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 137 : (void) pgpa_parse(*newval, &error);
434 :
435 137 : if (error != NULL)
436 17 : GUC_check_errdetail("Could not parse advice: %s", error);
437 :
438 137 : MemoryContextSwitchTo(oldcontext);
439 137 : MemoryContextDelete(tmpcontext);
440 :
441 137 : return (error == NULL);
442 : }
443 :
444 : /*
445 : * Search a list of DefElem objects for a given defname.
446 : */
447 : static DefElem *
448 3956 : find_defelem_by_defname(List *deflist, char *defname)
449 : {
450 4109 : foreach_node(DefElem, item, deflist)
451 : {
452 4047 : if (strcmp(item->defname, defname) == 0)
453 3925 : return item;
454 : }
455 :
456 31 : return NULL;
457 : }
|
