From d72a3e2130d1039fba733f2a36b14933acdf1ad1 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Tue, 22 Apr 2025 12:47:16 -0400 Subject: [PATCH] Port pg_plan_advice over from the subplanname branch. Re-add build system integration. thing more --- contrib/Makefile | 1 + contrib/meson.build | 1 + contrib/pg_plan_advice/Makefile | 25 + contrib/pg_plan_advice/analyze_plan.c | 85 ++++ contrib/pg_plan_advice/create_advice.c | 461 ++++++++++++++++++ contrib/pg_plan_advice/meson.build | 30 ++ .../pg_plan_advice/pg_plan_advice--1.0.sql | 9 + contrib/pg_plan_advice/pg_plan_advice.c | 122 +++++ contrib/pg_plan_advice/pg_plan_advice.control | 5 + contrib/pg_plan_advice/pg_plan_advice.h | 20 + contrib/pg_plan_advice/pgpa_join.h | 53 ++ src/tools/pgindent/typedefs.list | 4 + 12 files changed, 816 insertions(+) create mode 100644 contrib/pg_plan_advice/Makefile create mode 100644 contrib/pg_plan_advice/analyze_plan.c create mode 100644 contrib/pg_plan_advice/create_advice.c create mode 100644 contrib/pg_plan_advice/meson.build create mode 100644 contrib/pg_plan_advice/pg_plan_advice--1.0.sql create mode 100644 contrib/pg_plan_advice/pg_plan_advice.c create mode 100644 contrib/pg_plan_advice/pg_plan_advice.control create mode 100644 contrib/pg_plan_advice/pg_plan_advice.h create mode 100644 contrib/pg_plan_advice/pgpa_join.h diff --git a/contrib/Makefile b/contrib/Makefile index 2f0a88d3f7..dd04c20acd 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -34,6 +34,7 @@ SUBDIRS = \ pg_freespacemap \ pg_logicalinspect \ pg_overexplain \ + pg_plan_advice \ pg_prewarm \ pg_stat_statements \ pg_surgery \ diff --git a/contrib/meson.build b/contrib/meson.build index ed30ee7d63..cb718dbdac 100644 --- a/contrib/meson.build +++ b/contrib/meson.build @@ -48,6 +48,7 @@ subdir('pgcrypto') subdir('pg_freespacemap') subdir('pg_logicalinspect') subdir('pg_overexplain') +subdir('pg_plan_advice') subdir('pg_prewarm') subdir('pgrowlocks') subdir('pg_stat_statements') diff --git a/contrib/pg_plan_advice/Makefile b/contrib/pg_plan_advice/Makefile new file mode 100644 index 0000000000..2c0c148092 --- /dev/null +++ b/contrib/pg_plan_advice/Makefile @@ -0,0 +1,25 @@ +# contrib/pg_plan_advice/Makefile + +MODULE_big = pg_plan_advice +OBJS = \ + $(WIN32RES) \ + create_advice.o \ + pg_plan_advice.o + +EXTENSION = pg_plan_advice +DATA = pg_plan_advice--1.0.sql +PGFILEDESC = "pg_plan_advice - help the planner get the right plan" + +REGRESS = pg_plan_advice +TAP_TESTS = 1 + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/pg_plan_advice +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/pg_plan_advice/analyze_plan.c b/contrib/pg_plan_advice/analyze_plan.c new file mode 100644 index 0000000000..a5da172487 --- /dev/null +++ b/contrib/pg_plan_advice/analyze_plan.c @@ -0,0 +1,85 @@ +typedef enum +{ + JSTRAT_CLUMP_DEGENERATE, + JSTRAT_CLUMP_PARTITIONWISE +} pgpa_join_clump_strategy; + +typedef struct +{ + unsigned nmembers; + Plan *plan; + Bitmapset *relids; + pgpa_join_clump_strategy strategy; +} pgpa_clumped_join; + +typedef enum +{ + JSTRAT_FOREIGN, + JSTRAT_MERGEJOIN_PLAIN, + JSTRAT_MERGEJOIN_MATERIALIZE, + JSTRAT_NESTLOOP_PLAIN, + JSTRAT_NESTLOOP_MATERIALIZE, + JSTRAT_NESTLOOP_MEMOIZE, + JSTRAT_HASHJOIN +} pgpa_join_strategy; + +typedef struct +{ + unsigned nallocated; + unsigned nused; + Plan *outer_subplan; + Index outer_rti; + pgpa_join_strategy **strategy; + Plan **inner_subplans; + Index *inner_rti; + pgpa_unrolled_join **inner_unrolled_join; + pgpa_clump_join **inner_clump_join; +} pgpa_unrolled_join; + +typedef struct +{ + PlannedStmt *pstmt; + List *top_unrolled_joins; + List *top_clumped_joins; + List *gather_relid_sets; + List *gather_merge_relid_sets; + bool beneath_gather; + Bitmapset *current_gather_relid_set; +} pgpa_statement_context; + +extern pgpa_unrolled_join *pgpa_make_unrolled_join(void); +extern void pgpa_set_outer(pgpa_unrolled_join *join, Plan *outer_subplan); +extern void pgpa_append_inner_scan(pgpa_unrolled_join *join, + Plan *inner_subplan); +extern void pgpa_append_inner_elided_scan(pgpa_unrolled_join *join, + ElidedNode *elided_node); +extern void pgpa_append_inner_unrolled_join(pgpa_unrolled_join *join, + pgpa_unrolled_join *subjoin); +extern void pgpa_append_inner_clumped_join(pgpa_unrolled_join *join, + pgpa_clumped_join *subjoin); + +static pgpa_join_strategy +get_join_strategy_and_children(Plan *plan, Plan **innerplan, Plan **outerplan) +{ +} + +static pgpa_clumped_join * +make_clumped_join(Plan *plan) +{ +} + +static pgpa_unrolled_join * +make_unrolled_join(Plan *plan) +{ +} + +extern pgpa_statement_context *pgpa_analyze_statement(PlannedStmt *pstmt); +extern void pgpa_assert_check(pgpa_statement_context *context); +extern void pgpa_get_context_advice(pgpa_statement_context *context, + StringInfo buf); +extern void pgpa_get_unrolled_join_advice(PlannedStmt *pstmt, + pgpa_unrolled_join *join); +extern void pgpa_get_clumped_join_advice(PlannedStmt *pstmt, + pgpa_clumped_join *join); +extern void pgpa_get_scan_advice(PlannedStmt *pstmt, Plan *scan); + diff --git a/contrib/pg_plan_advice/create_advice.c b/contrib/pg_plan_advice/create_advice.c new file mode 100644 index 0000000000..bb44d09856 --- /dev/null +++ b/contrib/pg_plan_advice/create_advice.c @@ -0,0 +1,461 @@ +/*------------------------------------------------------------------------- + * + * create_advice.c + * generate advice from a finished plan that can be fed back into + * future planning cycles if desired + * + * Copyright (c) 2016-2024, PostgreSQL Global Development Group + * + * contrib/pg_plan_advice/pg_plan_advice.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "commands/explain.h" +#include "parser/parsetree.h" +#include "pg_plan_advice.h" +#include "utils/builtins.h" +#include "utils/lsyscache.h" + +typedef enum +{ + JOIN_NONE, + JOIN_FOREIGN, + JOIN_MERGEJOIN_PLAIN, + JOIN_MERGEJOIN_MATERIALIZE, + JOIN_NESTLOOP_PLAIN, + JOIN_NESTLOOP_MATERIALIZE, + JOIN_NESTLOOP_MEMOIZE, + JOIN_HASHJOIN, + JOIN_PARTITIONWISE +} join_strategy; + +typedef struct +{ + PlannedStmt *stmt; + int rtable_length; + SubPlanRTInfo **rtable_subplans; + char **rtable_names; + Plan **rtable_scans; + bool *rtable_drivingjoin; + join_strategy *rtable_joinstrat; + List *all_join_orders; +} advice_context; + +typedef struct +{ + bool inner_side; + join_strategy jstrat; + Node *join_order; +} join_traversal; + +static void scan_plan_tree(advice_context *context, Plan *plan, + join_traversal *jtraversal); +static void scan_plan_tree_list(advice_context *context, List *list); + +static void +init_advice_context(advice_context *context, PlannedStmt *stmt) +{ + int rtable_length = list_length(stmt->rtable); + + context->stmt = stmt; + context->rtable_length = list_length(stmt->rtable); + context->rtable_subplans = palloc0_array(SubPlanRTInfo *, rtable_length); + context->rtable_names = palloc0_array(char *, rtable_length); + context->rtable_scans = palloc0_array(Plan *, rtable_length); + context->rtable_drivingjoin = palloc0_array(bool, rtable_length); + context->rtable_joinstrat = palloc0_array(join_strategy, rtable_length); + context->all_join_orders = NIL; +} + +static void +generate_relation_identifiers(advice_context *context) +{ + PlannedStmt *stmt = context->stmt; + Index rti; + Index *topparent; + + topparent = CreateParentRelationMap(stmt->rtable, stmt->appendRelations); + + /* Main loop over the entire flattened range table. */ + for (rti = 1; rti <= context->rtable_length; ++rti) + { + const char *result; + + result = MakeRelationIdentifier(stmt->rtable, + topparent, + stmt->subrtinfos, + rti); + + /* Save the name we just generated. */ + context->rtable_names[rti - 1] = unconstify(char *, result); + } +} + +static void +scan_plan_tree(advice_context *context, Plan *plan, join_traversal *jtraversal) +{ + Node *join_order = NULL; + Index rti; + + if (IsA(plan, NestLoop) || IsA(plan, HashJoin) || IsA(plan, MergeJoin)) + { + join_traversal outer_join_traversal; + join_traversal inner_join_traversal; + + /* + * The outermost table in a set of joins needs no join strategy advice; + * in the case of a bushy join, the outermost table of each clump + * needs join strategy advice to indicate how the clump as a whole + * should be joined. + */ + outer_join_traversal.inner_side = false; + outer_join_traversal.jstrat = JOIN_NONE; + outer_join_traversal.join_order = NULL; + if (jtraversal != NULL) + outer_join_traversal.jstrat = jtraversal->jstrat; + else + outer_join_traversal.jstrat = JOIN_NONE; + outer_join_traversal.join_order = NULL; + scan_plan_tree(context, plan->lefttree, &outer_join_traversal); + + /* + * Any table on the inner side of a join needs join strategy advice. + */ + inner_join_traversal.inner_side = true; + inner_join_traversal.join_order = NULL; + if (IsA(plan, MergeJoin)) + { + if (IsA(plan->righttree, Material)) + inner_join_traversal.jstrat = JOIN_MERGEJOIN_MATERIALIZE; + else + inner_join_traversal.jstrat = JOIN_MERGEJOIN_PLAIN; + } + else if (IsA(plan, NestLoop)) + { + if (IsA(plan->righttree, Material)) + inner_join_traversal.jstrat = JOIN_NESTLOOP_MATERIALIZE; + else if (IsA(plan->righttree, Memoize)) + inner_join_traversal.jstrat = JOIN_NESTLOOP_MEMOIZE; + else + inner_join_traversal.jstrat = JOIN_NESTLOOP_PLAIN; + } + else if (IsA(plan, HashJoin)) + inner_join_traversal.jstrat = JOIN_HASHJOIN; + else + elog(ERROR, "unknown node type: %d", nodeTag(plan)); + scan_plan_tree(context, plan->righttree, &inner_join_traversal); + + /* + * We assume that left-deep (or outer-deep) join trees are the norm; + * hence JOIN(JOIN(A,B),C) is represented as (A B C), whereas + * JOIN(A,JOIN(B,C)) is represnted as (A (B C)). + */ + if (IsA(outer_join_traversal.join_order, List)) + join_order = (Node *) + lappend((List *) outer_join_traversal.join_order, + inner_join_traversal.join_order); + else + join_order = (Node *) + list_make2(outer_join_traversal.join_order, + inner_join_traversal.join_order); + } + else if (jtraversal != NULL && !jtraversal->inner_side && + IsA(plan, Sort)) + { + Assert(plan->lefttree != NULL && plan->righttree == NULL); + scan_plan_tree(context, plan->lefttree, jtraversal); + } + else if (jtraversal != NULL && jtraversal->inner_side && + (IsA(plan, Material) || IsA(plan, Memoize) || IsA(plan, Hash) || + IsA(plan, Sort))) + { + Assert(plan->lefttree != NULL && plan->righttree == NULL); + scan_plan_tree(context, plan->lefttree, jtraversal); + } + else + { + if (plan->lefttree != NULL) + scan_plan_tree(context, plan->lefttree, NULL); + if (plan->righttree != NULL) + scan_plan_tree(context, plan->righttree, NULL); + + if (jtraversal != NULL) + join_order = (Node *) plan; + } + + if (join_order != NULL) + { + if (jtraversal == NULL) + context->all_join_orders = lappend(context->all_join_orders, + join_order); + else + { + Assert(jtraversal->join_order == NULL); + jtraversal->join_order = join_order; + } + } + + /* recurse into any special children */ + switch (nodeTag(plan)) + { + case T_Append: + scan_plan_tree_list(context, ((Append *) plan)->appendplans); + break; + case T_MergeAppend: + scan_plan_tree_list(context, ((MergeAppend *) plan)->mergeplans); + break; + case T_BitmapAnd: + scan_plan_tree_list(context, ((BitmapAnd *) plan)->bitmapplans); + break; + case T_BitmapOr: + scan_plan_tree_list(context, ((BitmapOr *) plan)->bitmapplans); + break; + case T_SubqueryScan: + scan_plan_tree(context, ((SubqueryScan *) plan)->subplan, NULL); + break; + case T_CustomScan: + scan_plan_tree_list(context, ((CustomScan *) plan)->custom_plans); + break; + default: + break; + } + + rti = GetScannedRTI(context->stmt, plan); + if (rti != 0) + { + if (context->rtable_scans[rti - 1] != NULL) + elog(ERROR, "rti %d is duplicated", rti); + context->rtable_scans[rti - 1] = plan; + if (jtraversal != NULL) + { + context->rtable_drivingjoin[rti - 1] = !jtraversal->inner_side; + context->rtable_joinstrat[rti - 1] = jtraversal->jstrat; + } + } +} + +static void +scan_plan_tree_list(advice_context *context, List *list) +{ + ListCell *lc; + + foreach(lc, list) + { + scan_plan_tree(context, lfirst(lc), NULL); + } +} + +static void +join_method_dump(StringInfo result, advice_context *context) +{ + Index rti; + + for (rti = 1; rti <= context->rtable_length; ++rti) + { + char *name = context->rtable_names[rti - 1]; + bool drivingjoin = context->rtable_drivingjoin[rti - 1]; + join_strategy jstrat = context->rtable_joinstrat[rti - 1]; + char *jstrat_name = NULL; + + switch (jstrat) + { + case JOIN_NONE: + break; + case JOIN_FOREIGN: + jstrat_name = drivingjoin ? "BUSHY_FOREIGN_JOIN" : "FOREIGN_JOIN"; + break; + case JOIN_MERGEJOIN_PLAIN: + jstrat_name = drivingjoin ? "BUSHY_MERGE_JOIN" : "MERGE_JOIN"; + break; + case JOIN_MERGEJOIN_MATERIALIZE: + jstrat_name = drivingjoin ? "BUSHY_MERGE_JOIN_MATERIALIZE" : "MERGE_JOIN_MATERIALIZE"; + break; + case JOIN_NESTLOOP_PLAIN: + jstrat_name = drivingjoin ? "BUSHY_NESTED_LOOP" : "NESTED_LOOP"; + break; + case JOIN_NESTLOOP_MATERIALIZE: + jstrat_name = drivingjoin ? "BUSHY_NESTED_LOOP_MATERIALIZE" : "NESTED_LOOP_MATERIALIZE"; + break; + case JOIN_NESTLOOP_MEMOIZE: + jstrat_name = drivingjoin ? "BUSHY_NESTED_LOOP_MEMOIZE" : "NESTED_LOOP_MEMOIZE"; + break; + case JOIN_HASHJOIN: + jstrat_name = drivingjoin ? "BUSHY_HASH_JOIN" : "HASH_JOIN"; + break; + case JOIN_PARTITIONWISE: + jstrat_name = drivingjoin ? "BUSHY_PARTITIONWISE" : "PARTITIONWISE"; + break; + } + + if (name == NULL) + name = "???"; + if (jstrat_name != NULL) + appendStringInfo(result, "%s(%s)\n", jstrat_name, name); + } +} + +static void +scan_method_dump(StringInfo result, advice_context *context) +{ + Index rti; + + for (rti = 1; rti <= context->rtable_length; ++rti) + { + char *name = context->rtable_names[rti - 1]; + Plan *plan = context->rtable_scans[rti - 1]; + char *scan_name = NULL; + Oid index_oid = InvalidOid; + + /* + * Convert the node tag of the scan to a string. We can ignore scan + * types for which there is no alternative, e.g. SubqueryScan, + * FunctionScan, ValuesScan. + */ + if (plan != NULL) + { + switch (nodeTag(plan)) + { + case T_SeqScan: + scan_name = "SEQ_SCAN"; + break; + case T_IndexScan: + scan_name = "INDEX_SCAN"; + index_oid = ((IndexScan *) plan)->indexid; + break; + case T_IndexOnlyScan: + scan_name = "INDEX_ONLY_SCAN"; + index_oid = ((IndexOnlyScan *) plan)->indexid; + break; + case T_BitmapHeapScan: + scan_name = "BITMAP_HEAP_SCAN"; + break; + case T_TidScan: + scan_name = "TID_SCAN"; + break; + case T_TidRangeScan: + scan_name = "TID_RANGE_SCAN"; + break; + case T_CustomScan: + scan_name = "CUSTOM_SCAN"; + break; + default: + break; + } + } + + if (name == NULL) + name = "???"; + if (OidIsValid(index_oid)) + { + char *indnsp; + char *indrel; + + indnsp = get_namespace_name_or_temp(get_rel_namespace(index_oid)); + indrel = get_rel_name(index_oid); + appendStringInfo(result, "%s(%s, %s.%s)\n", scan_name, name, + quote_identifier(indnsp), quote_identifier(indrel)); + } + else if (scan_name != NULL) + appendStringInfo(result, "%s(%s)\n", scan_name, name); + } +} + +static void +join_order_dump_recursive(StringInfo result, advice_context *context, Node *n) +{ + if (IsA(n, List)) + { + ListCell *lc; + bool first = true; + + appendStringInfoString(result, "("); + foreach(lc, (List *) n) + { + if (first) + first = false; + else + appendStringInfoString(result, " "); + join_order_dump_recursive(result, context, (Node *) lfirst(lc)); + } + appendStringInfoString(result, ")"); + } + else + { + Index rti = GetScannedRTI(context->stmt, (Plan *) n); + + if (rti != 0) + appendStringInfoString(result, context->rtable_names[rti - 1]); + else + appendStringInfo(result, "?[rti=%d,nodetag=%d]", (int) rti, + (int) nodeTag(n)); + } +} + +static void +join_order_dump(StringInfo result, advice_context *context) +{ + ListCell *lc; + + foreach(lc, context->all_join_orders) + { + List *l = lfirst_node(List, lc); + ListCell *lc2; + bool first = true; + + appendStringInfoString(result, "JOIN_ORDER("); + foreach(lc2, l) + { + if (first) + first = false; + else + appendStringInfoString(result, " "); + join_order_dump_recursive(result, context, (Node *) lfirst(lc2)); + } + appendStringInfoString(result, ")\n"); + } +} + +/* + * Generate advice from a PlannedStmt, and append it to the buffer provided. + * + * Returns false if the PlannedStmt has no plan tree, otherwise true. + */ +bool +append_advice_from_plan(StringInfo buf, PlannedStmt *stmt) +{ + advice_context context; + ListCell *lc; + + /* If this is a utility statement, there's no useful work to be done. */ + if (stmt->planTree == NULL) + return false; + + /* Initialization steps. */ + init_advice_context(&context, stmt); + generate_relation_identifiers(&context); + + /* First, scan the main plan tree. */ + scan_plan_tree(&context, stmt->planTree, NULL); + + /* + * Now, scan the list of InitPlans and SubPlans, which, confusingly, + * are collectively known as subplans. Some subplans may have been elided + * during planning, so skip any NULL entries in the array. + */ + foreach(lc, stmt->subplans) + { + Plan *plan = lfirst(lc); + + if (plan != NULL) + scan_plan_tree(&context, plan, NULL); + } + + /* Finally, append advice to the output buffer. */ + join_order_dump(buf, &context); + join_method_dump(buf, &context); + scan_method_dump(buf, &context); + + return true; +} diff --git a/contrib/pg_plan_advice/meson.build b/contrib/pg_plan_advice/meson.build new file mode 100644 index 0000000000..2f05b7ff54 --- /dev/null +++ b/contrib/pg_plan_advice/meson.build @@ -0,0 +1,30 @@ +# Copyright (c) 2022-2024, PostgreSQL Global Development Group + +pg_plan_advice_sources = files( + 'create_advice.c', + 'pg_plan_advice.c', +) + +if host_system == 'windows' + pg_plan_advice_sources += rc_lib_gen.process(win32ver_rc, extra_args: [ + '--NAME', 'pg_plan_advice', + '--FILEDESC', 'pg_plan_advice - help the planner get the right plan',]) +endif + +pg_plan_advice = shared_module('pg_plan_advice', + pg_plan_advice_sources, + kwargs: contrib_mod_args, +) +contrib_targets += pg_plan_advice + +install_data( + 'pg_plan_advice--1.0.sql', + 'pg_plan_advice.control', + kwargs: contrib_data_args, +) + +tests += { + 'name': 'pg_plan_advice', + 'sd': meson.current_source_dir(), + 'bd': meson.current_build_dir(), +} diff --git a/contrib/pg_plan_advice/pg_plan_advice--1.0.sql b/contrib/pg_plan_advice/pg_plan_advice--1.0.sql new file mode 100644 index 0000000000..a6b3e9238a --- /dev/null +++ b/contrib/pg_plan_advice/pg_plan_advice--1.0.sql @@ -0,0 +1,9 @@ +/* contrib/pg_plan_advice/pg_plan_advice--1.1.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pg_plan_advice" to load this file. \quit + +CREATE FUNCTION pg_get_advice(text) +RETURNS text +AS 'MODULE_PATHNAME', 'pg_get_advice' +LANGUAGE C STRICT; diff --git a/contrib/pg_plan_advice/pg_plan_advice.c b/contrib/pg_plan_advice/pg_plan_advice.c new file mode 100644 index 0000000000..ad7db88acb --- /dev/null +++ b/contrib/pg_plan_advice/pg_plan_advice.c @@ -0,0 +1,122 @@ +/*------------------------------------------------------------------------- + * + * pg_plan_advice.c + * Main entrypoints for generating and applying planner advice + * + * Copyright (c) 2016-2024, PostgreSQL Global Development Group + * + * contrib/pg_plan_advice/pg_plan_advice.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "funcapi.h" +#include "pg_plan_advice.h" +#include "tcop/tcopprot.h" +#include "utils/builtins.h" + +static const struct config_enum_entry loglevel_options[] = { + {"disabled", -1, false}, + {"debug5", DEBUG5, false}, + {"debug4", DEBUG4, false}, + {"debug3", DEBUG3, false}, + {"debug2", DEBUG2, false}, + {"debug1", DEBUG1, false}, + {"debug", DEBUG2, true}, + {"log", LOG, false}, + {"info", INFO, true}, + {"notice", NOTICE, false}, + {"warning", WARNING, false}, + {"error", ERROR, false}, + {NULL, 0, false} +}; + +PG_MODULE_MAGIC; + +/* GUC variables */ +static int pg_plan_advice_log_level = -1; /* disabled */ + +PG_FUNCTION_INFO_V1(pg_get_advice); + +/* Saved hook values */ +static ExecutorStart_hook_type prev_ExecutorStart = NULL; + +static void pg_plan_advice_ExecutorStart(QueryDesc *queryDesc, int eflags); + +/* + * Initialize this module + */ +void +_PG_init(void) +{ + /* Define custom GUC variables. */ + DefineCustomEnumVariable("pg_plan_advice.log_level", + "Generate advice for each executed plan and log it at the given level.", + NULL, + &pg_plan_advice_log_level, + -1, + loglevel_options, + PGC_USERSET, + 0, + NULL, + NULL, + NULL); + + MarkGUCPrefixReserved("pg_plan_advice"); + + /* Install hooks. */ + prev_ExecutorStart = ExecutorStart_hook; + ExecutorStart_hook = pg_plan_advice_ExecutorStart; +} + +static void +pg_plan_advice_ExecutorStart(QueryDesc *queryDesc, int eflags) +{ + if (pg_plan_advice_log_level != -1) + { + StringInfoData buf; + + initStringInfo(&buf); + append_advice_from_plan(&buf, queryDesc->plannedstmt); + if (buf.len > 0) + ereport(pg_plan_advice_log_level, + errmsg("plan advice: %s", buf.data)); + } + + if (prev_ExecutorStart) + prev_ExecutorStart(queryDesc, eflags); + else + standard_ExecutorStart(queryDesc, eflags); +} + +/* + * Generate and advice for a single query and return it via a text column. + */ +Datum +pg_get_advice(PG_FUNCTION_ARGS) +{ + char *query = text_to_cstring(PG_GETARG_TEXT_PP(0)); + List *raw_parsetree_list; + StringInfoData result; + + initStringInfo(&result); + + raw_parsetree_list = pg_parse_query(query); + foreach_node(RawStmt, parsetree, raw_parsetree_list) + { + List *stmt_list; + + stmt_list = pg_analyze_and_rewrite_fixedparams(parsetree, query, + NULL, 0, NULL); + stmt_list = pg_plan_queries(stmt_list, query, CURSOR_OPT_PARALLEL_OK, + NULL); + + foreach_node(PlannedStmt, stmt, stmt_list) + { + append_advice_from_plan(&result, stmt); + } + } + + PG_RETURN_TEXT_P(cstring_to_text(result.data)); +} diff --git a/contrib/pg_plan_advice/pg_plan_advice.control b/contrib/pg_plan_advice/pg_plan_advice.control new file mode 100644 index 0000000000..aa6fdc9e7b --- /dev/null +++ b/contrib/pg_plan_advice/pg_plan_advice.control @@ -0,0 +1,5 @@ +# pg_plan_advice extension +comment = 'help the planner get the right plan' +default_version = '1.0' +module_pathname = '$libdir/pg_plan_advice' +relocatable = true diff --git a/contrib/pg_plan_advice/pg_plan_advice.h b/contrib/pg_plan_advice/pg_plan_advice.h new file mode 100644 index 0000000000..5fd5493b0c --- /dev/null +++ b/contrib/pg_plan_advice/pg_plan_advice.h @@ -0,0 +1,20 @@ +/*------------------------------------------------------------------------- + * + * pg_plan_advice.h + * header file for pg_plan_advice contrib module + * + * Copyright (c) 2016-2024, PostgreSQL Global Development Group + * + * contrib/pg_plan_advice/pg_plan_advice.h + * + *------------------------------------------------------------------------- + */ +#ifndef PG_PLAN_ADVICE_H +#define PG_PLAN_ADVICE_H + +#include "nodes/plannodes.h" + +/* create_advice.c */ +extern bool append_advice_from_plan(StringInfo buf, PlannedStmt *stmt); + +#endif diff --git a/contrib/pg_plan_advice/pgpa_join.h b/contrib/pg_plan_advice/pgpa_join.h new file mode 100644 index 0000000000..ef2f003a65 --- /dev/null +++ b/contrib/pg_plan_advice/pgpa_join.h @@ -0,0 +1,53 @@ +#ifndef PGPA_JOIN_H +#define PGPA_JOIN_H + +typedef enum +{ + JSTRAT_CLUMP_DEGENERATE, + JSTRAT_CLUMP_PARTITIONWISE +} pgpa_join_clump_strategy; + +typedef struct +{ + unsigned nmembers; + Plan *plan; + Bitmapset *relids; + pgpa_join_clump_strategy strategy; +} pgpa_clumped_join; + +typedef enum +{ + JSTRAT_FOREIGN, + JSTRAT_MERGEJOIN_PLAIN, + JSTRAT_MERGEJOIN_MATERIALIZE, + JSTRAT_NESTLOOP_PLAIN, + JSTRAT_NESTLOOP_MATERIALIZE, + JSTRAT_NESTLOOP_MEMOIZE, + JSTRAT_HASHJOIN +} pgpa_join_strategy; + +typedef struct +{ + unsigned nallocated; + unsigned nused; + Plan *outer_subplan; + Index outer_rti; + pgpa_join_strategy **strategy; + Plan **inner_subplans; + Index *inner_rti; + pgpa_unrolled_join **inner_unrolled_join; + pgpa_clump_join **inner_clump_join; +} pgpa_unrolled_join; + +extern pgpa_unrolled_join *pgpa_make_unrolled_join(void); +extern void pgpa_set_outer(pgpa_unrolled_join *join, Plan *outer_subplan); +extern void pgpa_append_inner_scan(pgpa_unrolled_join *join, + Plan *inner_subplan); +extern void pgpa_append_inner_elided_scan(pgpa_unrolled_join *join, + ElidedNode *elided_node); +extern void pgpa_append_inner_unrolled_join(pgpa_unrolled_join *join, + pgpa_unrolled_join *subjoin); +extern void pgpa_append_inner_clumped_join(pgpa_unrolled_join *join, + pgpa_clumped_join *subjoin); + +#endif diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index f4ae78224a..af72139dab 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -4311,3 +4311,7 @@ ExplainOptionHandler overexplain_options SubPlanRTInfo ElidedNode +pgpa_join_clump_strategy +pgpa_join_strategy +pgpa_clumped_join +pgpa_unrolled_join -- 2.39.5