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_messages, \
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 typedef struct Hint Hint;
83 typedef struct PlanHint PlanHint;
85 typedef Hint *(*HintCreateFunction) (char *hint_str, char *keyword);
86 typedef void (*HintDeleteFunction) (Hint *hint);
87 typedef const char *(*HintParseFunction) (Hint *hint, PlanHint *plan, Query *parse, const char *str);
90 typedef enum HintStatus
92 HINT_STATE_NOTUSED = 0, /* specified relation not used in query */
93 HINT_STATE_DUPLICATION, /* specified hint duplication */
94 HINT_STATE_USED, /* hint is used */
95 /* execute error (parse error does not include it) */
99 /* common data for all hints. */
102 const char *hint_str; /* must not do pfree */
103 const char *keyword; /* must not do pfree */
105 HintDeleteFunction delete_func;
106 HintParseFunction parser_func;
109 /* scan method hints */
110 typedef struct ScanMethodHint
115 unsigned char enforce_mask;
118 /* join method hints */
119 typedef struct JoinMethodHint
124 unsigned char enforce_mask;
128 /* join order hints */
129 typedef struct LeadingHint
132 List *relations; /* relation names specified in Leading hint */
135 /* change a run-time parameter hints */
136 typedef struct SetHint
139 char *name; /* name of variable */
144 * Describes a context of hint processing.
148 char *hint_str; /* original hint string */
150 /* for scan method hints */
151 int nscan_hints; /* # of valid scan hints */
152 int max_scan_hints; /* # of slots for scan hints */
153 ScanMethodHint **scan_hints; /* parsed scan hints */
155 /* for join method hints */
156 int njoin_hints; /* # of valid join hints */
157 int max_join_hints; /* # of slots for join hints */
158 JoinMethodHint **join_hints; /* parsed join hints */
160 int nlevel; /* # of relations to be joined */
161 List **join_hint_level;
163 /* for Leading hints */
164 List *leading; /* relation names specified in Leading hint */
167 GucContext context; /* which GUC parameters can we set? */
168 List *set_hints; /* parsed Set hints */
172 * Describes a hint parser module which is bound with particular hint keyword.
174 typedef struct HintParser
178 HintCreateFunction create_func;
181 /* Module callbacks */
185 static PlannedStmt *pg_hint_plan_planner(Query *parse, int cursorOptions,
186 ParamListInfo boundParams);
187 static void pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
188 bool inhparent, RelOptInfo *rel);
189 static RelOptInfo *pg_hint_plan_join_search(PlannerInfo *root, int levels_needed,
192 static Hint *ScanMethodHintCreate(char *hint_str, char *keyword);
193 static void ScanMethodHintDelete(ScanMethodHint *hint);
194 static const char *ScanMethodHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str);
195 static Hint *JoinMethodHintCreate(char *hint_str, char *keyword);
196 static void JoinMethodHintDelete(JoinMethodHint *hint);
197 static const char *JoinMethodHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str);
198 static Hint *LeadingHintCreate(char *hint_str, char *keyword);
199 static void LeadingHintDelete(LeadingHint *hint);
200 static const char *LeadingHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str);
201 static Hint *SetHintCreate(char *hint_str, char *keyword);
202 static void SetHintDelete(SetHint *hint);
203 static const char *SetHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str);
205 static const char *OrderedHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str);
208 RelOptInfo *standard_join_search_org(PlannerInfo *root, int levels_needed, List *initial_rels);
209 void pg_hint_plan_join_search_one_level(PlannerInfo *root, int level);
210 static void make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
211 static void make_rels_by_clauseless_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
212 static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel);
213 static void set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte);
217 static bool pg_hint_plan_enable = true;
218 static bool pg_hint_plan_debug_print = false;
219 static int pg_hint_plan_parse_messages = INFO;
221 static const struct config_enum_entry parse_messages_level_options[] = {
222 {"debug", DEBUG2, true},
223 {"debug5", DEBUG5, false},
224 {"debug4", DEBUG4, false},
225 {"debug3", DEBUG3, false},
226 {"debug2", DEBUG2, false},
227 {"debug1", DEBUG1, false},
229 {"info", INFO, false},
230 {"notice", NOTICE, false},
231 {"warning", WARNING, false},
232 {"error", ERROR, false},
234 {"fatal", FATAL, true},
235 {"panic", PANIC, true},
240 /* Saved hook values in case of unload */
241 static planner_hook_type prev_planner_hook = NULL;
242 static get_relation_info_hook_type prev_get_relation_info = NULL;
243 static join_search_hook_type prev_join_search = NULL;
245 /* フック関数をまたがって使用する情報を管理する */
246 static PlanHint *global = NULL;
248 static const HintParser parsers[] = {
249 {HINT_SEQSCAN, true, ScanMethodHintCreate},
250 {HINT_INDEXSCAN, true, ScanMethodHintCreate},
251 {HINT_BITMAPSCAN, true, ScanMethodHintCreate},
252 {HINT_TIDSCAN, true, ScanMethodHintCreate},
253 {HINT_NOSEQSCAN, true, ScanMethodHintCreate},
254 {HINT_NOINDEXSCAN, true, ScanMethodHintCreate},
255 {HINT_NOBITMAPSCAN, true, ScanMethodHintCreate},
256 {HINT_NOTIDSCAN, true, ScanMethodHintCreate},
257 #if PG_VERSION_NUM >= 90200
258 {HINT_INDEXONLYSCAN, true, ScanMethodHintCreate},
259 {HINT_NOINDEXONLYSCAN, true, ScanMethodHintCreate},
261 {HINT_NESTLOOP, true, JoinMethodHintCreate},
262 {HINT_MERGEJOIN, true, JoinMethodHintCreate},
263 {HINT_HASHJOIN, true, JoinMethodHintCreate},
264 {HINT_NONESTLOOP, true, JoinMethodHintCreate},
265 {HINT_NOMERGEJOIN, true, JoinMethodHintCreate},
266 {HINT_NOHASHJOIN, true, JoinMethodHintCreate},
267 {HINT_LEADING, true, LeadingHintCreate},
268 {HINT_SET, true, SetHintCreate},
273 * Module load callbacks
278 /* Define custom GUC variables. */
279 DefineCustomBoolVariable("pg_hint_plan.enable",
280 "Instructions or hints to the planner using block comments.",
282 &pg_hint_plan_enable,
290 DefineCustomBoolVariable("pg_hint_plan.debug_print",
291 "Logs each query's parse results of the hint.",
293 &pg_hint_plan_debug_print,
301 DefineCustomEnumVariable("pg_hint_plan.parse_messages",
302 "Messege level of the parse error.",
304 &pg_hint_plan_parse_messages,
306 parse_messages_level_options,
314 prev_planner_hook = planner_hook;
315 planner_hook = pg_hint_plan_planner;
316 prev_get_relation_info = get_relation_info_hook;
317 get_relation_info_hook = pg_hint_plan_get_relation_info;
318 prev_join_search = join_search_hook;
319 join_search_hook = pg_hint_plan_join_search;
323 * Module unload callback
329 /* Uninstall hooks. */
330 planner_hook = prev_planner_hook;
331 get_relation_info_hook = prev_get_relation_info;
332 join_search_hook = prev_join_search;
336 ScanMethodHintCreate(char *hint_str, char *keyword)
338 ScanMethodHint *hint;
340 hint = palloc(sizeof(ScanMethodHint));
341 hint->base.hint_str = hint_str;
342 hint->base.keyword = keyword;
343 hint->base.used = false;
344 hint->base.delete_func = (HintDeleteFunction) ScanMethodHintDelete;
345 hint->base.parser_func = (HintParseFunction) ScanMethodHintParse;
346 hint->relname = NULL;
347 hint->indexnames = NIL;
348 hint->enforce_mask = 0;
350 return (Hint *) hint;
354 ScanMethodHintDelete(ScanMethodHint *hint)
360 pfree(hint->relname);
361 list_free_deep(hint->indexnames);
366 JoinMethodHintCreate(char *hint_str, char *keyword)
368 JoinMethodHint *hint;
370 hint = palloc(sizeof(JoinMethodHint));
371 hint->base.hint_str = hint_str;
372 hint->base.keyword = keyword;
373 hint->base.used = false;
374 hint->base.delete_func = (HintDeleteFunction) JoinMethodHintDelete;
375 hint->base.parser_func = (HintParseFunction) JoinMethodHintParse;
377 hint->relnames = NULL;
378 hint->enforce_mask = 0;
379 hint->joinrelids = NULL;
381 return (Hint *) hint;
385 JoinMethodHintDelete(JoinMethodHint *hint)
394 for (i = 0; i < hint->nrels; i++)
395 pfree(hint->relnames[i]);
396 pfree(hint->relnames);
398 bms_free(hint->joinrelids);
403 LeadingHintCreate(char *hint_str, char *keyword)
407 hint = palloc(sizeof(LeadingHint));
408 hint->base.hint_str = hint_str;
409 hint->base.keyword = keyword;
410 hint->base.used = false;
411 hint->base.delete_func = (HintDeleteFunction)LeadingHintDelete;
412 hint->base.parser_func = (HintParseFunction) LeadingHintParse;
413 hint->relations = NIL;
415 return (Hint *) hint;
419 LeadingHintDelete(LeadingHint *hint)
424 list_free_deep(hint->relations);
429 SetHintCreate(char *hint_str, char *keyword)
433 hint = palloc(sizeof(SetHint));
434 hint->base.hint_str = hint_str;
435 hint->base.keyword = keyword;
436 hint->base.used = false;
437 hint->base.delete_func = (HintDeleteFunction) SetHintDelete;
438 hint->base.parser_func = (HintParseFunction) SetHintParse;
442 return (Hint *) hint;
446 SetHintDelete(SetHint *hint)
463 hint = palloc(sizeof(PlanHint));
464 hint->hint_str = NULL;
465 hint->nscan_hints = 0;
466 hint->max_scan_hints = 0;
467 hint->scan_hints = NULL;
468 hint->njoin_hints = 0;
469 hint->max_join_hints = 0;
470 hint->join_hints = NULL;
472 hint->join_hint_level = NULL;
474 hint->context = superuser() ? PGC_SUSET : PGC_USERSET;
475 hint->set_hints = NIL;
481 PlanHintDelete(PlanHint *hint)
490 pfree(hint->hint_str);
492 for (i = 0; i < hint->nscan_hints; i++)
493 ScanMethodHintDelete(hint->scan_hints[i]);
494 if (hint->scan_hints)
495 pfree(hint->scan_hints);
497 for (i = 0; i < hint->njoin_hints; i++)
498 JoinMethodHintDelete(hint->join_hints[i]);
499 if (hint->join_hints)
500 pfree(hint->join_hints);
502 for (i = 2; i <= hint->nlevel; i++)
503 list_free(hint->join_hint_level[i]);
504 if (hint->join_hint_level)
505 pfree(hint->join_hint_level);
507 list_free_deep(hint->leading);
509 foreach(l, hint->set_hints)
510 SetHintDelete((SetHint *) lfirst(l));
511 list_free(hint->set_hints);
517 PlanHintIsempty(PlanHint *hint)
519 if (hint->nscan_hints == 0 &&
520 hint->njoin_hints == 0 &&
521 hint->leading == NIL &&
522 hint->set_hints == NIL)
528 /* TODO オブジェクト名のクォート処理を追加 */
530 PlanHintDump(PlanHint *hint)
535 bool is_first = true;
539 elog(LOG, "no hint");
543 initStringInfo(&buf);
544 appendStringInfo(&buf, "/*\n");
545 for (i = 0; i < hint->nscan_hints; i++)
547 ScanMethodHint *h = hint->scan_hints[i];
549 switch(h->enforce_mask)
551 case(ENABLE_SEQSCAN):
552 appendStringInfo(&buf, "%s(", HINT_SEQSCAN);
554 case(ENABLE_INDEXSCAN):
555 appendStringInfo(&buf, "%s(", HINT_INDEXSCAN);
557 case(ENABLE_BITMAPSCAN):
558 appendStringInfo(&buf, "%s(", HINT_BITMAPSCAN);
560 case(ENABLE_TIDSCAN):
561 appendStringInfo(&buf, "%s(", HINT_TIDSCAN);
563 case(ENABLE_INDEXSCAN | ENABLE_BITMAPSCAN | ENABLE_TIDSCAN):
564 appendStringInfo(&buf, "%s(", HINT_NOSEQSCAN);
566 case(ENABLE_SEQSCAN | ENABLE_BITMAPSCAN | ENABLE_TIDSCAN):
567 appendStringInfo(&buf, "%s(", HINT_NOINDEXSCAN);
569 case(ENABLE_SEQSCAN | ENABLE_INDEXSCAN | ENABLE_TIDSCAN):
570 appendStringInfo(&buf, "%s(", HINT_NOBITMAPSCAN);
572 case(ENABLE_SEQSCAN | ENABLE_INDEXSCAN | ENABLE_BITMAPSCAN):
573 appendStringInfo(&buf, "%s(", HINT_NOTIDSCAN);
576 appendStringInfoString(&buf, "\?\?\?(");
579 appendStringInfo(&buf, "%s", h->relname);
580 foreach(n, h->indexnames)
581 appendStringInfo(&buf, " %s", (char *) lfirst(n));
582 appendStringInfoString(&buf, ")\n");
585 for (i = 0; i < hint->njoin_hints; i++)
587 JoinMethodHint *h = hint->join_hints[i];
589 switch(h->enforce_mask)
591 case(ENABLE_NESTLOOP):
592 appendStringInfo(&buf, "%s(", HINT_NESTLOOP);
594 case(ENABLE_MERGEJOIN):
595 appendStringInfo(&buf, "%s(", HINT_MERGEJOIN);
597 case(ENABLE_HASHJOIN):
598 appendStringInfo(&buf, "%s(", HINT_HASHJOIN);
600 case(ENABLE_ALL_JOIN ^ ENABLE_NESTLOOP):
601 appendStringInfo(&buf, "%s(", HINT_NONESTLOOP);
603 case(ENABLE_ALL_JOIN ^ ENABLE_MERGEJOIN):
604 appendStringInfo(&buf, "%s(", HINT_NOMERGEJOIN);
606 case(ENABLE_ALL_JOIN ^ ENABLE_HASHJOIN):
607 appendStringInfo(&buf, "%s(", HINT_NOHASHJOIN);
609 case(ENABLE_ALL_JOIN):
612 appendStringInfoString(&buf, "\?\?\?(");
615 appendStringInfo(&buf, "%s", h->relnames[0]);
616 for (i = 1; i < h->nrels; i++)
617 appendStringInfo(&buf, " %s", h->relnames[i]);
618 appendStringInfoString(&buf, ")\n");
621 foreach(l, hint->set_hints)
623 SetHint *h = (SetHint *) lfirst(l);
624 appendStringInfo(&buf, "%s(%s %s)\n", HINT_SET, h->name, h->value);
627 foreach(l, hint->leading)
631 appendStringInfo(&buf, "%s(%s", HINT_LEADING, (char *)lfirst(l));
635 appendStringInfo(&buf, " %s", (char *)lfirst(l));
638 appendStringInfoString(&buf, ")\n");
640 appendStringInfoString(&buf, "*/");
642 elog(LOG, "%s", buf.data);
648 RelnameCmp(const void *a, const void *b)
650 const char *relnamea = *((const char **) a);
651 const char *relnameb = *((const char **) b);
653 return strcmp(relnamea, relnameb);
657 ScanMethodHintCmp(const void *a, const void *b, bool order)
659 const ScanMethodHint *hinta = *((const ScanMethodHint **) a);
660 const ScanMethodHint *hintb = *((const ScanMethodHint **) b);
663 if ((result = RelnameCmp(&hinta->relname, &hintb->relname)) != 0)
668 return hinta->base.hint_str - hintb->base.hint_str;
674 ScanMethodHintCmpIsOrder(const void *a, const void *b)
676 return ScanMethodHintCmp(a, b, true);
680 JoinMethodHintCmp(const void *a, const void *b, bool order)
682 const JoinMethodHint *hinta = *((const JoinMethodHint **) a);
683 const JoinMethodHint *hintb = *((const JoinMethodHint **) b);
685 if (hinta->nrels == hintb->nrels)
688 for (i = 0; i < hinta->nrels; i++)
691 if ((result = RelnameCmp(&hinta->relnames[i], &hintb->relnames[i])) != 0)
697 return hinta->base.hint_str - hintb->base.hint_str;
702 return hinta->nrels - hintb->nrels;
706 JoinMethodHintCmpIsOrder(const void *a, const void *b)
708 return JoinMethodHintCmp(a, b, true);
711 #if PG_VERSION_NUM < 90200
713 set_config_option_wrapper(const char *name, const char *value,
714 GucContext context, GucSource source,
715 GucAction action, bool changeVal, int elevel)
718 MemoryContext ccxt = CurrentMemoryContext;
722 result = set_config_option(name, value, context, source,
733 ecxt = MemoryContextSwitchTo(ccxt);
734 errdata = CopyErrorData();
735 ereport(elevel, (errcode(errdata->sqlerrcode),
736 errmsg("%s", errdata->message),
737 errdata->detail ? errdetail("%s", errdata->detail) : 0,
738 errdata->hint ? errhint("%s", errdata->hint) : 0));
739 FreeErrorData(errdata);
741 MemoryContextSwitchTo(ecxt);
748 #define set_config_option(name, value, context, source, \
749 action, changeVal, elevel) \
750 set_config_option_wrapper(name, value, context, source, \
751 action, changeVal, elevel)
755 set_config_options(List *options, GucContext context)
761 save_nestlevel = NewGUCNestLevel();
765 SetHint *hint = (SetHint *) lfirst(l);
768 result = set_config_option(hint->name, hint->value, context,
769 PGC_S_SESSION, GUC_ACTION_SAVE, true,
770 pg_hint_plan_parse_messages);
773 return save_nestlevel;
776 #define SET_CONFIG_OPTION(name, enforce_mask, type_bits) \
777 set_config_option((name), \
778 ((enforce_mask) & (type_bits)) ? "true" : "false", \
779 context, PGC_S_SESSION, GUC_ACTION_SAVE, true, ERROR)
782 set_join_config_options(unsigned char enforce_mask, GucContext context)
784 SET_CONFIG_OPTION("enable_nestloop", enforce_mask, ENABLE_NESTLOOP);
785 SET_CONFIG_OPTION("enable_mergejoin", enforce_mask, ENABLE_MERGEJOIN);
786 SET_CONFIG_OPTION("enable_hashjoin", enforce_mask, ENABLE_HASHJOIN);
790 set_scan_config_options(unsigned char enforce_mask, GucContext context)
792 SET_CONFIG_OPTION("enable_seqscan", enforce_mask, ENABLE_SEQSCAN);
793 SET_CONFIG_OPTION("enable_indexscan", enforce_mask, ENABLE_INDEXSCAN);
794 SET_CONFIG_OPTION("enable_bitmapscan", enforce_mask, ENABLE_BITMAPSCAN);
795 SET_CONFIG_OPTION("enable_tidscan", enforce_mask, ENABLE_TIDSCAN);
796 #if PG_VERSION_NUM >= 90200
797 SET_CONFIG_OPTION("enable_indexonlyscan", enforce_mask, ENABLE_INDEXSCAN);
806 parse_keyword(const char *str, StringInfo buf)
810 while (!isspace(*str) && *str != '(' && *str != '\0')
811 appendStringInfoChar(buf, *str++);
817 skip_opened_parenthesis(const char *str)
823 parse_ereport(str, ("Opened parenthesis is necessary."));
833 skip_closed_parenthesis(const char *str)
839 parse_ereport(str, ("Closed parenthesis is necessary."));
849 * 二重引用符で囲まれているかもしれないトークンを読み取り word 引数に palloc
850 * で確保したバッファに格納してそのポインタを返す。
852 * 正常にパースできた場合は残りの文字列の先頭位置を、異常があった場合は NULL を
856 parse_quote_value(const char *str, char **word, char *value_type)
864 initStringInfo(&buf);
877 /* 二重引用符が閉じられていない場合はパース中断 */
881 parse_ereport(str, ("Unterminated quoted %s.", value_type));
886 * エスケープ対象のダブルクウォートをスキップする。
887 * もしブロックコメントの開始文字列や終了文字列もオブジェクト名とし
888 * て使用したい場合は、/ と * もエスケープ対象とすることで使用できる
889 * が、処理対象としていない。もしテーブル名にこれらの文字が含まれる
890 * 場合は、エイリアスを指定する必要がある。
900 if (isspace(*str) || *str == ')' || *str == '\0')
903 appendStringInfoCharMacro(&buf, *str++);
909 parse_ereport(str, ("%s is necessary.", value_type));
919 skip_option_delimiter(const char *str)
927 parse_ereport(str, ("Must be specified space."));
935 parse_hints(PlanHint *plan, Query *parse, const char *str)
940 initStringInfo(&buf);
943 const HintParser *parser;
945 /* in error message, we output the comment including the keyword. */
948 /* parse only the keyword of the hint. */
949 resetStringInfo(&buf);
950 str = parse_keyword(str, &buf);
952 for (parser = parsers; parser->keyword != NULL; parser++)
954 char *keyword = parser->keyword;
957 if (strcasecmp(buf.data, keyword) != 0)
960 hint = parser->create_func(head, keyword);
962 if (parser->have_paren)
964 /* parser of each hint does parse in a parenthesis. */
965 if ((str = skip_opened_parenthesis(str)) == NULL ||
966 (str = hint->parser_func(hint, plan, parse, str)) == NULL ||
967 (str = skip_closed_parenthesis(str)) == NULL)
969 hint->delete_func(hint);
976 if ((str = hint->parser_func(hint, plan, parse, str)) == NULL)
978 hint->delete_func(hint);
984 * 直前のヒントに括弧の指定がなければ次のヒントの間に空白が必要
986 if (!isspace(*str) && *str == '\0')
987 parse_ereport(str, ("Delimiter of the hint is necessary."));
995 if (parser->keyword == NULL)
997 parse_ereport(head, ("Keyword \"%s\" does not exist.", buf.data));
1009 * Do basic parsing of the query head comment.
1012 parse_head_comment(Query *parse)
1021 /* get client-supplied query string. */
1022 p = debug_query_string;
1026 /* extract query head comment. */
1027 len = strlen(HINT_START);
1029 if (strncmp(p, HINT_START, len))
1035 if ((tail = strstr(p, HINT_END)) == NULL)
1037 parse_ereport(debug_query_string, ("unterminated /* comment"));
1041 /* 入れ子にしたブロックコメントはサポートしない */
1042 if ((head = strstr(p, HINT_START)) != NULL && head < tail)
1043 parse_ereport(head, ("block comments nest doesn't supported"));
1047 head = palloc(len + 1);
1048 memcpy(head, p, len);
1052 plan = PlanHintCreate();
1053 plan->hint_str = head;
1055 /* parse each hint. */
1056 if (!parse_hints(plan, parse, p))
1059 /* 重複したScan条件をを除外する */
1060 qsort(plan->scan_hints, plan->nscan_hints, sizeof(ScanMethodHint *), ScanMethodHintCmpIsOrder);
1061 for (i = 0; i < plan->nscan_hints - 1;)
1063 int result = ScanMethodHintCmp(plan->scan_hints + i,
1064 plan->scan_hints + i + 1, false);
1069 /* 後で指定したヒントを有効にする */
1070 plan->nscan_hints--;
1071 memmove(plan->scan_hints + i, plan->scan_hints + i + 1,
1072 sizeof(ScanMethodHint *) * (plan->nscan_hints - i));
1076 /* 重複したJoin条件をを除外する */
1077 qsort(plan->join_hints, plan->njoin_hints, sizeof(JoinMethodHint *), JoinMethodHintCmpIsOrder);
1078 for (i = 0; i < plan->njoin_hints - 1;)
1080 int result = JoinMethodHintCmp(plan->join_hints + i,
1081 plan->join_hints + i + 1, false);
1086 /* 後で指定したヒントを有効にする */
1087 plan->njoin_hints--;
1088 memmove(plan->join_hints + i, plan->join_hints + i + 1,
1089 sizeof(JoinMethodHint *) * (plan->njoin_hints - i));
1097 * スキャン方式ヒントのカッコ内をパースする
1100 ScanMethodHintParse(Hint *base_hint, PlanHint *plan, Query *parse, const char *str)
1102 ScanMethodHint *hint = (ScanMethodHint *) base_hint;
1103 const char *keyword = hint->base.keyword;
1106 * スキャン方式のヒントでリレーション名が読み取れない場合はヒント無効
1108 if ((str = parse_quote_value(str, &hint->relname, "ralation name")) == NULL)
1114 * インデックスリストを受け付けるヒントであれば、インデックス参照をパース
1117 if (strcmp(keyword, HINT_INDEXSCAN) == 0 ||
1118 #if PG_VERSION_NUM >= 90200
1119 strcmp(keyword, HINT_INDEXONLYSCAN) == 0 ||
1121 strcmp(keyword, HINT_BITMAPSCAN) == 0)
1123 while (*str != ')' && *str != '\0')
1127 str = parse_quote_value(str, &indexname, "index name");
1131 hint->indexnames = lappend(hint->indexnames, indexname);
1136 /* カッコが閉じていなければヒント無効。 */
1137 skip_space(str); /* just in case */
1140 parse_ereport(str, ("Closed parenthesis is necessary."));
1145 * ヒントごとに決まっている許容スキャン方式をビットマスクとして設定
1147 if (strcasecmp(keyword, HINT_SEQSCAN) == 0)
1148 hint->enforce_mask = ENABLE_SEQSCAN;
1149 else if (strcasecmp(keyword, HINT_INDEXSCAN) == 0)
1150 hint->enforce_mask = ENABLE_INDEXSCAN;
1151 else if (strcasecmp(keyword, HINT_BITMAPSCAN) == 0)
1152 hint->enforce_mask = ENABLE_BITMAPSCAN;
1153 else if (strcasecmp(keyword, HINT_TIDSCAN) == 0)
1154 hint->enforce_mask = ENABLE_TIDSCAN;
1155 else if (strcasecmp(keyword, HINT_NOSEQSCAN) == 0)
1156 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_SEQSCAN;
1157 else if (strcasecmp(keyword, HINT_NOINDEXSCAN) == 0)
1158 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_INDEXSCAN;
1159 else if (strcasecmp(keyword, HINT_NOBITMAPSCAN) == 0)
1160 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_BITMAPSCAN;
1161 else if (strcasecmp(keyword, HINT_NOTIDSCAN) == 0)
1162 hint->enforce_mask = ENABLE_ALL_SCAN ^ ENABLE_TIDSCAN;
1165 parse_ereport(str, ("unrecognized hint keyword \"%s\"", keyword));
1170 * 出来上がったヒント情報を追加。スロットが足りない場合は二倍に拡張する。
1172 if (plan->nscan_hints == 0)
1174 plan->max_scan_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1175 plan->scan_hints = palloc(sizeof(ScanMethodHint *) * plan->max_scan_hints);
1177 else if (plan->nscan_hints == plan->max_scan_hints)
1179 plan->max_scan_hints *= 2;
1180 plan->scan_hints = repalloc(plan->scan_hints,
1181 sizeof(ScanMethodHint *) * plan->max_scan_hints);
1183 plan->scan_hints[plan->nscan_hints] = hint;
1184 plan->nscan_hints++;
1190 JoinMethodHintParse(Hint *base_hint, PlanHint *plan, Query *parse, const char *str)
1193 JoinMethodHint *hint = (JoinMethodHint *) base_hint;
1194 const char *keyword = hint->base.keyword;
1198 hint->relnames = palloc(sizeof(char *));
1200 while ((str = parse_quote_value(str, &relname, "table name")) != NULL)
1203 hint->relnames = repalloc(hint->relnames, sizeof(char *) * hint->nrels);
1204 hint->relnames[hint->nrels - 1] = relname;
1214 /* Join 対象のテーブルは最低でも2つ指定する必要がある */
1215 if (hint->nrels < 2)
1217 parse_ereport(str, ("Specified relation more than two."));
1222 qsort(hint->relnames, hint->nrels, sizeof(char *), RelnameCmp);
1224 if (strcasecmp(keyword, HINT_NESTLOOP) == 0)
1225 hint->enforce_mask = ENABLE_NESTLOOP;
1226 else if (strcasecmp(keyword, HINT_MERGEJOIN) == 0)
1227 hint->enforce_mask = ENABLE_MERGEJOIN;
1228 else if (strcasecmp(keyword, HINT_HASHJOIN) == 0)
1229 hint->enforce_mask = ENABLE_HASHJOIN;
1230 else if (strcasecmp(keyword, HINT_NONESTLOOP) == 0)
1231 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_NESTLOOP;
1232 else if (strcasecmp(keyword, HINT_NOMERGEJOIN) == 0)
1233 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_MERGEJOIN;
1234 else if (strcasecmp(keyword, HINT_NOHASHJOIN) == 0)
1235 hint->enforce_mask = ENABLE_ALL_JOIN ^ ENABLE_HASHJOIN;
1238 parse_ereport(str, ("unrecognized hint keyword \"%s\"", keyword));
1242 if (plan->njoin_hints == 0)
1244 plan->max_join_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1245 plan->join_hints = palloc(sizeof(JoinMethodHint *) * plan->max_join_hints);
1247 else if (plan->njoin_hints == plan->max_join_hints)
1249 plan->max_join_hints *= 2;
1250 plan->join_hints = repalloc(plan->join_hints,
1251 sizeof(JoinMethodHint *) * plan->max_join_hints);
1254 plan->join_hints[plan->njoin_hints] = hint;
1255 plan->njoin_hints++;
1261 LeadingHintParse(Hint *hint, PlanHint *plan, Query *parse, const char *str)
1266 * すでに指定済みの場合は、後で指定したヒントが有効にするため、登録済みの
1269 list_free_deep(plan->leading);
1270 plan->leading = NIL;
1272 while ((str = parse_quote_value(str, &relname, "relation name")) != NULL)
1276 plan->leading = lappend(plan->leading, relname);
1285 parse_ereport(str, ("Must be specified space."));
1290 /* テーブル指定が1つのみの場合は、ヒントを無効にし、パースを続ける */
1291 if (list_length(plan->leading) == 1)
1293 parse_ereport(str, ("In %s hint, specified relation name 2 or more.", HINT_LEADING));
1294 list_free_deep(plan->leading);
1295 plan->leading = NIL;
1302 SetHintParse(Hint *base_hint, PlanHint *plan, Query *parse, const char *str)
1304 SetHint *hint = (SetHint *) base_hint;
1306 if ((str = parse_quote_value(str, &hint->name, "parameter name")) == NULL ||
1307 (str = skip_option_delimiter(str)) == NULL ||
1308 (str = parse_quote_value(str, &hint->value, "parameter value")) == NULL)
1314 parse_ereport(str, ("Closed parenthesis is necessary."));
1317 plan->set_hints = lappend(plan->set_hints, hint);
1324 * Oracle の ORDERD ヒントの実装
1327 OrderedHintParse(Hint *base_hint, PlanHint *plan, Query *parse, const char *str);
1329 SetHint *hint = (SetHint *) base_hint;
1331 hint->name = pstrdup("join_collapse_limit");
1332 hint->value = pstrdup("1");
1333 plan->set_hints = lappend(plan->set_hints, hint);
1339 static PlannedStmt *
1340 pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
1343 PlannedStmt *result;
1346 * hintが指定されない、または空のhintを指定された場合は通常のparser処理をお
1348 * 他のフック関数で実行されるhint処理をスキップするために、global 変数をNULL
1351 if (!pg_hint_plan_enable ||
1352 (global = parse_head_comment(parse)) == NULL ||
1353 PlanHintIsempty(global))
1355 PlanHintDelete(global);
1358 if (prev_planner_hook)
1359 return (*prev_planner_hook) (parse, cursorOptions, boundParams);
1361 return standard_planner(parse, cursorOptions, boundParams);
1364 /* Set hint で指定されたGUCパラメータを設定する */
1365 save_nestlevel = set_config_options(global->set_hints, global->context);
1367 if (global->leading != NIL)
1368 set_join_config_options(0, global->context);
1371 * TODO ビュー定義で指定したテーブル数が1つの場合にもこのタイミングでGUCを変更する必
1374 if (list_length(parse->rtable) == 1 &&
1375 ((RangeTblEntry *) linitial(parse->rtable))->rtekind == RTE_RELATION)
1378 RangeTblEntry *rte = linitial(parse->rtable);
1380 for (i = 0; i < global->nscan_hints; i++)
1382 ScanMethodHint *hint = global->scan_hints[i];
1384 if (RelnameCmp(&rte->eref->aliasname, &hint->relname) != 0)
1385 parse_ereport(hint->base.hint_str, ("Relation \"%s\" does not exist.", hint->relname));
1387 set_scan_config_options(hint->enforce_mask, global->context);
1391 if (prev_planner_hook)
1392 result = (*prev_planner_hook) (parse, cursorOptions, boundParams);
1394 result = standard_planner(parse, cursorOptions, boundParams);
1397 * Restore the GUC variables we set above.
1399 AtEOXact_GUC(true, save_nestlevel);
1401 if (pg_hint_plan_debug_print)
1403 PlanHintDump(global);
1405 elog_node_display(INFO, "rtable", parse->rtable, true);
1409 PlanHintDelete(global);
1416 * aliasnameと一致するSCANヒントを探す
1418 static ScanMethodHint *
1419 find_scan_hint(RangeTblEntry *rte)
1423 for (i = 0; i < global->nscan_hints; i++)
1425 ScanMethodHint *hint = global->scan_hints[i];
1427 if (RelnameCmp(&rte->eref->aliasname, &hint->relname) == 0)
1435 pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
1436 bool inhparent, RelOptInfo *rel)
1438 ScanMethodHint *hint;
1443 if (prev_get_relation_info)
1444 (*prev_get_relation_info) (root, relationObjectId, inhparent, rel);
1446 /* 有効なヒントが指定されなかった場合は処理をスキップする。 */
1450 if (rel->reloptkind != RELOPT_BASEREL)
1453 if ((hint = find_scan_hint(root->simple_rte_array[rel->relid])) == NULL)
1456 /* インデックスを全て削除し、スキャンに使えなくする */
1457 if (hint->enforce_mask == ENABLE_SEQSCAN)
1459 list_free_deep(rel->indexlist);
1460 rel->indexlist = NIL;
1465 /* 後でパスを作り直すため、ここではなにもしない */
1466 if (hint->indexnames == NULL)
1469 /* 指定されたインデックスのみをのこす */
1471 for (cell = list_head(rel->indexlist); cell; cell = next)
1473 IndexOptInfo *info = (IndexOptInfo *) lfirst(cell);
1474 char *indexname = get_rel_name(info->indexoid);
1476 bool use_index = false;
1480 foreach(l, hint->indexnames)
1482 if (RelnameCmp(&indexname, &lfirst(l)) == 0)
1490 rel->indexlist = list_delete_cell(rel->indexlist, cell, prev);
1499 scan_relid_aliasname(PlannerInfo *root, char *aliasname, bool check_ambiguous, const char *str)
1501 /* TODO refnameRangeTblEntry を参考 */
1505 for (i = 1; i < root->simple_rel_array_size; i++)
1507 if (root->simple_rel_array[i] == NULL)
1510 Assert(i == root->simple_rel_array[i]->relid);
1512 if (RelnameCmp(&aliasname, &root->simple_rte_array[i]->eref->aliasname)
1516 if (!check_ambiguous)
1520 parse_ereport(str, ("relation name \"%s\" is ambiguous", aliasname));
1529 * relidビットマスクと一致するヒントを探す
1531 static JoinMethodHint *
1532 scan_join_hint(Relids joinrelids)
1537 join_hint = global->join_hint_level[bms_num_members(joinrelids)];
1538 foreach(l, join_hint)
1540 JoinMethodHint *hint = (JoinMethodHint *) lfirst(l);
1541 if (bms_equal(joinrelids, hint->joinrelids))
1549 * 結合方式のヒントを使用しやすい構造に変換する。
1552 transform_join_hints(PlanHint *plan, PlannerInfo *root, int level, List *initial_rels)
1559 plan->nlevel = root->simple_rel_array_size - 1;
1560 plan->join_hint_level = palloc0(sizeof(List *) * (root->simple_rel_array_size));
1561 for (i = 0; i < plan->njoin_hints; i++)
1563 JoinMethodHint *hint = plan->join_hints[i];
1567 for (j = 0; j < hint->nrels; j++)
1569 char *relname = hint->relnames[j];
1571 relid = scan_relid_aliasname(root, relname, true, hint->base.hint_str);
1574 parse_ereport(hint->base.hint_str, ("Relation \"%s\" does not exist.", relname));
1578 hint->joinrelids = bms_add_member(hint->joinrelids, relid);
1584 plan->join_hint_level[hint->nrels] =
1585 lappend(plan->join_hint_level[hint->nrels], hint);
1588 /* Leading hint は、全ての join 方式が有効な hint として登録する */
1591 foreach(l, plan->leading)
1593 char *relname = (char *)lfirst(l);
1594 JoinMethodHint *hint;
1596 i = scan_relid_aliasname(root, relname, true, plan->hint_str);
1599 parse_ereport(plan->hint_str, ("Relation \"%s\" does not exist.", relname));
1600 list_free_deep(plan->leading);
1601 plan->leading = NIL;
1605 joinrelids = bms_add_member(joinrelids, i);
1611 if (njoinrels > plan->nlevel)
1613 parse_ereport(plan->hint_str, ("In %s hint, specified relation name %d or less.", HINT_LEADING, plan->nlevel));
1617 /* Leading で指定した組み合わせ以外の join hint を削除する */
1618 hint = scan_join_hint(joinrelids);
1619 list_free(plan->join_hint_level[njoinrels]);
1621 plan->join_hint_level[njoinrels] = lappend(NIL, hint);
1625 * Here relnames is not set, since Relids bitmap is sufficient to
1626 * control paths of this query afterwards.
1628 // TODO plan->hint_strをLeadingHint構造に変更後修正
1629 hint = (JoinMethodHint *) JoinMethodHintCreate(plan->hint_str, HINT_LEADING);
1630 hint->nrels = njoinrels;
1631 hint->enforce_mask = ENABLE_ALL_JOIN;
1632 hint->joinrelids = bms_copy(joinrelids);
1633 plan->join_hint_level[njoinrels] = lappend(NIL, hint);
1635 if (plan->njoin_hints == 0)
1637 plan->max_join_hints = HINT_ARRAY_DEFAULT_INITSIZE;
1638 plan->join_hints = palloc(sizeof(JoinMethodHint *) * plan->max_join_hints);
1640 else if (plan->njoin_hints == plan->max_join_hints)
1642 plan->max_join_hints *= 2;
1643 plan->join_hints = repalloc(plan->join_hints,
1644 sizeof(JoinMethodHint *) * plan->max_join_hints);
1647 plan->join_hints[plan->njoin_hints] = hint;
1648 plan->njoin_hints++;
1652 bms_free(joinrelids);
1656 rebuild_scan_path(PlanHint *plan, PlannerInfo *root, int level, List *initial_rels)
1659 int save_nestlevel = 0;
1661 for (i = 0; i < plan->nscan_hints; i++)
1663 ScanMethodHint *hint = plan->scan_hints[i];
1666 if (hint->enforce_mask == ENABLE_SEQSCAN)
1669 foreach(l, initial_rels)
1671 RelOptInfo *rel = (RelOptInfo *) lfirst(l);
1672 RangeTblEntry *rte = root->simple_rte_array[rel->relid];
1675 * スキャン方式が選択できるリレーションのみ、スキャンパスを再生成
1678 if (rel->reloptkind != RELOPT_BASEREL ||
1679 rte->rtekind == RTE_VALUES ||
1680 RelnameCmp(&hint->relname, &rte->eref->aliasname) != 0)
1684 * 複数のスキャンヒントが指定されていた場合でも、1つのネストレベルで
1685 * スキャン関連のGUCパラメータを変更する。
1687 if (save_nestlevel == 0)
1688 save_nestlevel = NewGUCNestLevel();
1691 * TODO ヒントで指定されたScan方式が最安価でない場合のみ、Pathを生成
1694 set_scan_config_options(hint->enforce_mask, plan->context);
1696 rel->pathlist = NIL; /* TODO 解放 */
1697 set_plain_rel_pathlist(root, rel, rte);
1704 * Restore the GUC variables we set above.
1706 if (save_nestlevel != 0)
1707 AtEOXact_GUC(true, save_nestlevel);
1711 * src/backend/optimizer/path/joinrels.c
1712 * export make_join_rel() をラップする関数
1714 * ヒントにしたがって、enabele_* パラメータを変更した上で、make_join_rel()を
1718 pg_hint_plan_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
1721 JoinMethodHint *hint;
1725 joinrelids = bms_union(rel1->relids, rel2->relids);
1726 hint = scan_join_hint(joinrelids);
1727 bms_free(joinrelids);
1730 return make_join_rel(root, rel1, rel2);
1732 save_nestlevel = NewGUCNestLevel();
1734 set_join_config_options(hint->enforce_mask, global->context);
1736 rel = make_join_rel(root, rel1, rel2);
1739 * Restore the GUC variables we set above.
1741 AtEOXact_GUC(true, save_nestlevel);
1747 pg_hint_plan_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
1750 * pg_hint_planが無効、または有効なヒントが1つも指定されなかった場合は、標準
1755 if (prev_join_search)
1756 return (*prev_join_search) (root, levels_needed, initial_rels);
1757 else if (enable_geqo && levels_needed >= geqo_threshold)
1758 return geqo(root, levels_needed, initial_rels);
1760 return standard_join_search(root, levels_needed, initial_rels);
1763 transform_join_hints(global, root, levels_needed, initial_rels);
1764 rebuild_scan_path(global, root, levels_needed, initial_rels);
1767 * GEQOを使用する条件を満たした場合は、GEQOを用いた結合方式の検索を行う。
1768 * このとき、スキャン方式のヒントとSetヒントのみが有効になり、結合方式や結合
1769 * 順序はヒント句は無効になりGEQOのアルゴリズムで決定される。
1771 if (enable_geqo && levels_needed >= geqo_threshold)
1772 return geqo(root, levels_needed, initial_rels);
1774 return standard_join_search_org(root, levels_needed, initial_rels);
1777 #define standard_join_search standard_join_search_org
1778 #define join_search_one_level pg_hint_plan_join_search_one_level
1779 #define make_join_rel pg_hint_plan_make_join_rel