X-Git-Url: http://git.osdn.net/view?a=blobdiff_plain;f=pg_hint_plan.c;h=43ac931dc1f1828805d618a98aa1948c9c68db86;hb=231a4448bb1a5a93d06b2e919d9b2fbbf40a61df;hp=505ffdfa6d9020dd6bc735dbff9023e3e6c5b634;hpb=f4e8b55ceae0f0d3b33f1822cffc9d7ed701ea30;p=pghintplan%2Fpg_hint_plan.git diff --git a/pg_hint_plan.c b/pg_hint_plan.c index 505ffdf..43ac931 100644 --- a/pg_hint_plan.c +++ b/pg_hint_plan.c @@ -3,13 +3,16 @@ * pg_hint_plan.c * hinting on how to execute a query for PostgreSQL * - * Copyright (c) 2012-2017, NIPPON TELEGRAPH AND TELEPHONE CORPORATION + * Copyright (c) 2012-2020, NIPPON TELEGRAPH AND TELEPHONE CORPORATION * *------------------------------------------------------------------------- */ #include #include "postgres.h" +#include "access/genam.h" +#include "access/heapam.h" +#include "access/relation.h" #include "catalog/pg_collation.h" #include "catalog/pg_index.h" #include "commands/prepare.h" @@ -17,11 +20,12 @@ #include "miscadmin.h" #include "nodes/nodeFuncs.h" #include "nodes/params.h" -#include "nodes/relation.h" +#include "optimizer/appendinfo.h" #include "optimizer/clauses.h" #include "optimizer/cost.h" #include "optimizer/geqo.h" #include "optimizer/joininfo.h" +#include "optimizer/optimizer.h" #include "optimizer/pathnode.h" #include "optimizer/paths.h" #include "optimizer/plancat.h" @@ -31,8 +35,10 @@ #include "parser/analyze.h" #include "parser/parsetree.h" #include "parser/scansup.h" +#include "partitioning/partbounds.h" #include "tcop/utility.h" #include "utils/builtins.h" +#include "utils/float.h" #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -91,12 +97,12 @@ PG_MODULE_MAGIC; #define HINT_ARRAY_DEFAULT_INITSIZE 8 -#define hint_ereport(str, detail) \ +#define hint_ereport(str, detail) hint_parse_ereport(str, detail) +#define hint_parse_ereport(str, detail) \ do { \ - ereport(pg_hint_plan_message_level, \ - (errmsg("pg_hint_plan%s: hint syntax error at or near \"%s\"", qnostr, (str)), \ + ereport(pg_hint_plan_parse_message_level, \ + (errmsg("pg_hint_plan: hint syntax error at or near \"%s\"", (str)), \ errdetail detail)); \ - msgqno = qno; \ } while(0) #define skip_space(str) \ @@ -224,6 +230,14 @@ static unsigned int msgqno = 0; static char qnostr[32]; static const char *current_hint_str = NULL; +/* + * However we usually take a hint stirng in post_parse_analyze_hook, we still + * need to do so in planner_hook when client starts query execution from the + * bind message on a prepared query. This variable prevent duplicate and + * sometimes harmful hint string retrieval. + */ +static bool current_hint_retrieved = false; + /* common data for all hints. */ struct Hint { @@ -350,9 +364,10 @@ struct HintState int init_min_para_tablescan_size; /* min_parallel_index_scan_size*/ int init_min_para_indexscan_size; - int init_paratup_cost; /* parallel_tuple_cost */ - int init_parasetup_cost;/* parallel_setup_cost */ + double init_paratup_cost; /* parallel_tuple_cost */ + double init_parasetup_cost;/* parallel_setup_cost */ + PlannerInfo *current_root; /* PlannerInfo for the followings */ Index parent_relid; /* inherit parent of table relid */ ScanMethodHint *parent_scan_hint; /* scan hint for the parent */ ParallelHint *parent_parallel_hint; /* parallel hint for the parent */ @@ -386,6 +401,11 @@ static void push_hint(HintState *hstate); static void pop_hint(void); static void pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query); +static void pg_hint_plan_ProcessUtility(PlannedStmt *pstmt, + const char *queryString, + ProcessUtilityContext context, + ParamListInfo params, QueryEnvironment *queryEnv, + DestReceiver *dest, char *completionTag); static PlannedStmt *pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams); static RelOptInfo *pg_hint_plan_join_search(PlannerInfo *root, @@ -459,8 +479,6 @@ void pg_hint_plan_set_rel_pathlist(PlannerInfo * root, RelOptInfo *rel, Index rti, RangeTblEntry *rte); static void create_plain_partial_paths(PlannerInfo *root, RelOptInfo *rel); -static void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, - List *live_childrels); static void make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels); static void make_rels_by_clauseless_joins(PlannerInfo *root, @@ -471,14 +489,6 @@ static void set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte); static void set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte); -static void generate_mergeappend_paths(PlannerInfo *root, RelOptInfo *rel, - List *live_childrels, - List *all_child_pathkeys, - List *partitioned_rels); -static Path *get_cheapest_parameterized_child_path(PlannerInfo *root, - RelOptInfo *rel, - Relids required_outer); -static List *accumulate_append_subpath(List *subpaths, Path *path); RelOptInfo *pg_hint_plan_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2); @@ -497,15 +507,19 @@ static void setup_scan_method_enforcement(ScanMethodHint *scanhint, HintState *state); static int set_config_int32_option(const char *name, int32 value, GucContext context); +static int set_config_double_option(const char *name, double value, + GucContext context); /* GUC variables */ static bool pg_hint_plan_enable_hint = true; static int debug_level = 0; -static int pg_hint_plan_message_level = INFO; +static int pg_hint_plan_parse_message_level = INFO; +static int pg_hint_plan_debug_message_level = LOG; /* Default is off, to keep backward compatibility. */ static bool pg_hint_plan_enable_hint_table = false; static int plpgsql_recurse_level = 0; /* PLpgSQL recursion level */ +static int recurse_level = 0; /* recursion level incl. direct SPI calls */ static int hint_inhibit_level = 0; /* Inhibit hinting if this is above 0 */ /* (This could not be above 1) */ static int max_hint_nworkers = -1; /* Maximum nworkers of Workers hints */ @@ -550,6 +564,7 @@ static post_parse_analyze_hook_type prev_post_parse_analyze_hook = NULL; static planner_hook_type prev_planner = NULL; static join_search_hook_type prev_join_search = NULL; static set_rel_pathlist_hook_type prev_set_rel_pathlist = NULL; +static ProcessUtility_hook_type prev_ProcessUtility_hook = NULL; /* Hold reference to currently active hint */ static HintState *current_hint_state = NULL; @@ -561,12 +576,6 @@ static HintState *current_hint_state = NULL; */ static List *HintStateStack = NIL; -/* - * Holds statement name during executing EXECUTE command. NULL for other - * statements. - */ -static char *stmt_name = NULL; - static const HintParser parsers[] = { {HINT_SEQSCAN, ScanMethodHintCreate, HINT_KEYWORD_SEQSCAN}, {HINT_INDEXSCAN, ScanMethodHintCreate, HINT_KEYWORD_INDEXSCAN}, @@ -644,7 +653,7 @@ _PG_init(void) DefineCustomEnumVariable("pg_hint_plan.parse_messages", "Message level of parse errors.", NULL, - &pg_hint_plan_message_level, + &pg_hint_plan_parse_message_level, INFO, parse_messages_level_options, PGC_USERSET, @@ -656,8 +665,8 @@ _PG_init(void) DefineCustomEnumVariable("pg_hint_plan.message_level", "Message level of debug messages.", NULL, - &pg_hint_plan_message_level, - INFO, + &pg_hint_plan_debug_message_level, + LOG, parse_messages_level_options, PGC_USERSET, 0, @@ -685,6 +694,8 @@ _PG_init(void) join_search_hook = pg_hint_plan_join_search; prev_set_rel_pathlist = set_rel_pathlist_hook; set_rel_pathlist_hook = pg_hint_plan_set_rel_pathlist; + prev_ProcessUtility_hook = ProcessUtility_hook; + ProcessUtility_hook = pg_hint_plan_ProcessUtility; /* setup PL/pgSQL plugin hook */ var_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin"); @@ -707,6 +718,7 @@ _PG_fini(void) planner_hook = prev_planner; join_search_hook = prev_join_search; set_rel_pathlist_hook = prev_set_rel_pathlist; + ProcessUtility_hook = prev_ProcessUtility_hook; /* uninstall PL/pgSQL plugin hook */ var_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin"); @@ -925,7 +937,7 @@ ParallelHintCreate(const char *hint_str, const char *keyword, { ParallelHint *hint; - hint = palloc(sizeof(ScanMethodHint)); + hint = palloc(sizeof(ParallelHint)); hint->base.hint_str = hint_str; hint->base.keyword = keyword; hint->base.hint_keyword = hint_keyword; @@ -972,6 +984,7 @@ HintStateCreate(void) hstate->init_min_para_indexscan_size = 0; hstate->init_paratup_cost = 0; hstate->init_parasetup_cost = 0; + hstate->current_root = NULL; hstate->parent_relid = 0; hstate->parent_scan_hint = NULL; hstate->parent_parallel_hint = NULL; @@ -1170,7 +1183,8 @@ RowsHintDesc(RowsHint *hint, StringInfo buf, bool nolf) quote_value(buf, hint->relnames[i]); } } - appendStringInfo(buf, " %s", hint->rows_str); + if (hint->rows_str != NULL) + appendStringInfo(buf, " %s", hint->rows_str); appendStringInfoString(buf, ")"); if (!nolf) appendStringInfoChar(buf, '\n'); @@ -1235,7 +1249,7 @@ HintStateDump(HintState *hstate) if (!hstate) { - elog(LOG, "pg_hint_plan:\nno hint"); + elog(pg_hint_plan_debug_message_level, "pg_hint_plan:\nno hint"); return; } @@ -1247,7 +1261,8 @@ HintStateDump(HintState *hstate) desc_hint_in_state(hstate, &buf, "duplication hint", HINT_STATE_DUPLICATION, false); desc_hint_in_state(hstate, &buf, "error hint", HINT_STATE_ERROR, false); - elog(LOG, "%s", buf.data); + ereport(pg_hint_plan_debug_message_level, + (errmsg ("%s", buf.data))); pfree(buf.data); } @@ -1259,7 +1274,7 @@ HintStateDump2(HintState *hstate) if (!hstate) { - elog(pg_hint_plan_message_level, + elog(pg_hint_plan_debug_message_level, "pg_hint_plan%s: HintStateDump: no hint", qnostr); return; } @@ -1272,7 +1287,7 @@ HintStateDump2(HintState *hstate) desc_hint_in_state(hstate, &buf, "}, {error hints", HINT_STATE_ERROR, true); appendStringInfoChar(&buf, '}'); - ereport(pg_hint_plan_message_level, + ereport(pg_hint_plan_debug_message_level, (errmsg("%s", buf.data), errhidestmt(true), errhidecontext(true))); @@ -1655,7 +1670,7 @@ parse_hints(HintState *hstate, Query *parse, const char *str) char *keyword = parser->keyword; Hint *hint; - if (strcasecmp(buf.data, keyword) != 0) + if (pg_strcasecmp(buf.data, keyword) != 0) continue; hint = parser->create_func(head, keyword, parser->hint_keyword); @@ -1724,7 +1739,7 @@ get_hints_from_table(const char *client_query, const char *client_application) char *hints = NULL; Oid argtypes[2] = { TEXTOID, TEXTOID }; Datum values[2]; - bool nulls[2] = { false, false }; + char nulls[2] = {' ', ' '}; text *qry; text *app; @@ -1794,26 +1809,46 @@ get_hints_from_table(const char *client_query, const char *client_application) /* * Get client-supplied query string. Addtion to that the jumbled query is * supplied if the caller requested. From the restriction of JumbleQuery, some - * kind of query needs special amendments. Reutrns NULL if the current hint - * string is still valid. + * kind of query needs special amendments. Reutrns NULL if this query doesn't + * change the current hint. This function returns NULL also when something + * wrong has happend and let the caller continue using the current hints. */ static const char * get_query_string(ParseState *pstate, Query *query, Query **jumblequery) { const char *p = debug_query_string; + /* + * If debug_query_string is set, it is the top level statement. But in some + * cases we reach here with debug_query_string set NULL for example in the + * case of DESCRIBE message handling or EXECUTE command. We may still see a + * candidate top-level query in pstate in the case. + */ + if (pstate && pstate->p_sourcetext) + p = pstate->p_sourcetext; + + /* We don't see a query string, return NULL */ + if (!p) + return NULL; + if (jumblequery != NULL) *jumblequery = query; if (query->commandType == CMD_UTILITY) { - Query *target_query = query; + Query *target_query = (Query *)query->utilityStmt; - /* Use the target query if EXPLAIN */ - if (IsA(query->utilityStmt, ExplainStmt)) + /* + * Some CMD_UTILITY statements have a subquery that we can hint on. + * Since EXPLAIN can be placed before other kind of utility statements + * and EXECUTE can be contained other kind of utility statements, these + * conditions are not mutually exclusive and should be considered in + * this order. + */ + if (IsA(target_query, ExplainStmt)) { - ExplainStmt *stmt = (ExplainStmt *)(query->utilityStmt); - + ExplainStmt *stmt = (ExplainStmt *)target_query; + Assert(IsA(stmt->query, Query)); target_query = (Query *)stmt->query; @@ -1823,53 +1858,68 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery) target_query = (Query *)target_query->utilityStmt; } + if (IsA(target_query, DeclareCursorStmt)) + { + DeclareCursorStmt *stmt = (DeclareCursorStmt *)target_query; + Query *query = (Query *)stmt->query; + + /* the target must be CMD_SELECT in this case */ + Assert(IsA(query, Query) && query->commandType == CMD_SELECT); + target_query = query; + } + if (IsA(target_query, CreateTableAsStmt)) { - /* - * Use the the body query for CREATE AS. The Query for jumble also - * replaced with the corresponding one. - */ CreateTableAsStmt *stmt = (CreateTableAsStmt *) target_query; - PreparedStatement *entry; - Query *tmp_query; Assert(IsA(stmt->query, Query)); - tmp_query = (Query *) stmt->query; + target_query = (Query *) stmt->query; - if (tmp_query->commandType == CMD_UTILITY && - IsA(tmp_query->utilityStmt, ExecuteStmt)) - { - ExecuteStmt *estmt = (ExecuteStmt *) tmp_query->utilityStmt; - entry = FetchPreparedStatement(estmt->name, true); - p = entry->plansource->query_string; - target_query = (Query *) linitial (entry->plansource->query_list); - } + /* strip out the top-level query for further processing */ + if (target_query->commandType == CMD_UTILITY && + target_query->utilityStmt != NULL) + target_query = (Query *)target_query->utilityStmt; } - else + if (IsA(target_query, ExecuteStmt)) { /* - * Use the prepared query for EXECUTE. The Query for jumble also - * replaced with the corresponding one. + * Use the prepared query for EXECUTE. The Query for jumble + * also replaced with the corresponding one. */ ExecuteStmt *stmt = (ExecuteStmt *)target_query; PreparedStatement *entry; entry = FetchPreparedStatement(stmt->name, true); - p = entry->plansource->query_string; - target_query = (Query *) linitial (entry->plansource->query_list); - } - /* We don't accept other than a Query other than a CMD_UTILITY */ - if (!IsA(target_query, Query) || - target_query->commandType == CMD_UTILITY) + if (entry->plansource->is_valid) + { + p = entry->plansource->query_string; + target_query = (Query *) linitial (entry->plansource->query_list); + } + else + { + /* igonre the hint for EXECUTE if invalidated */ + p = NULL; + target_query = NULL; + } + } + + /* JumbleQuery accespts only a non-utility Query */ + if (target_query && + (!IsA(target_query, Query) || + target_query->utilityStmt != NULL)) target_query = NULL; if (jumblequery) *jumblequery = target_query; } - /* Return NULL if the pstate is not identical to the top-level query */ - else if (strcmp(pstate->p_sourcetext, p) != 0) + /* + * Return NULL if pstate is not of top-level query. We don't need this + * when jumble info is not requested or cannot do this when pstate is NULL. + */ + else if (!jumblequery && pstate && pstate->p_sourcetext != p && + strcmp(pstate->p_sourcetext, p) != 0) p = NULL; return p; @@ -2323,6 +2373,8 @@ RowsHintParse(RowsHint *hint, HintState *hstate, Query *parse, List *name_list = NIL; char *rows_str; char *end_ptr; + ListCell *l; + int i = 0; if ((str = parse_parentheses(str, &name_list, hint_keyword)) == NULL) return NULL; @@ -2330,23 +2382,28 @@ RowsHintParse(RowsHint *hint, HintState *hstate, Query *parse, /* Last element must be rows specification */ hint->nrels = list_length(name_list) - 1; - if (hint->nrels > 0) + if (hint->nrels < 1) { - ListCell *l; - int i = 0; + hint_ereport(str, + ("%s hint needs at least one relation followed by one correction term.", + hint->base.keyword)); + hint->base.state = HINT_STATE_ERROR; - /* - * Transform relation names from list to array to sort them with qsort - * after. - */ - hint->relnames = palloc(sizeof(char *) * hint->nrels); - foreach (l, name_list) - { - if (hint->nrels <= i) - break; - hint->relnames[i] = lfirst(l); - i++; - } + return str; + } + + + /* + * Transform relation names from list to array to sort them with qsort + * after. + */ + hint->relnames = palloc(sizeof(char *) * hint->nrels); + foreach (l, name_list) + { + if (hint->nrels <= i) + break; + hint->relnames[i] = lfirst(l); + i++; } /* Retieve rows estimation */ @@ -2461,9 +2518,9 @@ ParallelHintParse(ParallelHint *hint, HintState *hstate, Query *parse, if (length == 3) { const char *modeparam = (const char *)list_nth(name_list, 2); - if (strcasecmp(modeparam, "hard") == 0) + if (pg_strcasecmp(modeparam, "hard") == 0) force_parallel = true; - else if (strcasecmp(modeparam, "soft") != 0) + else if (pg_strcasecmp(modeparam, "soft") != 0) { hint_ereport(modeparam, ("enforcement must be soft or hard: %s", @@ -2569,16 +2626,32 @@ set_config_int32_option(const char *name, int32 value, GucContext context) if (snprintf(buf, 16, "%d", value) < 0) { - ereport(pg_hint_plan_message_level, - (errmsg ("Cannot set integer value: %d: %s", - max_hint_nworkers, strerror(errno)))); + ereport(pg_hint_plan_parse_message_level, + (errmsg ("Failed to convert integer to string: %d", value))); return false; } return set_config_option_noerror(name, buf, context, PGC_S_SESSION, GUC_ACTION_SAVE, true, - pg_hint_plan_message_level); + pg_hint_plan_parse_message_level); +} + +/* + * Sets GUC parameter of double type without throwing exceptions. Returns false + * if something wrong. + */ +static int +set_config_double_option(const char *name, double value, GucContext context) +{ + char *buf = float8out_internal(value); + int result; + + result = set_config_option_noerror(name, buf, context, + PGC_S_SESSION, GUC_ACTION_SAVE, true, + pg_hint_plan_parse_message_level); + pfree(buf); + return result; } /* setup scan method enforcement according to given options */ @@ -2597,7 +2670,7 @@ setup_guc_enforcement(SetHint **options, int noptions, GucContext context) result = set_config_option_noerror(hint->name, hint->value, context, PGC_S_SESSION, GUC_ACTION_SAVE, true, - pg_hint_plan_message_level); + pg_hint_plan_parse_message_level); if (result != 0) hint->base.state = HINT_STATE_USED; else @@ -2626,10 +2699,10 @@ setup_parallel_plan_enforcement(ParallelHint *hint, HintState *state) state->init_nworkers, state->context); /* force means that enforce parallel as far as possible */ - if (hint && hint->force_parallel) + if (hint && hint->force_parallel && hint->nworkers > 0) { - set_config_int32_option("parallel_tuple_cost", 0, state->context); - set_config_int32_option("parallel_setup_cost", 0, state->context); + set_config_double_option("parallel_tuple_cost", 0.0, state->context); + set_config_double_option("parallel_setup_cost", 0.0, state->context); set_config_int32_option("min_parallel_table_scan_size", 0, state->context); set_config_int32_option("min_parallel_index_scan_size", 0, @@ -2637,9 +2710,9 @@ setup_parallel_plan_enforcement(ParallelHint *hint, HintState *state) } else { - set_config_int32_option("parallel_tuple_cost", + set_config_double_option("parallel_tuple_cost", state->init_paratup_cost, state->context); - set_config_int32_option("parallel_setup_cost", + set_config_double_option("parallel_setup_cost", state->init_parasetup_cost, state->context); set_config_int32_option("min_parallel_table_scan_size", state->init_min_para_tablescan_size, @@ -2739,24 +2812,25 @@ pop_hint(void) } /* - * Retrieve and store a hint string from given query or from the hint table. - * If we are using the hint table, the query string is needed to be normalized. - * However, ParseState, which is not available in planner_hook, is required to - * check if the query tree (Query) is surely corresponding to the target query. + * Retrieve and store hint string from given query or from the hint table. */ static void -pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query) +get_current_hint_string(ParseState *pstate, Query *query) { const char *query_str; MemoryContext oldcontext; - if (prev_post_parse_analyze_hook) - prev_post_parse_analyze_hook(pstate, query); - /* do nothing under hint table search */ if (hint_inhibit_level > 0) return; + /* We alredy have one, don't parse it again. */ + if (current_hint_retrieved) + return; + + /* Don't parse the current query hereafter */ + current_hint_retrieved = true; + if (!pg_hint_plan_enable_hint) { if (current_hint_str) @@ -2797,8 +2871,8 @@ pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query) if (jumblequery) { /* - * XXX: normalizing code is copied from pg_stat_statements.c, so be - * careful to PostgreSQL's version up. + * XXX: normalization code is copied from pg_stat_statements.c. + * Make sure to keep up-to-date with it. */ jstate.jumble = (unsigned char *) palloc(JUMBLE_SIZE); jstate.jumble_len = 0; @@ -2822,8 +2896,7 @@ pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query) */ query_len = strlen(query_str) + 1; normalized_query = - generate_normalized_query(&jstate, query_str, - &query_len, + generate_normalized_query(&jstate, query_str, 0, &query_len, GetDatabaseEncoding()); /* @@ -2838,7 +2911,7 @@ pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query) if (debug_level > 1) { if (current_hint_str) - ereport(pg_hint_plan_message_level, + ereport(pg_hint_plan_debug_message_level, (errmsg("pg_hint_plan[qno=0x%x]: " "post_parse_analyze_hook: " "hints from table: \"%s\": " @@ -2849,7 +2922,7 @@ pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query) errhidestmt(msgqno != qno), errhidecontext(msgqno != qno))); else - ereport(pg_hint_plan_message_level, + ereport(pg_hint_plan_debug_message_level, (errmsg("pg_hint_plan[qno=0x%x]: " "no match found in table: " "application name = \"%s\", " @@ -2863,7 +2936,7 @@ pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query) } } - /* retrun if we have hint here*/ + /* retrun if we have hint here */ if (current_hint_str) return; } @@ -2874,8 +2947,8 @@ pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query) { /* * get hints from the comment. However we may have the same query - * string with the previous call, but just retrieving hints is expected - * to be faster than checking for identicalness before retrieval. + * string with the previous call, but the extra comparison seems no + * use.. */ if (current_hint_str) pfree((void *)current_hint_str); @@ -2884,21 +2957,30 @@ pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query) current_hint_str = get_hints_from_comment(query_str); MemoryContextSwitchTo(oldcontext); } + else + { + /* + * Failed to get query. We would be in fetching invalidated + * plancache. Try the next chance. + */ + current_hint_retrieved = false; + } if (debug_level > 1) { - if (debug_level == 1 && - (stmt_name || strcmp(query_str, debug_query_string))) - ereport(pg_hint_plan_message_level, + if (debug_level == 1 && query_str && debug_query_string && + strcmp(query_str, debug_query_string)) + ereport(pg_hint_plan_debug_message_level, (errmsg("hints in comment=\"%s\"", current_hint_str ? current_hint_str : "(none)"), errhidestmt(msgqno != qno), errhidecontext(msgqno != qno))); else - ereport(pg_hint_plan_message_level, - (errmsg("hints in comment=\"%s\", stmt=\"%s\", query=\"%s\", debug_query_string=\"%s\"", + ereport(pg_hint_plan_debug_message_level, + (errmsg("hints in comment=\"%s\", query=\"%s\", debug_query_string=\"%s\"", current_hint_str ? current_hint_str : "(none)", - stmt_name, query_str, debug_query_string), + query_str ? query_str : "(none)", + debug_query_string ? debug_query_string : "(none)"), errhidestmt(msgqno != qno), errhidecontext(msgqno != qno))); msgqno = qno; @@ -2906,6 +2988,44 @@ pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query) } /* + * Retrieve hint string from the current query. + */ +static void +pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query) +{ + if (prev_post_parse_analyze_hook) + prev_post_parse_analyze_hook(pstate, query); + + /* always retrieve hint from the top-level query string */ + if (plpgsql_recurse_level == 0) + current_hint_retrieved = false; + + get_current_hint_string(pstate, query); +} + +/* + * We need to reset current_hint_retrieved flag always when a command execution + * is finished. This is true even for a pure utility command that doesn't + * involve planning phase. + */ +static void +pg_hint_plan_ProcessUtility(PlannedStmt *pstmt, const char *queryString, + ProcessUtilityContext context, + ParamListInfo params, QueryEnvironment *queryEnv, + DestReceiver *dest, char *completionTag) +{ + if (prev_ProcessUtility_hook) + prev_ProcessUtility_hook(pstmt, queryString, context, params, queryEnv, + dest, completionTag); + else + standard_ProcessUtility(pstmt, queryString, context, params, queryEnv, + dest, completionTag); + + if (plpgsql_recurse_level == 0) + current_hint_retrieved = false; +} + +/* * Read and set up hint information */ static PlannedStmt * @@ -2914,6 +3034,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) int save_nestlevel; PlannedStmt *result; HintState *hstate; + const char *prev_hint_str = NULL; /* * Use standard planner if pg_hint_plan is disabled or current nesting @@ -2923,7 +3044,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0) { if (debug_level > 1) - ereport(pg_hint_plan_message_level, + ereport(pg_hint_plan_debug_message_level, (errmsg ("pg_hint_plan%s: planner: enable_hint=%d," " hint_inhibit_level=%d", qnostr, pg_hint_plan_enable_hint, @@ -2938,19 +3059,25 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) * Support for nested plpgsql functions. This is quite ugly but this is the * only point I could find where I can get the query string. */ - if (plpgsql_recurse_level > 0) + if (plpgsql_recurse_level > 0 && + error_context_stack && error_context_stack->arg) { MemoryContext oldcontext; - if (current_hint_str) - pfree((void *)current_hint_str); - oldcontext = MemoryContextSwitchTo(TopMemoryContext); current_hint_str = get_hints_from_comment((char *)error_context_stack->arg); MemoryContextSwitchTo(oldcontext); } + /* + * Query execution in extended protocol can be started without the analyze + * phase. In the case retrieve hint string here. + */ + if (!current_hint_str) + get_current_hint_string(NULL, parse); + + /* No hint, go the normal way */ if (!current_hint_str) goto standard_planner_proc; @@ -2994,13 +3121,22 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) if (debug_level > 1) { - ereport(pg_hint_plan_message_level, + ereport(pg_hint_plan_debug_message_level, (errhidestmt(msgqno != qno), errmsg("pg_hint_plan%s: planner", qnostr))); msgqno = qno; } /* + * The planner call below may replace current_hint_str. Store and restore + * it so that the subsequent planning in the upper level doesn't get + * confused. + */ + recurse_level++; + prev_hint_str = current_hint_str; + current_hint_str = NULL; + + /* * Use PG_TRY mechanism to recover GUC parameters and current_hint_state to * the state when this planner started when error occurred in planner. */ @@ -3010,6 +3146,9 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) result = (*prev_planner) (parse, cursorOptions, boundParams); else result = standard_planner(parse, cursorOptions, boundParams); + + current_hint_str = prev_hint_str; + recurse_level--; } PG_CATCH(); { @@ -3017,12 +3156,25 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) * Rollback changes of GUC parameters, and pop current hint context * from hint stack to rewind the state. */ + current_hint_str = prev_hint_str; + recurse_level--; AtEOXact_GUC(true, save_nestlevel); pop_hint(); PG_RE_THROW(); } PG_END_TRY(); + + /* + * current_hint_str is useless after planning of the top-level query. + */ + if (recurse_level < 1 && current_hint_str) + { + pfree((void *)current_hint_str); + current_hint_str = NULL; + current_hint_retrieved = false; + } + /* Print hint in debug mode. */ if (debug_level == 1) HintStateDump(current_hint_state); @@ -3041,7 +3193,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) standard_planner_proc: if (debug_level > 1) { - ereport(pg_hint_plan_message_level, + ereport(pg_hint_plan_debug_message_level, (errhidestmt(msgqno != qno), errmsg("pg_hint_plan%s: planner: no valid hint", qnostr))); @@ -3049,9 +3201,15 @@ standard_planner_proc: } current_hint_state = NULL; if (prev_planner) - return (*prev_planner) (parse, cursorOptions, boundParams); + result = (*prev_planner) (parse, cursorOptions, boundParams); else - return standard_planner(parse, cursorOptions, boundParams); + result = standard_planner(parse, cursorOptions, boundParams); + + /* The upper-level planner still needs the current hint state */ + if (HintStateStack != NIL) + current_hint_state = (HintState *) lfirst(list_head(HintStateStack)); + + return result; } /* @@ -3340,7 +3498,7 @@ restrict_indexes(PlannerInfo *root, ScanMethodHint *hint, RelOptInfo *rel, break; c_attname = get_attname(relationObjectId, - info->indexkeys[i]); + info->indexkeys[i], false); /* deny if any of column attributes don't match */ if (strcmp(p_attname, c_attname) != 0 || @@ -3367,7 +3525,7 @@ restrict_indexes(PlannerInfo *root, ScanMethodHint *hint, RelOptInfo *rel, /* check expressions if both expressions are available */ if (p_info->expression_str && - !heap_attisnull(ht_idx, Anum_pg_index_indexprs)) + !heap_attisnull(ht_idx, Anum_pg_index_indexprs, NULL)) { Datum exprsDatum; bool isnull; @@ -3398,7 +3556,7 @@ restrict_indexes(PlannerInfo *root, ScanMethodHint *hint, RelOptInfo *rel, /* compare index predicates */ if (p_info->indpred_str && - !heap_attisnull(ht_idx, Anum_pg_index_indpred)) + !heap_attisnull(ht_idx, Anum_pg_index_indpred, NULL)) { Datum predDatum; bool isnull; @@ -3469,7 +3627,7 @@ restrict_indexes(PlannerInfo *root, ScanMethodHint *hint, RelOptInfo *rel, initStringInfo(&rel_buf); quote_value(&rel_buf, disprelname); - ereport(LOG, + ereport(pg_hint_plan_debug_message_level, (errmsg("available indexes for %s(%s):%s", hint->base.keyword, rel_buf.data, @@ -3503,9 +3661,14 @@ get_parent_index_info(Oid indexoid, Oid relid) p_info->opclass = (Oid *) palloc(sizeof(Oid) * index->indnatts); p_info->indoption = (int16 *) palloc(sizeof(Oid) * index->indnatts); + /* + * Collect relation attribute names of index columns for index + * identification, not index attribute names. NULL means expression index + * columns. + */ for (i = 0; i < index->indnatts; i++) { - attname = get_attname(relid, index->indkey.values[i]); + attname = get_attname(relid, index->indkey.values[i], true); p_info->column_names = lappend(p_info->column_names, attname); p_info->indcollation[i] = indexRelation->rd_indcollation[i]; @@ -3517,7 +3680,8 @@ get_parent_index_info(Oid indexoid, Oid relid) * to check to match the expression's parameter of index with child indexes */ p_info->expression_str = NULL; - if(!heap_attisnull(indexRelation->rd_indextuple, Anum_pg_index_indexprs)) + if(!heap_attisnull(indexRelation->rd_indextuple, Anum_pg_index_indexprs, + NULL)) { Datum exprsDatum; bool isnull; @@ -3537,7 +3701,8 @@ get_parent_index_info(Oid indexoid, Oid relid) * to check to match the predicate's parameter of index with child indexes */ p_info->indpred_str = NULL; - if(!heap_attisnull(indexRelation->rd_indextuple, Anum_pg_index_indpred)) + if(!heap_attisnull(indexRelation->rd_indextuple, Anum_pg_index_indpred, + NULL)) { Datum predDatum; bool isnull; @@ -3573,7 +3738,7 @@ reset_hint_enforcement() * bitmap of HintTypeBitmap. If shint or phint is not NULL, set used hint * there respectively. */ -static bool +static int setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel, ScanMethodHint **rshint, ParallelHint **rphint) { @@ -3598,8 +3763,18 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel, */ if (inhparent) { + /* set up only parallel hints for parent relation */ + phint = find_parallel_hint(root, rel->relid); + if (phint) + { + setup_parallel_plan_enforcement(phint, current_hint_state); + if (rphint) *rphint = phint; + ret |= HINT_BM_PARALLEL; + return ret; + } + if (debug_level > 1) - ereport(pg_hint_plan_message_level, + ereport(pg_hint_plan_debug_message_level, (errhidestmt(true), errmsg ("pg_hint_plan%s: setup_hint_enforcement" " skipping inh parent: relation=%u(%s), inhparent=%d," @@ -3610,33 +3785,58 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel, return 0; } - /* Find the parent for this relation other than the registered parent */ - foreach (l, root->append_rel_list) + /* + * Forget about the parent of another subquery, but don't forget if the + * inhTargetkind of the root is not INHKIND_NONE, which signals the root + * contains only appendrel members. See inheritance_planner for details. + * + * (PG12.0) 428b260f87 added one more planning cycle for updates on + * partitioned tables and hints set up in the cycle are overriden by the + * second cycle. Since I didn't find no apparent distinction between the + * PlannerRoot of the cycle and that of ordinary CMD_SELECT, pg_hint_plan + * accepts both cycles and the later one wins. In the second cycle root + * doesn't have inheritance information at all so use the parent_relid set + * in the first cycle. + */ + if (root->inhTargetKind == INHKIND_NONE) { - AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l); + if (root != current_hint_state->current_root) + current_hint_state->parent_relid = 0; - if (appinfo->child_relid == rel->relid) + /* Find the parent for this relation other than the registered parent */ + foreach (l, root->append_rel_list) { - if (current_hint_state->parent_relid != appinfo->parent_relid) - new_parent_relid = appinfo->parent_relid; - break; + AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l); + + if (appinfo->child_relid == rel->relid) + { + if (current_hint_state->parent_relid != appinfo->parent_relid) + { + new_parent_relid = appinfo->parent_relid; + current_hint_state->current_root = root; + } + break; + } } - } - if (!l) - { - /* This relation doesn't have a parent. Cancel current_hint_state. */ - current_hint_state->parent_relid = 0; - current_hint_state->parent_scan_hint = NULL; - current_hint_state->parent_parallel_hint = NULL; + if (!l) + { + /* + * This relation doesn't have a parent. Cancel + * current_hint_state. + */ + current_hint_state->parent_relid = 0; + current_hint_state->parent_scan_hint = NULL; + current_hint_state->parent_parallel_hint = NULL; + } } if (new_parent_relid > 0) { /* * Here we found a new parent for the current relation. Scan continues - * hint to other childrens of this parent so remember it * to avoid - * hinthintredundant setup cost. + * hint to other childrens of this parent so remember it to avoid + * redundant setup cost. */ current_hint_state->parent_relid = new_parent_relid; @@ -3718,7 +3918,7 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel, if (shint == current_hint_state->parent_scan_hint) additional_message = " by parent hint"; - ereport(pg_hint_plan_message_level, + ereport(pg_hint_plan_debug_message_level, (errhidestmt(true), errmsg ("pg_hint_plan%s: setup_hint_enforcement" " index deletion%s:" @@ -3748,7 +3948,7 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel, if (!shint && ! phint) { if (debug_level > 1) - ereport(pg_hint_plan_message_level, + ereport(pg_hint_plan_debug_message_level, (errhidestmt (true), errmsg ("pg_hint_plan%s: setup_hint_enforcement" " no hint applied:" @@ -4373,6 +4573,38 @@ pg_hint_plan_join_search(PlannerInfo *root, int levels_needed, rel = pg_hint_plan_standard_join_search(root, levels_needed, initial_rels); + /* + * Adjust number of parallel workers of the result rel to the largest + * number of the component paths. + */ + if (current_hint_state->num_hints[HINT_TYPE_PARALLEL] > 0) + { + ListCell *lc; + int nworkers = 0; + + foreach (lc, initial_rels) + { + ListCell *lcp; + RelOptInfo *rel = (RelOptInfo *) lfirst(lc); + + foreach (lcp, rel->partial_pathlist) + { + Path *path = (Path *) lfirst(lcp); + + if (nworkers < path-> parallel_workers) + nworkers = path-> parallel_workers; + } + } + + foreach (lc, rel->partial_pathlist) + { + Path *path = (Path *) lfirst(lc); + + if (path->parallel_safe && path->parallel_workers < nworkers) + path->parallel_workers = nworkers; + } + } + for (i = 2; i <= nbaserel; i++) { list_free(current_hint_state->join_hint_level[i]); @@ -4419,11 +4651,65 @@ pg_hint_plan_set_rel_pathlist(PlannerInfo * root, RelOptInfo *rel, * We can accept only plain relations, foreign tables and table saples are * also unacceptable. See set_rel_pathlist. */ - if (rel->rtekind != RTE_RELATION || + if ((rel->rtekind != RTE_RELATION && + rel->rtekind != RTE_SUBQUERY)|| rte->relkind == RELKIND_FOREIGN_TABLE || rte->tablesample != NULL) return; + /* + * Even though UNION ALL node doesn't have particular name so usually it is + * unhintable, turn on parallel when it contains parallel nodes. + */ + if (rel->rtekind == RTE_SUBQUERY) + { + ListCell *lc; + bool inhibit_nonparallel = false; + + if (rel->partial_pathlist == NIL) + return; + + foreach(lc, rel->partial_pathlist) + { + ListCell *lcp; + AppendPath *apath = (AppendPath *) lfirst(lc); + int parallel_workers = 0; + + if (!IsA(apath, AppendPath)) + continue; + + foreach (lcp, apath->subpaths) + { + Path *spath = (Path *) lfirst(lcp); + + if (spath->parallel_aware && + parallel_workers < spath->parallel_workers) + parallel_workers = spath->parallel_workers; + } + + apath->path.parallel_workers = parallel_workers; + inhibit_nonparallel = true; + } + + if (inhibit_nonparallel) + { + ListCell *lc; + + foreach(lc, rel->pathlist) + { + Path *path = (Path *) lfirst(lc); + + if (path->startup_cost < disable_cost) + { + path->startup_cost += disable_cost; + path->total_cost += disable_cost; + } + } + } + + return; + } + /* We cannot handle if this requires an outer */ if (rel->lateral_relids) return; @@ -4435,49 +4721,96 @@ pg_hint_plan_set_rel_pathlist(PlannerInfo * root, RelOptInfo *rel, /* Here, we regenerate paths with the current hint restriction */ if (found_hints & HINT_BM_SCAN_METHOD || found_hints & HINT_BM_PARALLEL) { - /* Just discard all the paths considered so far */ - list_free_deep(rel->pathlist); - rel->pathlist = NIL; + /* + * When hint is specified on non-parent relations, discard existing + * paths and regenerate based on the hint considered. Otherwise we + * already have hinted childx paths then just adjust the number of + * planned number of workers. + */ + if (root->simple_rte_array[rel->relid]->inh) + { + /* enforce number of workers if requested */ + if (phint && phint->force_parallel) + { + if (phint->nworkers == 0) + { + list_free_deep(rel->partial_pathlist); + rel->partial_pathlist = NIL; + } + else + { + /* prioritize partial paths */ + foreach (l, rel->partial_pathlist) + { + Path *ppath = (Path *) lfirst(l); - /* Remove all the partial paths if Parallel hint is specfied */ - if ((found_hints & HINT_BM_PARALLEL) && rel->partial_pathlist) + if (ppath->parallel_safe) + { + ppath->parallel_workers = phint->nworkers; + ppath->startup_cost = 0; + ppath->total_cost = 0; + } + } + + /* disable non-partial paths */ + foreach (l, rel->pathlist) + { + Path *ppath = (Path *) lfirst(l); + + if (ppath->startup_cost < disable_cost) + { + ppath->startup_cost += disable_cost; + ppath->total_cost += disable_cost; + } + } + } + } + } + else { + /* Just discard all the paths considered so far */ + list_free_deep(rel->pathlist); + rel->pathlist = NIL; list_free_deep(rel->partial_pathlist); rel->partial_pathlist = NIL; - } - /* Regenerate paths with the current enforcement */ - set_plain_rel_pathlist(root, rel, rte); + /* Regenerate paths with the current enforcement */ + set_plain_rel_pathlist(root, rel, rte); - /* Additional work to enforce parallel query execution */ - if (phint && phint->nworkers > 0) - { - /* Lower the priorities of non-parallel paths */ - foreach (l, rel->pathlist) + /* Additional work to enforce parallel query execution */ + if (phint && phint->nworkers > 0) { - Path *path = (Path *) lfirst(l); - - if (path->startup_cost < disable_cost) + /* + * For Parallel Append to be planned properly, we shouldn't set + * the costs of non-partial paths to disable-value. Lower the + * priority of non-parallel paths by setting partial path costs + * to 0 instead. + */ + foreach (l, rel->partial_pathlist) { - path->startup_cost += disable_cost; - path->total_cost += disable_cost; + Path *path = (Path *) lfirst(l); + + path->startup_cost = 0; + path->total_cost = 0; } - } - /* enforce number of workers if requested */ - if (phint->force_parallel) - { - foreach (l, rel->partial_pathlist) + /* enforce number of workers if requested */ + if (phint->force_parallel) { - Path *ppath = (Path *) lfirst(l); + foreach (l, rel->partial_pathlist) + { + Path *ppath = (Path *) lfirst(l); - ppath->parallel_workers = phint->nworkers; + if (ppath->parallel_safe) + ppath->parallel_workers = phint->nworkers; + } } - } - /* Generate gather paths for base rels */ - if (rel->reloptkind == RELOPT_BASEREL) - generate_gather_paths(root, rel); + /* Generate gather paths */ + if (rel->reloptkind == RELOPT_BASEREL && + bms_membership(root->all_baserels) != BMS_SINGLETON) + generate_gather_paths(root, rel, false); + } } } @@ -4566,7 +4899,7 @@ void plpgsql_query_erase_callback(ResourceReleasePhase phase, bool isTopLevel, void *arg) { - if (phase != RESOURCE_RELEASE_AFTER_LOCKS) + if (!isTopLevel || phase != RESOURCE_RELEASE_AFTER_LOCKS) return; /* Cancel plpgsql nest level*/ plpgsql_recurse_level = 0;