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 *ParseIndexScanMethod(PlanHint *plan, Query *parse, char *keyword, const char *str);
161 static const char *ParseJoinMethod(PlanHint *plan, Query *parse, char *keyword, const char *str);
162 static const char *ParseLeading(PlanHint *plan, Query *parse, char *keyword, const char *str);
163 static const char *ParseSet(PlanHint *plan, Query *parse, char *keyword, const char *str);
165 static const char *Ordered(PlanHint *plan, Query *parse, char *keyword, const char *str);
168 RelOptInfo *standard_join_search_org(PlannerInfo *root, int levels_needed, List *initial_rels);
169 void pg_hint_plan_join_search_one_level(PlannerInfo *root, int level);
170 static void make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
171 static void make_rels_by_clauseless_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
172 static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel);
173 static void set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte);
177 static bool pg_hint_plan_enable = true;
178 static bool pg_hint_plan_debug_print = false;
179 static int pg_hint_plan_parse_message = INFO;
181 static const struct config_enum_entry parse_message_level_options[] = {
182 {"debug", DEBUG2, true},
183 {"debug5", DEBUG5, false},
184 {"debug4", DEBUG4, false},
185 {"debug3", DEBUG3, false},
186 {"debug2", DEBUG2, false},
187 {"debug1", DEBUG1, false},
189 {"info", INFO, false},
190 {"notice", NOTICE, false},
191 {"warning", WARNING, false},
192 {"error", ERROR, false},
194 {"fatal", FATAL, true},
195 {"panic", PANIC, true},
200 /* Saved hook values in case of unload */
201 static planner_hook_type prev_planner_hook = NULL;
202 static get_relation_info_hook_type prev_get_relation_info = NULL;
203 static join_search_hook_type prev_join_search = NULL;
205 /* フック関数をまたがって使用する情報を管理する */
206 static PlanHint *global = NULL;
208 static const HintParser parsers[] = {
209 {HINT_SEQSCAN, true, ParseScanMethod},
210 {HINT_INDEXSCAN, true, ParseIndexScanMethod},
211 {HINT_BITMAPSCAN, true, ParseIndexScanMethod},
212 {HINT_TIDSCAN, true, ParseScanMethod},
213 {HINT_NOSEQSCAN, true, ParseScanMethod},
214 {HINT_NOINDEXSCAN, true, ParseScanMethod},
215 {HINT_NOBITMAPSCAN, true, ParseScanMethod},
216 {HINT_NOTIDSCAN, true, ParseScanMethod},
217 #if PG_VERSION_NUM >= 90200
218 {HINT_INDEXONLYSCAN, true, ParseIndexScanMethod},
219 {HINT_NOINDEXONLYSCAN, true, ParseIndexScanMethod},
221 {HINT_NESTLOOP, true, ParseJoinMethod},
222 {HINT_MERGEJOIN, true, ParseJoinMethod},
223 {HINT_HASHJOIN, true, ParseJoinMethod},
224 {HINT_NONESTLOOP, true, ParseJoinMethod},
225 {HINT_NOMERGEJOIN, true, ParseJoinMethod},
226 {HINT_NOHASHJOIN, true, ParseJoinMethod},
227 {HINT_LEADING, true, ParseLeading},
228 {HINT_SET, true, ParseSet},
233 * Module load callbacks
238 /* Define custom GUC variables. */
239 DefineCustomBoolVariable("pg_hint_plan.enable",
240 "Instructions or hints to the planner using block comments.",
242 &pg_hint_plan_enable,
250 DefineCustomBoolVariable("pg_hint_plan.debug_print",
251 "Logs each query's parse results of the hint.",
253 &pg_hint_plan_debug_print,
261 DefineCustomEnumVariable("pg_hint_plan.parse_message",
262 "Messege level of the parse error.",
264 &pg_hint_plan_parse_message,
266 parse_message_level_options,
274 prev_planner_hook = planner_hook;
275 planner_hook = pg_hint_plan_planner;
276 prev_get_relation_info = get_relation_info_hook;
277 get_relation_info_hook = pg_hint_plan_get_relation_info;
278 prev_join_search = join_search_hook;
279 join_search_hook = pg_hint_plan_join_search;
283 * Module unload callback
289 /* Uninstall hooks. */
290 planner_hook = prev_planner_hook;
291 get_relation_info_hook = prev_get_relation_info;
292 join_search_hook = prev_join_search;
300 hint = palloc(sizeof(ScanHint));
301 hint->opt_str = NULL;
302 hint->relname = NULL;
303 hint->indexnames = NIL;
304 hint->enforce_mask = 0;
310 ScanHintDelete(ScanHint *hint)
316 pfree(hint->relname);
317 list_free_deep(hint->indexnames);
326 hint = palloc(sizeof(JoinHint));
327 hint->opt_str = NULL;
329 hint->relnames = NULL;
330 hint->enforce_mask = 0;
331 hint->joinrelids = NULL;
337 JoinHintDelete(JoinHint *hint)
346 for (i = 0; i < hint->nrels; i++)
347 pfree(hint->relnames[i]);
348 pfree(hint->relnames);
350 bms_free(hint->joinrelids);
359 hint = palloc(sizeof(SetHint));
367 SetHintDelete(SetHint *hint)
384 hint = palloc(sizeof(PlanHint));
385 hint->hint_str = NULL;
386 hint->nscan_hints = 0;
387 hint->max_scan_hints = 0;
388 hint->scan_hints = NULL;
389 hint->njoin_hints = 0;
390 hint->max_join_hints = 0;
391 hint->join_hints = NULL;
393 hint->join_hint_level = NULL;
395 hint->context = superuser() ? PGC_SUSET : PGC_USERSET;
396 hint->set_hints = NIL;
402 PlanHintDelete(PlanHint *hint)
411 pfree(hint->hint_str);
413 for (i = 0; i < hint->nscan_hints; i++)
414 ScanHintDelete(hint->scan_hints[i]);
415 if (hint->scan_hints)
416 pfree(hint->scan_hints);
418 for (i = 0; i < hint->njoin_hints; i++)
419 JoinHintDelete(hint->join_hints[i]);
420 if (hint->join_hints)
421 pfree(hint->join_hints);
423 for (i = 2; i <= hint->nlevel; i++)
424 list_free(hint->join_hint_level[i]);
425 if (hint->join_hint_level)
426 pfree(hint->join_hint_level);
428 list_free_deep(hint->leading);
430 foreach(l, hint->set_hints)
431 SetHintDelete((SetHint *) lfirst(l));
432 list_free(hint->set_hints);
438 PlanHintIsempty(PlanHint *hint)
440 if (hint->nscan_hints == 0 &&
441 hint->njoin_hints == 0 &&
442 hint->leading == NIL &&
443 hint->set_hints == NIL)
449 /* TODO オブジェクト名のクォート処理を追加 */
451 PlanHintDump(PlanHint *hint)
456 bool is_first = true;
460 elog(INFO, "no hint");
464 initStringInfo(&buf);
465 appendStringInfo(&buf, "/*\n");
466 for (i = 0; i < hint->nscan_hints; i++)
468 ScanHint *h = hint->scan_hints[i];
470 switch(h->enforce_mask)
472 case(ENABLE_SEQSCAN):
473 appendStringInfo(&buf, "%s(", HINT_SEQSCAN);
475 case(ENABLE_INDEXSCAN):
476 appendStringInfo(&buf, "%s(", HINT_INDEXSCAN);
478 case(ENABLE_BITMAPSCAN):
479 appendStringInfo(&buf, "%s(", HINT_BITMAPSCAN);
481 case(ENABLE_TIDSCAN):
482 appendStringInfo(&buf, "%s(", HINT_TIDSCAN);
484 case(ENABLE_INDEXSCAN | ENABLE_BITMAPSCAN | ENABLE_TIDSCAN):
485 appendStringInfo(&buf, "%s(", HINT_NOSEQSCAN);
487 case(ENABLE_SEQSCAN | ENABLE_BITMAPSCAN | ENABLE_TIDSCAN):
488 appendStringInfo(&buf, "%s(", HINT_NOINDEXSCAN);
490 case(ENABLE_SEQSCAN | ENABLE_INDEXSCAN | ENABLE_TIDSCAN):
491 appendStringInfo(&buf, "%s(", HINT_NOBITMAPSCAN);
493 case(ENABLE_SEQSCAN | ENABLE_INDEXSCAN | ENABLE_BITMAPSCAN):
494 appendStringInfo(&buf, "%s(", HINT_NOTIDSCAN);
497 appendStringInfoString(&buf, "\?\?\?(");
500 appendStringInfo(&buf, "%s", h->relname);
501 foreach(n, h->indexnames)
502 appendStringInfo(&buf, " %s", (char *) lfirst(n));
503 appendStringInfoString(&buf, ")\n");
506 for (i = 0; i < hint->njoin_hints; i++)
508 JoinHint *h = hint->join_hints[i];
510 switch(h->enforce_mask)
512 case(ENABLE_NESTLOOP):
513 appendStringInfo(&buf, "%s(", HINT_NESTLOOP);
515 case(ENABLE_MERGEJOIN):
516 appendStringInfo(&buf, "%s(", HINT_MERGEJOIN);
518 case(ENABLE_HASHJOIN):
519 appendStringInfo(&buf, "%s(", HINT_HASHJOIN);
521 case(ENABLE_ALL_JOIN ^ ENABLE_NESTLOOP):
522 appendStringInfo(&buf, "%s(", HINT_NONESTLOOP);
524 case(ENABLE_ALL_JOIN ^ ENABLE_MERGEJOIN):
525 appendStringInfo(&buf, "%s(", HINT_NOMERGEJOIN);
527 case(ENABLE_ALL_JOIN ^ ENABLE_HASHJOIN):
528 appendStringInfo(&buf, "%s(", HINT_NOHASHJOIN);
530 case(ENABLE_ALL_JOIN):
533 appendStringInfoString(&buf, "\?\?\?(");
536 appendStringInfo(&buf, "%s", h->relnames[0]);
537 for (i = 1; i < h->nrels; i++)
538 appendStringInfo(&buf, " %s", h->relnames[i]);
539 appendStringInfoString(&buf, ")\n");
542 foreach(l, hint->set_hints)
544 SetHint *h = (SetHint *) lfirst(l);
545 appendStringInfo(&buf, "%s(%s %s)\n", HINT_SET, h->name, h->value);
548 foreach(l, hint->leading)
552 appendStringInfo(&buf, "%s(%s", HINT_LEADING, (char *)lfirst(l));
556 appendStringInfo(&buf, " %s", (char *)lfirst(l));
559 appendStringInfoString(&buf, ")\n");
561 appendStringInfoString(&buf, "*/");
563 elog(INFO, "%s", buf.data);
569 RelnameCmp(const void *a, const void *b)
571 const char *relnamea = *((const char **) a);
572 const char *relnameb = *((const char **) b);
574 return strcmp(relnamea, relnameb);
578 ScanHintCmp(const void *a, const void *b, bool order)
580 const ScanHint *hinta = *((const ScanHint **) a);
581 const ScanHint *hintb = *((const ScanHint **) b);
584 if ((result = strcmp(hinta->relname, hintb->relname)) != 0)
589 return hinta->opt_str - hintb->opt_str;
595 ScanHintCmpIsOrder(const void *a, const void *b)
597 return ScanHintCmp(a, b, true);
601 JoinHintCmp(const void *a, const void *b, bool order)
603 const JoinHint *hinta = *((const JoinHint **) a);
604 const JoinHint *hintb = *((const JoinHint **) b);
606 if (hinta->nrels == hintb->nrels)
609 for (i = 0; i < hinta->nrels; i++)
612 if ((result = strcmp(hinta->relnames[i], hintb->relnames[i])) != 0)
618 return hinta->opt_str - hintb->opt_str;
623 return hinta->nrels - hintb->nrels;
627 JoinHintCmpIsOrder(const void *a, const void *b)
629 return JoinHintCmp(a, b, true);
632 #if PG_VERSION_NUM < 90200
634 set_config_option_wrapper(const char *name, const char *value,
635 GucContext context, GucSource source,
636 GucAction action, bool changeVal, int elevel)
639 MemoryContext ccxt = CurrentMemoryContext;
643 result = set_config_option(name, value, context, source,
654 ecxt = MemoryContextSwitchTo(ccxt);
655 errdata = CopyErrorData();
656 ereport(elevel, (errcode(errdata->sqlerrcode),
657 errmsg("%s", errdata->message),
658 errdata->detail ? errdetail("%s", errdata->detail) : 0,
659 errdata->hint ? errhint("%s", errdata->hint) : 0));
660 FreeErrorData(errdata);
662 MemoryContextSwitchTo(ecxt);
669 #define set_config_option(name, value, context, source, \
670 action, changeVal, elevel) \
671 set_config_option_wrapper(name, value, context, source, \
672 action, changeVal, elevel)
676 set_config_options(List *options, GucContext context)
682 save_nestlevel = NewGUCNestLevel();
686 SetHint *hint = (SetHint *) lfirst(l);
689 result = set_config_option(hint->name, hint->value, context,
690 PGC_S_SESSION, GUC_ACTION_SAVE, true,
691 pg_hint_plan_parse_message);
694 return save_nestlevel;
697 #define SET_CONFIG_OPTION(name, enforce_mask, type_bits) \
698 set_config_option((name), \
699 ((enforce_mask) & (type_bits)) ? "true" : "false", \
700 context, PGC_S_SESSION, GUC_ACTION_SAVE, true, ERROR)
703 set_join_config_options(unsigned char enforce_mask, GucContext context)
705 SET_CONFIG_OPTION("enable_nestloop", enforce_mask, ENABLE_NESTLOOP);
706 SET_CONFIG_OPTION("enable_mergejoin", enforce_mask, ENABLE_MERGEJOIN);
707 SET_CONFIG_OPTION("enable_hashjoin", enforce_mask, ENABLE_HASHJOIN);
711 set_scan_config_options(unsigned char enforce_mask, GucContext context)
713 SET_CONFIG_OPTION("enable_seqscan", enforce_mask, ENABLE_SEQSCAN);
714 SET_CONFIG_OPTION("enable_indexscan", enforce_mask, ENABLE_INDEXSCAN);
715 SET_CONFIG_OPTION("enable_bitmapscan", enforce_mask, ENABLE_BITMAPSCAN);
716 SET_CONFIG_OPTION("enable_tidscan", enforce_mask, ENABLE_TIDSCAN);
717 #if PG_VERSION_NUM >= 90200
718 SET_CONFIG_OPTION("enable_indexonlyscan", enforce_mask, ENABLE_INDEXSCAN);
727 parse_keyword(const char *str, StringInfo buf)
731 while (!isspace(*str) && *str != '(' && *str != '\0')
732 appendStringInfoChar(buf, *str++);
738 skip_opened_parenthesis(const char *str)
744 parse_ereport(str, ("Opened parenthesis is necessary."));
754 skip_closed_parenthesis(const char *str)
760 parse_ereport(str, ("Closed parenthesis is necessary."));
770 * 二重引用符で囲まれているかもしれないトークンを読み取り word 引数に palloc
771 * で確保したバッファに格納してそのポインタを返す。
773 * 正常にパースできた場合は残りの文字列の先頭位置を、異常があった場合は NULL を
777 parse_quote_value(const char *str, char **word, char *value_type)
785 initStringInfo(&buf);
798 /* 二重引用符が閉じられていない場合はパース中断 */
802 parse_ereport(str, ("Unterminated quoted %s.", value_type));
807 * エスケープ対象のダブルクウォートをスキップする。
808 * もしブロックコメントの開始文字列や終了文字列もオブジェクト名とし
809 * て使用したい場合は、/ と * もエスケープ対象とすることで使用できる
810 * が、処理対象としていない。もしテーブル名にこれらの文字が含まれる
811 * 場合は、エイリアスを指定する必要がある。
821 if (isspace(*str) || *str == ')' || *str == '\0')
824 appendStringInfoCharMacro(&buf, *str++);
830 parse_ereport(str, ("%s is necessary.", value_type));
840 skip_option_delimiter(const char *str)
848 parse_ereport(str, ("Must be specified space."));
856 parse_hints(PlanHint *plan, Query *parse, const char *str)
861 initStringInfo(&buf);
864 const HintParser *parser;
866 /* in error message, we output the comment including the keyword. */
869 /* parse only the keyword of the hint. */
870 resetStringInfo(&buf);
871 str = parse_keyword(str, &buf);
873 for (parser = parsers; parser->keyword != NULL; parser++)
875 char *keyword = parser->keyword;
877 if (strcasecmp(buf.data, keyword) != 0)
880 if (parser->have_paren)
882 /* parser of each hint does parse in a parenthesis. */
883 if ((str = skip_opened_parenthesis(str)) == NULL ||
884 (str = parser->hint_parser(plan, parse, keyword, str)) == NULL ||
885 (str = skip_closed_parenthesis(str)) == NULL)
893 if ((str = parser->hint_parser(plan, parse, keyword, str)) == NULL)
900 * 直前のヒントに括弧の指定がなければ次のヒントの間に空白が必要
902 if (!isspace(*str) && *str == '\0')
903 parse_ereport(str, ("Delimiter of the hint is necessary."));
911 if (parser->keyword == NULL)
913 parse_ereport(head, ("Keyword \"%s\" does not exist.", buf.data));
925 * Do basic parsing of the query head comment.
928 parse_head_comment(Query *parse)
937 /* get client-supplied query string. */
938 p = debug_query_string;
943 /* extract query head comment. */
944 len = strlen(HINT_START);
946 if (strncmp(p, HINT_START, len))
952 if ((tail = strstr(p, HINT_END)) == NULL)
953 elog(ERROR, "unterminated /* comment at or near \"%s\"",
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 parse_scan_method(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 * ヒントごとに決まっている許容スキャン方式をビットマスクとして設定
1034 if (strcasecmp(keyword, HINT_SEQSCAN) == 0)
1035 hint->enforce_mask = ENABLE_SEQSCAN;
1036 else if (strcasecmp(keyword, HINT_INDEXSCAN) == 0)
1037 hint->enforce_mask = ENABLE_INDEXSCAN;
1038 else if (strcasecmp(keyword, HINT_BITMAPSCAN) == 0)
1039 hint->enforce_mask = ENABLE_BITMAPSCAN;
1040 else if (strcasecmp(keyword, HINT_TIDSCAN) == 0)
1041 hint->enforce_mask = ENABLE_TIDSCAN;
1042 else if (strcasecmp(keyword, HINT_NOSEQSCAN) == 0)
1043 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_SEQSCAN;
1044 else if (strcasecmp(keyword, HINT_NOINDEXSCAN) == 0)
1045 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_INDEXSCAN;
1046 else if (strcasecmp(keyword, HINT_NOBITMAPSCAN) == 0)
1047 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_BITMAPSCAN;
1048 else if (strcasecmp(keyword, HINT_NOTIDSCAN) == 0)
1049 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_TIDSCAN;
1052 elog(ERROR, "unrecognized hint keyword \"%s\"", keyword);
1057 * 出来上がったヒント情報を追加。スロットが足りない場合は二倍に拡張する。
1059 if (plan->nscan_hints == 0)
1061 plan->max_scan_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1062 plan->scan_hints = palloc(sizeof(JoinHint *) * plan->max_scan_hints);
1064 else if (plan->nscan_hints == plan->max_scan_hints)
1066 plan->max_scan_hints *= 2;
1067 plan->scan_hints = repalloc(plan->scan_hints,
1068 sizeof(JoinHint *) * plan->max_scan_hints);
1071 plan->scan_hints[plan->nscan_hints] = hint;
1072 plan->nscan_hints++;
1078 ParseScanMethod(PlanHint *plan, Query *parse, char *keyword, const char *str)
1082 if ((str = parse_scan_method(plan, parse, keyword, str)) == NULL)
1085 hint = plan->scan_hints[plan->nscan_hints - 1];
1090 parse_ereport(str, ("Closed parenthesis is necessary."));
1091 plan->nscan_hints--;
1092 ScanHintDelete(hint);
1100 ParseIndexScanMethod(PlanHint *plan, Query *parse, char *keyword, const char *str)
1105 if ((str = parse_scan_method(plan, parse, keyword, str)) == NULL)
1108 hint = plan->scan_hints[plan->nscan_hints - 1];
1110 /* インデックス参照をパースする。 */
1114 * TODO 直前のオブジェクト名がクウォート処理されていた場合の処理を実装
1120 if ((str = parse_quote_value(str, &indexname, "index name")) == NULL)
1122 plan->nscan_hints--;
1123 ScanHintDelete(hint);
1127 hint->indexnames = lappend(hint->indexnames, indexname);
1134 ParseJoinMethod(PlanHint *plan, Query *parse, char *keyword, const char *str)
1141 hint = JoinHintCreate();
1142 hint->opt_str = str;
1143 hint->relnames = palloc(sizeof(char *));
1145 while ((str = parse_quote_value(str, &relname, "table name")) != NULL)
1148 hint->relnames = repalloc(hint->relnames, sizeof(char *) * hint->nrels);
1149 hint->relnames[hint->nrels - 1] = relname;
1158 JoinHintDelete(hint);
1162 /* Join 対象のテーブルは最低でも2つ指定する必要がある */
1163 if (hint->nrels < 2)
1165 JoinHintDelete(hint);
1166 parse_ereport(str, ("Specified relation more than two."));
1171 qsort(hint->relnames, hint->nrels, sizeof(char *), RelnameCmp);
1173 if (strcasecmp(keyword, HINT_NESTLOOP) == 0)
1174 hint->enforce_mask = ENABLE_NESTLOOP;
1175 else if (strcasecmp(keyword, HINT_MERGEJOIN) == 0)
1176 hint->enforce_mask = ENABLE_MERGEJOIN;
1177 else if (strcasecmp(keyword, HINT_HASHJOIN) == 0)
1178 hint->enforce_mask = ENABLE_HASHJOIN;
1179 else if (strcasecmp(keyword, HINT_NONESTLOOP) == 0)
1180 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_NESTLOOP;
1181 else if (strcasecmp(keyword, HINT_NOMERGEJOIN) == 0)
1182 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_MERGEJOIN;
1183 else if (strcasecmp(keyword, HINT_NOHASHJOIN) == 0)
1184 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_HASHJOIN;
1186 elog(ERROR, "unrecognized hint keyword \"%s\"", keyword);
1188 if (plan->njoin_hints == 0)
1190 plan->max_join_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1191 plan->join_hints = palloc(sizeof(JoinHint *) * plan->max_join_hints);
1193 else if (plan->njoin_hints == plan->max_join_hints)
1195 plan->max_join_hints *= 2;
1196 plan->join_hints = repalloc(plan->join_hints,
1197 sizeof(JoinHint *) * plan->max_join_hints);
1200 plan->join_hints[plan->njoin_hints] = hint;
1201 plan->njoin_hints++;
1207 ParseLeading(PlanHint *plan, Query *parse, char *keyword, const char *str)
1212 * すでに指定済みの場合は、後で指定したヒントが有効にするため、登録済みの
1215 list_free_deep(plan->leading);
1216 plan->leading = NIL;
1218 while ((str = parse_quote_value(str, &relname, "relation name")) != NULL)
1222 plan->leading = lappend(plan->leading, relname);
1231 parse_ereport(str, ("Must be specified space."));
1236 /* テーブル指定が1つのみの場合は、ヒントを無効にし、パースを続ける */
1237 if (list_length(plan->leading) == 1)
1239 parse_ereport(str, ("In %s hint, specified relation name 2 or more.", HINT_LEADING));
1240 list_free_deep(plan->leading);
1241 plan->leading = NIL;
1248 ParseSet(PlanHint *plan, Query *parse, char *keyword, const char *str)
1252 hint = SetHintCreate();
1254 if ((str = parse_quote_value(str, &hint->name, "parameter name")) == NULL ||
1255 (str = skip_option_delimiter(str)) == NULL ||
1256 (str = parse_quote_value(str, &hint->value, "parameter value")) == NULL)
1258 SetHintDelete(hint);
1265 parse_ereport(str, ("Closed parenthesis is necessary."));
1266 SetHintDelete(hint);
1269 plan->set_hints = lappend(plan->set_hints, hint);
1276 * Oracle の ORDERD ヒントの実装
1279 Ordered(PlanHint *plan, Query *parse, char *keyword, const char *str)
1283 hint = SetHintCreate();
1284 hint->name = pstrdup("join_collapse_limit");
1285 hint->value = pstrdup("1");
1286 plan->set_hints = lappend(plan->set_hints, hint);
1292 static PlannedStmt *
1293 pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
1296 PlannedStmt *result;
1299 * hintが指定されない、または空のhintを指定された場合は通常のparser処理をお
1301 * 他のフック関数で実行されるhint処理をスキップするために、global 変数をNULL
1304 if (!pg_hint_plan_enable ||
1305 (global = parse_head_comment(parse)) == NULL ||
1306 PlanHintIsempty(global))
1308 PlanHintDelete(global);
1311 if (prev_planner_hook)
1312 return (*prev_planner_hook) (parse, cursorOptions, boundParams);
1314 return standard_planner(parse, cursorOptions, boundParams);
1317 /* Set hint で指定されたGUCパラメータを設定する */
1318 save_nestlevel = set_config_options(global->set_hints, global->context);
1320 if (global->leading != NIL)
1321 set_join_config_options(0, global->context);
1324 * TODO ビュー定義で指定したテーブル数が1つの場合にもこのタイミングでGUCを変更する必
1327 if (list_length(parse->rtable) == 1 &&
1328 ((RangeTblEntry *) linitial(parse->rtable))->rtekind == RTE_RELATION)
1331 RangeTblEntry *rte = linitial(parse->rtable);
1333 for (i = 0; i < global->nscan_hints; i++)
1335 ScanHint *hint = global->scan_hints[i];
1337 if (strcmp(hint->relname, rte->eref->aliasname) != 0)
1338 parse_ereport(hint->opt_str, ("Relation \"%s\" does not exist.", hint->relname));
1340 set_scan_config_options(hint->enforce_mask, global->context);
1344 if (prev_planner_hook)
1345 result = (*prev_planner_hook) (parse, cursorOptions, boundParams);
1347 result = standard_planner(parse, cursorOptions, boundParams);
1350 * Restore the GUC variables we set above.
1352 AtEOXact_GUC(true, save_nestlevel);
1354 if (pg_hint_plan_debug_print)
1356 PlanHintDump(global);
1358 elog_node_display(INFO, "rtable", parse->rtable, true);
1362 PlanHintDelete(global);
1369 * aliasnameと一致するSCANヒントを探す
1372 find_scan_hint(RangeTblEntry *rte)
1376 for (i = 0; i < global->nscan_hints; i++)
1378 ScanHint *hint = global->scan_hints[i];
1380 if (strcmp(rte->eref->aliasname, hint->relname) == 0)
1388 pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
1389 bool inhparent, RelOptInfo *rel)
1396 if (prev_get_relation_info)
1397 (*prev_get_relation_info) (root, relationObjectId, inhparent, rel);
1399 /* 有効なヒントが指定されなかった場合は処理をスキップする。 */
1403 if (rel->reloptkind != RELOPT_BASEREL)
1406 if ((hint = find_scan_hint(root->simple_rte_array[rel->relid])) == NULL)
1409 /* インデックスを全て削除し、スキャンに使えなくする */
1410 if (hint->enforce_mask == ENABLE_SEQSCAN)
1412 list_free_deep(rel->indexlist);
1413 rel->indexlist = NIL;
1418 /* 後でパスを作り直すため、ここではなにもしない */
1419 if (hint->indexnames == NULL)
1422 /* 指定されたインデックスのみをのこす */
1424 for (cell = list_head(rel->indexlist); cell; cell = next)
1426 IndexOptInfo *info = (IndexOptInfo *) lfirst(cell);
1427 char *indexname = get_rel_name(info->indexoid);
1429 bool use_index = false;
1433 foreach(l, hint->indexnames)
1435 if (strcmp(indexname, lfirst(l)) == 0)
1443 rel->indexlist = list_delete_cell(rel->indexlist, cell, prev);
1452 scan_relid_aliasname(PlannerInfo *root, char *aliasname, bool check_ambiguous, const char *str)
1454 /* TODO refnameRangeTblEntry を参考 */
1458 for (i = 1; i < root->simple_rel_array_size; i++)
1460 if (root->simple_rel_array[i] == NULL)
1463 Assert(i == root->simple_rel_array[i]->relid);
1465 if (strcmp(aliasname, root->simple_rte_array[i]->eref->aliasname) != 0)
1468 if (!check_ambiguous)
1472 parse_ereport(str, ("relation name \"%s\" is ambiguous", aliasname));
1481 * relidビットマスクと一致するヒントを探す
1484 scan_join_hint(Relids joinrelids)
1489 join_hint = global->join_hint_level[bms_num_members(joinrelids)];
1490 foreach(l, join_hint)
1492 JoinHint *hint = (JoinHint *) lfirst(l);
1493 if (bms_equal(joinrelids, hint->joinrelids))
1501 * ヒントを使用しやすい構造に変換する。
1504 rebuild_join_hints(PlanHint *plan, PlannerInfo *root, int level, List *initial_rels)
1511 plan->nlevel = root->simple_rel_array_size - 1;
1512 plan->join_hint_level = palloc0(sizeof(List *) * (root->simple_rel_array_size));
1513 for (i = 0; i < plan->njoin_hints; i++)
1515 JoinHint *hint = plan->join_hints[i];
1519 for (j = 0; j < hint->nrels; j++)
1521 char *relname = hint->relnames[j];
1523 relid = scan_relid_aliasname(root, relname, true, hint->opt_str);
1526 parse_ereport(hint->opt_str, ("Relation \"%s\" does not exist.", relname));
1530 hint->joinrelids = bms_add_member(hint->joinrelids, relid);
1536 plan->join_hint_level[hint->nrels] =
1537 lappend(plan->join_hint_level[hint->nrels], hint);
1540 /* Leading hint は、全ての join 方式が有効な hint として登録する */
1543 foreach(l, plan->leading)
1545 char *relname = (char *)lfirst(l);
1548 i = scan_relid_aliasname(root, relname, true, plan->hint_str);
1551 parse_ereport(plan->hint_str, ("Relation \"%s\" does not exist.", relname));
1552 list_free_deep(plan->leading);
1553 plan->leading = NIL;
1557 joinrelids = bms_add_member(joinrelids, i);
1563 if (njoinrels > plan->nlevel)
1565 parse_ereport(plan->hint_str, ("In %s hint, specified relation name %d or less.", HINT_LEADING, plan->nlevel));
1569 /* Leading で指定した組み合わせ以外の join hint を削除する */
1570 hint = scan_join_hint(joinrelids);
1571 list_free(plan->join_hint_level[njoinrels]);
1573 plan->join_hint_level[njoinrels] = lappend(NIL, hint);
1577 * Here relnames is not set, since Relids bitmap is sufficient to
1578 * control paths of this query afterwards.
1580 hint = JoinHintCreate();
1581 hint->nrels = njoinrels;
1582 hint->enforce_mask = ENABLE_ALL_JOIN;
1583 hint->joinrelids = bms_copy(joinrelids);
1584 plan->join_hint_level[njoinrels] = lappend(NIL, hint);
1586 if (plan->njoin_hints == 0)
1588 plan->max_join_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1589 plan->join_hints = palloc(sizeof(JoinHint *) * plan->max_join_hints);
1591 else if (plan->njoin_hints == plan->max_join_hints)
1593 plan->max_join_hints *= 2;
1594 plan->join_hints = repalloc(plan->join_hints,
1595 sizeof(JoinHint *) * plan->max_join_hints);
1598 plan->join_hints[plan->njoin_hints] = hint;
1599 plan->njoin_hints++;
1603 bms_free(joinrelids);
1607 rebuild_scan_path(PlanHint *plan, PlannerInfo *root, int level, List *initial_rels)
1610 int save_nestlevel = 0;
1612 for (i = 0; i < plan->nscan_hints; i++)
1614 ScanHint *hint = plan->scan_hints[i];
1617 if (hint->enforce_mask == ENABLE_SEQSCAN)
1620 foreach(l, initial_rels)
1622 RelOptInfo *rel = (RelOptInfo *) lfirst(l);
1623 RangeTblEntry *rte = root->simple_rte_array[rel->relid];
1625 if (rel->reloptkind != RELOPT_BASEREL ||
1626 strcmp(hint->relname, rte->eref->aliasname) != 0)
1629 if (save_nestlevel != 0)
1630 save_nestlevel = NewGUCNestLevel();
1633 * TODO ヒントで指定されたScan方式が最安価でない場合のみ、Pathを生成
1636 set_scan_config_options(hint->enforce_mask, plan->context);
1638 rel->pathlist = NIL; /* TODO 解放 */
1639 set_plain_rel_pathlist(root, rel, rte);
1646 * Restore the GUC variables we set above.
1648 if (save_nestlevel != 0)
1649 AtEOXact_GUC(true, save_nestlevel);
1653 * src/backend/optimizer/path/joinrels.c
1654 * export make_join_rel() をラップする関数
1656 * ヒントにしたがって、enabele_* パラメータを変更した上で、make_join_rel()を
1660 pg_hint_plan_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
1667 joinrelids = bms_union(rel1->relids, rel2->relids);
1668 hint = scan_join_hint(joinrelids);
1669 bms_free(joinrelids);
1672 return make_join_rel(root, rel1, rel2);
1674 save_nestlevel = NewGUCNestLevel();
1676 set_join_config_options(hint->enforce_mask, global->context);
1678 rel = make_join_rel(root, rel1, rel2);
1681 * Restore the GUC variables we set above.
1683 AtEOXact_GUC(true, save_nestlevel);
1689 pg_hint_plan_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
1692 * pg_hint_planが無効、または有効なヒントが1つも指定されなかった場合は、標準
1697 if (prev_join_search)
1698 return (*prev_join_search) (root, levels_needed, initial_rels);
1699 else if (enable_geqo && levels_needed >= geqo_threshold)
1700 return geqo(root, levels_needed, initial_rels);
1702 return standard_join_search(root, levels_needed, initial_rels);
1705 rebuild_join_hints(global, root, levels_needed, initial_rels);
1706 rebuild_scan_path(global, root, levels_needed, initial_rels);
1709 * GEQOを使用する条件を満たした場合は、GEQOを用いた結合方式の検索を行う。
1710 * このとき、スキャン方式のヒントとSetヒントのみが有効になり、結合方式や結合
1711 * 順序はヒント句は無効になりGEQOのアルゴリズムで決定される。
1713 if (enable_geqo && levels_needed >= geqo_threshold)
1714 return geqo(root, levels_needed, initial_rels);
1716 return standard_join_search_org(root, levels_needed, initial_rels);
1719 #define standard_join_search standard_join_search_org
1720 #define join_search_one_level pg_hint_plan_join_search_one_level
1721 #define make_join_rel pg_hint_plan_make_join_rel