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 BLOCK_COMMENT_START "/*"
31 #define BLOCK_COMMENT_END "*/"
32 #define HINT_COMMENT_KEYWORD "+"
33 #define HINT_START BLOCK_COMMENT_START HINT_COMMENT_KEYWORD
34 #define HINT_END BLOCK_COMMENT_END
37 #define HINT_SEQSCAN "SeqScan"
38 #define HINT_INDEXSCAN "IndexScan"
39 #define HINT_BITMAPSCAN "BitmapScan"
40 #define HINT_TIDSCAN "TidScan"
41 #define HINT_NOSEQSCAN "NoSeqScan"
42 #define HINT_NOINDEXSCAN "NoIndexScan"
43 #define HINT_NOBITMAPSCAN "NoBitmapScan"
44 #define HINT_NOTIDSCAN "NoTidScan"
45 #if PG_VERSION_NUM >= 90200
46 #define HINT_INDEXONLYSCAN "IndexonlyScan"
47 #define HINT_NOINDEXONLYSCAN "NoIndexonlyScan"
49 #define HINT_NESTLOOP "NestLoop"
50 #define HINT_MERGEJOIN "MergeJoin"
51 #define HINT_HASHJOIN "HashJoin"
52 #define HINT_NONESTLOOP "NoNestLoop"
53 #define HINT_NOMERGEJOIN "NoMergeJoin"
54 #define HINT_NOHASHJOIN "NoHashJoin"
55 #define HINT_LEADING "Leading"
56 #define HINT_SET "Set"
59 #define HINT_ARRAY_DEFAULT_INITSIZE 8
61 #define parse_ereport(str, detail) \
62 ereport(pg_hint_plan_parse_messages, \
63 (errmsg("hint syntax error at or near \"%s\"", (str)), \
66 #define skip_space(str) \
67 while (isspace(*str)) \
72 ENABLE_SEQSCAN = 0x01,
73 ENABLE_INDEXSCAN = 0x02,
74 ENABLE_BITMAPSCAN = 0x04,
75 ENABLE_TIDSCAN = 0x08,
76 ENABLE_NESTLOOP = 0x10,
77 ENABLE_MERGEJOIN = 0x20,
78 ENABLE_HASHJOIN = 0x40
81 #define ENABLE_ALL_SCAN (ENABLE_SEQSCAN | ENABLE_INDEXSCAN | ENABLE_BITMAPSCAN \
83 #define ENABLE_ALL_JOIN (ENABLE_NESTLOOP | ENABLE_MERGEJOIN | ENABLE_HASHJOIN)
85 typedef struct Hint Hint;
86 typedef struct PlanHint PlanHint;
88 typedef Hint *(*HintCreateFunction) (char *hint_str, char *keyword);
89 typedef void (*HintDeleteFunction) (Hint *hint);
90 typedef const char *(*HintParseFunction) (Hint *hint, PlanHint *plan, Query *parse, const char *str);
93 typedef enum HintStatus
95 HINT_STATE_NOTUSED = 0, /* specified relation not used in query */
96 HINT_STATE_DUPLICATION, /* specified hint duplication */
97 HINT_STATE_USED, /* hint is used */
98 /* execute error (parse error does not include it) */
102 /* common data for all hints. */
105 const char *hint_str; /* must not do pfree */
106 const char *keyword; /* must not do pfree */
108 HintDeleteFunction delete_func;
109 HintParseFunction parser_func;
112 /* scan method hints */
113 typedef struct ScanMethodHint
118 unsigned char enforce_mask;
121 /* join method hints */
122 typedef struct JoinMethodHint
127 unsigned char enforce_mask;
131 /* join order hints */
132 typedef struct LeadingHint
135 List *relations; /* relation names specified in Leading hint */
138 /* change a run-time parameter hints */
139 typedef struct SetHint
142 char *name; /* name of variable */
147 * Describes a context of hint processing.
151 char *hint_str; /* original hint string */
153 /* for scan method hints */
154 int nscan_hints; /* # of valid scan hints */
155 int max_scan_hints; /* # of slots for scan hints */
156 ScanMethodHint **scan_hints; /* parsed scan hints */
158 /* for join method hints */
159 int njoin_hints; /* # of valid join hints */
160 int max_join_hints; /* # of slots for join hints */
161 JoinMethodHint **join_hints; /* parsed join hints */
163 int nlevel; /* # of relations to be joined */
164 List **join_hint_level;
166 /* for Leading hints */
167 List *leading; /* relation names specified in Leading hint */
170 GucContext context; /* which GUC parameters can we set? */
171 List *set_hints; /* parsed Set hints */
175 * Describes a hint parser module which is bound with particular hint keyword.
177 typedef struct HintParser
181 HintCreateFunction create_func;
184 /* Module callbacks */
188 static PlannedStmt *pg_hint_plan_planner(Query *parse, int cursorOptions,
189 ParamListInfo boundParams);
190 static void pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
191 bool inhparent, RelOptInfo *rel);
192 static RelOptInfo *pg_hint_plan_join_search(PlannerInfo *root, int levels_needed,
195 static Hint *ScanMethodHintCreate(char *hint_str, char *keyword);
196 static void ScanMethodHintDelete(ScanMethodHint *hint);
197 static const char *ScanMethodHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str);
198 static Hint *JoinMethodHintCreate(char *hint_str, char *keyword);
199 static void JoinMethodHintDelete(JoinMethodHint *hint);
200 static const char *JoinMethodHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str);
201 static Hint *LeadingHintCreate(char *hint_str, char *keyword);
202 static void LeadingHintDelete(LeadingHint *hint);
203 static const char *LeadingHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str);
204 static Hint *SetHintCreate(char *hint_str, char *keyword);
205 static void SetHintDelete(SetHint *hint);
206 static const char *SetHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str);
208 static const char *OrderedHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str);
211 RelOptInfo *standard_join_search_org(PlannerInfo *root, int levels_needed, List *initial_rels);
212 void pg_hint_plan_join_search_one_level(PlannerInfo *root, int level);
213 static void make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
214 static void make_rels_by_clauseless_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
215 static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel);
216 static void set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte);
220 static bool pg_hint_plan_enable = true;
221 static bool pg_hint_plan_debug_print = false;
222 static int pg_hint_plan_parse_messages = INFO;
224 static const struct config_enum_entry parse_messages_level_options[] = {
225 {"debug", DEBUG2, true},
226 {"debug5", DEBUG5, false},
227 {"debug4", DEBUG4, false},
228 {"debug3", DEBUG3, false},
229 {"debug2", DEBUG2, false},
230 {"debug1", DEBUG1, false},
232 {"info", INFO, false},
233 {"notice", NOTICE, false},
234 {"warning", WARNING, false},
235 {"error", ERROR, false},
237 {"fatal", FATAL, true},
238 {"panic", PANIC, true},
243 /* Saved hook values in case of unload */
244 static planner_hook_type prev_planner_hook = NULL;
245 static get_relation_info_hook_type prev_get_relation_info = NULL;
246 static join_search_hook_type prev_join_search = NULL;
248 /* フック関数をまたがって使用する情報を管理する */
249 static PlanHint *global = NULL;
251 static const HintParser parsers[] = {
252 {HINT_SEQSCAN, true, ScanMethodHintCreate},
253 {HINT_INDEXSCAN, true, ScanMethodHintCreate},
254 {HINT_BITMAPSCAN, true, ScanMethodHintCreate},
255 {HINT_TIDSCAN, true, ScanMethodHintCreate},
256 {HINT_NOSEQSCAN, true, ScanMethodHintCreate},
257 {HINT_NOINDEXSCAN, true, ScanMethodHintCreate},
258 {HINT_NOBITMAPSCAN, true, ScanMethodHintCreate},
259 {HINT_NOTIDSCAN, true, ScanMethodHintCreate},
260 #if PG_VERSION_NUM >= 90200
261 {HINT_INDEXONLYSCAN, true, ScanMethodHintCreate},
262 {HINT_NOINDEXONLYSCAN, true, ScanMethodHintCreate},
264 {HINT_NESTLOOP, true, JoinMethodHintCreate},
265 {HINT_MERGEJOIN, true, JoinMethodHintCreate},
266 {HINT_HASHJOIN, true, JoinMethodHintCreate},
267 {HINT_NONESTLOOP, true, JoinMethodHintCreate},
268 {HINT_NOMERGEJOIN, true, JoinMethodHintCreate},
269 {HINT_NOHASHJOIN, true, JoinMethodHintCreate},
270 {HINT_LEADING, true, LeadingHintCreate},
271 {HINT_SET, true, SetHintCreate},
276 * Module load callbacks
281 /* Define custom GUC variables. */
282 DefineCustomBoolVariable("pg_hint_plan.enable",
283 "Instructions or hints to the planner using block comments.",
285 &pg_hint_plan_enable,
293 DefineCustomBoolVariable("pg_hint_plan.debug_print",
294 "Logs each query's parse results of the hint.",
296 &pg_hint_plan_debug_print,
304 DefineCustomEnumVariable("pg_hint_plan.parse_messages",
305 "Messege level of the parse error.",
307 &pg_hint_plan_parse_messages,
309 parse_messages_level_options,
317 prev_planner_hook = planner_hook;
318 planner_hook = pg_hint_plan_planner;
319 prev_get_relation_info = get_relation_info_hook;
320 get_relation_info_hook = pg_hint_plan_get_relation_info;
321 prev_join_search = join_search_hook;
322 join_search_hook = pg_hint_plan_join_search;
326 * Module unload callback
332 /* Uninstall hooks. */
333 planner_hook = prev_planner_hook;
334 get_relation_info_hook = prev_get_relation_info;
335 join_search_hook = prev_join_search;
339 ScanMethodHintCreate(char *hint_str, char *keyword)
341 ScanMethodHint *hint;
343 hint = palloc(sizeof(ScanMethodHint));
344 hint->base.hint_str = hint_str;
345 hint->base.keyword = keyword;
346 hint->base.used = false;
347 hint->base.delete_func = (HintDeleteFunction) ScanMethodHintDelete;
348 hint->base.parser_func = (HintParseFunction) ScanMethodHintParse;
349 hint->relname = NULL;
350 hint->indexnames = NIL;
351 hint->enforce_mask = 0;
353 return (Hint *) hint;
357 ScanMethodHintDelete(ScanMethodHint *hint)
363 pfree(hint->relname);
364 list_free_deep(hint->indexnames);
369 JoinMethodHintCreate(char *hint_str, char *keyword)
371 JoinMethodHint *hint;
373 hint = palloc(sizeof(JoinMethodHint));
374 hint->base.hint_str = hint_str;
375 hint->base.keyword = keyword;
376 hint->base.used = false;
377 hint->base.delete_func = (HintDeleteFunction) JoinMethodHintDelete;
378 hint->base.parser_func = (HintParseFunction) JoinMethodHintParse;
380 hint->relnames = NULL;
381 hint->enforce_mask = 0;
382 hint->joinrelids = NULL;
384 return (Hint *) hint;
388 JoinMethodHintDelete(JoinMethodHint *hint)
397 for (i = 0; i < hint->nrels; i++)
398 pfree(hint->relnames[i]);
399 pfree(hint->relnames);
401 bms_free(hint->joinrelids);
406 LeadingHintCreate(char *hint_str, char *keyword)
410 hint = palloc(sizeof(LeadingHint));
411 hint->base.hint_str = hint_str;
412 hint->base.keyword = keyword;
413 hint->base.used = false;
414 hint->base.delete_func = (HintDeleteFunction)LeadingHintDelete;
415 hint->base.parser_func = (HintParseFunction) LeadingHintParse;
416 hint->relations = NIL;
418 return (Hint *) hint;
422 LeadingHintDelete(LeadingHint *hint)
427 list_free_deep(hint->relations);
432 SetHintCreate(char *hint_str, char *keyword)
436 hint = palloc(sizeof(SetHint));
437 hint->base.hint_str = hint_str;
438 hint->base.keyword = keyword;
439 hint->base.used = false;
440 hint->base.delete_func = (HintDeleteFunction) SetHintDelete;
441 hint->base.parser_func = (HintParseFunction) SetHintParse;
445 return (Hint *) hint;
449 SetHintDelete(SetHint *hint)
466 hint = palloc(sizeof(PlanHint));
467 hint->hint_str = NULL;
468 hint->nscan_hints = 0;
469 hint->max_scan_hints = 0;
470 hint->scan_hints = NULL;
471 hint->njoin_hints = 0;
472 hint->max_join_hints = 0;
473 hint->join_hints = NULL;
475 hint->join_hint_level = NULL;
477 hint->context = superuser() ? PGC_SUSET : PGC_USERSET;
478 hint->set_hints = NIL;
484 PlanHintDelete(PlanHint *hint)
493 pfree(hint->hint_str);
495 for (i = 0; i < hint->nscan_hints; i++)
496 ScanMethodHintDelete(hint->scan_hints[i]);
497 if (hint->scan_hints)
498 pfree(hint->scan_hints);
500 for (i = 0; i < hint->njoin_hints; i++)
501 JoinMethodHintDelete(hint->join_hints[i]);
502 if (hint->join_hints)
503 pfree(hint->join_hints);
505 for (i = 2; i <= hint->nlevel; i++)
506 list_free(hint->join_hint_level[i]);
507 if (hint->join_hint_level)
508 pfree(hint->join_hint_level);
510 list_free_deep(hint->leading);
512 foreach(l, hint->set_hints)
513 SetHintDelete((SetHint *) lfirst(l));
514 list_free(hint->set_hints);
520 PlanHintIsempty(PlanHint *hint)
522 if (hint->nscan_hints == 0 &&
523 hint->njoin_hints == 0 &&
524 hint->leading == NIL &&
525 hint->set_hints == NIL)
531 /* TODO オブジェクト名のクォート処理を追加 */
533 PlanHintDump(PlanHint *hint)
538 bool is_first = true;
542 elog(LOG, "no hint");
546 initStringInfo(&buf);
547 appendStringInfo(&buf, "/*\n");
548 for (i = 0; i < hint->nscan_hints; i++)
550 ScanMethodHint *h = hint->scan_hints[i];
552 switch(h->enforce_mask)
554 case(ENABLE_SEQSCAN):
555 appendStringInfo(&buf, "%s(", HINT_SEQSCAN);
557 case(ENABLE_INDEXSCAN):
558 appendStringInfo(&buf, "%s(", HINT_INDEXSCAN);
560 case(ENABLE_BITMAPSCAN):
561 appendStringInfo(&buf, "%s(", HINT_BITMAPSCAN);
563 case(ENABLE_TIDSCAN):
564 appendStringInfo(&buf, "%s(", HINT_TIDSCAN);
566 case(ENABLE_INDEXSCAN | ENABLE_BITMAPSCAN | ENABLE_TIDSCAN):
567 appendStringInfo(&buf, "%s(", HINT_NOSEQSCAN);
569 case(ENABLE_SEQSCAN | ENABLE_BITMAPSCAN | ENABLE_TIDSCAN):
570 appendStringInfo(&buf, "%s(", HINT_NOINDEXSCAN);
572 case(ENABLE_SEQSCAN | ENABLE_INDEXSCAN | ENABLE_TIDSCAN):
573 appendStringInfo(&buf, "%s(", HINT_NOBITMAPSCAN);
575 case(ENABLE_SEQSCAN | ENABLE_INDEXSCAN | ENABLE_BITMAPSCAN):
576 appendStringInfo(&buf, "%s(", HINT_NOTIDSCAN);
579 appendStringInfoString(&buf, "\?\?\?(");
582 appendStringInfo(&buf, "%s", h->relname);
583 foreach(n, h->indexnames)
584 appendStringInfo(&buf, " %s", (char *) lfirst(n));
585 appendStringInfoString(&buf, ")\n");
588 for (i = 0; i < hint->njoin_hints; i++)
590 JoinMethodHint *h = hint->join_hints[i];
592 switch(h->enforce_mask)
594 case(ENABLE_NESTLOOP):
595 appendStringInfo(&buf, "%s(", HINT_NESTLOOP);
597 case(ENABLE_MERGEJOIN):
598 appendStringInfo(&buf, "%s(", HINT_MERGEJOIN);
600 case(ENABLE_HASHJOIN):
601 appendStringInfo(&buf, "%s(", HINT_HASHJOIN);
603 case(ENABLE_ALL_JOIN ^ ENABLE_NESTLOOP):
604 appendStringInfo(&buf, "%s(", HINT_NONESTLOOP);
606 case(ENABLE_ALL_JOIN ^ ENABLE_MERGEJOIN):
607 appendStringInfo(&buf, "%s(", HINT_NOMERGEJOIN);
609 case(ENABLE_ALL_JOIN ^ ENABLE_HASHJOIN):
610 appendStringInfo(&buf, "%s(", HINT_NOHASHJOIN);
612 case(ENABLE_ALL_JOIN):
615 appendStringInfoString(&buf, "\?\?\?(");
618 appendStringInfo(&buf, "%s", h->relnames[0]);
619 for (i = 1; i < h->nrels; i++)
620 appendStringInfo(&buf, " %s", h->relnames[i]);
621 appendStringInfoString(&buf, ")\n");
624 foreach(l, hint->set_hints)
626 SetHint *h = (SetHint *) lfirst(l);
627 appendStringInfo(&buf, "%s(%s %s)\n", HINT_SET, h->name, h->value);
630 foreach(l, hint->leading)
634 appendStringInfo(&buf, "%s(%s", HINT_LEADING, (char *)lfirst(l));
638 appendStringInfo(&buf, " %s", (char *)lfirst(l));
641 appendStringInfoString(&buf, ")\n");
643 appendStringInfoString(&buf, "*/");
645 elog(LOG, "%s", buf.data);
651 RelnameCmp(const void *a, const void *b)
653 const char *relnamea = *((const char **) a);
654 const char *relnameb = *((const char **) b);
656 return strcmp(relnamea, relnameb);
660 ScanMethodHintCmp(const void *a, const void *b, bool order)
662 const ScanMethodHint *hinta = *((const ScanMethodHint **) a);
663 const ScanMethodHint *hintb = *((const ScanMethodHint **) b);
666 if ((result = RelnameCmp(&hinta->relname, &hintb->relname)) != 0)
671 return hinta->base.hint_str - hintb->base.hint_str;
677 ScanMethodHintCmpIsOrder(const void *a, const void *b)
679 return ScanMethodHintCmp(a, b, true);
683 JoinMethodHintCmp(const void *a, const void *b, bool order)
685 const JoinMethodHint *hinta = *((const JoinMethodHint **) a);
686 const JoinMethodHint *hintb = *((const JoinMethodHint **) b);
688 if (hinta->nrels == hintb->nrels)
691 for (i = 0; i < hinta->nrels; i++)
694 if ((result = RelnameCmp(&hinta->relnames[i], &hintb->relnames[i])) != 0)
700 return hinta->base.hint_str - hintb->base.hint_str;
705 return hinta->nrels - hintb->nrels;
709 JoinMethodHintCmpIsOrder(const void *a, const void *b)
711 return JoinMethodHintCmp(a, b, true);
714 #if PG_VERSION_NUM < 90200
716 set_config_option_wrapper(const char *name, const char *value,
717 GucContext context, GucSource source,
718 GucAction action, bool changeVal, int elevel)
721 MemoryContext ccxt = CurrentMemoryContext;
725 result = set_config_option(name, value, context, source,
736 ecxt = MemoryContextSwitchTo(ccxt);
737 errdata = CopyErrorData();
738 ereport(elevel, (errcode(errdata->sqlerrcode),
739 errmsg("%s", errdata->message),
740 errdata->detail ? errdetail("%s", errdata->detail) : 0,
741 errdata->hint ? errhint("%s", errdata->hint) : 0));
742 FreeErrorData(errdata);
744 MemoryContextSwitchTo(ecxt);
751 #define set_config_option(name, value, context, source, \
752 action, changeVal, elevel) \
753 set_config_option_wrapper(name, value, context, source, \
754 action, changeVal, elevel)
758 set_config_options(List *options, GucContext context)
764 save_nestlevel = NewGUCNestLevel();
768 SetHint *hint = (SetHint *) lfirst(l);
771 result = set_config_option(hint->name, hint->value, context,
772 PGC_S_SESSION, GUC_ACTION_SAVE, true,
773 pg_hint_plan_parse_messages);
776 return save_nestlevel;
779 #define SET_CONFIG_OPTION(name, enforce_mask, type_bits) \
780 set_config_option((name), \
781 ((enforce_mask) & (type_bits)) ? "true" : "false", \
782 context, PGC_S_SESSION, GUC_ACTION_SAVE, true, ERROR)
785 set_join_config_options(unsigned char enforce_mask, GucContext context)
787 SET_CONFIG_OPTION("enable_nestloop", enforce_mask, ENABLE_NESTLOOP);
788 SET_CONFIG_OPTION("enable_mergejoin", enforce_mask, ENABLE_MERGEJOIN);
789 SET_CONFIG_OPTION("enable_hashjoin", enforce_mask, ENABLE_HASHJOIN);
793 set_scan_config_options(unsigned char enforce_mask, GucContext context)
795 SET_CONFIG_OPTION("enable_seqscan", enforce_mask, ENABLE_SEQSCAN);
796 SET_CONFIG_OPTION("enable_indexscan", enforce_mask, ENABLE_INDEXSCAN);
797 SET_CONFIG_OPTION("enable_bitmapscan", enforce_mask, ENABLE_BITMAPSCAN);
798 SET_CONFIG_OPTION("enable_tidscan", enforce_mask, ENABLE_TIDSCAN);
799 #if PG_VERSION_NUM >= 90200
800 SET_CONFIG_OPTION("enable_indexonlyscan", enforce_mask, ENABLE_INDEXSCAN);
809 parse_keyword(const char *str, StringInfo buf)
813 while (!isspace(*str) && *str != '(' && *str != '\0')
814 appendStringInfoChar(buf, *str++);
820 skip_opened_parenthesis(const char *str)
826 parse_ereport(str, ("Opened parenthesis is necessary."));
836 skip_closed_parenthesis(const char *str)
842 parse_ereport(str, ("Closed parenthesis is necessary."));
852 * 二重引用符で囲まれているかもしれないトークンを読み取り word 引数に palloc
853 * で確保したバッファに格納してそのポインタを返す。
855 * 正常にパースできた場合は残りの文字列の先頭位置を、異常があった場合は NULL を
859 parse_quote_value(const char *str, char **word, char *value_type)
867 initStringInfo(&buf);
880 /* 二重引用符が閉じられていない場合はパース中断 */
884 parse_ereport(str, ("Unterminated quoted %s.", value_type));
889 * エスケープ対象のダブルクウォートをスキップする。
890 * もしブロックコメントの開始文字列や終了文字列もオブジェクト名とし
891 * て使用したい場合は、/ と * もエスケープ対象とすることで使用できる
892 * が、処理対象としていない。もしテーブル名にこれらの文字が含まれる
893 * 場合は、エイリアスを指定する必要がある。
903 if (isspace(*str) || *str == ')' || *str == '\0')
906 appendStringInfoCharMacro(&buf, *str++);
912 parse_ereport(str, ("%s is necessary.", value_type));
922 skip_option_delimiter(const char *str)
930 parse_ereport(str, ("Must be specified space."));
938 parse_hints(PlanHint *plan, Query *parse, const char *str)
943 initStringInfo(&buf);
946 const HintParser *parser;
948 /* in error message, we output the comment including the keyword. */
951 /* parse only the keyword of the hint. */
952 resetStringInfo(&buf);
953 str = parse_keyword(str, &buf);
955 for (parser = parsers; parser->keyword != NULL; parser++)
957 char *keyword = parser->keyword;
960 if (strcasecmp(buf.data, keyword) != 0)
963 hint = parser->create_func(head, keyword);
965 if (parser->have_paren)
967 /* parser of each hint does parse in a parenthesis. */
968 if ((str = skip_opened_parenthesis(str)) == NULL ||
969 (str = hint->parser_func(hint, plan, parse, str)) == NULL ||
970 (str = skip_closed_parenthesis(str)) == NULL)
972 hint->delete_func(hint);
979 if ((str = hint->parser_func(hint, plan, parse, str)) == NULL)
981 hint->delete_func(hint);
987 * 直前のヒントに括弧の指定がなければ次のヒントの間に空白が必要
989 if (!isspace(*str) && *str == '\0')
990 parse_ereport(str, ("Delimiter of the hint is necessary."));
998 if (parser->keyword == NULL)
1000 parse_ereport(head, ("Keyword \"%s\" does not exist.", buf.data));
1012 * Do basic parsing of the query head comment.
1015 parse_head_comment(Query *parse)
1024 /* get client-supplied query string. */
1025 p = debug_query_string;
1029 /* extract query head comment. */
1030 len = strlen(HINT_START);
1032 if (strncmp(p, HINT_START, len))
1039 /* find hint end keyword. */
1040 if ((tail = strstr(p, HINT_END)) == NULL)
1042 parse_ereport(head, ("unterminated block comment"));
1046 /* 入れ子にしたブロックコメントはサポートしない */
1047 if ((head = strstr(p, BLOCK_COMMENT_START)) != NULL && head < tail)
1048 parse_ereport(head, ("block comments nest doesn't supported"));
1052 head = palloc(len + 1);
1053 memcpy(head, p, len);
1057 plan = PlanHintCreate();
1058 plan->hint_str = head;
1060 /* parse each hint. */
1061 if (!parse_hints(plan, parse, p))
1064 /* 重複したScan条件をを除外する */
1065 qsort(plan->scan_hints, plan->nscan_hints, sizeof(ScanMethodHint *), ScanMethodHintCmpIsOrder);
1066 for (i = 0; i < plan->nscan_hints - 1;)
1068 int result = ScanMethodHintCmp(plan->scan_hints + i,
1069 plan->scan_hints + i + 1, false);
1074 /* 後で指定したヒントを有効にする */
1075 plan->nscan_hints--;
1076 memmove(plan->scan_hints + i, plan->scan_hints + i + 1,
1077 sizeof(ScanMethodHint *) * (plan->nscan_hints - i));
1081 /* 重複したJoin条件をを除外する */
1082 qsort(plan->join_hints, plan->njoin_hints, sizeof(JoinMethodHint *), JoinMethodHintCmpIsOrder);
1083 for (i = 0; i < plan->njoin_hints - 1;)
1085 int result = JoinMethodHintCmp(plan->join_hints + i,
1086 plan->join_hints + i + 1, false);
1091 /* 後で指定したヒントを有効にする */
1092 plan->njoin_hints--;
1093 memmove(plan->join_hints + i, plan->join_hints + i + 1,
1094 sizeof(JoinMethodHint *) * (plan->njoin_hints - i));
1102 * スキャン方式ヒントのカッコ内をパースする
1105 ScanMethodHintParse(Hint *base_hint, PlanHint *plan, Query *parse, const char *str)
1107 ScanMethodHint *hint = (ScanMethodHint *) base_hint;
1108 const char *keyword = hint->base.keyword;
1111 * スキャン方式のヒントでリレーション名が読み取れない場合はヒント無効
1113 if ((str = parse_quote_value(str, &hint->relname, "ralation name")) == NULL)
1119 * インデックスリストを受け付けるヒントであれば、インデックス参照をパース
1122 if (strcmp(keyword, HINT_INDEXSCAN) == 0 ||
1123 #if PG_VERSION_NUM >= 90200
1124 strcmp(keyword, HINT_INDEXONLYSCAN) == 0 ||
1126 strcmp(keyword, HINT_BITMAPSCAN) == 0)
1128 while (*str != ')' && *str != '\0')
1132 str = parse_quote_value(str, &indexname, "index name");
1136 hint->indexnames = lappend(hint->indexnames, indexname);
1141 /* カッコが閉じていなければヒント無効。 */
1142 skip_space(str); /* just in case */
1145 parse_ereport(str, ("Closed parenthesis is necessary."));
1150 * ヒントごとに決まっている許容スキャン方式をビットマスクとして設定
1152 if (strcasecmp(keyword, HINT_SEQSCAN) == 0)
1153 hint->enforce_mask = ENABLE_SEQSCAN;
1154 else if (strcasecmp(keyword, HINT_INDEXSCAN) == 0)
1155 hint->enforce_mask = ENABLE_INDEXSCAN;
1156 else if (strcasecmp(keyword, HINT_BITMAPSCAN) == 0)
1157 hint->enforce_mask = ENABLE_BITMAPSCAN;
1158 else if (strcasecmp(keyword, HINT_TIDSCAN) == 0)
1159 hint->enforce_mask = ENABLE_TIDSCAN;
1160 else if (strcasecmp(keyword, HINT_NOSEQSCAN) == 0)
1161 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_SEQSCAN;
1162 else if (strcasecmp(keyword, HINT_NOINDEXSCAN) == 0)
1163 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_INDEXSCAN;
1164 else if (strcasecmp(keyword, HINT_NOBITMAPSCAN) == 0)
1165 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_BITMAPSCAN;
1166 else if (strcasecmp(keyword, HINT_NOTIDSCAN) == 0)
1167 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_TIDSCAN;
1170 parse_ereport(str, ("unrecognized hint keyword \"%s\"", keyword));
1175 * 出来上がったヒント情報を追加。スロットが足りない場合は二倍に拡張する。
1177 if (plan->nscan_hints == 0)
1179 plan->max_scan_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1180 plan->scan_hints = palloc(sizeof(ScanMethodHint *) * plan->max_scan_hints);
1182 else if (plan->nscan_hints == plan->max_scan_hints)
1184 plan->max_scan_hints *= 2;
1185 plan->scan_hints = repalloc(plan->scan_hints,
1186 sizeof(ScanMethodHint *) * plan->max_scan_hints);
1188 plan->scan_hints[plan->nscan_hints] = hint;
1189 plan->nscan_hints++;
1195 JoinMethodHintParse(Hint *base_hint, PlanHint *plan, Query *parse, const char *str)
1198 JoinMethodHint *hint = (JoinMethodHint *) base_hint;
1199 const char *keyword = hint->base.keyword;
1203 hint->relnames = palloc(sizeof(char *));
1205 while ((str = parse_quote_value(str, &relname, "table name")) != NULL)
1208 hint->relnames = repalloc(hint->relnames, sizeof(char *) * hint->nrels);
1209 hint->relnames[hint->nrels - 1] = relname;
1219 /* Join 対象のテーブルは最低でも2つ指定する必要がある */
1220 if (hint->nrels < 2)
1222 parse_ereport(str, ("Specified relation more than two."));
1227 qsort(hint->relnames, hint->nrels, sizeof(char *), RelnameCmp);
1229 if (strcasecmp(keyword, HINT_NESTLOOP) == 0)
1230 hint->enforce_mask = ENABLE_NESTLOOP;
1231 else if (strcasecmp(keyword, HINT_MERGEJOIN) == 0)
1232 hint->enforce_mask = ENABLE_MERGEJOIN;
1233 else if (strcasecmp(keyword, HINT_HASHJOIN) == 0)
1234 hint->enforce_mask = ENABLE_HASHJOIN;
1235 else if (strcasecmp(keyword, HINT_NONESTLOOP) == 0)
1236 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_NESTLOOP;
1237 else if (strcasecmp(keyword, HINT_NOMERGEJOIN) == 0)
1238 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_MERGEJOIN;
1239 else if (strcasecmp(keyword, HINT_NOHASHJOIN) == 0)
1240 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_HASHJOIN;
1243 parse_ereport(str, ("unrecognized hint keyword \"%s\"", keyword));
1247 if (plan->njoin_hints == 0)
1249 plan->max_join_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1250 plan->join_hints = palloc(sizeof(JoinMethodHint *) * plan->max_join_hints);
1252 else if (plan->njoin_hints == plan->max_join_hints)
1254 plan->max_join_hints *= 2;
1255 plan->join_hints = repalloc(plan->join_hints,
1256 sizeof(JoinMethodHint *) * plan->max_join_hints);
1259 plan->join_hints[plan->njoin_hints] = hint;
1260 plan->njoin_hints++;
1266 LeadingHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str)
1271 * すでに指定済みの場合は、後で指定したヒントが有効にするため、登録済みの
1274 list_free_deep(plan->leading);
1275 plan->leading = NIL;
1277 while ((str = parse_quote_value(str, &relname, "relation name")) != NULL)
1281 plan->leading = lappend(plan->leading, relname);
1290 parse_ereport(str, ("Must be specified space."));
1295 /* テーブル指定が1つのみの場合は、ヒントを無効にし、パースを続ける */
1296 if (list_length(plan->leading) == 1)
1298 parse_ereport(str, ("In %s hint, specified relation name 2 or more.", HINT_LEADING));
1299 list_free_deep(plan->leading);
1300 plan->leading = NIL;
1307 SetHintParse(Hint *base_hint, PlanHint *plan, Query *parse, const char *str)
1309 SetHint *hint = (SetHint *) base_hint;
1311 if ((str = parse_quote_value(str, &hint->name, "parameter name")) == NULL ||
1312 (str = skip_option_delimiter(str)) == NULL ||
1313 (str = parse_quote_value(str, &hint->value, "parameter value")) == NULL)
1319 parse_ereport(str, ("Closed parenthesis is necessary."));
1322 plan->set_hints = lappend(plan->set_hints, hint);
1329 * Oracle の ORDERD ヒントの実装
1332 OrderedHintParse(Hint *base_hint, PlanHint *plan, Query *parse, const char *str);
1334 SetHint *hint = (SetHint *) base_hint;
1336 hint->name = pstrdup("join_collapse_limit");
1337 hint->value = pstrdup("1");
1338 plan->set_hints = lappend(plan->set_hints, hint);
1344 static PlannedStmt *
1345 pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
1348 PlannedStmt *result;
1351 * hintが指定されない、または空のhintを指定された場合は通常のparser処理をお
1353 * 他のフック関数で実行されるhint処理をスキップするために、global 変数をNULL
1356 if (!pg_hint_plan_enable ||
1357 (global = parse_head_comment(parse)) == NULL ||
1358 PlanHintIsempty(global))
1360 PlanHintDelete(global);
1363 if (prev_planner_hook)
1364 return (*prev_planner_hook) (parse, cursorOptions, boundParams);
1366 return standard_planner(parse, cursorOptions, boundParams);
1369 /* Set hint で指定されたGUCパラメータを設定する */
1370 save_nestlevel = set_config_options(global->set_hints, global->context);
1372 if (global->leading != NIL)
1373 set_join_config_options(0, global->context);
1376 * TODO ビュー定義で指定したテーブル数が1つの場合にもこのタイミングでGUCを変更する必
1379 if (list_length(parse->rtable) == 1 &&
1380 ((RangeTblEntry *) linitial(parse->rtable))->rtekind == RTE_RELATION)
1383 RangeTblEntry *rte = linitial(parse->rtable);
1385 for (i = 0; i < global->nscan_hints; i++)
1387 ScanMethodHint *hint = global->scan_hints[i];
1389 if (RelnameCmp(&rte->eref->aliasname, &hint->relname) != 0)
1390 parse_ereport(hint->base.hint_str, ("Relation \"%s\" does not exist.", hint->relname));
1392 set_scan_config_options(hint->enforce_mask, global->context);
1396 if (prev_planner_hook)
1397 result = (*prev_planner_hook) (parse, cursorOptions, boundParams);
1399 result = standard_planner(parse, cursorOptions, boundParams);
1402 * Restore the GUC variables we set above.
1404 AtEOXact_GUC(true, save_nestlevel);
1406 if (pg_hint_plan_debug_print)
1408 PlanHintDump(global);
1410 elog_node_display(INFO, "rtable", parse->rtable, true);
1414 PlanHintDelete(global);
1421 * aliasnameと一致するSCANヒントを探す
1423 static ScanMethodHint *
1424 find_scan_hint(RangeTblEntry *rte)
1428 for (i = 0; i < global->nscan_hints; i++)
1430 ScanMethodHint *hint = global->scan_hints[i];
1432 if (RelnameCmp(&rte->eref->aliasname, &hint->relname) == 0)
1440 pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
1441 bool inhparent, RelOptInfo *rel)
1443 ScanMethodHint *hint;
1448 if (prev_get_relation_info)
1449 (*prev_get_relation_info) (root, relationObjectId, inhparent, rel);
1451 /* 有効なヒントが指定されなかった場合は処理をスキップする。 */
1455 if (rel->reloptkind != RELOPT_BASEREL)
1458 if ((hint = find_scan_hint(root->simple_rte_array[rel->relid])) == NULL)
1461 /* インデックスを全て削除し、スキャンに使えなくする */
1462 if (hint->enforce_mask == ENABLE_SEQSCAN)
1464 list_free_deep(rel->indexlist);
1465 rel->indexlist = NIL;
1470 /* 後でパスを作り直すため、ここではなにもしない */
1471 if (hint->indexnames == NULL)
1474 /* 指定されたインデックスのみをのこす */
1476 for (cell = list_head(rel->indexlist); cell; cell = next)
1478 IndexOptInfo *info = (IndexOptInfo *) lfirst(cell);
1479 char *indexname = get_rel_name(info->indexoid);
1481 bool use_index = false;
1485 foreach(l, hint->indexnames)
1487 if (RelnameCmp(&indexname, &lfirst(l)) == 0)
1495 rel->indexlist = list_delete_cell(rel->indexlist, cell, prev);
1504 scan_relid_aliasname(PlannerInfo *root, char *aliasname, bool check_ambiguous, const char *str)
1506 /* TODO refnameRangeTblEntry を参考 */
1510 for (i = 1; i < root->simple_rel_array_size; i++)
1512 if (root->simple_rel_array[i] == NULL)
1515 Assert(i == root->simple_rel_array[i]->relid);
1517 if (RelnameCmp(&aliasname, &root->simple_rte_array[i]->eref->aliasname)
1521 if (!check_ambiguous)
1525 parse_ereport(str, ("relation name \"%s\" is ambiguous", aliasname));
1534 * relidビットマスクと一致するヒントを探す
1536 static JoinMethodHint *
1537 scan_join_hint(Relids joinrelids)
1542 join_hint = global->join_hint_level[bms_num_members(joinrelids)];
1543 foreach(l, join_hint)
1545 JoinMethodHint *hint = (JoinMethodHint *) lfirst(l);
1546 if (bms_equal(joinrelids, hint->joinrelids))
1554 * 結合方式のヒントを使用しやすい構造に変換する。
1557 transform_join_hints(PlanHint *plan, PlannerInfo *root, int level, List *initial_rels)
1564 plan->nlevel = root->simple_rel_array_size - 1;
1565 plan->join_hint_level = palloc0(sizeof(List *) * (root->simple_rel_array_size));
1566 for (i = 0; i < plan->njoin_hints; i++)
1568 JoinMethodHint *hint = plan->join_hints[i];
1572 for (j = 0; j < hint->nrels; j++)
1574 char *relname = hint->relnames[j];
1576 relid = scan_relid_aliasname(root, relname, true, hint->base.hint_str);
1579 parse_ereport(hint->base.hint_str, ("Relation \"%s\" does not exist.", relname));
1583 hint->joinrelids = bms_add_member(hint->joinrelids, relid);
1589 plan->join_hint_level[hint->nrels] =
1590 lappend(plan->join_hint_level[hint->nrels], hint);
1593 /* Leading hint は、全ての join 方式が有効な hint として登録する */
1596 foreach(l, plan->leading)
1598 char *relname = (char *)lfirst(l);
1599 JoinMethodHint *hint;
1601 i = scan_relid_aliasname(root, relname, true, plan->hint_str);
1604 parse_ereport(plan->hint_str, ("Relation \"%s\" does not exist.", relname));
1605 list_free_deep(plan->leading);
1606 plan->leading = NIL;
1610 joinrelids = bms_add_member(joinrelids, i);
1616 if (njoinrels > plan->nlevel)
1618 parse_ereport(plan->hint_str, ("In %s hint, specified relation name %d or less.", HINT_LEADING, plan->nlevel));
1622 /* Leading で指定した組み合わせ以外の join hint を削除する */
1623 hint = scan_join_hint(joinrelids);
1624 list_free(plan->join_hint_level[njoinrels]);
1626 plan->join_hint_level[njoinrels] = lappend(NIL, hint);
1630 * Here relnames is not set, since Relids bitmap is sufficient to
1631 * control paths of this query afterwards.
1633 // TODO plan->hint_strをLeadingHint構造に変更後修正
1634 hint = (JoinMethodHint *) JoinMethodHintCreate(plan->hint_str, HINT_LEADING);
1635 hint->nrels = njoinrels;
1636 hint->enforce_mask = ENABLE_ALL_JOIN;
1637 hint->joinrelids = bms_copy(joinrelids);
1638 plan->join_hint_level[njoinrels] = lappend(NIL, hint);
1640 if (plan->njoin_hints == 0)
1642 plan->max_join_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1643 plan->join_hints = palloc(sizeof(JoinMethodHint *) * plan->max_join_hints);
1645 else if (plan->njoin_hints == plan->max_join_hints)
1647 plan->max_join_hints *= 2;
1648 plan->join_hints = repalloc(plan->join_hints,
1649 sizeof(JoinMethodHint *) * plan->max_join_hints);
1652 plan->join_hints[plan->njoin_hints] = hint;
1653 plan->njoin_hints++;
1657 bms_free(joinrelids);
1661 rebuild_scan_path(PlanHint *plan, PlannerInfo *root, int level, List *initial_rels)
1664 int save_nestlevel = 0;
1666 for (i = 0; i < plan->nscan_hints; i++)
1668 ScanMethodHint *hint = plan->scan_hints[i];
1671 if (hint->enforce_mask == ENABLE_SEQSCAN)
1674 foreach(l, initial_rels)
1676 RelOptInfo *rel = (RelOptInfo *) lfirst(l);
1677 RangeTblEntry *rte = root->simple_rte_array[rel->relid];
1680 * スキャン方式が選択できるリレーションのみ、スキャンパスを再生成
1683 if (rel->reloptkind != RELOPT_BASEREL ||
1684 rte->rtekind == RTE_VALUES ||
1685 RelnameCmp(&hint->relname, &rte->eref->aliasname) != 0)
1689 * 複数のスキャンヒントが指定されていた場合でも、1つのネストレベルで
1690 * スキャン関連のGUCパラメータを変更する。
1692 if (save_nestlevel == 0)
1693 save_nestlevel = NewGUCNestLevel();
1696 * TODO ヒントで指定されたScan方式が最安価でない場合のみ、Pathを生成
1699 set_scan_config_options(hint->enforce_mask, plan->context);
1701 rel->pathlist = NIL; /* TODO 解放 */
1702 set_plain_rel_pathlist(root, rel, rte);
1709 * Restore the GUC variables we set above.
1711 if (save_nestlevel != 0)
1712 AtEOXact_GUC(true, save_nestlevel);
1716 * src/backend/optimizer/path/joinrels.c
1717 * export make_join_rel() をラップする関数
1719 * ヒントにしたがって、enabele_* パラメータを変更した上で、make_join_rel()を
1723 pg_hint_plan_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
1726 JoinMethodHint *hint;
1730 joinrelids = bms_union(rel1->relids, rel2->relids);
1731 hint = scan_join_hint(joinrelids);
1732 bms_free(joinrelids);
1735 return make_join_rel(root, rel1, rel2);
1737 save_nestlevel = NewGUCNestLevel();
1739 set_join_config_options(hint->enforce_mask, global->context);
1741 rel = make_join_rel(root, rel1, rel2);
1744 * Restore the GUC variables we set above.
1746 AtEOXact_GUC(true, save_nestlevel);
1752 pg_hint_plan_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
1755 * pg_hint_planが無効、または有効なヒントが1つも指定されなかった場合は、標準
1760 if (prev_join_search)
1761 return (*prev_join_search) (root, levels_needed, initial_rels);
1762 else if (enable_geqo && levels_needed >= geqo_threshold)
1763 return geqo(root, levels_needed, initial_rels);
1765 return standard_join_search(root, levels_needed, initial_rels);
1768 transform_join_hints(global, root, levels_needed, initial_rels);
1769 rebuild_scan_path(global, root, levels_needed, initial_rels);
1772 * GEQOを使用する条件を満たした場合は、GEQOを用いた結合方式の検索を行う。
1773 * このとき、スキャン方式のヒントとSetヒントのみが有効になり、結合方式や結合
1774 * 順序はヒント句は無効になりGEQOのアルゴリズムで決定される。
1776 if (enable_geqo && levels_needed >= geqo_threshold)
1777 return geqo(root, levels_needed, initial_rels);
1779 return standard_join_search_org(root, levels_needed, initial_rels);
1782 #define standard_join_search standard_join_search_org
1783 #define join_search_one_level pg_hint_plan_join_search_one_level
1784 #define make_join_rel pg_hint_plan_make_join_rel