X-Git-Url: http://git.osdn.net/view?a=blobdiff_plain;f=pg_hint_plan.c;h=8534cea35771f0bb414e10a0ef955989ede7daa3;hb=e6a90040835ccfb32479f2c42f5baecaa72d9ec0;hp=17753c219b048f48af8080d1ef79c8830d40da8b;hpb=e4e2df36671ac5b1ec3a2d4201029dc8fc9148e1;p=pghintplan%2Fpg_hint_plan.git diff --git a/pg_hint_plan.c b/pg_hint_plan.c index 17753c2..8534cea 100644 --- a/pg_hint_plan.c +++ b/pg_hint_plan.c @@ -1,53 +1,56 @@ /*------------------------------------------------------------------------- * * pg_hint_plan.c - * do instructions or hints to the planner using C-style block comments - * of the SQL. + * hinting on how to execute a query for PostgreSQL * - * Copyright (c) 2012-2014, NIPPON TELEGRAPH AND TELEPHONE CORPORATION + * Copyright (c) 2012-2021, 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" #include "mb/pg_wchar.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" +#include "nodes/params.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" #include "optimizer/planner.h" #include "optimizer/prep.h" #include "optimizer/restrictinfo.h" +#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" +#include "utils/snapmgr.h" #include "utils/syscache.h" +#include "utils/resowner.h" #include "catalog/pg_class.h" #include "executor/spi.h" #include "catalog/pg_type.h" -/* - * We have our own header file "plpgsql-9.1", which is necessary to support - * hints for queries in PL/pgSQL blocks, in pg_hint_plan source package, - * because PostgreSQL 9.1 doesn't provide the header file as a part of - * installation. This header file is a copy of src/pl/plpgsql/src/plpgsql.h in - * PostgreSQL 9.1.9 source tree, - * - * On the other hand, 9.2 installation provides that header file for external - * modules, so we include the header in ordinary place. - */ #include "plpgsql.h" /* partially copied from pg_stat_statements */ @@ -80,6 +83,8 @@ PG_MODULE_MAGIC; #define HINT_INDEXONLYSCAN "IndexOnlyScan" #define HINT_INDEXONLYSCANREGEXP "IndexOnlyScanRegexp" #define HINT_NOINDEXONLYSCAN "NoIndexOnlyScan" +#define HINT_PARALLEL "Parallel" + #define HINT_NESTLOOP "NestLoop" #define HINT_MERGEJOIN "MergeJoin" #define HINT_HASHJOIN "HashJoin" @@ -89,13 +94,18 @@ PG_MODULE_MAGIC; #define HINT_LEADING "Leading" #define HINT_SET "Set" #define HINT_ROWS "Rows" +#define HINT_MEMOIZE "Memoize" +#define HINT_NOMEMOIZE "NoMemoize" #define HINT_ARRAY_DEFAULT_INITSIZE 8 -#define hint_ereport(str, detail) \ - ereport(pg_hint_plan_parse_messages, \ - (errmsg("hint syntax error at or near \"%s\"", (str)), \ - errdetail detail)) +#define hint_ereport(str, detail) hint_parse_ereport(str, detail) +#define hint_parse_ereport(str, detail) \ + do { \ + ereport(pg_hint_plan_parse_message_level, \ + (errmsg("pg_hint_plan: hint syntax error at or near \"%s\"", (str)), \ + errdetail detail)); \ + } while(0) #define skip_space(str) \ while (isspace(*str)) \ @@ -114,7 +124,8 @@ enum { ENABLE_NESTLOOP = 0x01, ENABLE_MERGEJOIN = 0x02, - ENABLE_HASHJOIN = 0x04 + ENABLE_HASHJOIN = 0x04, + ENABLE_MEMOIZE = 0x08 } JOIN_TYPE_BITS; #define ENABLE_ALL_SCAN (ENABLE_SEQSCAN | ENABLE_INDEXSCAN | \ @@ -140,18 +151,33 @@ typedef enum HintKeyword HINT_KEYWORD_INDEXONLYSCAN, HINT_KEYWORD_INDEXONLYSCANREGEXP, HINT_KEYWORD_NOINDEXONLYSCAN, + HINT_KEYWORD_NESTLOOP, HINT_KEYWORD_MERGEJOIN, HINT_KEYWORD_HASHJOIN, HINT_KEYWORD_NONESTLOOP, HINT_KEYWORD_NOMERGEJOIN, HINT_KEYWORD_NOHASHJOIN, + HINT_KEYWORD_LEADING, HINT_KEYWORD_SET, HINT_KEYWORD_ROWS, + HINT_KEYWORD_PARALLEL, + HINT_KEYWORD_MEMOIZE, + HINT_KEYWORD_NOMEMOIZE, + HINT_KEYWORD_UNRECOGNIZED } HintKeyword; +#define SCAN_HINT_ACCEPTS_INDEX_NAMES(kw) \ + (kw == HINT_KEYWORD_INDEXSCAN || \ + kw == HINT_KEYWORD_INDEXSCANREGEXP || \ + kw == HINT_KEYWORD_INDEXONLYSCAN || \ + kw == HINT_KEYWORD_INDEXONLYSCANREGEXP || \ + kw == HINT_KEYWORD_BITMAPSCAN || \ + kw == HINT_KEYWORD_BITMAPSCANREGEXP) + + typedef struct Hint Hint; typedef struct HintState HintState; @@ -159,13 +185,12 @@ typedef Hint *(*HintCreateFunction) (const char *hint_str, const char *keyword, HintKeyword hint_keyword); typedef void (*HintDeleteFunction) (Hint *hint); -typedef void (*HintDescFunction) (Hint *hint, StringInfo buf); +typedef void (*HintDescFunction) (Hint *hint, StringInfo buf, bool nolf); typedef int (*HintCmpFunction) (const Hint *a, const Hint *b); typedef const char *(*HintParseFunction) (Hint *hint, HintState *hstate, Query *parse, const char *str); /* hint types */ -#define NUM_HINT_TYPE 5 typedef enum HintType { HINT_TYPE_SCAN_METHOD, @@ -173,14 +198,25 @@ typedef enum HintType HINT_TYPE_LEADING, HINT_TYPE_SET, HINT_TYPE_ROWS, + HINT_TYPE_PARALLEL, + HINT_TYPE_MEMOIZE, + + NUM_HINT_TYPE } HintType; +typedef enum HintTypeBitmap +{ + HINT_BM_SCAN_METHOD = 1, + HINT_BM_PARALLEL = 2 +} HintTypeBitmap; + static const char *HintTypeName[] = { "scan method", "join method", "leading", "set", "rows", + "parallel" }; /* hint status */ @@ -196,6 +232,21 @@ typedef enum HintStatus #define hint_state_enabled(hint) ((hint)->base.state == HINT_STATE_NOTUSED || \ (hint)->base.state == HINT_STATE_USED) +static unsigned int qno = 0; +static unsigned int msgqno = 0; +static char qnostr[32]; +static const char *current_hint_str = NULL; + +/* + * We can utilize in-core generated jumble state in post_parse_analyze_hook. + * On the other hand there's a case where we're forced to get hints in + * planner_hook, where we don't have a jumble state. If we a query had not a + * hint, we need to try to retrieve hints twice or more for one query, which is + * the quite common case. To avoid such case, this variables is set true when + * we *try* hint retrieval. + */ +static bool current_hint_retrieved = false; + /* common data for all hints. */ struct Hint { @@ -287,6 +338,16 @@ typedef struct RowsHint double rows; } RowsHint; +/* parallel hints */ +typedef struct ParallelHint +{ + Hint base; + char *relname; + char *nworkers_str; /* original string of nworkers */ + int nworkers; /* num of workers specified by Worker */ + bool force_parallel; /* force parallel scan */ +} ParallelHint; + /* * Describes a context of hint processing. */ @@ -304,26 +365,33 @@ struct HintState /* for scan method hints */ ScanMethodHint **scan_hints; /* parsed scan hints */ - int init_scan_mask; /* initial value scan parameter */ - Index parent_relid; /* inherit parent table relid */ - ScanMethodHint *parent_hint; /* inherit parent table scan hint */ - List *parent_index_infos; /* information of inherit parent table's - * index */ - /* for join method hints */ + /* Initial values of parameters */ + int init_scan_mask; /* enable_* mask */ + int init_nworkers; /* max_parallel_workers_per_gather */ + /* min_parallel_table_scan_size*/ + int init_min_para_tablescan_size; + /* min_parallel_index_scan_size*/ + int init_min_para_indexscan_size; + 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 */ + List *parent_index_infos; /* list of parent table's index */ + JoinMethodHint **join_hints; /* parsed join hints */ int init_join_mask; /* initial value join parameter */ List **join_hint_level; - - /* for Leading hint */ + List **memoize_hint_level; LeadingHint **leading_hint; /* parsed Leading hints */ - - /* for Set hints */ SetHint **set_hints; /* parsed Set hints */ GucContext context; /* which GUC parameters can we set? */ - - /* for Rows hints */ RowsHint **rows_hints; /* parsed Rows hints */ + ParallelHint **parallel_hints; /* parsed Parallel hints */ + JoinMethodHint **memoize_hints; /* parsed Memoize hints */ }; /* @@ -336,6 +404,9 @@ typedef struct HintParser HintKeyword hint_keyword; } HintParser; +static bool enable_hint_table_check(bool *newval, void **extra, GucSource source); +static void assign_enable_hint_table(bool newval, void *extra); + /* Module callbacks */ void _PG_init(void); void _PG_fini(void); @@ -343,56 +414,70 @@ void _PG_fini(void); static void push_hint(HintState *hstate); static void pop_hint(void); -static void pg_hint_plan_ProcessUtility(Node *parsetree, - const char *queryString, - ProcessUtilityContext context, - ParamListInfo params, - DestReceiver *dest, char *completionTag); -static PlannedStmt *pg_hint_plan_planner(Query *parse, int cursorOptions, +static void pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query, + JumbleState *jstate); +static PlannedStmt *pg_hint_plan_planner(Query *parse, const char *query_string, + int cursorOptions, ParamListInfo boundParams); -static void pg_hint_plan_get_relation_info(PlannerInfo *root, - Oid relationObjectId, - bool inhparent, RelOptInfo *rel); static RelOptInfo *pg_hint_plan_join_search(PlannerInfo *root, int levels_needed, List *initial_rels); +/* Scan method hint callbacks */ static Hint *ScanMethodHintCreate(const char *hint_str, const char *keyword, HintKeyword hint_keyword); static void ScanMethodHintDelete(ScanMethodHint *hint); -static void ScanMethodHintDesc(ScanMethodHint *hint, StringInfo buf); +static void ScanMethodHintDesc(ScanMethodHint *hint, StringInfo buf, bool nolf); static int ScanMethodHintCmp(const ScanMethodHint *a, const ScanMethodHint *b); static const char *ScanMethodHintParse(ScanMethodHint *hint, HintState *hstate, Query *parse, const char *str); + +/* Join method hint callbacks */ static Hint *JoinMethodHintCreate(const char *hint_str, const char *keyword, HintKeyword hint_keyword); static void JoinMethodHintDelete(JoinMethodHint *hint); -static void JoinMethodHintDesc(JoinMethodHint *hint, StringInfo buf); +static void JoinMethodHintDesc(JoinMethodHint *hint, StringInfo buf, bool nolf); static int JoinMethodHintCmp(const JoinMethodHint *a, const JoinMethodHint *b); static const char *JoinMethodHintParse(JoinMethodHint *hint, HintState *hstate, Query *parse, const char *str); + +/* Leading hint callbacks */ static Hint *LeadingHintCreate(const char *hint_str, const char *keyword, HintKeyword hint_keyword); static void LeadingHintDelete(LeadingHint *hint); -static void LeadingHintDesc(LeadingHint *hint, StringInfo buf); +static void LeadingHintDesc(LeadingHint *hint, StringInfo buf, bool nolf); static int LeadingHintCmp(const LeadingHint *a, const LeadingHint *b); static const char *LeadingHintParse(LeadingHint *hint, HintState *hstate, Query *parse, const char *str); + +/* Set hint callbacks */ static Hint *SetHintCreate(const char *hint_str, const char *keyword, HintKeyword hint_keyword); static void SetHintDelete(SetHint *hint); -static void SetHintDesc(SetHint *hint, StringInfo buf); +static void SetHintDesc(SetHint *hint, StringInfo buf, bool nolf); static int SetHintCmp(const SetHint *a, const SetHint *b); static const char *SetHintParse(SetHint *hint, HintState *hstate, Query *parse, const char *str); + +/* Rows hint callbacks */ static Hint *RowsHintCreate(const char *hint_str, const char *keyword, HintKeyword hint_keyword); static void RowsHintDelete(RowsHint *hint); -static void RowsHintDesc(RowsHint *hint, StringInfo buf); +static void RowsHintDesc(RowsHint *hint, StringInfo buf, bool nolf); static int RowsHintCmp(const RowsHint *a, const RowsHint *b); static const char *RowsHintParse(RowsHint *hint, HintState *hstate, Query *parse, const char *str); -static Hint *LeadingHintCreate(const char *hint_str, const char *keyword, + +/* Parallel hint callbacks */ +static Hint *ParallelHintCreate(const char *hint_str, const char *keyword, + HintKeyword hint_keyword); +static void ParallelHintDelete(ParallelHint *hint); +static void ParallelHintDesc(ParallelHint *hint, StringInfo buf, bool nolf); +static int ParallelHintCmp(const ParallelHint *a, const ParallelHint *b); +static const char *ParallelHintParse(ParallelHint *hint, HintState *hstate, + Query *parse, const char *str); + +static Hint *MemoizeHintCreate(const char *hint_str, const char *keyword, HintKeyword hint_keyword); static void quote_value(StringInfo buf, const char *value); @@ -404,21 +489,19 @@ RelOptInfo *pg_hint_plan_standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels); void pg_hint_plan_join_search_one_level(PlannerInfo *root, int level); +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 make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *old_rel, + List *other_rels_list, ListCell *other_rels); static void make_rels_by_clauseless_joins(PlannerInfo *root, RelOptInfo *old_rel, - ListCell *other_rels); + List *other_rels); static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel); -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); -static Path *get_cheapest_parameterized_child_path(PlannerInfo *root, - RelOptInfo *rel, - Relids required_outer); -static List *accumulate_append_subpath(List *subpaths, Path *path); +static void set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, + RangeTblEntry *rte); RelOptInfo *pg_hint_plan_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2); @@ -426,13 +509,36 @@ static void pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt); static void pg_hint_plan_plpgsql_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt); +static void plpgsql_query_erase_callback(ResourceReleasePhase phase, + bool isCommit, + bool isTopLevel, + void *arg); +static int set_config_option_noerror(const char *name, const char *value, + GucContext context, GucSource source, + GucAction action, bool changeVal, int elevel); +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 bool pg_hint_plan_debug_print = false; -static int pg_hint_plan_parse_messages = INFO; +static int debug_level = 0; +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 bool pg_hint_plan_hints_anywhere = 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 */ + +static bool hint_table_deactivated = false; static const struct config_enum_entry parse_messages_level_options[] = { {"debug", DEBUG2, true}, @@ -453,27 +559,38 @@ static const struct config_enum_entry parse_messages_level_options[] = { {NULL, 0, false} }; +static const struct config_enum_entry parse_debug_level_options[] = { + {"off", 0, false}, + {"on", 1, false}, + {"detailed", 2, false}, + {"verbose", 3, false}, + {"0", 0, true}, + {"1", 1, true}, + {"2", 2, true}, + {"3", 3, true}, + {"no", 0, true}, + {"yes", 1, true}, + {"false", 0, true}, + {"true", 1, true}, + {NULL, 0, false} +}; + /* Saved hook values in case of unload */ -static ProcessUtility_hook_type prev_ProcessUtility = NULL; +static post_parse_analyze_hook_type prev_post_parse_analyze_hook = NULL; static planner_hook_type prev_planner = NULL; -static get_relation_info_hook_type prev_get_relation_info = NULL; static join_search_hook_type prev_join_search = NULL; +static set_rel_pathlist_hook_type prev_set_rel_pathlist = NULL; /* Hold reference to currently active hint */ -static HintState *current_hint = NULL; +static HintState *current_hint_state = NULL; /* * List of hint contexts. We treat the head of the list as the Top of the - * context stack, so current_hint always points the first element of this list. + * context stack, so current_hint_state always points the first element of this + * list. */ 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}, @@ -490,23 +607,24 @@ static const HintParser parsers[] = { {HINT_INDEXONLYSCANREGEXP, ScanMethodHintCreate, HINT_KEYWORD_INDEXONLYSCANREGEXP}, {HINT_NOINDEXONLYSCAN, ScanMethodHintCreate, HINT_KEYWORD_NOINDEXONLYSCAN}, + {HINT_NESTLOOP, JoinMethodHintCreate, HINT_KEYWORD_NESTLOOP}, {HINT_MERGEJOIN, JoinMethodHintCreate, HINT_KEYWORD_MERGEJOIN}, {HINT_HASHJOIN, JoinMethodHintCreate, HINT_KEYWORD_HASHJOIN}, {HINT_NONESTLOOP, JoinMethodHintCreate, HINT_KEYWORD_NONESTLOOP}, {HINT_NOMERGEJOIN, JoinMethodHintCreate, HINT_KEYWORD_NOMERGEJOIN}, {HINT_NOHASHJOIN, JoinMethodHintCreate, HINT_KEYWORD_NOHASHJOIN}, + {HINT_LEADING, LeadingHintCreate, HINT_KEYWORD_LEADING}, {HINT_SET, SetHintCreate, HINT_KEYWORD_SET}, {HINT_ROWS, RowsHintCreate, HINT_KEYWORD_ROWS}, + {HINT_PARALLEL, ParallelHintCreate, HINT_KEYWORD_PARALLEL}, + {HINT_MEMOIZE, MemoizeHintCreate, HINT_KEYWORD_MEMOIZE}, + {HINT_NOMEMOIZE, MemoizeHintCreate, HINT_KEYWORD_NOMEMOIZE}, + {NULL, NULL, HINT_KEYWORD_UNRECOGNIZED} }; -/* - * PL/pgSQL plugin for retrieving string representation of each query during - * function execution. - */ -const char *plpgsql_query_string = NULL; PLpgSQL_plugin plugin_funcs = { NULL, NULL, @@ -517,9 +635,6 @@ PLpgSQL_plugin plugin_funcs = { NULL, }; -/* Current nesting depth of SPI calls, used to prevent recursive calls */ -static int nested_level = 0; - /* * Module load callbacks */ @@ -534,17 +649,18 @@ _PG_init(void) NULL, &pg_hint_plan_enable_hint, true, - PGC_USERSET, + PGC_USERSET, 0, NULL, NULL, NULL); - DefineCustomBoolVariable("pg_hint_plan.debug_print", + DefineCustomEnumVariable("pg_hint_plan.debug_print", "Logs results of hint parsing.", NULL, - &pg_hint_plan_debug_print, + &debug_level, false, + parse_debug_level_options, PGC_USERSET, 0, NULL, @@ -554,7 +670,7 @@ _PG_init(void) DefineCustomEnumVariable("pg_hint_plan.parse_messages", "Message level of parse errors.", NULL, - &pg_hint_plan_parse_messages, + &pg_hint_plan_parse_message_level, INFO, parse_messages_level_options, PGC_USERSET, @@ -563,30 +679,57 @@ _PG_init(void) NULL, NULL); + DefineCustomEnumVariable("pg_hint_plan.message_level", + "Message level of debug messages.", + NULL, + &pg_hint_plan_debug_message_level, + LOG, + parse_messages_level_options, + PGC_USERSET, + 0, + NULL, + NULL, + NULL); + DefineCustomBoolVariable("pg_hint_plan.enable_hint_table", - "Force planner to not get hint by using table lookups.", + "Let pg_hint_plan look up the hint table.", NULL, &pg_hint_plan_enable_hint_table, false, PGC_USERSET, 0, + enable_hint_table_check, + assign_enable_hint_table, + NULL); + + DefineCustomBoolVariable("pg_hint_plan.hints_anywhere", + "Read hints from anywhere in a query.", + "This option lets pg_hint_plan ignore syntax so be cautious for false reads.", + &pg_hint_plan_hints_anywhere, + false, + PGC_USERSET, + 0, NULL, NULL, NULL); + EmitWarningsOnPlaceholders("pg_hint_plan"); + /* Install hooks. */ - prev_ProcessUtility = ProcessUtility_hook; - ProcessUtility_hook = pg_hint_plan_ProcessUtility; + prev_post_parse_analyze_hook = post_parse_analyze_hook; + post_parse_analyze_hook = pg_hint_plan_post_parse_analyze; prev_planner = planner_hook; planner_hook = pg_hint_plan_planner; - prev_get_relation_info = get_relation_info_hook; - get_relation_info_hook = pg_hint_plan_get_relation_info; prev_join_search = join_search_hook; 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; /* setup PL/pgSQL plugin hook */ var_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin"); *var_ptr = &plugin_funcs; + + RegisterResourceReleaseCallback(plpgsql_query_erase_callback, NULL); } /* @@ -599,16 +742,40 @@ _PG_fini(void) PLpgSQL_plugin **var_ptr; /* Uninstall hooks. */ - ProcessUtility_hook = prev_ProcessUtility; planner_hook = prev_planner; - get_relation_info_hook = prev_get_relation_info; join_search_hook = prev_join_search; + set_rel_pathlist_hook = prev_set_rel_pathlist; /* uninstall PL/pgSQL plugin hook */ var_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin"); *var_ptr = NULL; } +static bool +enable_hint_table_check(bool *newval, void **extra, GucSource source) +{ + if (*newval) + { + EnableQueryId(); + + if (!IsQueryIdEnabled()) + { + GUC_check_errmsg("table hint is not activated because queryid is not available"); + GUC_check_errhint("Set compute_query_id to on or auto to use hint table."); + return false; + } + } + + return true; +} + +static void +assign_enable_hint_table(bool newval, void *extra) +{ + if (!newval) + hint_table_deactivated = false; +} + /* * create and delete functions the hint object */ @@ -815,6 +982,58 @@ RowsHintDelete(RowsHint *hint) pfree(hint); } +static Hint * +ParallelHintCreate(const char *hint_str, const char *keyword, + HintKeyword hint_keyword) +{ + ParallelHint *hint; + + hint = palloc(sizeof(ParallelHint)); + hint->base.hint_str = hint_str; + hint->base.keyword = keyword; + hint->base.hint_keyword = hint_keyword; + hint->base.type = HINT_TYPE_PARALLEL; + hint->base.state = HINT_STATE_NOTUSED; + hint->base.delete_func = (HintDeleteFunction) ParallelHintDelete; + hint->base.desc_func = (HintDescFunction) ParallelHintDesc; + hint->base.cmp_func = (HintCmpFunction) ParallelHintCmp; + hint->base.parse_func = (HintParseFunction) ParallelHintParse; + hint->relname = NULL; + hint->nworkers = 0; + hint->nworkers_str = "0"; + + return (Hint *) hint; +} + +static void +ParallelHintDelete(ParallelHint *hint) +{ + if (!hint) + return; + + if (hint->relname) + pfree(hint->relname); + pfree(hint); +} + + +static Hint * +MemoizeHintCreate(const char *hint_str, const char *keyword, + HintKeyword hint_keyword) +{ + /* + * MemoizeHintCreate shares the same struct with JoinMethodHint and the + * only difference is the hint type. + */ + JoinMethodHint *hint = + (JoinMethodHint *)JoinMethodHintCreate(hint_str, keyword, hint_keyword); + + hint->base.type = HINT_TYPE_MEMOIZE; + + return (Hint *) hint; +} + + static HintState * HintStateCreate(void) { @@ -828,16 +1047,25 @@ HintStateCreate(void) memset(hstate->num_hints, 0, sizeof(hstate->num_hints)); hstate->scan_hints = NULL; hstate->init_scan_mask = 0; + hstate->init_nworkers = 0; + hstate->init_min_para_tablescan_size = 0; + 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_hint = NULL; + hstate->parent_scan_hint = NULL; + hstate->parent_parallel_hint = NULL; hstate->parent_index_infos = NIL; hstate->join_hints = NULL; hstate->init_join_mask = 0; hstate->join_hint_level = NULL; + hstate->memoize_hint_level = NULL; hstate->leading_hint = NULL; hstate->context = superuser() ? PGC_SUSET : PGC_USERSET; hstate->set_hints = NULL; hstate->rows_hints = NULL; + hstate->parallel_hints = NULL; return hstate; } @@ -853,12 +1081,18 @@ HintStateDelete(HintState *hstate) if (hstate->hint_str) pfree(hstate->hint_str); - for (i = 0; i < hstate->num_hints[HINT_TYPE_SCAN_METHOD]; i++) + for (i = 0; i < hstate->nall_hints ; i++) hstate->all_hints[i]->delete_func(hstate->all_hints[i]); if (hstate->all_hints) pfree(hstate->all_hints); if (hstate->parent_index_infos) list_free(hstate->parent_index_infos); + + /* + * We have another few or dozen of palloced block in the struct, but don't + * bother completely clean up all of them since they will be cleaned-up at + * the end of this query. + */ } /* @@ -893,7 +1127,7 @@ quote_value(StringInfo buf, const char *value) } static void -ScanMethodHintDesc(ScanMethodHint *hint, StringInfo buf) +ScanMethodHintDesc(ScanMethodHint *hint, StringInfo buf, bool nolf) { ListCell *l; @@ -907,11 +1141,13 @@ ScanMethodHintDesc(ScanMethodHint *hint, StringInfo buf) quote_value(buf, (char *) lfirst(l)); } } - appendStringInfoString(buf, ")\n"); + appendStringInfoString(buf, ")"); + if (!nolf) + appendStringInfoChar(buf, '\n'); } static void -JoinMethodHintDesc(JoinMethodHint *hint, StringInfo buf) +JoinMethodHintDesc(JoinMethodHint *hint, StringInfo buf, bool nolf) { int i; @@ -925,8 +1161,9 @@ JoinMethodHintDesc(JoinMethodHint *hint, StringInfo buf) quote_value(buf, hint->relnames[i]); } } - appendStringInfoString(buf, ")\n"); - + appendStringInfoString(buf, ")"); + if (!nolf) + appendStringInfoChar(buf, '\n'); } static void @@ -957,7 +1194,7 @@ OuterInnerDesc(OuterInnerRels *outer_inner, StringInfo buf) } static void -LeadingHintDesc(LeadingHint *hint, StringInfo buf) +LeadingHintDesc(LeadingHint *hint, StringInfo buf, bool nolf) { appendStringInfo(buf, "%s(", HINT_LEADING); if (hint->outer_inner == NULL) @@ -980,11 +1217,13 @@ LeadingHintDesc(LeadingHint *hint, StringInfo buf) else OuterInnerDesc(hint->outer_inner, buf); - appendStringInfoString(buf, ")\n"); + appendStringInfoString(buf, ")"); + if (!nolf) + appendStringInfoChar(buf, '\n'); } static void -SetHintDesc(SetHint *hint, StringInfo buf) +SetHintDesc(SetHint *hint, StringInfo buf, bool nolf) { bool is_first = true; ListCell *l; @@ -999,11 +1238,13 @@ SetHintDesc(SetHint *hint, StringInfo buf) quote_value(buf, (char *) lfirst(l)); } - appendStringInfo(buf, ")\n"); + appendStringInfo(buf, ")"); + if (!nolf) + appendStringInfoChar(buf, '\n'); } static void -RowsHintDesc(RowsHint *hint, StringInfo buf) +RowsHintDesc(RowsHint *hint, StringInfo buf, bool nolf) { int i; @@ -1017,9 +1258,32 @@ RowsHintDesc(RowsHint *hint, StringInfo buf) quote_value(buf, hint->relnames[i]); } } - appendStringInfo(buf, " %s", hint->rows_str); - appendStringInfoString(buf, ")\n"); + if (hint->rows_str != NULL) + appendStringInfo(buf, " %s", hint->rows_str); + appendStringInfoString(buf, ")"); + if (!nolf) + appendStringInfoChar(buf, '\n'); +} + +static void +ParallelHintDesc(ParallelHint *hint, StringInfo buf, bool nolf) +{ + appendStringInfo(buf, "%s(", hint->base.keyword); + if (hint->relname != NULL) + { + quote_value(buf, hint->relname); + /* number of workers */ + appendStringInfoCharMacro(buf, ' '); + quote_value(buf, hint->nworkers_str); + /* application mode of num of workers */ + appendStringInfoCharMacro(buf, ' '); + appendStringInfoString(buf, + (hint->force_parallel ? "hard" : "soft")); + } + appendStringInfoString(buf, ")"); + if (!nolf) + appendStringInfoChar(buf, '\n'); } /* @@ -1028,18 +1292,26 @@ RowsHintDesc(RowsHint *hint, StringInfo buf) */ static void desc_hint_in_state(HintState *hstate, StringInfo buf, const char *title, - HintStatus state) + HintStatus state, bool nolf) { - int i; + int i, nshown; + + appendStringInfo(buf, "%s:", title); + if (!nolf) + appendStringInfoChar(buf, '\n'); - appendStringInfo(buf, "%s:\n", title); + nshown = 0; for (i = 0; i < hstate->nall_hints; i++) { if (hstate->all_hints[i]->state != state) continue; - hstate->all_hints[i]->desc_func(hstate->all_hints[i], buf); + hstate->all_hints[i]->desc_func(hstate->all_hints[i], buf, nolf); + nshown++; } + + if (nolf && nshown == 0) + appendStringInfoString(buf, "(none)"); } /* @@ -1052,19 +1324,48 @@ 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; } initStringInfo(&buf); appendStringInfoString(&buf, "pg_hint_plan:\n"); - desc_hint_in_state(hstate, &buf, "used hint", HINT_STATE_USED); - desc_hint_in_state(hstate, &buf, "not used hint", HINT_STATE_NOTUSED); - desc_hint_in_state(hstate, &buf, "duplication hint", HINT_STATE_DUPLICATION); - desc_hint_in_state(hstate, &buf, "error hint", HINT_STATE_ERROR); + desc_hint_in_state(hstate, &buf, "used hint", HINT_STATE_USED, false); + desc_hint_in_state(hstate, &buf, "not used hint", HINT_STATE_NOTUSED, false); + desc_hint_in_state(hstate, &buf, "duplication hint", HINT_STATE_DUPLICATION, false); + desc_hint_in_state(hstate, &buf, "error hint", HINT_STATE_ERROR, false); + + ereport(pg_hint_plan_debug_message_level, + (errmsg ("%s", buf.data))); + + pfree(buf.data); +} + +static void +HintStateDump2(HintState *hstate) +{ + StringInfoData buf; + + if (!hstate) + { + elog(pg_hint_plan_debug_message_level, + "pg_hint_plan%s: HintStateDump: no hint", qnostr); + return; + } - elog(LOG, "%s", buf.data); + initStringInfo(&buf); + appendStringInfo(&buf, "pg_hint_plan%s: HintStateDump: ", qnostr); + desc_hint_in_state(hstate, &buf, "{used hints", HINT_STATE_USED, true); + desc_hint_in_state(hstate, &buf, "}, {not used hints", HINT_STATE_NOTUSED, true); + desc_hint_in_state(hstate, &buf, "}, {duplicate hints", HINT_STATE_DUPLICATION, true); + desc_hint_in_state(hstate, &buf, "}, {error hints", HINT_STATE_ERROR, true); + appendStringInfoChar(&buf, '}'); + + ereport(pg_hint_plan_debug_message_level, + (errmsg("%s", buf.data), + errhidestmt(true), + errhidecontext(true))); pfree(buf.data); } @@ -1137,6 +1438,12 @@ RowsHintCmp(const RowsHint *a, const RowsHint *b) } static int +ParallelHintCmp(const ParallelHint *a, const ParallelHint *b) +{ + return RelnameCmp(&a->relname, &b->relname); +} + +static int HintCmp(const void *a, const void *b) { const Hint *hinta = *((const Hint **) a); @@ -1438,7 +1745,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); @@ -1507,13 +1814,21 @@ 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; PG_TRY(); { - ++nested_level; + bool snapshot_set = false; + + hint_inhibit_level++; + + if (!ActiveSnapshotSet()) + { + PushActiveSnapshot(GetTransactionSnapshot()); + snapshot_set = true; + } SPI_connect(); @@ -1550,12 +1865,15 @@ get_hints_from_table(const char *client_query, const char *client_application) } SPI_finish(); - - --nested_level; + + if (snapshot_set) + PopActiveSnapshot(); + + hint_inhibit_level--; } PG_CATCH(); { - --nested_level; + hint_inhibit_level--; PG_RE_THROW(); } PG_END_TRY(); @@ -1564,29 +1882,6 @@ get_hints_from_table(const char *client_query, const char *client_application) } /* - * Get client-supplied query string. - */ -static const char * -get_query_string(void) -{ - const char *p; - - if (stmt_name) - { - PreparedStatement *entry; - - entry = FetchPreparedStatement(stmt_name, true); - p = entry->plansource->query_string; - } - else if (plpgsql_query_string) - p = plpgsql_query_string; - else - p = debug_query_string; - - return p; -} - -/* * Get hints from the head block comment in client-supplied query string. */ static const char * @@ -1604,33 +1899,35 @@ get_hints_from_comment(const char *p) hint_head = strstr(p, HINT_START); if (hint_head == NULL) return NULL; - for (;p < hint_head; p++) + if (!pg_hint_plan_hints_anywhere) { - /* - * Allow these characters precedes hint comment: - * - digits - * - alphabets which are in ASCII range - * - space, tabs and new-lines - * - underscores, for identifier - * - commas, for SELECT clause, EXPLAIN and PREPARE - * - parentheses, for EXPLAIN and PREPARE - * - * Note that we don't use isalpha() nor isalnum() in ctype.h here to - * avoid behavior which depends on locale setting. - */ - if (!(*p >= '0' && *p <= '9') && - !(*p >= 'A' && *p <= 'Z') && - !(*p >= 'a' && *p <= 'z') && - !isspace(*p) && - *p != '_' && - *p != ',' && - *p != '(' && *p != ')') - return NULL; + for (;p < hint_head; p++) + { + /* + * Allow these characters precedes hint comment: + * - digits + * - alphabets which are in ASCII range + * - space, tabs and new-lines + * - underscores, for identifier + * - commas, for SELECT clause, EXPLAIN and PREPARE + * - parentheses, for EXPLAIN and PREPARE + * + * Note that we don't use isalpha() nor isalnum() in ctype.h here to + * avoid behavior which depends on locale setting. + */ + if (!(*p >= '0' && *p <= '9') && + !(*p >= 'A' && *p <= 'Z') && + !(*p >= 'a' && *p <= 'z') && + !isspace(*p) && + *p != '_' && + *p != ',' && + *p != '(' && *p != ')') + return NULL; + } } - len = strlen(HINT_START); - head = (char *) p; - p += len; + head = (char *)hint_head; + p = head + strlen(HINT_START); skip_space(p); /* find hint end keyword. */ @@ -1670,6 +1967,9 @@ create_hintstate(Query *parse, const char *hints) if (hints == NULL) return NULL; + /* -1 means that no Parallel hint is specified. */ + max_hint_nworkers = -1; + p = hints; hstate = HintStateCreate(); hstate->hint_str = (char *) hints; @@ -1737,6 +2037,10 @@ create_hintstate(Query *parse, const char *hints) hstate->num_hints[HINT_TYPE_LEADING]); hstate->rows_hints = (RowsHint **) (hstate->set_hints + hstate->num_hints[HINT_TYPE_SET]); + hstate->parallel_hints = (ParallelHint **) (hstate->rows_hints + + hstate->num_hints[HINT_TYPE_ROWS]); + hstate->memoize_hints = (JoinMethodHint **) (hstate->parallel_hints + + hstate->num_hints[HINT_TYPE_PARALLEL]); return hstate; } @@ -1758,31 +2062,24 @@ ScanMethodHintParse(ScanMethodHint *hint, HintState *hstate, Query *parse, /* Parse relation name and index name(s) if given hint accepts. */ length = list_length(name_list); - if (length > 0) - { - hint->relname = linitial(name_list); - hint->indexnames = list_delete_first(name_list); - - /* check whether the hint accepts index name(s). */ - if (length != 1 && - hint_keyword != HINT_KEYWORD_INDEXSCAN && - hint_keyword != HINT_KEYWORD_INDEXSCANREGEXP && - hint_keyword != HINT_KEYWORD_INDEXONLYSCAN && - hint_keyword != HINT_KEYWORD_INDEXONLYSCANREGEXP && - hint_keyword != HINT_KEYWORD_BITMAPSCAN && - hint_keyword != HINT_KEYWORD_BITMAPSCANREGEXP) - { - hint_ereport(str, - ("%s hint accepts only one relation.", - hint->base.keyword)); - hint->base.state = HINT_STATE_ERROR; - return str; - } + + /* at least twp parameters required */ + if (length < 1) + { + hint_ereport(str, + ("%s hint requires a relation.", hint->base.keyword)); + hint->base.state = HINT_STATE_ERROR; + return str; } - else + + hint->relname = linitial(name_list); + hint->indexnames = list_delete_first(name_list); + + /* check whether the hint accepts index name(s) */ + if (length > 1 && !SCAN_HINT_ACCEPTS_INDEX_NAMES(hint_keyword)) { hint_ereport(str, - ("%s hint requires a relation.", + ("%s hint accepts only one relation.", hint->base.keyword)); hint->base.state = HINT_STATE_ERROR; return str; @@ -1907,6 +2204,10 @@ JoinMethodHintParse(JoinMethodHint *hint, HintState *hstate, Query *parse, case HINT_KEYWORD_NOHASHJOIN: hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_HASHJOIN; break; + case HINT_KEYWORD_MEMOIZE: + case HINT_KEYWORD_NOMEMOIZE: + /* nothing to do here */ + break; default: hint_ereport(str, ("Unrecognized hint keyword \"%s\".", keyword)); return NULL; @@ -2036,6 +2337,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; @@ -2043,23 +2346,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 */ @@ -2119,50 +2427,204 @@ RowsHintParse(RowsHint *hint, HintState *hstate, Query *parse, return str; } -/* - * set GUC parameter functions - */ - -static int -set_config_option_wrapper(const char *name, const char *value, - GucContext context, GucSource source, - GucAction action, bool changeVal, int elevel) +static const char * +ParallelHintParse(ParallelHint *hint, HintState *hstate, Query *parse, + const char *str) { - int result = 0; - MemoryContext ccxt = CurrentMemoryContext; + HintKeyword hint_keyword = hint->base.hint_keyword; + List *name_list = NIL; + int length; + char *end_ptr; + int nworkers; + bool force_parallel = false; - PG_TRY(); - { - result = set_config_option(name, value, context, source, - action, changeVal, 0); - } - PG_CATCH(); - { - ErrorData *errdata; + if ((str = parse_parentheses(str, &name_list, hint_keyword)) == NULL) + return NULL; - /* Save error info */ - MemoryContextSwitchTo(ccxt); - errdata = CopyErrorData(); - FlushErrorState(); + /* Parse relation name and index name(s) if given hint accepts. */ + length = list_length(name_list); - ereport(elevel, (errcode(errdata->sqlerrcode), - errmsg("%s", errdata->message), - errdata->detail ? errdetail("%s", errdata->detail) : 0, - errdata->hint ? errhint("%s", errdata->hint) : 0)); - FreeErrorData(errdata); + if (length < 2 || length > 3) + { + hint_ereport(")", + ("wrong number of arguments (%d): %s", + length, hint->base.keyword)); + hint->base.state = HINT_STATE_ERROR; + return str; } - PG_END_TRY(); + + hint->relname = linitial(name_list); + + /* The second parameter is number of workers */ + hint->nworkers_str = list_nth(name_list, 1); + nworkers = strtod(hint->nworkers_str, &end_ptr); + if (*end_ptr || nworkers < 0 || nworkers > max_worker_processes) + { + if (*end_ptr) + hint_ereport(hint->nworkers_str, + ("number of workers must be a number: %s", + hint->base.keyword)); + else if (nworkers < 0) + hint_ereport(hint->nworkers_str, + ("number of workers must be greater than zero: %s", + hint->base.keyword)); + else if (nworkers > max_worker_processes) + hint_ereport(hint->nworkers_str, + ("number of workers = %d is larger than max_worker_processes(%d): %s", + nworkers, max_worker_processes, hint->base.keyword)); + + hint->base.state = HINT_STATE_ERROR; + } + + hint->nworkers = nworkers; + + /* optional third parameter is specified */ + if (length == 3) + { + const char *modeparam = (const char *)list_nth(name_list, 2); + if (pg_strcasecmp(modeparam, "hard") == 0) + force_parallel = true; + else if (pg_strcasecmp(modeparam, "soft") != 0) + { + hint_ereport(modeparam, + ("enforcement must be soft or hard: %s", + hint->base.keyword)); + hint->base.state = HINT_STATE_ERROR; + } + } + + hint->force_parallel = force_parallel; + + if (hint->base.state != HINT_STATE_ERROR && + nworkers > max_hint_nworkers) + max_hint_nworkers = nworkers; + + return str; +} + +/* + * set GUC parameter functions + */ + +static int +get_current_scan_mask() +{ + int mask = 0; + + if (enable_seqscan) + mask |= ENABLE_SEQSCAN; + if (enable_indexscan) + mask |= ENABLE_INDEXSCAN; + if (enable_bitmapscan) + mask |= ENABLE_BITMAPSCAN; + if (enable_tidscan) + mask |= ENABLE_TIDSCAN; + if (enable_indexonlyscan) + mask |= ENABLE_INDEXONLYSCAN; + + return mask; +} + +static int +get_current_join_mask() +{ + int mask = 0; + + if (enable_nestloop) + mask |= ENABLE_NESTLOOP; + if (enable_mergejoin) + mask |= ENABLE_MERGEJOIN; + if (enable_hashjoin) + mask |= ENABLE_HASHJOIN; + if (enable_memoize) + mask |= ENABLE_MEMOIZE; + + return mask; +} + +/* + * Sets GUC prameters without throwing exception. Reutrns false if something + * wrong. + */ +static int +set_config_option_noerror(const char *name, const char *value, + GucContext context, GucSource source, + GucAction action, bool changeVal, int elevel) +{ + int result = 0; + MemoryContext ccxt = CurrentMemoryContext; + + PG_TRY(); + { + result = set_config_option(name, value, context, source, + action, changeVal, 0, false); + } + PG_CATCH(); + { + ErrorData *errdata; + + /* Save error info */ + MemoryContextSwitchTo(ccxt); + errdata = CopyErrorData(); + FlushErrorState(); + + ereport(elevel, + (errcode(errdata->sqlerrcode), + errmsg("%s", errdata->message), + errdata->detail ? errdetail("%s", errdata->detail) : 0, + errdata->hint ? errhint("%s", errdata->hint) : 0)); + msgqno = qno; + FreeErrorData(errdata); + } + PG_END_TRY(); return result; } +/* + * Sets GUC parameter of int32 type without throwing exceptions. Returns false + * if something wrong. + */ static int -set_config_options(SetHint **options, int noptions, GucContext context) +set_config_int32_option(const char *name, int32 value, GucContext context) { - int i; - int save_nestlevel; + char buf[16]; /* enough for int32 */ - save_nestlevel = NewGUCNestLevel(); + if (snprintf(buf, 16, "%d", value) < 0) + { + 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_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 */ +static void +setup_guc_enforcement(SetHint **options, int noptions, GucContext context) +{ + int i; for (i = 0; i < noptions; i++) { @@ -2172,35 +2634,91 @@ set_config_options(SetHint **options, int noptions, GucContext context) if (!hint_state_enabled(hint)) continue; - result = set_config_option_wrapper(hint->name, hint->value, context, + result = set_config_option_noerror(hint->name, hint->value, context, PGC_S_SESSION, GUC_ACTION_SAVE, true, - pg_hint_plan_parse_messages); + pg_hint_plan_parse_message_level); if (result != 0) hint->base.state = HINT_STATE_USED; else hint->base.state = HINT_STATE_ERROR; } - return save_nestlevel; + return; +} + +/* + * Setup parallel execution environment. + * + * If hint is not NULL, set up using it, elsewise reset to initial environment. + */ +static void +setup_parallel_plan_enforcement(ParallelHint *hint, HintState *state) +{ + if (hint) + { + hint->base.state = HINT_STATE_USED; + set_config_int32_option("max_parallel_workers_per_gather", + hint->nworkers, state->context); + } + else + set_config_int32_option("max_parallel_workers_per_gather", + state->init_nworkers, state->context); + + /* force means that enforce parallel as far as possible */ + if (hint && hint->force_parallel && hint->nworkers > 0) + { + 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, + state->context); + } + else + { + set_config_double_option("parallel_tuple_cost", + state->init_paratup_cost, state->context); + 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, + state->context); + set_config_int32_option("min_parallel_index_scan_size", + state->init_min_para_indexscan_size, + state->context); + } } #define SET_CONFIG_OPTION(name, type_bits) \ - set_config_option_wrapper((name), \ + set_config_option_noerror((name), \ (mask & (type_bits)) ? "true" : "false", \ context, PGC_S_SESSION, GUC_ACTION_SAVE, true, ERROR) + +/* + * Setup GUC environment to enforce scan methods. If scanhint is NULL, reset + * GUCs to the saved state in state. + */ static void -set_scan_config_options(unsigned char enforce_mask, GucContext context) +setup_scan_method_enforcement(ScanMethodHint *scanhint, HintState *state) { + unsigned char enforce_mask = state->init_scan_mask; + GucContext context = state->context; unsigned char mask; + if (scanhint) + { + enforce_mask = scanhint->enforce_mask; + scanhint->base.state = HINT_STATE_USED; + } + if (enforce_mask == ENABLE_SEQSCAN || enforce_mask == ENABLE_INDEXSCAN || enforce_mask == ENABLE_BITMAPSCAN || enforce_mask == ENABLE_TIDSCAN || enforce_mask == (ENABLE_INDEXSCAN | ENABLE_INDEXONLYSCAN) ) mask = enforce_mask; else - mask = enforce_mask & current_hint->init_scan_mask; + mask = enforce_mask & current_hint_state->init_scan_mask; SET_CONFIG_OPTION("enable_seqscan", ENABLE_SEQSCAN); SET_CONFIG_OPTION("enable_indexscan", ENABLE_INDEXSCAN); @@ -2210,7 +2728,8 @@ set_scan_config_options(unsigned char enforce_mask, GucContext context) } static void -set_join_config_options(unsigned char enforce_mask, GucContext context) +set_join_config_options(unsigned char enforce_mask, bool set_memoize, + GucContext context) { unsigned char mask; @@ -2218,295 +2737,423 @@ set_join_config_options(unsigned char enforce_mask, GucContext context) enforce_mask == ENABLE_HASHJOIN) mask = enforce_mask; else - mask = enforce_mask & current_hint->init_join_mask; + mask = enforce_mask & current_hint_state->init_join_mask; SET_CONFIG_OPTION("enable_nestloop", ENABLE_NESTLOOP); SET_CONFIG_OPTION("enable_mergejoin", ENABLE_MERGEJOIN); SET_CONFIG_OPTION("enable_hashjoin", ENABLE_HASHJOIN); + + if (set_memoize) + SET_CONFIG_OPTION("enable_memoize", ENABLE_MEMOIZE); + + /* + * Hash join may be rejected for the reason of estimated memory usage. Try + * getting rid of that limitation. + */ + if (enforce_mask == ENABLE_HASHJOIN) + { + char buf[32]; + int new_multipler; + + /* See final_cost_hashjoin(). */ + new_multipler = MAX_KILOBYTES / work_mem; + + /* See guc.c for the upper limit */ + if (new_multipler >= 1000) + new_multipler = 1000; + + if (new_multipler > hash_mem_multiplier) + { + snprintf(buf, sizeof(buf), UINT64_FORMAT, (uint64)new_multipler); + set_config_option_noerror("hash_mem_multiplier", buf, + context, PGC_S_SESSION, GUC_ACTION_SAVE, + true, ERROR); + } + } } /* - * pg_hint_plan hook functions + * Push a hint into hint stack which is implemented with List struct. Head of + * list is top of stack. */ +static void +push_hint(HintState *hstate) +{ + /* Prepend new hint to the list means pushing to stack. */ + HintStateStack = lcons(hstate, HintStateStack); + + /* Pushed hint is the one which should be used hereafter. */ + current_hint_state = hstate; +} +/* Pop a hint from hint stack. Popped hint is automatically discarded. */ static void -pg_hint_plan_ProcessUtility(Node *parsetree, const char *queryString, - ProcessUtilityContext context, - ParamListInfo params, - DestReceiver *dest, char *completionTag) +pop_hint(void) { - Node *node; + /* Hint stack must not be empty. */ + if(HintStateStack == NIL) + elog(ERROR, "hint stack is empty"); - /* - * Use standard planner if pg_hint_plan is disabled or current nesting - * depth is nesting depth of SPI calls. + /* + * Take a hint at the head from the list, and free it. Switch + * current_hint_state to point new head (NULL if the list is empty). */ - if (!pg_hint_plan_enable_hint || nested_level > 0) - { - if (prev_ProcessUtility) - (*prev_ProcessUtility) (parsetree, queryString, - context, params, - dest, completionTag); - else - standard_ProcessUtility(parsetree, queryString, - context, params, - dest, completionTag); - return; - } + HintStateStack = list_delete_first(HintStateStack); + HintStateDelete(current_hint_state); + if(HintStateStack == NIL) + current_hint_state = NULL; + else + current_hint_state = (HintState *) lfirst(list_head(HintStateStack)); +} - node = parsetree; - if (IsA(node, ExplainStmt)) - { - /* - * Draw out parse tree of actual query from Query struct of EXPLAIN - * statement. - */ - ExplainStmt *stmt; - Query *query; +/* + * Retrieve and store hint string from given query or from the hint table. + */ +static void +get_current_hint_string(Query *query, const char *query_str, + JumbleState *jstate) +{ + MemoryContext oldcontext; - stmt = (ExplainStmt *) node; + /* We shouldn't get here for internal queries. */ + Assert (hint_inhibit_level == 0); - Assert(IsA(stmt->query, Query)); - query = (Query *) stmt->query; + /* We shouldn't get here if hint is disabled. */ + Assert (pg_hint_plan_enable_hint); - if (query->commandType == CMD_UTILITY && query->utilityStmt != NULL) - node = query->utilityStmt; - } + /* Do not anything if we have already tried to get hints for this query. */ + if (current_hint_retrieved) + return; - /* - * If the query was a EXECUTE or CREATE TABLE AS EXECUTE, get query string - * specified to preceding PREPARE command to use it as source of hints. - */ - if (IsA(node, ExecuteStmt)) - { - ExecuteStmt *stmt; + /* No way to retrieve hints from empty string. */ + if (!query_str) + return; - stmt = (ExecuteStmt *) node; - stmt_name = stmt->name; - } + /* Don't parse the current query hereafter */ + current_hint_retrieved = true; - /* - * CREATE AS EXECUTE behavior has changed since 9.2, so we must handle it - * specially here. - */ - if (IsA(node, CreateTableAsStmt)) + /* Make sure trashing old hint string */ + if (current_hint_str) { - CreateTableAsStmt *stmt; - Query *query; + pfree((void *)current_hint_str); + current_hint_str = NULL; + } + + /* increment the query number */ + qnostr[0] = 0; + if (debug_level > 1) + snprintf(qnostr, sizeof(qnostr), "[qno=0x%x]", qno++); + qno++; - stmt = (CreateTableAsStmt *) node; - Assert(IsA(stmt->query, Query)); - query = (Query *) stmt->query; + /* search the hint table for a hint if requested */ + if (pg_hint_plan_enable_hint_table) + { + int query_len; + char *normalized_query; - if (query->commandType == CMD_UTILITY && - IsA(query->utilityStmt, ExecuteStmt)) + if (!IsQueryIdEnabled()) { - ExecuteStmt *estmt = (ExecuteStmt *) query->utilityStmt; - stmt_name = estmt->name; + /* + * compute_query_id was turned off while enable_hint_table is + * on. Do not go ahead and complain once until it is turned on + * again. + */ + if (!hint_table_deactivated) + ereport(WARNING, + (errmsg ("hint table feature is deactivated because queryid is not available"), + errhint("Set compute_query_id to \"auto\" or \"on\" to use hint table."))); + + hint_table_deactivated = true; + return; } - } - if (stmt_name) - { - PG_TRY(); + if (hint_table_deactivated) { - if (prev_ProcessUtility) - (*prev_ProcessUtility) (parsetree, queryString, - context, params, - dest, completionTag); - else - standard_ProcessUtility(parsetree, queryString, - context, params, - dest, completionTag); + ereport(LOG, (errmsg ("hint table feature is reactivated"))); + hint_table_deactivated = false; } - PG_CATCH(); + + if (!jstate) + jstate = JumbleQuery(query, query_str); + + if (!jstate) + return; + + /* + * Normalize the query string by replacing constants with '?' + */ + /* + * Search hint string which is stored keyed by query string + * and application name. The query string is normalized to allow + * fuzzy matching. + * + * Adding 1 byte to query_len ensures that the returned string has + * a terminating NULL. + */ + query_len = strlen(query_str) + 1; + normalized_query = + generate_normalized_query(jstate, query_str, 0, &query_len); + + /* + * find a hint for the normalized query. the result should be in + * TopMemoryContext + */ + oldcontext = MemoryContextSwitchTo(TopMemoryContext); + current_hint_str = + get_hints_from_table(normalized_query, application_name); + MemoryContextSwitchTo(oldcontext); + + if (debug_level > 1) { - stmt_name = NULL; - PG_RE_THROW(); + if (current_hint_str) + ereport(pg_hint_plan_debug_message_level, + (errmsg("pg_hint_plan[qno=0x%x]: " + "hints from table: \"%s\": " + "normalized_query=\"%s\", " + "application name =\"%s\"", + qno, current_hint_str, + normalized_query, application_name), + errhidestmt(msgqno != qno), + errhidecontext(msgqno != qno))); + else + ereport(pg_hint_plan_debug_message_level, + (errmsg("pg_hint_plan[qno=0x%x]: " + "no match found in table: " + "application name = \"%s\", " + "normalized_query=\"%s\"", + qno, application_name, + normalized_query), + errhidestmt(msgqno != qno), + errhidecontext(msgqno != qno))); + + msgqno = qno; } - PG_END_TRY(); - - stmt_name = NULL; - return; + /* retrun if we have hint string here */ + if (current_hint_str) + return; } - if (prev_ProcessUtility) - (*prev_ProcessUtility) (parsetree, queryString, - context, params, - dest, completionTag); + /* get hints from the comment */ + oldcontext = MemoryContextSwitchTo(TopMemoryContext); + current_hint_str = get_hints_from_comment(query_str); + MemoryContextSwitchTo(oldcontext); + + if (debug_level > 1) + { + if (debug_level == 2 && 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 - standard_ProcessUtility(parsetree, queryString, - context, params, - dest, completionTag); + 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)", + query_str ? query_str : "(none)", + debug_query_string ? debug_query_string : "(none)"), + errhidestmt(msgqno != qno), + errhidecontext(msgqno != qno))); + msgqno = qno; + } } /* - * Push a hint into hint stack which is implemented with List struct. Head of - * list is top of stack. + * Retrieve hint string from the current query. */ static void -push_hint(HintState *hstate) +pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query, + JumbleState *jstate) { - /* Prepend new hint to the list means pushing to stack. */ - HintStateStack = lcons(hstate, HintStateStack); + if (prev_post_parse_analyze_hook) + prev_post_parse_analyze_hook(pstate, query, jstate); - /* Pushed hint is the one which should be used hereafter. */ - current_hint = hstate; -} + if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0) + return; -/* Pop a hint from hint stack. Popped hint is automatically discarded. */ -static void -pop_hint(void) -{ - /* Hint stack must not be empty. */ - if(HintStateStack == NIL) - elog(ERROR, "hint stack is empty"); + /* always retrieve hint from the top-level query string */ + if (plpgsql_recurse_level == 0) + current_hint_retrieved = false; /* - * Take a hint at the head from the list, and free it. Switch current_hint - * to point new head (NULL if the list is empty). + * Jumble state is required when hint table is used. This is the only + * chance to have one already generated in-core. If it's not the case, no + * use to do the work now and pg_hint_plan_planner() will do the all work. */ - HintStateStack = list_delete_first(HintStateStack); - HintStateDelete(current_hint); - if(HintStateStack == NIL) - current_hint = NULL; - else - current_hint = (HintState *) lfirst(list_head(HintStateStack)); + if (jstate) + get_current_hint_string(query, pstate->p_sourcetext, jstate); } +/* + * Read and set up hint information + */ static PlannedStmt * -pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) +pg_hint_plan_planner(Query *parse, const char *query_string, int cursorOptions, ParamListInfo boundParams) { - const char *hints = NULL; - const char *query; - char *norm_query; - pgssJumbleState jstate; - int query_len; 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 * depth is nesting depth of SPI calls. Other hook functions try to change - * plan with current_hint if any, so set it to NULL. + * plan with current_hint_state if any, so set it to NULL. */ - if (!pg_hint_plan_enable_hint || nested_level > 0) - goto standard_planner_proc; + if (!pg_hint_plan_enable_hint || hint_inhibit_level > 0) + { + if (debug_level > 1) + 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, + hint_inhibit_level), + errhidestmt(msgqno != qno))); + msgqno = qno; - /* Create hint struct from client-supplied query string. */ - query = get_query_string(); + goto standard_planner_proc; + } /* - * Create hintstate from hint specified for the query, if any. - * - * First we lookup hint in pg_hint.hints table by normalized query string, - * unless pg_hint_plan.enable_hint_table is OFF. - * This parameter provides option to avoid overhead of table lookup during - * planning. - * - * If no hint was found, then we try to get hint from special query comment. + * SQL commands invoked in plpgsql functions may also have hints. In that + * case override the upper level hint by the new hint. */ - if (pg_hint_plan_enable_hint_table) + if (plpgsql_recurse_level > 0) { - /* - * Search hint information which is stored for the query and the - * application. Query string is normalized before using in condition - * in order to allow fuzzy matching. - * - * XXX: normalizing code is copied from pg_stat_statements.c, so be - * careful when supporting PostgreSQL's version up. - */ - jstate.jumble = (unsigned char *) palloc(JUMBLE_SIZE); - jstate.jumble_len = 0; - jstate.clocations_buf_size = 32; - jstate.clocations = (pgssLocationLen *) - palloc(jstate.clocations_buf_size * sizeof(pgssLocationLen)); - jstate.clocations_count = 0; - JumbleQuery(&jstate, parse); - /* - * generate_normalized_query() copies exact given query_len bytes, so we - * add 1 byte for null-termination here. As comments on - * generate_normalized_query says, generate_normalized_query doesn't - * take care of null-terminate, but additional 1 byte ensures that '\0' - * byte in the source buffer to be copied into norm_query. - */ - query_len = strlen(query) + 1; - norm_query = generate_normalized_query(&jstate, - query, - &query_len, - GetDatabaseEncoding()); - hints = get_hints_from_table(norm_query, application_name); - elog(DEBUG1, - "pg_hint_plan: get_hints_from_table [%s][%s]=>[%s]", - norm_query, application_name, hints ? hints : "(none)"); + const char *tmp_hint_str = current_hint_str; + + /* don't let get_current_hint_string free this string */ + current_hint_str = NULL; + + current_hint_retrieved = false; + + get_current_hint_string(parse, query_string, NULL); + + if (current_hint_str == NULL) + current_hint_str = tmp_hint_str; + else if (tmp_hint_str != NULL) + pfree((void *)tmp_hint_str); } - if (hints == NULL) - hints = get_hints_from_comment(query); - hstate = create_hintstate(parse, hints); + else + get_current_hint_string(parse, query_string, NULL); - /* - * Use standard planner if the statement has not valid hint. Other hook - * functions try to change plan with current_hint if any, so set it to - * NULL. - */ - if (!hstate) + /* No hints, go the normal way */ + if (!current_hint_str) goto standard_planner_proc; + /* parse the hint into hint state struct */ + hstate = create_hintstate(parse, pstrdup(current_hint_str)); + + /* run standard planner if we're given with no valid hints */ + if (!hstate) + { + /* forget invalid hint string */ + if (current_hint_str) + { + pfree((void *)current_hint_str); + current_hint_str = NULL; + } + + goto standard_planner_proc; + } + /* * Push new hint struct to the hint stack to disable previous hint context. */ push_hint(hstate); - /* Set GUC parameters which are specified with Set hint. */ - save_nestlevel = set_config_options(current_hint->set_hints, - current_hint->num_hints[HINT_TYPE_SET], - current_hint->context); + /* Set scan enforcement here. */ + save_nestlevel = NewGUCNestLevel(); - if (enable_seqscan) - current_hint->init_scan_mask |= ENABLE_SEQSCAN; - if (enable_indexscan) - current_hint->init_scan_mask |= ENABLE_INDEXSCAN; - if (enable_bitmapscan) - current_hint->init_scan_mask |= ENABLE_BITMAPSCAN; - if (enable_tidscan) - current_hint->init_scan_mask |= ENABLE_TIDSCAN; - if (enable_indexonlyscan) - current_hint->init_scan_mask |= ENABLE_INDEXONLYSCAN; - if (enable_nestloop) - current_hint->init_join_mask |= ENABLE_NESTLOOP; - if (enable_mergejoin) - current_hint->init_join_mask |= ENABLE_MERGEJOIN; - if (enable_hashjoin) - current_hint->init_join_mask |= ENABLE_HASHJOIN; + /* Apply Set hints, then save it as the initial state */ + setup_guc_enforcement(current_hint_state->set_hints, + current_hint_state->num_hints[HINT_TYPE_SET], + current_hint_state->context); + + current_hint_state->init_scan_mask = get_current_scan_mask(); + current_hint_state->init_join_mask = get_current_join_mask(); + current_hint_state->init_min_para_tablescan_size = + min_parallel_table_scan_size; + current_hint_state->init_min_para_indexscan_size = + min_parallel_index_scan_size; + current_hint_state->init_paratup_cost = parallel_tuple_cost; + current_hint_state->init_parasetup_cost = parallel_setup_cost; /* - * Use PG_TRY mechanism to recover GUC parameters and current_hint to the - * state when this planner started when error occurred in planner. + * max_parallel_workers_per_gather should be non-zero here if Workers hint + * is specified. + */ + if (max_hint_nworkers > 0 && max_parallel_workers_per_gather < 1) + set_config_int32_option("max_parallel_workers_per_gather", + 1, current_hint_state->context); + current_hint_state->init_nworkers = max_parallel_workers_per_gather; + + if (debug_level > 1) + { + 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. */ PG_TRY(); { if (prev_planner) - result = (*prev_planner) (parse, cursorOptions, boundParams); + result = (*prev_planner) (parse, query_string, + cursorOptions, boundParams); else - result = standard_planner(parse, cursorOptions, boundParams); + result = standard_planner(parse, query_string, + cursorOptions, boundParams); + + current_hint_str = prev_hint_str; + recurse_level--; } PG_CATCH(); { /* * Rollback changes of GUC parameters, and pop current hint context - * from hint stack to rewind the state. + * from hint stack to rewind the state. current_hint_str will be freed + * by context deletion. */ + 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. + * There's a case where the caller has multiple queries. This causes hint + * parsing multiple times for the same string but we don't have a simple + * and reliable way to distinguish that case from the case where of + * separate queries. + */ + if (recurse_level < 1) + current_hint_retrieved = false; + /* Print hint in debug mode. */ - if (pg_hint_plan_debug_print) - HintStateDump(current_hint); + if (debug_level == 1) + HintStateDump(current_hint_state); + else if (debug_level > 1) + HintStateDump2(current_hint_state); /* * Rollback changes of GUC parameters, and pop current hint context from @@ -2518,50 +3165,171 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) return result; standard_planner_proc: - current_hint = NULL; + if (debug_level > 1) + { + ereport(pg_hint_plan_debug_message_level, + (errhidestmt(msgqno != qno), + errmsg("pg_hint_plan%s: planner: no valid hint", + qnostr))); + msgqno = qno; + } + current_hint_state = NULL; if (prev_planner) - return (*prev_planner) (parse, cursorOptions, boundParams); + result = (*prev_planner) (parse, query_string, + cursorOptions, boundParams); else - return standard_planner(parse, cursorOptions, boundParams); + result = standard_planner(parse, query_string, + 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; } /* - * Return scan method hint which matches given aliasname. + * Find scan method hint to be applied to the given relation + * */ static ScanMethodHint * -find_scan_hint(PlannerInfo *root, Index relid, RelOptInfo *rel) +find_scan_hint(PlannerInfo *root, Index relid) { + RelOptInfo *rel; RangeTblEntry *rte; + ScanMethodHint *real_name_hint = NULL; + ScanMethodHint *alias_hint = NULL; int i; + /* This should not be a join rel */ + Assert(relid > 0); + rel = root->simple_rel_array[relid]; + /* - * We can't apply scan method hint if the relation is: - * - not a base relation - * - not an ordinary relation (such as join and subquery) + * This function is called for any RelOptInfo or its inheritance parent if + * any. If we are called from inheritance planner, the RelOptInfo for the + * parent of target child relation is not set in the planner info. + * + * Otherwise we should check that the reloptinfo is base relation or + * inheritance children. */ - if (rel && (rel->reloptkind != RELOPT_BASEREL || rel->rtekind != RTE_RELATION)) + if (rel && + rel->reloptkind != RELOPT_BASEREL && + rel->reloptkind != RELOPT_OTHER_MEMBER_REL) return NULL; + /* + * This is baserel or appendrel children. We can refer to RangeTblEntry. + */ rte = root->simple_rte_array[relid]; + Assert(rte); - /* We can't force scan method of foreign tables */ - if (rte->relkind == RELKIND_FOREIGN_TABLE) + /* We don't hint on other than relation and foreign tables */ + if (rte->rtekind != RTE_RELATION || + rte->relkind == RELKIND_FOREIGN_TABLE) return NULL; /* Find scan method hint, which matches given names, from the list. */ - for (i = 0; i < current_hint->num_hints[HINT_TYPE_SCAN_METHOD]; i++) + for (i = 0; i < current_hint_state->num_hints[HINT_TYPE_SCAN_METHOD]; i++) { - ScanMethodHint *hint = current_hint->scan_hints[i]; + ScanMethodHint *hint = current_hint_state->scan_hints[i]; /* We ignore disabled hints. */ if (!hint_state_enabled(hint)) continue; - if (RelnameCmp(&rte->eref->aliasname, &hint->relname) == 0) - return hint; + if (!alias_hint && + RelnameCmp(&rte->eref->aliasname, &hint->relname) == 0) + alias_hint = hint; + + /* check the real name for appendrel children */ + if (!real_name_hint && + rel && rel->reloptkind == RELOPT_OTHER_MEMBER_REL) + { + char *realname = get_rel_name(rte->relid); + + if (realname && RelnameCmp(&realname, &hint->relname) == 0) + real_name_hint = hint; + } + + /* No more match expected, break */ + if(alias_hint && real_name_hint) + break; } - return NULL; + /* real name match precedes alias match */ + if (real_name_hint) + return real_name_hint; + + return alias_hint; +} + +static ParallelHint * +find_parallel_hint(PlannerInfo *root, Index relid) +{ + RelOptInfo *rel; + RangeTblEntry *rte; + ParallelHint *real_name_hint = NULL; + ParallelHint *alias_hint = NULL; + int i; + + /* This should not be a join rel */ + Assert(relid > 0); + rel = root->simple_rel_array[relid]; + + /* + * Parallel planning is appliable only on base relation, which has + * RelOptInfo. + */ + if (!rel) + return NULL; + + /* + * We have set root->glob->parallelModeOK if needed. What we should do here + * is just following the decision of planner. + */ + if (!rel->consider_parallel) + return NULL; + + /* + * This is baserel or appendrel children. We can refer to RangeTblEntry. + */ + rte = root->simple_rte_array[relid]; + Assert(rte); + + /* Find parallel method hint, which matches given names, from the list. */ + for (i = 0; i < current_hint_state->num_hints[HINT_TYPE_PARALLEL]; i++) + { + ParallelHint *hint = current_hint_state->parallel_hints[i]; + + /* We ignore disabled hints. */ + if (!hint_state_enabled(hint)) + continue; + + if (!alias_hint && + RelnameCmp(&rte->eref->aliasname, &hint->relname) == 0) + alias_hint = hint; + + /* check the real name for appendrel children */ + if (!real_name_hint && + rel && rel->reloptkind == RELOPT_OTHER_MEMBER_REL) + { + char *realname = get_rel_name(rte->relid); + + if (realname && RelnameCmp(&realname, &hint->relname) == 0) + real_name_hint = hint; + } + + /* No more match expected, break */ + if(alias_hint && real_name_hint) + break; + } + + /* real name match precedes alias match */ + if (real_name_hint) + return real_name_hint; + + return alias_hint; } /* @@ -2591,13 +3359,16 @@ regexpeq(const char *s1, const char *s2) return DatumGetBool(result); } + +/* Remove indexes instructed not to use by hint. */ static void -delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId) +restrict_indexes(PlannerInfo *root, ScanMethodHint *hint, RelOptInfo *rel, + bool using_parent_hint) { ListCell *cell; - ListCell *prev; - ListCell *next; StringInfoData buf; + RangeTblEntry *rte = root->simple_rte_array[rel->relid]; + Oid relationObjectId = rte->relid; /* * We delete all the IndexOptInfo list and prevent you from being usable by @@ -2623,19 +3394,16 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId) * Leaving only an specified index, we delete it from a IndexOptInfo list * other than it. */ - prev = NULL; - if (pg_hint_plan_debug_print) + if (debug_level > 0) initStringInfo(&buf); - for (cell = list_head(rel->indexlist); cell; cell = next) + foreach (cell, rel->indexlist) { IndexOptInfo *info = (IndexOptInfo *) lfirst(cell); char *indexname = get_rel_name(info->indexoid); ListCell *l; bool use_index = false; - next = lnext(cell); - foreach(l, hint->indexnames) { char *hintname = (char *) lfirst(l); @@ -2649,7 +3417,7 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId) if (result) { use_index = true; - if (pg_hint_plan_debug_print) + if (debug_level > 0) { appendStringInfoCharMacro(&buf, ' '); quote_value(&buf, indexname); @@ -2660,84 +3428,75 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId) } /* - * to make the index a candidate when definition of this index is - * matched with the index's definition of current_hint. + * Apply index restriction of parent hint to children. Since index + * inheritance is not explicitly described we should search for an + * children's index with the same definition to that of the parent. */ - if (OidIsValid(relationObjectId) && !use_index) + if (using_parent_hint && !use_index) { - foreach(l, current_hint->parent_index_infos) + foreach(l, current_hint_state->parent_index_infos) { int i; HeapTuple ht_idx; ParentIndexInfo *p_info = (ParentIndexInfo *)lfirst(l); - /* check to match the parameter of unique */ - if (p_info->indisunique != info->unique) - continue; - - /* check to match the parameter of index's method */ - if (p_info->method != info->relam) - continue; - - /* to check to match the indexkey's configuration */ - if ((list_length(p_info->column_names)) != - info->ncolumns) + /* + * we check the 'same' index by comparing uniqueness, access + * method and index key columns. + */ + if (p_info->indisunique != info->unique || + p_info->method != info->relam || + list_length(p_info->column_names) != info->ncolumns) continue; - /* check to match the indexkey's configuration */ + /* Check if index key columns match */ for (i = 0; i < info->ncolumns; i++) { char *c_attname = NULL; char *p_attname = NULL; - p_attname = - list_nth(p_info->column_names, i); + p_attname = list_nth(p_info->column_names, i); - /* both are expressions */ + /* + * if both of the key of the same position are expressions, + * ignore them for now and check later. + */ if (info->indexkeys[i] == 0 && !p_attname) continue; - /* one's column is expression, the other is not */ + /* deny if one is expression while another is not */ if (info->indexkeys[i] == 0 || !p_attname) break; c_attname = get_attname(relationObjectId, - info->indexkeys[i]); - - if (strcmp(p_attname, c_attname) != 0) - break; - - if (p_info->indcollation[i] != info->indexcollations[i]) - break; - - if (p_info->opclass[i] != info->opcintype[i]) - break; - - if (((p_info->indoption[i] & INDOPTION_DESC) != 0) != - info->reverse_sort[i]) + info->indexkeys[i], false); + + /* deny if any of column attributes don't match */ + if (strcmp(p_attname, c_attname) != 0 || + p_info->indcollation[i] != info->indexcollations[i] || + p_info->opclass[i] != info->opcintype[i]|| + ((p_info->indoption[i] & INDOPTION_DESC) != 0) + != info->reverse_sort[i] || + ((p_info->indoption[i] & INDOPTION_NULLS_FIRST) != 0) + != info->nulls_first[i]) break; - - if (((p_info->indoption[i] & INDOPTION_NULLS_FIRST) != 0) != - info->nulls_first[i]) - break; - } + /* deny this if any difference found */ if (i != info->ncolumns) continue; + /* check on key expressions */ if ((p_info->expression_str && (info->indexprs != NIL)) || (p_info->indpred_str && (info->indpred != NIL))) { - /* - * Fetch the pg_index tuple by the Oid of the index - */ + /* fetch the index of this child */ ht_idx = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(info->indexoid)); - /* check to match the expression's parameter of index */ + /* 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; @@ -2756,28 +3515,24 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId) ObjectIdGetDatum( relationObjectId)); + /* deny if expressions don't match */ if (strcmp(p_info->expression_str, text_to_cstring(DatumGetTextP(result))) != 0) { /* Clean up */ ReleaseSysCache(ht_idx); - continue; } } - /* Check to match the predicate's parameter of index */ + /* 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; Datum result; - /* - * to change the predicate's parameter of child's - * index to strings - */ predDatum = SysCacheGetAttr(INDEXRELID, ht_idx, Anum_pg_index_indpred, &isnull); @@ -2792,7 +3547,6 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId) { /* Clean up */ ReleaseSysCache(ht_idx); - continue; } } @@ -2808,7 +3562,7 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId) use_index = true; /* to log the candidate of index */ - if (pg_hint_plan_debug_print) + if (debug_level > 0) { appendStringInfoCharMacro(&buf, ' '); quote_value(&buf, indexname); @@ -2819,27 +3573,30 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId) } if (!use_index) - rel->indexlist = list_delete_cell(rel->indexlist, cell, prev); - else - prev = cell; - + rel->indexlist = foreach_delete_current(rel->indexlist, cell); + pfree(indexname); } - if (pg_hint_plan_debug_print) + if (debug_level > 0) { - char *relname; StringInfoData rel_buf; + char *disprelname = ""; - if (OidIsValid(relationObjectId)) - relname = get_rel_name(relationObjectId); + /* + * If this hint targetted the parent, use the real name of this + * child. Otherwise use hint specification. + */ + if (using_parent_hint) + disprelname = get_rel_name(rte->relid); else - relname = hint->relname; + disprelname = hint->relname; + initStringInfo(&rel_buf); - quote_value(&rel_buf, relname); + 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, @@ -2873,9 +3630,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]; @@ -2887,7 +3649,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; @@ -2907,7 +3670,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; @@ -2928,88 +3692,117 @@ get_parent_index_info(Oid indexoid, Oid relid) return p_info; } +/* + * cancel hint enforcement + */ static void -pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId, - bool inhparent, RelOptInfo *rel) +reset_hint_enforcement() { - ScanMethodHint *hint = NULL; - ListCell *l; - Index new_parent_relid = 0; + setup_scan_method_enforcement(NULL, current_hint_state); + setup_parallel_plan_enforcement(NULL, current_hint_state); +} - if (prev_get_relation_info) - (*prev_get_relation_info) (root, relationObjectId, inhparent, rel); +/* + * Set planner guc parameters according to corresponding scan hints. Returns + * bitmap of HintTypeBitmap. If shint or phint is not NULL, set used hint + * there respectively. + */ +static int +setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel, + ScanMethodHint **rshint, ParallelHint **rphint) +{ + Index new_parent_relid = 0; + ListCell *l; + ScanMethodHint *shint = NULL; + ParallelHint *phint = NULL; + bool inhparent = root->simple_rte_array[rel->relid]->inh; + Oid relationObjectId = root->simple_rte_array[rel->relid]->relid; + int ret = 0; - /* - * Do nothing if we don't have a valid hint in this context or current - * nesting depth is at SPI calls. - */ - if (!current_hint || nested_level > 0) - return; + /* reset returns if requested */ + if (rshint != NULL) *rshint = NULL; + if (rphint != NULL) *rphint = NULL; /* * We could register the parent relation of the following children here - * when inhparent == true but inheritnce planner doesn't request - * information for inheritance parents. We also cannot distinguish the - * caller so we should always find the parents without this function being - * called for them. + * when inhparent == true but inheritnce planner doesn't call this function + * for parents. Since we cannot distinguish who called this function we + * cannot do other than always seeking the parent regardless of who called + * this function. */ if (inhparent) - return; - - /* Find the parent for this relation */ - foreach (l, root->append_rel_list) { - AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l); - - if (appinfo->child_relid == rel->relid) + /* set up only parallel hints for parent relation */ + phint = find_parallel_hint(root, rel->relid); + if (phint) { - if (current_hint->parent_relid != appinfo->parent_relid) - new_parent_relid = appinfo->parent_relid; - break; + 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_debug_message_level, + (errhidestmt(true), + errmsg ("pg_hint_plan%s: setup_hint_enforcement" + " skipping inh parent: relation=%u(%s), inhparent=%d," + " current_hint_state=%p, hint_inhibit_level=%d", + qnostr, relationObjectId, + get_rel_name(relationObjectId), + inhparent, current_hint_state, hint_inhibit_level))); + return 0; } - if (!l) + if (bms_num_members(rel->top_parent_relids) == 1) + { + new_parent_relid = bms_next_member(rel->top_parent_relids, -1); + current_hint_state->current_root = root; + Assert(new_parent_relid > 0); + } + else { - /* This relation doesn't have a parent. Cancel current_hint. */ - current_hint->parent_relid = 0; - current_hint->parent_hint = NULL; + /* 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 parent relation different from the remembered one. - * Remember it, apply the scan mask of it and then resolve the index - * restriction in order to be used by its children. + * Here we found a new parent for the current relation. Scan continues + * hint to other childrens of this parent so remember it to avoid + * redundant setup cost. */ - int scanmask = current_hint->init_scan_mask; - ScanMethodHint *parent_hint; - - current_hint->parent_relid = new_parent_relid; + current_hint_state->parent_relid = new_parent_relid; + /* Find hints for the parent */ + current_hint_state->parent_scan_hint = + find_scan_hint(root, current_hint_state->parent_relid); + + current_hint_state->parent_parallel_hint = + find_parallel_hint(root, current_hint_state->parent_relid); + /* - * Get and apply the hint for the new parent relation. It should be an - * ordinary relation so calling find_scan_hint with rel == NULL is - * safe. + * If hint is found for the parent, apply it for this child instead + * of its own. */ - current_hint->parent_hint = parent_hint = - find_scan_hint(root, current_hint->parent_relid, NULL); - - if (parent_hint) + if (current_hint_state->parent_scan_hint) { - scanmask = current_hint->parent_hint->enforce_mask; - parent_hint->base.state = HINT_STATE_USED; + ScanMethodHint * pshint = current_hint_state->parent_scan_hint; + + pshint->base.state = HINT_STATE_USED; - /* Resolve index name mask (if any) using the parent. */ - if (parent_hint->indexnames) + /* Apply index mask in the same manner to the parent. */ + if (pshint->indexnames) { Oid parentrel_oid; Relation parent_rel; parentrel_oid = - root->simple_rte_array[current_hint->parent_relid]->relid; - parent_rel = heap_open(parentrel_oid, NoLock); + root->simple_rte_array[current_hint_state->parent_relid]->relid; + parent_rel = table_open(parentrel_oid, NoLock); /* Search the parent relation for indexes match the hint spec */ foreach(l, RelationGetIndexList(parent_rel)) @@ -3019,7 +3812,7 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId, ListCell *lc; ParentIndexInfo *parent_index_info; - foreach(lc, parent_hint->indexnames) + foreach(lc, pshint->indexnames) { if (RelnameCmp(&indexname, &lfirst(lc)) == 0) break; @@ -3029,37 +3822,89 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId, parent_index_info = get_parent_index_info(indexoid, parentrel_oid); - current_hint->parent_index_infos = - lappend(current_hint->parent_index_infos, parent_index_info); + current_hint_state->parent_index_infos = + lappend(current_hint_state->parent_index_infos, + parent_index_info); } - heap_close(parent_rel, NoLock); + table_close(parent_rel, NoLock); } } - - set_scan_config_options(scanmask, current_hint->context); } - if (current_hint->parent_hint != 0) - { - delete_indexes(current_hint->parent_hint, rel, - relationObjectId); + shint = find_scan_hint(root, rel->relid); + if (!shint) + shint = current_hint_state->parent_scan_hint; + + if (shint) + { + bool using_parent_hint = + (shint == current_hint_state->parent_scan_hint); + + ret |= HINT_BM_SCAN_METHOD; + + /* Setup scan enforcement environment */ + setup_scan_method_enforcement(shint, current_hint_state); + + /* restrict unwanted inexes */ + restrict_indexes(root, shint, rel, using_parent_hint); + + if (debug_level > 1) + { + char *additional_message = ""; + + if (shint == current_hint_state->parent_scan_hint) + additional_message = " by parent hint"; + + ereport(pg_hint_plan_debug_message_level, + (errhidestmt(true), + errmsg ("pg_hint_plan%s: setup_hint_enforcement" + " index deletion%s:" + " relation=%u(%s), inhparent=%d, " + "current_hint_state=%p," + " hint_inhibit_level=%d, scanmask=0x%x", + qnostr, additional_message, + relationObjectId, + get_rel_name(relationObjectId), + inhparent, current_hint_state, + hint_inhibit_level, + shint->enforce_mask))); + } + } + + /* Do the same for parallel plan enforcement */ + phint = find_parallel_hint(root, rel->relid); + if (!phint) + phint = current_hint_state->parent_parallel_hint; - /* Scan fixation status is the same to the parent. */ - return; - } + setup_parallel_plan_enforcement(phint, current_hint_state); - /* This table doesn't have a parent hint. Apply its own hint if any. */ - if ((hint = find_scan_hint(root, rel->relid, rel)) != NULL) - { - set_scan_config_options(hint->enforce_mask, current_hint->context); - hint->base.state = HINT_STATE_USED; + if (phint) + ret |= HINT_BM_PARALLEL; - delete_indexes(hint, rel, InvalidOid); + /* Nothing to apply. Reset the scan mask to intial state */ + if (!shint && ! phint) + { + if (debug_level > 1) + ereport(pg_hint_plan_debug_message_level, + (errhidestmt (true), + errmsg ("pg_hint_plan%s: setup_hint_enforcement" + " no hint applied:" + " relation=%u(%s), inhparent=%d, current_hint=%p," + " hint_inhibit_level=%d, scanmask=0x%x", + qnostr, relationObjectId, + get_rel_name(relationObjectId), + inhparent, current_hint_state, hint_inhibit_level, + current_hint_state->init_scan_mask))); + + setup_scan_method_enforcement(NULL, current_hint_state); + + return ret; } - else - set_scan_config_options(current_hint->init_scan_mask, - current_hint->context); - return; + + if (rshint != NULL) *rshint = shint; + if (rphint != NULL) *rphint = phint; + + return ret; } /* @@ -3129,7 +3974,30 @@ find_join_hint(Relids joinrelids) List *join_hint; ListCell *l; - join_hint = current_hint->join_hint_level[bms_num_members(joinrelids)]; + join_hint = current_hint_state->join_hint_level[bms_num_members(joinrelids)]; + + foreach(l, join_hint) + { + JoinMethodHint *hint = (JoinMethodHint *) lfirst(l); + + if (bms_equal(joinrelids, hint->joinrelids)) + return hint; + } + + return NULL; +} + + +/* + * Return memoize hint which matches given joinrelids. + */ +static JoinMethodHint * +find_memoize_hint(Relids joinrelids) +{ + List *join_hint; + ListCell *l; + + join_hint = current_hint_state->memoize_hint_level[bms_num_members(joinrelids)]; foreach(l, join_hint) { @@ -3142,6 +4010,7 @@ find_join_hint(Relids joinrelids) return NULL; } + static Relids OuterInnerJoinCreate(OuterInnerRels *outer_inner, LeadingHint *leading_hint, PlannerInfo *root, List *initial_rels, HintState *hstate, int nbaserel) @@ -3161,8 +4030,8 @@ OuterInnerJoinCreate(OuterInnerRels *outer_inner, LeadingHint *leading_hint, leading_hint->base.hint_str)); } - outer_rels = lfirst(outer_inner->outer_inner_pair->head); - inner_rels = lfirst(outer_inner->outer_inner_pair->tail); + outer_rels = linitial(outer_inner->outer_inner_pair); + inner_rels = llast(outer_inner->outer_inner_pair); outer_relids = OuterInnerJoinCreate(outer_rels, leading_hint, @@ -3297,6 +4166,24 @@ transform_join_hints(HintState *hstate, PlannerInfo *root, int nbaserel, lappend(hstate->join_hint_level[hint->nrels], hint); } + /* ditto for memoize hints */ + for (i = 0; i < hstate->num_hints[HINT_TYPE_MEMOIZE]; i++) + { + JoinMethodHint *hint = hstate->join_hints[i]; + + if (!hint_state_enabled(hint) || hint->nrels > nbaserel) + continue; + + hint->joinrelids = create_bms_of_relids(&(hint->base), root, + initial_rels, hint->nrels, hint->relnames); + + if (hint->joinrelids == NULL || hint->base.state == HINT_STATE_ERROR) + continue; + + hstate->memoize_hint_level[hint->nrels] = + lappend(hstate->memoize_hint_level[hint->nrels], hint); + } + /* * Create bitmap of relids from alias names for each rows hint. * Bitmaps are more handy than strings in join searching. @@ -3469,26 +4356,19 @@ transform_join_hints(HintState *hstate, PlannerInfo *root, int nbaserel, { if (hstate->join_hint_level[i] != NIL) { - ListCell *prev = NULL; - ListCell *next = NULL; - for(l = list_head(hstate->join_hint_level[i]); l; l = next) + foreach (l, hstate->join_hint_level[i]) { JoinMethodHint *hint = (JoinMethodHint *)lfirst(l); - next = lnext(l); - if (hint->inner_nrels == 0 && !(bms_intersect(hint->joinrelids, joinrelids) == NULL || bms_equal(bms_union(hint->joinrelids, joinrelids), hint->joinrelids))) { hstate->join_hint_level[i] = - list_delete_cell(hstate->join_hint_level[i], l, - prev); + foreach_delete_current(hstate->join_hint_level[i], l); } - else - prev = l; } } } @@ -3498,91 +4378,14 @@ transform_join_hints(HintState *hstate, PlannerInfo *root, int nbaserel, if (hint_state_enabled(lhint)) { - set_join_config_options(DISABLE_ALL_JOIN, current_hint->context); + set_join_config_options(DISABLE_ALL_JOIN, false, + current_hint_state->context); return true; } return false; } /* - * set_plain_rel_pathlist - * Build access paths for a plain relation (no subquery, no inheritance) - * - * This function was copied and edited from set_plain_rel_pathlist() in - * src/backend/optimizer/path/allpaths.c - */ -static void -set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) -{ - /* Consider sequential scan */ - add_path(rel, create_seqscan_path(root, rel, NULL)); - - /* Consider index scans */ - create_index_paths(root, rel); - - /* Consider TID scans */ - create_tidscan_paths(root, rel); - - /* Now find the cheapest of the paths for this rel */ - set_cheapest(rel); -} - -static void -rebuild_scan_path(HintState *hstate, PlannerInfo *root, int level, - List *initial_rels) -{ - ListCell *l; - - foreach(l, initial_rels) - { - RelOptInfo *rel = (RelOptInfo *) lfirst(l); - RangeTblEntry *rte; - ScanMethodHint *hint; - - /* Skip relations which we can't choose scan method. */ - if (rel->reloptkind != RELOPT_BASEREL || rel->rtekind != RTE_RELATION) - continue; - - rte = root->simple_rte_array[rel->relid]; - - /* We can't force scan method of foreign tables */ - if (rte->relkind == RELKIND_FOREIGN_TABLE) - continue; - - /* - * Create scan paths with GUC parameters which are at the beginning of - * planner if scan method hint is not specified, otherwise use - * specified hints and mark the hint as used. - */ - if ((hint = find_scan_hint(root, rel->relid, rel)) == NULL) - set_scan_config_options(hstate->init_scan_mask, - hstate->context); - else - { - set_scan_config_options(hint->enforce_mask, hstate->context); - hint->base.state = HINT_STATE_USED; - } - - list_free_deep(rel->pathlist); - rel->pathlist = NIL; - if (rte->inh) - { - /* It's an "append relation", process accordingly */ - set_append_rel_pathlist(root, rel, rel->relid, rte); - } - else - { - set_plain_rel_pathlist(root, rel, rte); - } - } - - /* - * Restore the GUC variables we set above. - */ - set_scan_config_options(hstate->init_scan_mask, hstate->context); -} - -/* * wrapper of make_join_rel() * * call make_join_rel() after changing enable_* parameters according to given @@ -3592,33 +4395,57 @@ static RelOptInfo * make_join_rel_wrapper(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) { Relids joinrelids; - JoinMethodHint *hint; + JoinMethodHint *join_hint; + JoinMethodHint *memoize_hint; RelOptInfo *rel; int save_nestlevel; joinrelids = bms_union(rel1->relids, rel2->relids); - hint = find_join_hint(joinrelids); + join_hint = find_join_hint(joinrelids); + memoize_hint = find_memoize_hint(joinrelids); bms_free(joinrelids); - if (!hint) - return pg_hint_plan_make_join_rel(root, rel1, rel2); + /* reject non-matching hints */ + if (join_hint && join_hint->inner_nrels != 0) + join_hint = NULL; + + if (memoize_hint && memoize_hint->inner_nrels != 0) + memoize_hint = NULL; - if (hint->inner_nrels == 0) + if (join_hint || memoize_hint) { save_nestlevel = NewGUCNestLevel(); - set_join_config_options(hint->enforce_mask, current_hint->context); + if (join_hint) + set_join_config_options(join_hint->enforce_mask, false, + current_hint_state->context); - rel = pg_hint_plan_make_join_rel(root, rel1, rel2); - hint->base.state = HINT_STATE_USED; + if (memoize_hint) + { + bool memoize = + memoize_hint->base.hint_keyword == HINT_KEYWORD_MEMOIZE; + set_config_option_noerror("enable_memoize", + memoize ? "true" : "false", + current_hint_state->context, + PGC_S_SESSION, GUC_ACTION_SAVE, + true, ERROR); + } + } + + /* do the work */ + rel = pg_hint_plan_make_join_rel(root, rel1, rel2); + + /* Restore the GUC variables we set above. */ + if (join_hint || memoize_hint) + { + if (join_hint) + join_hint->base.state = HINT_STATE_USED; + + if (memoize_hint) + memoize_hint->base.state = HINT_STATE_USED; - /* - * Restore the GUC variables we set above. - */ AtEOXact_GUC(true, save_nestlevel); } - else - rel = pg_hint_plan_make_join_rel(root, rel1, rel2); return rel; } @@ -3635,54 +4462,65 @@ add_paths_to_joinrel_wrapper(PlannerInfo *root, SpecialJoinInfo *sjinfo, List *restrictlist) { - ScanMethodHint *scan_hint = NULL; Relids joinrelids; JoinMethodHint *join_hint; + JoinMethodHint *memoize_hint; int save_nestlevel; - if ((scan_hint = find_scan_hint(root, innerrel->relid, innerrel)) != NULL) - { - set_scan_config_options(scan_hint->enforce_mask, current_hint->context); - scan_hint->base.state = HINT_STATE_USED; - } - joinrelids = bms_union(outerrel->relids, innerrel->relids); join_hint = find_join_hint(joinrelids); + memoize_hint = find_memoize_hint(joinrelids); bms_free(joinrelids); - if (join_hint && join_hint->inner_nrels != 0) + /* reject the found hints if they don't match this join */ + if (join_hint && join_hint->inner_nrels == 0) + join_hint = NULL; + + if (memoize_hint && memoize_hint->inner_nrels == 0) + memoize_hint = NULL; + + /* set up configuration if needed */ + if (join_hint || memoize_hint) { save_nestlevel = NewGUCNestLevel(); - if (bms_equal(join_hint->inner_joinrelids, innerrel->relids)) + if (join_hint) { - - set_join_config_options(join_hint->enforce_mask, - current_hint->context); - - add_paths_to_joinrel(root, joinrel, outerrel, innerrel, jointype, - sjinfo, restrictlist); - join_hint->base.state = HINT_STATE_USED; + if (bms_equal(join_hint->inner_joinrelids, innerrel->relids)) + set_join_config_options(join_hint->enforce_mask, false, + current_hint_state->context); + else + set_join_config_options(DISABLE_ALL_JOIN, false, + current_hint_state->context); } - else + + if (memoize_hint) { - set_join_config_options(DISABLE_ALL_JOIN, current_hint->context); - add_paths_to_joinrel(root, joinrel, outerrel, innerrel, jointype, - sjinfo, restrictlist); + bool memoize = + memoize_hint->base.hint_keyword == HINT_KEYWORD_MEMOIZE; + set_config_option_noerror("enable_memoize", + memoize ? "true" : "false", + current_hint_state->context, + PGC_S_SESSION, GUC_ACTION_SAVE, + true, ERROR); } + } + + /* generate paths */ + add_paths_to_joinrel(root, joinrel, outerrel, innerrel, jointype, + sjinfo, restrictlist); + + /* restore GUC variables */ + if (join_hint || memoize_hint) + { + if (join_hint) + join_hint->base.state = HINT_STATE_USED; + + if (memoize_hint) + memoize_hint->base.state = HINT_STATE_USED; - /* - * Restore the GUC variables we set above. - */ AtEOXact_GUC(true, save_nestlevel); } - else - add_paths_to_joinrel(root, joinrel, outerrel, innerrel, jointype, - sjinfo, restrictlist); - - if (scan_hint != NULL) - set_scan_config_options(current_hint->init_scan_mask, - current_hint->context); } static int @@ -3724,7 +4562,7 @@ pg_hint_plan_join_search(PlannerInfo *root, int levels_needed, * valid hint is supplied or current nesting depth is nesting depth of SPI * calls. */ - if (!current_hint || nested_level > 0) + if (!current_hint_state || hint_inhibit_level > 0) { if (prev_join_search) return (*prev_join_search) (root, levels_needed, initial_rels); @@ -3734,9 +4572,6 @@ pg_hint_plan_join_search(PlannerInfo *root, int levels_needed, return standard_join_search(root, levels_needed, initial_rels); } - /* We apply scan method hint rebuild scan path. */ - rebuild_scan_path(current_hint, root, levels_needed, initial_rels); - /* * In the case using GEQO, only scan method hints and Set hints have * effect. Join method and join order is not controllable by hints. @@ -3745,68 +4580,260 @@ pg_hint_plan_join_search(PlannerInfo *root, int levels_needed, return geqo(root, levels_needed, initial_rels); nbaserel = get_num_baserels(initial_rels); - current_hint->join_hint_level = palloc0(sizeof(List *) * (nbaserel + 1)); + current_hint_state->join_hint_level = + palloc0(sizeof(List *) * (nbaserel + 1)); join_method_hints = palloc0(sizeof(JoinMethodHint *) * (nbaserel + 1)); + current_hint_state->memoize_hint_level = + palloc0(sizeof(List *) * (nbaserel + 1)); - leading_hint_enable = transform_join_hints(current_hint, root, nbaserel, + leading_hint_enable = transform_join_hints(current_hint_state, + root, nbaserel, initial_rels, join_method_hints); 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->join_hint_level[i]); + list_free(current_hint_state->join_hint_level[i]); /* free Leading hint only */ if (join_method_hints[i] != NULL && join_method_hints[i]->enforce_mask == ENABLE_ALL_JOIN) JoinMethodHintDelete(join_method_hints[i]); } - pfree(current_hint->join_hint_level); + pfree(current_hint_state->join_hint_level); pfree(join_method_hints); if (leading_hint_enable) - set_join_config_options(current_hint->init_join_mask, - current_hint->context); + set_join_config_options(current_hint_state->init_join_mask, true, + current_hint_state->context); return rel; } /* - * set_rel_pathlist - * Build access paths for a base relation - * - * This function was copied and edited from set_rel_pathlist() in - * src/backend/optimizer/path/allpaths.c + * Force number of wokers if instructed by hint */ -static void -set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, - Index rti, RangeTblEntry *rte) +void +pg_hint_plan_set_rel_pathlist(PlannerInfo * root, RelOptInfo *rel, + Index rti, RangeTblEntry *rte) { + ParallelHint *phint; + ListCell *l; + int found_hints; + + /* call the previous hook */ + if (prev_set_rel_pathlist) + prev_set_rel_pathlist(root, rel, rti, rte); + + /* Nothing to do if no hint available */ + if (current_hint_state == NULL) + return; + + /* Don't touch dummy rels. */ if (IS_DUMMY_REL(rel)) + return; + + /* + * We can accept only plain relations, foreign tables and table saples are + * also unacceptable. See set_rel_pathlist. + */ + 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) { - /* We already proved the relation empty, so nothing more to do */ - } - else if (rte->inh) - { - /* It's an "append relation", process accordingly */ - set_append_rel_pathlist(root, rel, rti, rte); + 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; } - else + + /* We cannot handle if this requires an outer */ + if (rel->lateral_relids) + return; + + /* Return if this relation gets no enfocement */ + if ((found_hints = setup_hint_enforcement(root, rel, NULL, &phint)) == 0) + return; + + /* Here, we regenerate paths with the current hint restriction */ + if (found_hints & HINT_BM_SCAN_METHOD || found_hints & HINT_BM_PARALLEL) { - if (rel->rtekind == RTE_RELATION) + /* + * 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) { - if (rte->relkind == RELKIND_RELATION) + /* enforce number of workers if requested */ + if (phint && phint->force_parallel) { - /* Plain relation */ - set_plain_rel_pathlist(root, rel, rte); + 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); + + 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 - elog(ERROR, "unexpected relkind: %c", rte->relkind); } else - elog(ERROR, "unexpected rtekind: %d", (int) rel->rtekind); + { + /* 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); + + /* Additional work to enforce parallel query execution */ + if (phint && phint->nworkers > 0) + { + /* + * 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 *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) + { + Path *ppath = (Path *) lfirst(l); + + if (ppath->parallel_safe) + ppath->parallel_workers = phint->nworkers; + } + } + + /* Generate gather paths */ + if (rel->reloptkind == RELOPT_BASEREL && + bms_membership(root->all_baserels) != BMS_SINGLETON) + generate_gather_paths(root, rel, false); + } + } } + + reset_hint_enforcement(); } /* @@ -3820,45 +4847,7 @@ set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, static void pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt) { - PLpgSQL_expr *expr = NULL; - - switch ((enum PLpgSQL_stmt_types) stmt->cmd_type) - { - case PLPGSQL_STMT_FORS: - expr = ((PLpgSQL_stmt_fors *) stmt)->query; - break; - case PLPGSQL_STMT_FORC: - expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_forc *)stmt)->curvar]))->cursor_explicit_expr; - break; - case PLPGSQL_STMT_RETURN_QUERY: - if (((PLpgSQL_stmt_return_query *) stmt)->query != NULL) - expr = ((PLpgSQL_stmt_return_query *) stmt)->query; - else - expr = ((PLpgSQL_stmt_return_query *) stmt)->dynquery; - break; - case PLPGSQL_STMT_EXECSQL: - expr = ((PLpgSQL_stmt_execsql *) stmt)->sqlstmt; - break; - case PLPGSQL_STMT_DYNEXECUTE: - expr = ((PLpgSQL_stmt_dynexecute *) stmt)->query; - break; - case PLPGSQL_STMT_DYNFORS: - expr = ((PLpgSQL_stmt_dynfors *) stmt)->query; - break; - case PLPGSQL_STMT_OPEN: - if (((PLpgSQL_stmt_open *) stmt)->query != NULL) - expr = ((PLpgSQL_stmt_open *) stmt)->query; - else if (((PLpgSQL_stmt_open *) stmt)->dynquery != NULL) - expr = ((PLpgSQL_stmt_open *) stmt)->dynquery; - else - expr = ((PLpgSQL_var *) (estate->datums[((PLpgSQL_stmt_open *)stmt)->curvar]))->cursor_explicit_expr; - break; - default: - break; - } - - if (expr) - plpgsql_query_string = expr->query; + plpgsql_recurse_level++; } /* @@ -3869,10 +4858,28 @@ pg_hint_plan_plpgsql_stmt_beg(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt) static void pg_hint_plan_plpgsql_stmt_end(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt) { - if ((enum PLpgSQL_stmt_types) stmt->cmd_type == PLPGSQL_STMT_EXECSQL) - plpgsql_query_string = NULL; + plpgsql_recurse_level--; } +void plpgsql_query_erase_callback(ResourceReleasePhase phase, + bool isCommit, + bool isTopLevel, + void *arg) +{ + if (!isTopLevel || phase != RESOURCE_RELEASE_AFTER_LOCKS) + return; + /* Cancel plpgsql nest level*/ + plpgsql_recurse_level = 0; +} + + +/* include core static functions */ +static void populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, + RelOptInfo *rel2, RelOptInfo *joinrel, + SpecialJoinInfo *sjinfo, List *restrictlist); +static void set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, + Index rti, RangeTblEntry *rte); + #define standard_join_search pg_hint_plan_standard_join_search #define join_search_one_level pg_hint_plan_join_search_one_level #define make_join_rel make_join_rel_wrapper