1 /*-------------------------------------------------------------------------
4 * do instructions or hints to the planner using C-style block comments
7 * Copyright (c) 2012, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
9 *-------------------------------------------------------------------------
12 #include "miscadmin.h"
13 #include "optimizer/geqo.h"
14 #include "optimizer/joininfo.h"
15 #include "optimizer/pathnode.h"
16 #include "optimizer/paths.h"
17 #include "optimizer/plancat.h"
18 #include "optimizer/planner.h"
19 #include "tcop/tcopprot.h"
20 #include "utils/lsyscache.h"
22 #ifdef PG_MODULE_MAGIC
26 #if PG_VERSION_NUM < 90100
27 #error unsupported PostgreSQL version
30 #define HINT_START "/*"
34 #define HINT_SEQSCAN "SeqScan"
35 #define HINT_INDEXSCAN "IndexScan"
36 #define HINT_BITMAPSCAN "BitmapScan"
37 #define HINT_TIDSCAN "TidScan"
38 #define HINT_NOSEQSCAN "NoSeqScan"
39 #define HINT_NOINDEXSCAN "NoIndexScan"
40 #define HINT_NOBITMAPSCAN "NoBitmapScan"
41 #define HINT_NOTIDSCAN "NoTidScan"
42 #if PG_VERSION_NUM >= 90200
43 #define HINT_INDEXONLYSCAN "IndexonlyScan"
44 #define HINT_NOINDEXONLYSCAN "NoIndexonlyScan"
46 #define HINT_NESTLOOP "NestLoop"
47 #define HINT_MERGEJOIN "MergeJoin"
48 #define HINT_HASHJOIN "HashJoin"
49 #define HINT_NONESTLOOP "NoNestLoop"
50 #define HINT_NOMERGEJOIN "NoMergeJoin"
51 #define HINT_NOHASHJOIN "NoHashJoin"
52 #define HINT_LEADING "Leading"
53 #define HINT_SET "Set"
56 #define HINT_ARRAY_DEFAULT_INITSIZE 8
58 #define parse_ereport(str, detail) \
59 ereport(pg_hint_plan_parse_message, \
60 (errmsg("hint syntax error at or near \"%s\"", (str)), \
63 #define skip_space(str) \
64 while (isspace(*str)) \
69 ENABLE_SEQSCAN = 0x01,
70 ENABLE_INDEXSCAN = 0x02,
71 ENABLE_BITMAPSCAN = 0x04,
72 ENABLE_TIDSCAN = 0x08,
73 ENABLE_NESTLOOP = 0x10,
74 ENABLE_MERGEJOIN = 0x20,
75 ENABLE_HASHJOIN = 0x40
78 #define ENABLE_ALL_SCAN (ENABLE_SEQSCAN | ENABLE_INDEXSCAN | ENABLE_BITMAPSCAN \
80 #define ENABLE_ALL_JOIN (ENABLE_NESTLOOP | ENABLE_MERGEJOIN | ENABLE_HASHJOIN)
82 /* scan method hints */
83 typedef struct ScanHint
85 const char *opt_str; /* must not do pfree */
88 unsigned char enforce_mask;
91 /* join method hints */
92 typedef struct JoinHint
94 const char *opt_str; /* must not do pfree */
97 unsigned char enforce_mask;
101 /* change a run-time parameter hints */
102 typedef struct SetHint
104 char *name; /* name of variable */
109 * Describes a context of hint processing.
111 typedef struct PlanHint
113 char *hint_str; /* original hint string */
115 /* for scan method hints */
116 int nscan_hints; /* # of valid scan hints */
117 int max_scan_hints; /* # of slots for scan hints */
118 ScanHint **scan_hints; /* parsed scan hints */
120 /* for join method hints */
121 int njoin_hints; /* # of valid join hints */
122 int max_join_hints; /* # of slots for join hints */
123 JoinHint **join_hints; /* parsed join hints */
125 int nlevel; /* # of relations to be joined */
126 List **join_hint_level;
128 /* for Leading hints */
129 List *leading; /* relation names specified in Leading hint */
132 GucContext context; /* which GUC parameters can we set? */
133 List *set_hints; /* parsed Set hints */
136 typedef const char *(*HintParserFunction) (PlanHint *plan, Query *parse, char *keyword, const char *str);
139 * Describes a hint parser module which is bound with particular hint keyword.
141 typedef struct HintParser
145 HintParserFunction hint_parser;
148 /* Module callbacks */
152 static PlannedStmt *pg_hint_plan_planner(Query *parse, int cursorOptions,
153 ParamListInfo boundParams);
154 static void pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
155 bool inhparent, RelOptInfo *rel);
156 static RelOptInfo *pg_hint_plan_join_search(PlannerInfo *root, int levels_needed,
159 static const char *ParseScanMethod(PlanHint *plan, Query *parse, char *keyword, const char *str);
160 static const char *ParseJoinMethod(PlanHint *plan, Query *parse, char *keyword, const char *str);
161 static const char *ParseLeading(PlanHint *plan, Query *parse, char *keyword, const char *str);
162 static const char *ParseSet(PlanHint *plan, Query *parse, char *keyword, const char *str);
164 static const char *Ordered(PlanHint *plan, Query *parse, char *keyword, const char *str);
167 RelOptInfo *standard_join_search_org(PlannerInfo *root, int levels_needed, List *initial_rels);
168 void pg_hint_plan_join_search_one_level(PlannerInfo *root, int level);
169 static void make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
170 static void make_rels_by_clauseless_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
171 static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel);
172 static void set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte);
176 static bool pg_hint_plan_enable = true;
177 static bool pg_hint_plan_debug_print = false;
178 static int pg_hint_plan_parse_message = INFO;
180 static const struct config_enum_entry parse_message_level_options[] = {
181 {"debug", DEBUG2, true},
182 {"debug5", DEBUG5, false},
183 {"debug4", DEBUG4, false},
184 {"debug3", DEBUG3, false},
185 {"debug2", DEBUG2, false},
186 {"debug1", DEBUG1, false},
188 {"info", INFO, false},
189 {"notice", NOTICE, false},
190 {"warning", WARNING, false},
191 {"error", ERROR, false},
193 {"fatal", FATAL, true},
194 {"panic", PANIC, true},
199 /* Saved hook values in case of unload */
200 static planner_hook_type prev_planner_hook = NULL;
201 static get_relation_info_hook_type prev_get_relation_info = NULL;
202 static join_search_hook_type prev_join_search = NULL;
204 /* フック関数をまたがって使用する情報を管理する */
205 static PlanHint *global = NULL;
207 static const HintParser parsers[] = {
208 {HINT_SEQSCAN, true, ParseScanMethod},
209 {HINT_INDEXSCAN, true, ParseScanMethod},
210 {HINT_BITMAPSCAN, true, ParseScanMethod},
211 {HINT_TIDSCAN, true, ParseScanMethod},
212 {HINT_NOSEQSCAN, true, ParseScanMethod},
213 {HINT_NOINDEXSCAN, true, ParseScanMethod},
214 {HINT_NOBITMAPSCAN, true, ParseScanMethod},
215 {HINT_NOTIDSCAN, true, ParseScanMethod},
216 #if PG_VERSION_NUM >= 90200
217 {HINT_INDEXONLYSCAN, true, ParseScanMethod},
218 {HINT_NOINDEXONLYSCAN, true, ParseScanMethod},
220 {HINT_NESTLOOP, true, ParseJoinMethod},
221 {HINT_MERGEJOIN, true, ParseJoinMethod},
222 {HINT_HASHJOIN, true, ParseJoinMethod},
223 {HINT_NONESTLOOP, true, ParseJoinMethod},
224 {HINT_NOMERGEJOIN, true, ParseJoinMethod},
225 {HINT_NOHASHJOIN, true, ParseJoinMethod},
226 {HINT_LEADING, true, ParseLeading},
227 {HINT_SET, true, ParseSet},
232 * Module load callbacks
237 /* Define custom GUC variables. */
238 DefineCustomBoolVariable("pg_hint_plan.enable",
239 "Instructions or hints to the planner using block comments.",
241 &pg_hint_plan_enable,
249 DefineCustomBoolVariable("pg_hint_plan.debug_print",
250 "Logs each query's parse results of the hint.",
252 &pg_hint_plan_debug_print,
260 DefineCustomEnumVariable("pg_hint_plan.parse_message",
261 "Messege level of the parse error.",
263 &pg_hint_plan_parse_message,
265 parse_message_level_options,
273 prev_planner_hook = planner_hook;
274 planner_hook = pg_hint_plan_planner;
275 prev_get_relation_info = get_relation_info_hook;
276 get_relation_info_hook = pg_hint_plan_get_relation_info;
277 prev_join_search = join_search_hook;
278 join_search_hook = pg_hint_plan_join_search;
282 * Module unload callback
288 /* Uninstall hooks. */
289 planner_hook = prev_planner_hook;
290 get_relation_info_hook = prev_get_relation_info;
291 join_search_hook = prev_join_search;
299 hint = palloc(sizeof(ScanHint));
300 hint->opt_str = NULL;
301 hint->relname = NULL;
302 hint->indexnames = NIL;
303 hint->enforce_mask = 0;
309 ScanHintDelete(ScanHint *hint)
315 pfree(hint->relname);
316 list_free_deep(hint->indexnames);
325 hint = palloc(sizeof(JoinHint));
326 hint->opt_str = NULL;
328 hint->relnames = NULL;
329 hint->enforce_mask = 0;
330 hint->joinrelids = NULL;
336 JoinHintDelete(JoinHint *hint)
345 for (i = 0; i < hint->nrels; i++)
346 pfree(hint->relnames[i]);
347 pfree(hint->relnames);
349 bms_free(hint->joinrelids);
358 hint = palloc(sizeof(SetHint));
366 SetHintDelete(SetHint *hint)
383 hint = palloc(sizeof(PlanHint));
384 hint->hint_str = NULL;
385 hint->nscan_hints = 0;
386 hint->max_scan_hints = 0;
387 hint->scan_hints = NULL;
388 hint->njoin_hints = 0;
389 hint->max_join_hints = 0;
390 hint->join_hints = NULL;
392 hint->join_hint_level = NULL;
394 hint->context = superuser() ? PGC_SUSET : PGC_USERSET;
395 hint->set_hints = NIL;
401 PlanHintDelete(PlanHint *hint)
410 pfree(hint->hint_str);
412 for (i = 0; i < hint->nscan_hints; i++)
413 ScanHintDelete(hint->scan_hints[i]);
414 if (hint->scan_hints)
415 pfree(hint->scan_hints);
417 for (i = 0; i < hint->njoin_hints; i++)
418 JoinHintDelete(hint->join_hints[i]);
419 if (hint->join_hints)
420 pfree(hint->join_hints);
422 for (i = 2; i <= hint->nlevel; i++)
423 list_free(hint->join_hint_level[i]);
424 if (hint->join_hint_level)
425 pfree(hint->join_hint_level);
427 list_free_deep(hint->leading);
429 foreach(l, hint->set_hints)
430 SetHintDelete((SetHint *) lfirst(l));
431 list_free(hint->set_hints);
437 PlanHintIsempty(PlanHint *hint)
439 if (hint->nscan_hints == 0 &&
440 hint->njoin_hints == 0 &&
441 hint->leading == NIL &&
442 hint->set_hints == NIL)
448 /* TODO オブジェクト名のクォート処理を追加 */
450 PlanHintDump(PlanHint *hint)
455 bool is_first = true;
459 elog(INFO, "no hint");
463 initStringInfo(&buf);
464 appendStringInfo(&buf, "/*\n");
465 for (i = 0; i < hint->nscan_hints; i++)
467 ScanHint *h = hint->scan_hints[i];
469 switch(h->enforce_mask)
471 case(ENABLE_SEQSCAN):
472 appendStringInfo(&buf, "%s(", HINT_SEQSCAN);
474 case(ENABLE_INDEXSCAN):
475 appendStringInfo(&buf, "%s(", HINT_INDEXSCAN);
477 case(ENABLE_BITMAPSCAN):
478 appendStringInfo(&buf, "%s(", HINT_BITMAPSCAN);
480 case(ENABLE_TIDSCAN):
481 appendStringInfo(&buf, "%s(", HINT_TIDSCAN);
483 case(ENABLE_INDEXSCAN | ENABLE_BITMAPSCAN | ENABLE_TIDSCAN):
484 appendStringInfo(&buf, "%s(", HINT_NOSEQSCAN);
486 case(ENABLE_SEQSCAN | ENABLE_BITMAPSCAN | ENABLE_TIDSCAN):
487 appendStringInfo(&buf, "%s(", HINT_NOINDEXSCAN);
489 case(ENABLE_SEQSCAN | ENABLE_INDEXSCAN | ENABLE_TIDSCAN):
490 appendStringInfo(&buf, "%s(", HINT_NOBITMAPSCAN);
492 case(ENABLE_SEQSCAN | ENABLE_INDEXSCAN | ENABLE_BITMAPSCAN):
493 appendStringInfo(&buf, "%s(", HINT_NOTIDSCAN);
496 appendStringInfoString(&buf, "\?\?\?(");
499 appendStringInfo(&buf, "%s", h->relname);
500 foreach(n, h->indexnames)
501 appendStringInfo(&buf, " %s", (char *) lfirst(n));
502 appendStringInfoString(&buf, ")\n");
505 for (i = 0; i < hint->njoin_hints; i++)
507 JoinHint *h = hint->join_hints[i];
509 switch(h->enforce_mask)
511 case(ENABLE_NESTLOOP):
512 appendStringInfo(&buf, "%s(", HINT_NESTLOOP);
514 case(ENABLE_MERGEJOIN):
515 appendStringInfo(&buf, "%s(", HINT_MERGEJOIN);
517 case(ENABLE_HASHJOIN):
518 appendStringInfo(&buf, "%s(", HINT_HASHJOIN);
520 case(ENABLE_ALL_JOIN ^ ENABLE_NESTLOOP):
521 appendStringInfo(&buf, "%s(", HINT_NONESTLOOP);
523 case(ENABLE_ALL_JOIN ^ ENABLE_MERGEJOIN):
524 appendStringInfo(&buf, "%s(", HINT_NOMERGEJOIN);
526 case(ENABLE_ALL_JOIN ^ ENABLE_HASHJOIN):
527 appendStringInfo(&buf, "%s(", HINT_NOHASHJOIN);
529 case(ENABLE_ALL_JOIN):
532 appendStringInfoString(&buf, "\?\?\?(");
535 appendStringInfo(&buf, "%s", h->relnames[0]);
536 for (i = 1; i < h->nrels; i++)
537 appendStringInfo(&buf, " %s", h->relnames[i]);
538 appendStringInfoString(&buf, ")\n");
541 foreach(l, hint->set_hints)
543 SetHint *h = (SetHint *) lfirst(l);
544 appendStringInfo(&buf, "%s(%s %s)\n", HINT_SET, h->name, h->value);
547 foreach(l, hint->leading)
551 appendStringInfo(&buf, "%s(%s", HINT_LEADING, (char *)lfirst(l));
555 appendStringInfo(&buf, " %s", (char *)lfirst(l));
558 appendStringInfoString(&buf, ")\n");
560 appendStringInfoString(&buf, "*/");
562 elog(INFO, "%s", buf.data);
568 RelnameCmp(const void *a, const void *b)
570 const char *relnamea = *((const char **) a);
571 const char *relnameb = *((const char **) b);
573 return strcmp(relnamea, relnameb);
577 ScanHintCmp(const void *a, const void *b, bool order)
579 const ScanHint *hinta = *((const ScanHint **) a);
580 const ScanHint *hintb = *((const ScanHint **) b);
583 if ((result = RelnameCmp(&hinta->relname, &hintb->relname)) != 0)
588 return hinta->opt_str - hintb->opt_str;
594 ScanHintCmpIsOrder(const void *a, const void *b)
596 return ScanHintCmp(a, b, true);
600 JoinHintCmp(const void *a, const void *b, bool order)
602 const JoinHint *hinta = *((const JoinHint **) a);
603 const JoinHint *hintb = *((const JoinHint **) b);
605 if (hinta->nrels == hintb->nrels)
608 for (i = 0; i < hinta->nrels; i++)
611 if ((result = RelnameCmp(&hinta->relnames[i], &hintb->relnames[i])) != 0)
617 return hinta->opt_str - hintb->opt_str;
622 return hinta->nrels - hintb->nrels;
626 JoinHintCmpIsOrder(const void *a, const void *b)
628 return JoinHintCmp(a, b, true);
631 #if PG_VERSION_NUM < 90200
633 set_config_option_wrapper(const char *name, const char *value,
634 GucContext context, GucSource source,
635 GucAction action, bool changeVal, int elevel)
638 MemoryContext ccxt = CurrentMemoryContext;
642 result = set_config_option(name, value, context, source,
653 ecxt = MemoryContextSwitchTo(ccxt);
654 errdata = CopyErrorData();
655 ereport(elevel, (errcode(errdata->sqlerrcode),
656 errmsg("%s", errdata->message),
657 errdata->detail ? errdetail("%s", errdata->detail) : 0,
658 errdata->hint ? errhint("%s", errdata->hint) : 0));
659 FreeErrorData(errdata);
661 MemoryContextSwitchTo(ecxt);
668 #define set_config_option(name, value, context, source, \
669 action, changeVal, elevel) \
670 set_config_option_wrapper(name, value, context, source, \
671 action, changeVal, elevel)
675 set_config_options(List *options, GucContext context)
681 save_nestlevel = NewGUCNestLevel();
685 SetHint *hint = (SetHint *) lfirst(l);
688 result = set_config_option(hint->name, hint->value, context,
689 PGC_S_SESSION, GUC_ACTION_SAVE, true,
690 pg_hint_plan_parse_message);
693 return save_nestlevel;
696 #define SET_CONFIG_OPTION(name, enforce_mask, type_bits) \
697 set_config_option((name), \
698 ((enforce_mask) & (type_bits)) ? "true" : "false", \
699 context, PGC_S_SESSION, GUC_ACTION_SAVE, true, ERROR)
702 set_join_config_options(unsigned char enforce_mask, GucContext context)
704 SET_CONFIG_OPTION("enable_nestloop", enforce_mask, ENABLE_NESTLOOP);
705 SET_CONFIG_OPTION("enable_mergejoin", enforce_mask, ENABLE_MERGEJOIN);
706 SET_CONFIG_OPTION("enable_hashjoin", enforce_mask, ENABLE_HASHJOIN);
710 set_scan_config_options(unsigned char enforce_mask, GucContext context)
712 SET_CONFIG_OPTION("enable_seqscan", enforce_mask, ENABLE_SEQSCAN);
713 SET_CONFIG_OPTION("enable_indexscan", enforce_mask, ENABLE_INDEXSCAN);
714 SET_CONFIG_OPTION("enable_bitmapscan", enforce_mask, ENABLE_BITMAPSCAN);
715 SET_CONFIG_OPTION("enable_tidscan", enforce_mask, ENABLE_TIDSCAN);
716 #if PG_VERSION_NUM >= 90200
717 SET_CONFIG_OPTION("enable_indexonlyscan", enforce_mask, ENABLE_INDEXSCAN);
726 parse_keyword(const char *str, StringInfo buf)
730 while (!isspace(*str) && *str != '(' && *str != '\0')
731 appendStringInfoChar(buf, *str++);
737 skip_opened_parenthesis(const char *str)
743 parse_ereport(str, ("Opened parenthesis is necessary."));
753 skip_closed_parenthesis(const char *str)
759 parse_ereport(str, ("Closed parenthesis is necessary."));
769 * 二重引用符で囲まれているかもしれないトークンを読み取り word 引数に palloc
770 * で確保したバッファに格納してそのポインタを返す。
772 * 正常にパースできた場合は残りの文字列の先頭位置を、異常があった場合は NULL を
776 parse_quote_value(const char *str, char **word, char *value_type)
784 initStringInfo(&buf);
797 /* 二重引用符が閉じられていない場合はパース中断 */
801 parse_ereport(str, ("Unterminated quoted %s.", value_type));
806 * エスケープ対象のダブルクウォートをスキップする。
807 * もしブロックコメントの開始文字列や終了文字列もオブジェクト名とし
808 * て使用したい場合は、/ と * もエスケープ対象とすることで使用できる
809 * が、処理対象としていない。もしテーブル名にこれらの文字が含まれる
810 * 場合は、エイリアスを指定する必要がある。
820 if (isspace(*str) || *str == ')' || *str == '\0')
823 appendStringInfoCharMacro(&buf, *str++);
829 parse_ereport(str, ("%s is necessary.", value_type));
839 skip_option_delimiter(const char *str)
847 parse_ereport(str, ("Must be specified space."));
855 parse_hints(PlanHint *plan, Query *parse, const char *str)
860 initStringInfo(&buf);
863 const HintParser *parser;
865 /* in error message, we output the comment including the keyword. */
868 /* parse only the keyword of the hint. */
869 resetStringInfo(&buf);
870 str = parse_keyword(str, &buf);
872 for (parser = parsers; parser->keyword != NULL; parser++)
874 char *keyword = parser->keyword;
876 if (strcasecmp(buf.data, keyword) != 0)
879 if (parser->have_paren)
881 /* parser of each hint does parse in a parenthesis. */
882 if ((str = skip_opened_parenthesis(str)) == NULL ||
883 (str = parser->hint_parser(plan, parse, keyword, str)) == NULL ||
884 (str = skip_closed_parenthesis(str)) == NULL)
892 if ((str = parser->hint_parser(plan, parse, keyword, str)) == NULL)
899 * 直前のヒントに括弧の指定がなければ次のヒントの間に空白が必要
901 if (!isspace(*str) && *str == '\0')
902 parse_ereport(str, ("Delimiter of the hint is necessary."));
910 if (parser->keyword == NULL)
912 parse_ereport(head, ("Keyword \"%s\" does not exist.", buf.data));
924 * Do basic parsing of the query head comment.
927 parse_head_comment(Query *parse)
936 /* get client-supplied query string. */
937 p = debug_query_string;
941 /* extract query head comment. */
942 len = strlen(HINT_START);
944 if (strncmp(p, HINT_START, len))
950 if ((tail = strstr(p, HINT_END)) == NULL)
952 parse_ereport(debug_query_string, ("unterminated /* comment"));
956 /* 入れ子にしたブロックコメントはサポートしない */
957 if ((head = strstr(p, HINT_START)) != NULL && head < tail)
958 parse_ereport(head, ("block comments nest doesn't supported"));
962 head = palloc(len + 1);
963 memcpy(head, p, len);
967 plan = PlanHintCreate();
968 plan->hint_str = head;
970 /* parse each hint. */
971 if (!parse_hints(plan, parse, p))
974 /* 重複したScan条件をを除外する */
975 qsort(plan->scan_hints, plan->nscan_hints, sizeof(ScanHint *), ScanHintCmpIsOrder);
976 for (i = 0; i < plan->nscan_hints - 1;)
978 int result = ScanHintCmp(plan->scan_hints + i,
979 plan->scan_hints + i + 1, false);
984 /* 後で指定したヒントを有効にする */
986 memmove(plan->scan_hints + i, plan->scan_hints + i + 1,
987 sizeof(ScanHint *) * (plan->nscan_hints - i));
991 /* 重複したJoin条件をを除外する */
992 qsort(plan->join_hints, plan->njoin_hints, sizeof(JoinHint *), JoinHintCmpIsOrder);
993 for (i = 0; i < plan->njoin_hints - 1;)
995 int result = JoinHintCmp(plan->join_hints + i,
996 plan->join_hints + i + 1, false);
1001 /* 後で指定したヒントを有効にする */
1002 plan->njoin_hints--;
1003 memmove(plan->join_hints + i, plan->join_hints + i + 1,
1004 sizeof(JoinHint *) * (plan->njoin_hints - i));
1012 * スキャン方式ヒントのカッコ内をパースする
1015 ParseScanMethod(PlanHint *plan, Query *parse, char *keyword, const char *str)
1019 hint = ScanHintCreate();
1020 hint->opt_str = str;
1023 * スキャン方式のヒントでリレーション名が読み取れない場合はヒント無効
1025 if ((str = parse_quote_value(str, &hint->relname, "ralation name")) == NULL)
1027 ScanHintDelete(hint);
1032 * インデックスリストを受け付けるヒントであれば、インデックス参照をパース
1035 if (strcmp(keyword, HINT_INDEXSCAN) == 0 ||
1036 #if PG_VERSION_NUM >= 90200
1037 strcmp(keyword, HINT_INDEXONLYSCAN) == 0 ||
1039 strcmp(keyword, HINT_BITMAPSCAN) == 0)
1042 while (*str != ')' && *str != '\0')
1046 str = parse_quote_value(str, &indexname, "index name");
1049 ScanHintDelete(hint);
1053 hint->indexnames = lappend(hint->indexnames, indexname);
1058 /* カッコが閉じていなければヒント無効。 */
1062 parse_ereport(str, ("Closed parenthesis is necessary."));
1063 ScanHintDelete(hint);
1068 * ヒントごとに決まっている許容スキャン方式をビットマスクとして設定
1070 if (strcasecmp(keyword, HINT_SEQSCAN) == 0)
1071 hint->enforce_mask = ENABLE_SEQSCAN;
1072 else if (strcasecmp(keyword, HINT_INDEXSCAN) == 0)
1073 hint->enforce_mask = ENABLE_INDEXSCAN;
1074 else if (strcasecmp(keyword, HINT_BITMAPSCAN) == 0)
1075 hint->enforce_mask = ENABLE_BITMAPSCAN;
1076 else if (strcasecmp(keyword, HINT_TIDSCAN) == 0)
1077 hint->enforce_mask = ENABLE_TIDSCAN;
1078 else if (strcasecmp(keyword, HINT_NOSEQSCAN) == 0)
1079 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_SEQSCAN;
1080 else if (strcasecmp(keyword, HINT_NOINDEXSCAN) == 0)
1081 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_INDEXSCAN;
1082 else if (strcasecmp(keyword, HINT_NOBITMAPSCAN) == 0)
1083 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_BITMAPSCAN;
1084 else if (strcasecmp(keyword, HINT_NOTIDSCAN) == 0)
1085 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_TIDSCAN;
1088 ScanHintDelete(hint);
1089 parse_ereport(str, ("unrecognized hint keyword \"%s\"", keyword));
1094 * 出来上がったヒント情報を追加。スロットが足りない場合は二倍に拡張する。
1096 if (plan->nscan_hints == 0)
1098 plan->max_scan_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1099 plan->scan_hints = palloc(sizeof(ScanHint *) * plan->max_scan_hints);
1101 else if (plan->nscan_hints == plan->max_scan_hints)
1103 plan->max_scan_hints *= 2;
1104 plan->scan_hints = repalloc(plan->scan_hints,
1105 sizeof(ScanHint *) * plan->max_scan_hints);
1107 plan->scan_hints[plan->nscan_hints] = hint;
1108 plan->nscan_hints++;
1114 ParseJoinMethod(PlanHint *plan, Query *parse, char *keyword, const char *str)
1121 hint = JoinHintCreate();
1122 hint->opt_str = str;
1123 hint->relnames = palloc(sizeof(char *));
1125 while ((str = parse_quote_value(str, &relname, "table name")) != NULL)
1128 hint->relnames = repalloc(hint->relnames, sizeof(char *) * hint->nrels);
1129 hint->relnames[hint->nrels - 1] = relname;
1138 JoinHintDelete(hint);
1142 /* Join 対象のテーブルは最低でも2つ指定する必要がある */
1143 if (hint->nrels < 2)
1145 JoinHintDelete(hint);
1146 parse_ereport(str, ("Specified relation more than two."));
1151 qsort(hint->relnames, hint->nrels, sizeof(char *), RelnameCmp);
1153 if (strcasecmp(keyword, HINT_NESTLOOP) == 0)
1154 hint->enforce_mask = ENABLE_NESTLOOP;
1155 else if (strcasecmp(keyword, HINT_MERGEJOIN) == 0)
1156 hint->enforce_mask = ENABLE_MERGEJOIN;
1157 else if (strcasecmp(keyword, HINT_HASHJOIN) == 0)
1158 hint->enforce_mask = ENABLE_HASHJOIN;
1159 else if (strcasecmp(keyword, HINT_NONESTLOOP) == 0)
1160 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_NESTLOOP;
1161 else if (strcasecmp(keyword, HINT_NOMERGEJOIN) == 0)
1162 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_MERGEJOIN;
1163 else if (strcasecmp(keyword, HINT_NOHASHJOIN) == 0)
1164 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_HASHJOIN;
1167 JoinHintDelete(hint);
1168 parse_ereport(str, ("unrecognized hint keyword \"%s\"", keyword));
1172 if (plan->njoin_hints == 0)
1174 plan->max_join_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1175 plan->join_hints = palloc(sizeof(JoinHint *) * plan->max_join_hints);
1177 else if (plan->njoin_hints == plan->max_join_hints)
1179 plan->max_join_hints *= 2;
1180 plan->join_hints = repalloc(plan->join_hints,
1181 sizeof(JoinHint *) * plan->max_join_hints);
1184 plan->join_hints[plan->njoin_hints] = hint;
1185 plan->njoin_hints++;
1191 ParseLeading(PlanHint *plan, Query *parse, char *keyword, const char *str)
1196 * すでに指定済みの場合は、後で指定したヒントが有効にするため、登録済みの
1199 list_free_deep(plan->leading);
1200 plan->leading = NIL;
1202 while ((str = parse_quote_value(str, &relname, "relation name")) != NULL)
1206 plan->leading = lappend(plan->leading, relname);
1215 parse_ereport(str, ("Must be specified space."));
1220 /* テーブル指定が1つのみの場合は、ヒントを無効にし、パースを続ける */
1221 if (list_length(plan->leading) == 1)
1223 parse_ereport(str, ("In %s hint, specified relation name 2 or more.", HINT_LEADING));
1224 list_free_deep(plan->leading);
1225 plan->leading = NIL;
1232 ParseSet(PlanHint *plan, Query *parse, char *keyword, const char *str)
1236 hint = SetHintCreate();
1238 if ((str = parse_quote_value(str, &hint->name, "parameter name")) == NULL ||
1239 (str = skip_option_delimiter(str)) == NULL ||
1240 (str = parse_quote_value(str, &hint->value, "parameter value")) == NULL)
1242 SetHintDelete(hint);
1249 parse_ereport(str, ("Closed parenthesis is necessary."));
1250 SetHintDelete(hint);
1253 plan->set_hints = lappend(plan->set_hints, hint);
1260 * Oracle の ORDERD ヒントの実装
1263 Ordered(PlanHint *plan, Query *parse, char *keyword, const char *str)
1267 hint = SetHintCreate();
1268 hint->name = pstrdup("join_collapse_limit");
1269 hint->value = pstrdup("1");
1270 plan->set_hints = lappend(plan->set_hints, hint);
1276 static PlannedStmt *
1277 pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
1280 PlannedStmt *result;
1283 * hintが指定されない、または空のhintを指定された場合は通常のparser処理をお
1285 * 他のフック関数で実行されるhint処理をスキップするために、global 変数をNULL
1288 if (!pg_hint_plan_enable ||
1289 (global = parse_head_comment(parse)) == NULL ||
1290 PlanHintIsempty(global))
1292 PlanHintDelete(global);
1295 if (prev_planner_hook)
1296 return (*prev_planner_hook) (parse, cursorOptions, boundParams);
1298 return standard_planner(parse, cursorOptions, boundParams);
1301 /* Set hint で指定されたGUCパラメータを設定する */
1302 save_nestlevel = set_config_options(global->set_hints, global->context);
1304 if (global->leading != NIL)
1305 set_join_config_options(0, global->context);
1308 * TODO ビュー定義で指定したテーブル数が1つの場合にもこのタイミングでGUCを変更する必
1311 if (list_length(parse->rtable) == 1 &&
1312 ((RangeTblEntry *) linitial(parse->rtable))->rtekind == RTE_RELATION)
1315 RangeTblEntry *rte = linitial(parse->rtable);
1317 for (i = 0; i < global->nscan_hints; i++)
1319 ScanHint *hint = global->scan_hints[i];
1321 if (RelnameCmp(&rte->eref->aliasname, &hint->relname) != 0)
1322 parse_ereport(hint->opt_str, ("Relation \"%s\" does not exist.", hint->relname));
1324 set_scan_config_options(hint->enforce_mask, global->context);
1328 if (prev_planner_hook)
1329 result = (*prev_planner_hook) (parse, cursorOptions, boundParams);
1331 result = standard_planner(parse, cursorOptions, boundParams);
1334 * Restore the GUC variables we set above.
1336 AtEOXact_GUC(true, save_nestlevel);
1338 if (pg_hint_plan_debug_print)
1340 PlanHintDump(global);
1342 elog_node_display(INFO, "rtable", parse->rtable, true);
1346 PlanHintDelete(global);
1353 * aliasnameと一致するSCANヒントを探す
1356 find_scan_hint(RangeTblEntry *rte)
1360 for (i = 0; i < global->nscan_hints; i++)
1362 ScanHint *hint = global->scan_hints[i];
1364 if (RelnameCmp(&rte->eref->aliasname, &hint->relname) == 0)
1372 pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
1373 bool inhparent, RelOptInfo *rel)
1380 if (prev_get_relation_info)
1381 (*prev_get_relation_info) (root, relationObjectId, inhparent, rel);
1383 /* 有効なヒントが指定されなかった場合は処理をスキップする。 */
1387 if (rel->reloptkind != RELOPT_BASEREL)
1390 if ((hint = find_scan_hint(root->simple_rte_array[rel->relid])) == NULL)
1393 /* インデックスを全て削除し、スキャンに使えなくする */
1394 if (hint->enforce_mask == ENABLE_SEQSCAN)
1396 list_free_deep(rel->indexlist);
1397 rel->indexlist = NIL;
1402 /* 後でパスを作り直すため、ここではなにもしない */
1403 if (hint->indexnames == NULL)
1406 /* 指定されたインデックスのみをのこす */
1408 for (cell = list_head(rel->indexlist); cell; cell = next)
1410 IndexOptInfo *info = (IndexOptInfo *) lfirst(cell);
1411 char *indexname = get_rel_name(info->indexoid);
1413 bool use_index = false;
1417 foreach(l, hint->indexnames)
1419 if (RelnameCmp(&indexname, &lfirst(l)) == 0)
1427 rel->indexlist = list_delete_cell(rel->indexlist, cell, prev);
1436 scan_relid_aliasname(PlannerInfo *root, char *aliasname, bool check_ambiguous, const char *str)
1438 /* TODO refnameRangeTblEntry を参考 */
1442 for (i = 1; i < root->simple_rel_array_size; i++)
1444 if (root->simple_rel_array[i] == NULL)
1447 Assert(i == root->simple_rel_array[i]->relid);
1449 if (RelnameCmp(&aliasname, &root->simple_rte_array[i]->eref->aliasname)
1453 if (!check_ambiguous)
1457 parse_ereport(str, ("relation name \"%s\" is ambiguous", aliasname));
1466 * relidビットマスクと一致するヒントを探す
1469 scan_join_hint(Relids joinrelids)
1474 join_hint = global->join_hint_level[bms_num_members(joinrelids)];
1475 foreach(l, join_hint)
1477 JoinHint *hint = (JoinHint *) lfirst(l);
1478 if (bms_equal(joinrelids, hint->joinrelids))
1486 * ヒントを使用しやすい構造に変換する。
1489 rebuild_join_hints(PlanHint *plan, PlannerInfo *root, int level, List *initial_rels)
1496 plan->nlevel = root->simple_rel_array_size - 1;
1497 plan->join_hint_level = palloc0(sizeof(List *) * (root->simple_rel_array_size));
1498 for (i = 0; i < plan->njoin_hints; i++)
1500 JoinHint *hint = plan->join_hints[i];
1504 for (j = 0; j < hint->nrels; j++)
1506 char *relname = hint->relnames[j];
1508 relid = scan_relid_aliasname(root, relname, true, hint->opt_str);
1511 parse_ereport(hint->opt_str, ("Relation \"%s\" does not exist.", relname));
1515 hint->joinrelids = bms_add_member(hint->joinrelids, relid);
1521 plan->join_hint_level[hint->nrels] =
1522 lappend(plan->join_hint_level[hint->nrels], hint);
1525 /* Leading hint は、全ての join 方式が有効な hint として登録する */
1528 foreach(l, plan->leading)
1530 char *relname = (char *)lfirst(l);
1533 i = scan_relid_aliasname(root, relname, true, plan->hint_str);
1536 parse_ereport(plan->hint_str, ("Relation \"%s\" does not exist.", relname));
1537 list_free_deep(plan->leading);
1538 plan->leading = NIL;
1542 joinrelids = bms_add_member(joinrelids, i);
1548 if (njoinrels > plan->nlevel)
1550 parse_ereport(plan->hint_str, ("In %s hint, specified relation name %d or less.", HINT_LEADING, plan->nlevel));
1554 /* Leading で指定した組み合わせ以外の join hint を削除する */
1555 hint = scan_join_hint(joinrelids);
1556 list_free(plan->join_hint_level[njoinrels]);
1558 plan->join_hint_level[njoinrels] = lappend(NIL, hint);
1562 * Here relnames is not set, since Relids bitmap is sufficient to
1563 * control paths of this query afterwards.
1565 hint = JoinHintCreate();
1566 hint->nrels = njoinrels;
1567 hint->enforce_mask = ENABLE_ALL_JOIN;
1568 hint->joinrelids = bms_copy(joinrelids);
1569 plan->join_hint_level[njoinrels] = lappend(NIL, hint);
1571 if (plan->njoin_hints == 0)
1573 plan->max_join_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1574 plan->join_hints = palloc(sizeof(JoinHint *) * plan->max_join_hints);
1576 else if (plan->njoin_hints == plan->max_join_hints)
1578 plan->max_join_hints *= 2;
1579 plan->join_hints = repalloc(plan->join_hints,
1580 sizeof(JoinHint *) * plan->max_join_hints);
1583 plan->join_hints[plan->njoin_hints] = hint;
1584 plan->njoin_hints++;
1588 bms_free(joinrelids);
1592 rebuild_scan_path(PlanHint *plan, PlannerInfo *root, int level, List *initial_rels)
1595 int save_nestlevel = 0;
1597 for (i = 0; i < plan->nscan_hints; i++)
1599 ScanHint *hint = plan->scan_hints[i];
1602 if (hint->enforce_mask == ENABLE_SEQSCAN)
1605 foreach(l, initial_rels)
1607 RelOptInfo *rel = (RelOptInfo *) lfirst(l);
1608 RangeTblEntry *rte = root->simple_rte_array[rel->relid];
1610 if (rel->reloptkind != RELOPT_BASEREL ||
1611 RelnameCmp(&hint->relname, &rte->eref->aliasname) != 0)
1614 if (save_nestlevel != 0)
1615 save_nestlevel = NewGUCNestLevel();
1618 * TODO ヒントで指定されたScan方式が最安価でない場合のみ、Pathを生成
1621 set_scan_config_options(hint->enforce_mask, plan->context);
1623 rel->pathlist = NIL; /* TODO 解放 */
1624 set_plain_rel_pathlist(root, rel, rte);
1631 * Restore the GUC variables we set above.
1633 if (save_nestlevel != 0)
1634 AtEOXact_GUC(true, save_nestlevel);
1638 * src/backend/optimizer/path/joinrels.c
1639 * export make_join_rel() をラップする関数
1641 * ヒントにしたがって、enabele_* パラメータを変更した上で、make_join_rel()を
1645 pg_hint_plan_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
1652 joinrelids = bms_union(rel1->relids, rel2->relids);
1653 hint = scan_join_hint(joinrelids);
1654 bms_free(joinrelids);
1657 return make_join_rel(root, rel1, rel2);
1659 save_nestlevel = NewGUCNestLevel();
1661 set_join_config_options(hint->enforce_mask, global->context);
1663 rel = make_join_rel(root, rel1, rel2);
1666 * Restore the GUC variables we set above.
1668 AtEOXact_GUC(true, save_nestlevel);
1674 pg_hint_plan_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
1677 * pg_hint_planが無効、または有効なヒントが1つも指定されなかった場合は、標準
1682 if (prev_join_search)
1683 return (*prev_join_search) (root, levels_needed, initial_rels);
1684 else if (enable_geqo && levels_needed >= geqo_threshold)
1685 return geqo(root, levels_needed, initial_rels);
1687 return standard_join_search(root, levels_needed, initial_rels);
1690 rebuild_join_hints(global, root, levels_needed, initial_rels);
1691 rebuild_scan_path(global, root, levels_needed, initial_rels);
1694 * GEQOを使用する条件を満たした場合は、GEQOを用いた結合方式の検索を行う。
1695 * このとき、スキャン方式のヒントとSetヒントのみが有効になり、結合方式や結合
1696 * 順序はヒント句は無効になりGEQOのアルゴリズムで決定される。
1698 if (enable_geqo && levels_needed >= geqo_threshold)
1699 return geqo(root, levels_needed, initial_rels);
1701 return standard_join_search_org(root, levels_needed, initial_rels);
1704 #define standard_join_search standard_join_search_org
1705 #define join_search_one_level pg_hint_plan_join_search_one_level
1706 #define make_join_rel pg_hint_plan_make_join_rel