From 580d6aa07e12ea15334baf1759adeaeee7a02a60 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Thu, 3 Jul 2025 13:54:17 -0400 Subject: [PATCH] EXPLAIN (PLAN_ADVICE) --- contrib/pg_plan_advice/pg_plan_advice.c | 157 ++++++++++++++++++++---- 1 file changed, 133 insertions(+), 24 deletions(-) diff --git a/contrib/pg_plan_advice/pg_plan_advice.c b/contrib/pg_plan_advice/pg_plan_advice.c index 17769b02c9..1965511683 100644 --- a/contrib/pg_plan_advice/pg_plan_advice.c +++ b/contrib/pg_plan_advice/pg_plan_advice.c @@ -17,27 +17,42 @@ #include "pgpa_output.h" #include "pgpa_walker.h" +#include "commands/defrem.h" +#include "commands/explain.h" +#include "commands/explain_format.h" +#include "commands/explain_state.h" #include "funcapi.h" #include "storage/dsm_registry.h" #include "utils/guc.h" PG_MODULE_MAGIC; -static pgpa_shared_state *pgpa_state = NULL; +static pgpa_shared_state * pgpa_state = NULL; static dsa_area *pgpa_dsa_area = NULL; /* GUC variables */ -int pg_plan_advice_local_collection_limit = 0; -int pg_plan_advice_shared_collection_limit = 0; +int pg_plan_advice_local_collection_limit = 0; +int pg_plan_advice_shared_collection_limit = 0; /* Saved hook values */ static ExecutorStart_hook_type prev_ExecutorStart = NULL; +static explain_per_plan_hook_type prev_explain_per_plan_hook = NULL; -/* Memory context */ +/* Other file-level globals */ +static int es_extension_id; static MemoryContext pgpa_memory_context = NULL; static bool pg_plan_advice_ExecutorStart(QueryDesc *queryDesc, int eflags); -static void pgpa_generate_advice(PlannedStmt *pstmt, const char *query_string); +static void pg_plan_advice_explain_option_handler(ExplainState *es, + DefElem *opt, + ParseState *pstate); +static void pg_plan_advice_explain_per_plan_hook(PlannedStmt *plannedstmt, + IntoClause *into, + ExplainState *es, + const char *queryString, + ParamListInfo params, + QueryEnvironment *queryEnv); +static char *pg_plan_advice_generate(PlannedStmt *pstmt); /* * Initialize this module. @@ -69,9 +84,18 @@ _PG_init(void) NULL, NULL); + /* Get an ID that we can use to cache data in an ExplainState. */ + es_extension_id = GetExplainExtensionId("pg_plan_advice"); + + /* Register the new EXPLAIN options implemented by this module. */ + RegisterExtensionExplainOption("plan_advice", + pg_plan_advice_explain_option_handler); + /* Install hooks */ prev_ExecutorStart = ExecutorStart_hook; ExecutorStart_hook = pg_plan_advice_ExecutorStart; + prev_explain_per_plan_hook = explain_per_plan_hook; + explain_per_plan_hook = pg_plan_advice_explain_per_plan_hook; } /* @@ -140,7 +164,7 @@ pg_plan_advice_dsa_area(void) { pgpa_shared_state *state = pg_plan_advice_attach(); dsa_handle area_handle; - MemoryContext oldcontext; + MemoryContext oldcontext; oldcontext = MemoryContextSwitchTo(pg_plan_advice_get_mcxt()); @@ -174,7 +198,27 @@ pg_plan_advice_ExecutorStart(QueryDesc *queryDesc, int eflags) if (pg_plan_advice_local_collection_limit > 0 || pg_plan_advice_shared_collection_limit > 0) - pgpa_generate_advice(pstmt, queryDesc->sourceText); + { + char *advice; + + advice = pg_plan_advice_generate(pstmt); + + /* + * If the advice string is non-empty, pass it to the collectors. + * + * A query such as SELECT 2+2 or SELECT * FROM generate_series(1,10) + * will not produce any advice, since there are no query planning + * decisions that can be influenced. It wouldn't exactly be wrong to + * record the query together with the empty advice string, but there + * doesn't seem to be much value in it, so skip it to save space. + * + * If this proves confusing to users, we might need to revist the + * behavior here. + */ + if (advice[0] != '\0') + pgpa_collect_advice(pstmt->queryId, queryDesc->sourceText, + advice); + } if (prev_ExecutorStart) return prev_ExecutorStart(queryDesc, eflags); @@ -183,10 +227,89 @@ pg_plan_advice_ExecutorStart(QueryDesc *queryDesc, int eflags) } /* - * Generate advice from a query plan and send it to the relevant collectors. + * Handler for EXPLAIN (PLAN_ADVICE). */ static void -pgpa_generate_advice(PlannedStmt *pstmt, const char *query_string) +pg_plan_advice_explain_option_handler(ExplainState *es, DefElem *opt, + ParseState *pstate) +{ + bool *plan_advice; + + plan_advice = GetExplainExtensionState(es, es_extension_id); + + if (plan_advice == NULL) + { + plan_advice = palloc0_object(bool); + SetExplainExtensionState(es, es_extension_id, plan_advice); + } + + *plan_advice = defGetBoolean(opt); +} + +/* + * If the PLAN_ADVICE option was specified -- and not set to FALSE -- generate + * advice from the provided plan and add it to the EXPLAIN output. + */ +static void +pg_plan_advice_explain_per_plan_hook(PlannedStmt *plannedstmt, + IntoClause *into, + ExplainState *es, + const char *queryString, + ParamListInfo params, + QueryEnvironment *queryEnv) +{ + bool *plan_advice; + + if (prev_explain_per_plan_hook) + prev_explain_per_plan_hook(plannedstmt, into, es, queryString, + params, queryEnv); + + plan_advice = GetExplainExtensionState(es, es_extension_id); + if (plan_advice != NULL && *plan_advice) + { + char *advice = pg_plan_advice_generate(plannedstmt); + + /* + * The advice string likely spans multiple lines; the last line will + * not end in a newline, but the others will. In text format, it looks + * nicest to indent each line of the advice separately, beginning on + * the line below the "Plan Advice" label. For non-text formats, it's + * best not to add any special handling. + */ + if (es->format != EXPLAIN_FORMAT_TEXT) + ExplainPropertyText("Plan Advice", advice, es); + else if (*advice != '\0') + { + char *s; + + ExplainIndentText(es); + appendStringInfo(es->str, "Plan Advice:\n"); + + es->indent++; + + while ((s = strchr(advice, '\n')) != NULL) + { + ExplainIndentText(es); + appendBinaryStringInfo(es->str, advice, (s - advice) + 1); + advice = s + 1; + } + + if (*advice != '\0') + { + ExplainIndentText(es); + appendStringInfo(es->str, "%s\n", advice); + } + + es->indent--; + } + } +} + +/* + * Generate advice from a query plan and send it to the relevant collectors. + */ +static char * +pg_plan_advice_generate(PlannedStmt *pstmt) { pgpa_plan_walker_context context; StringInfoData buf; @@ -215,19 +338,5 @@ pgpa_generate_advice(PlannedStmt *pstmt, const char *query_string) /* Put advice into string form. */ initStringInfo(&buf); pgpa_output_advice(&buf, &context, rt_identifiers); - - /* - * If the advice string is non-empty, pass it to the collectors. - * - * A query such as SELECT 2+2 or SELECT * FROM generate_series(1,10) - * will not produce any advice, since there are no query planning decisions - * that can be influenced. It wouldn't exactly be wrong to record the - * query together with the empty advice string, but there doesn't seem to - * be much value in it, so skip it to save space. - * - * If this proves confusing to users, we might need to revist the behavior - * here. - */ - if (buf.data[0] != '\0') - pgpa_collect_advice(pstmt->queryId, query_string, buf.data); + return buf.data; } -- 2.39.5