1 /*-------------------------------------------------------------------------
4 * Track statement execution in current/last transaction.
6 * Copyright (c) 2011, PostgreSQL Global Development Group
9 * contrib/pg_hint_plan/pg_hint_plan.c
11 *-------------------------------------------------------------------------
15 #include "nodes/print.h"
16 #include "utils/elog.h"
17 #include "utils/builtins.h"
18 #include "utils/memutils.h"
19 #include "optimizer/cost.h"
20 #include "optimizer/joininfo.h"
21 #include "optimizer/pathnode.h"
22 #include "optimizer/paths.h"
24 #ifdef PG_MODULE_MAGIC
28 #define HASH_ENTRIES 201
32 ENABLE_SEQSCAN = 0x01,
33 ENABLE_INDEXSCAN = 0x02,
34 ENABLE_BITMAPSCAN = 0x04,
35 ENABLE_TIDSCAN = 0x08,
36 ENABLE_NESTLOOP = 0x10,
37 ENABLE_MERGEJOIN = 0x20,
38 ENABLE_HASHJOIN = 0x40
41 typedef struct tidlist
47 typedef struct hash_entry
50 unsigned char enforce_mask;
51 struct hash_entry *next;
54 static HashEntry *hashent[HASH_ENTRIES];
55 static bool print_log = false;
57 /* Module callbacks */
61 /* Join Method Hints */
62 typedef struct RelIdInfo
69 typedef struct JoinHint
74 unsigned char enforce_mask;
77 typedef struct GucVariables
80 bool enable_indexscan;
81 bool enable_bitmapscan;
87 bool enable_mergejoin;
91 static void backup_guc(GucVariables *backup);
92 static void restore_guc(GucVariables *backup);
93 static void set_guc(unsigned char enforce_mask);
94 static void build_join_hints(PlannerInfo *root, int level, List *initial_rels);
95 static RelOptInfo *my_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2);
96 static RelOptInfo *my_join_search(PlannerInfo *root, int levels_needed,
98 static void my_join_search_one_level(PlannerInfo *root, int level);
99 static void make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
100 static void make_rels_by_clauseless_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
101 static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel);
103 static join_search_hook_type org_join_search = NULL;
104 static List **join_hint_level = NULL;
107 * Module load callbacks
112 org_join_search = join_search_hook;
113 join_search_hook = my_join_search;
117 * Module unload callback
122 join_search_hook = org_join_search;
126 * pg_add_hint()で登録した個別のヒントを、使用しやすい構造に変換する。
129 set_relids(HashEntry *ent, RelIdInfo **relids, int nrels)
135 hint = palloc(sizeof(JoinHint));
136 hint->joinrelids = NULL;
137 hint->relidinfos = NIL;
139 for (i = 0; i < ent->tidlist.nrels; i++)
141 for (j = 0; j < nrels; j++)
143 if (ent->tidlist.oids[i] == relids[j]->oid)
145 hint->relidinfos = lappend(hint->relidinfos, relids[j]);
147 bms_add_member(hint->joinrelids, relids[j]->relid);
154 list_free(hint->relidinfos);
160 hint->nrels = ent->tidlist.nrels;
161 hint->enforce_mask = ent->enforce_mask;
167 * pg_add_hint()で登録したヒントから、今回のクエリで使用するもののみ抽出し、
171 build_join_hints(PlannerInfo *root, int level, List *initial_rels)
178 relids = palloc(sizeof(RelIdInfo *) * root->simple_rel_array_size);
183 foreach(l, initial_rels)
185 RelOptInfo *rel = (RelOptInfo *) lfirst(l);
186 elog_node_display(INFO, "initial_rels", rel, true);
188 elog_node_display(INFO, "root", root, true);
189 elog(INFO, "%s(simple_rel_array_size:%d, level:%d, query_level:%d, parent_root:%p)",
190 __func__, root->simple_rel_array_size, level, root->query_level, root->parent_root);
193 for (i = 0, nrels = 0; i < root->simple_rel_array_size; i++)
195 if (root->simple_rel_array[i] == NULL)
198 relids[nrels] = palloc(sizeof(RelIdInfo));
200 Assert(i == root->simple_rel_array[i]->relid);
202 relids[nrels]->relid = i;
203 relids[nrels]->oid = root->simple_rte_array[i]->relid;
204 relids[nrels]->eref = root->simple_rte_array[i]->eref;
205 //elog(INFO, "%d:%d:%d:%s", i, relids[nrels]->relid, relids[nrels]->oid, relids[nrels]->eref->aliasname);
210 join_hint_level = palloc0(sizeof(List *) * (root->simple_rel_array_size));
212 for (i = 0; i < HASH_ENTRIES; i++)
216 for (next = hashent[i]; next; next = next->next)
219 if (!(next->enforce_mask & ENABLE_HASHJOIN) &&
220 !(next->enforce_mask & ENABLE_NESTLOOP) &&
221 !(next->enforce_mask & ENABLE_MERGEJOIN))
224 if ((hint = set_relids(next, relids, nrels)) == NULL)
227 lv = bms_num_members(hint->joinrelids);
228 join_hint_level[lv] = lappend(join_hint_level[lv], hint);
234 backup_guc(GucVariables *backup)
236 backup->enable_seqscan = enable_seqscan;
237 backup->enable_indexscan = enable_indexscan;
238 backup->enable_bitmapscan = enable_bitmapscan;
239 backup->enable_tidscan = enable_tidscan;
240 backup->enable_sort = enable_sort;
241 backup->enable_hashagg = enable_hashagg;
242 backup->enable_nestloop = enable_nestloop;
243 backup->enable_material = enable_material;
244 backup->enable_mergejoin = enable_mergejoin;
245 backup->enable_hashjoin = enable_hashjoin;
249 restore_guc(GucVariables *backup)
251 enable_seqscan = backup->enable_seqscan;
252 enable_indexscan = backup->enable_indexscan;
253 enable_bitmapscan = backup->enable_bitmapscan;
254 enable_tidscan = backup->enable_tidscan;
255 enable_sort = backup->enable_sort;
256 enable_hashagg = backup->enable_hashagg;
257 enable_nestloop = backup->enable_nestloop;
258 enable_material = backup->enable_material;
259 enable_mergejoin = backup->enable_mergejoin;
260 enable_hashjoin = backup->enable_hashjoin;
264 set_guc(unsigned char enforce_mask)
266 enable_mergejoin = enforce_mask & ENABLE_MERGEJOIN ? true : false;
267 enable_hashjoin = enforce_mask & ENABLE_HASHJOIN ? true : false;
268 enable_nestloop = enforce_mask & ENABLE_NESTLOOP ? true : false;
272 * relidビットマスクと一致するヒントを探す
275 find_join_hint(Relids joinrelids)
280 join_hint = join_hint_level[bms_num_members(joinrelids)];
281 foreach(l, join_hint)
283 JoinHint *hint = (JoinHint *) lfirst(l);
284 if (bms_equal(joinrelids, hint->joinrelids))
292 * src/backend/optimizer/path/joinrels.c
293 * export make_join_rel() をラップする関数
295 * ヒントにしたがって、enabele_* パラメータを変更した上で、make_join_rel()を
299 my_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
306 joinrelids = bms_union(rel1->relids, rel2->relids);
307 hint = find_join_hint(joinrelids);
308 bms_free(joinrelids);
313 set_guc(hint->enforce_mask);
316 rel = make_join_rel(root, rel1, rel2);
325 * PostgreSQL 本体から流用した関数
329 * src/backend/optimizer/path/allpaths.c
330 * export standard_join_search() を流用
333 * build_join_hints() の呼び出しを追加
336 * standard_join_search
337 * Find possible joinpaths for a query by successively finding ways
338 * to join component relations into join relations.
340 * 'levels_needed' is the number of iterations needed, ie, the number of
341 * independent jointree items in the query. This is > 1.
343 * 'initial_rels' is a list of RelOptInfo nodes for each independent
344 * jointree item. These are the components to be joined together.
345 * Note that levels_needed == list_length(initial_rels).
347 * Returns the final level of join relations, i.e., the relation that is
348 * the result of joining all the original relations together.
349 * At least one implementation path must be provided for this relation and
350 * all required sub-relations.
352 * To support loadable plugins that modify planner behavior by changing the
353 * join searching algorithm, we provide a hook variable that lets a plugin
354 * replace or supplement this function. Any such hook must return the same
355 * final join relation as the standard code would, but it might have a
356 * different set of implementation paths attached, and only the sub-joinrels
357 * needed for these paths need have been instantiated.
359 * Note to plugin authors: the functions invoked during standard_join_search()
360 * modify root->join_rel_list and root->join_rel_hash. If you want to do more
361 * than one join-order search, you'll probably need to save and restore the
362 * original states of those data structures. See geqo_eval() for an example.
365 my_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
371 * This function cannot be invoked recursively within any one planning
372 * problem, so join_rel_level[] can't be in use already.
374 Assert(root->join_rel_level == NULL);
377 * We employ a simple "dynamic programming" algorithm: we first find all
378 * ways to build joins of two jointree items, then all ways to build joins
379 * of three items (from two-item joins and single items), then four-item
380 * joins, and so on until we have considered all ways to join all the
381 * items into one rel.
383 * root->join_rel_level[j] is a list of all the j-item rels. Initially we
384 * set root->join_rel_level[1] to represent all the single-jointree-item
387 root->join_rel_level = (List **) palloc0((levels_needed + 1) * sizeof(List *));
389 root->join_rel_level[1] = initial_rels;
391 build_join_hints(root, levels_needed, initial_rels);
393 for (lev = 2; lev <= levels_needed; lev++)
398 * Determine all possible pairs of relations to be joined at this
399 * level, and build paths for making each one from every available
400 * pair of lower-level relations.
402 my_join_search_one_level(root, lev);
405 * Do cleanup work on each just-processed rel.
407 foreach(lc, root->join_rel_level[lev])
409 rel = (RelOptInfo *) lfirst(lc);
411 /* Find and save the cheapest paths for this rel */
414 #ifdef OPTIMIZER_DEBUG
415 debug_print_rel(root, rel);
421 * We should have a single rel at the final level.
423 if (root->join_rel_level[levels_needed] == NIL)
424 elog(ERROR, "failed to build any %d-way joins", levels_needed);
425 Assert(list_length(root->join_rel_level[levels_needed]) == 1);
427 rel = (RelOptInfo *) linitial(root->join_rel_level[levels_needed]);
429 root->join_rel_level = NULL;
435 * src/backend/optimizer/path/joinrels.c
436 * static join_search_one_level() を流用
439 * make_join_rel() の呼び出しをラップする、my_make_join_rel()の呼び出しに変更
442 * join_search_one_level
443 * Consider ways to produce join relations containing exactly 'level'
444 * jointree items. (This is one step of the dynamic-programming method
445 * embodied in standard_join_search.) Join rel nodes for each feasible
446 * combination of lower-level rels are created and returned in a list.
447 * Implementation paths are created for each such joinrel, too.
449 * level: level of rels we want to make this time
450 * root->join_rel_level[j], 1 <= j < level, is a list of rels containing j items
452 * The result is returned in root->join_rel_level[level].
455 my_join_search_one_level(PlannerInfo *root, int level)
457 List **joinrels = root->join_rel_level;
461 Assert(joinrels[level] == NIL);
463 /* Set join_cur_level so that new joinrels are added to proper list */
464 root->join_cur_level = level;
467 * First, consider left-sided and right-sided plans, in which rels of
468 * exactly level-1 member relations are joined against initial relations.
469 * We prefer to join using join clauses, but if we find a rel of level-1
470 * members that has no join clauses, we will generate Cartesian-product
471 * joins against all initial rels not already contained in it.
473 * In the first pass (level == 2), we try to join each initial rel to each
474 * initial rel that appears later in joinrels[1]. (The mirror-image joins
475 * are handled automatically by make_join_rel.) In later passes, we try
476 * to join rels of size level-1 from joinrels[level-1] to each initial rel
479 foreach(r, joinrels[level - 1])
481 RelOptInfo *old_rel = (RelOptInfo *) lfirst(r);
482 ListCell *other_rels;
485 other_rels = lnext(r); /* only consider remaining initial
488 other_rels = list_head(joinrels[1]); /* consider all initial
491 if (old_rel->joininfo != NIL || old_rel->has_eclass_joins ||
492 has_join_restriction(root, old_rel))
495 * Note that if all available join clauses for this rel require
496 * more than one other rel, we will fail to make any joins against
497 * it here. In most cases that's OK; it'll be considered by
498 * "bushy plan" join code in a higher-level pass where we have
499 * those other rels collected into a join rel.
501 * See also the last-ditch case below.
503 make_rels_by_clause_joins(root,
510 * Oops, we have a relation that is not joined to any other
511 * relation, either directly or by join-order restrictions.
512 * Cartesian product time.
514 make_rels_by_clauseless_joins(root,
521 * Now, consider "bushy plans" in which relations of k initial rels are
522 * joined to relations of level-k initial rels, for 2 <= k <= level-2.
524 * We only consider bushy-plan joins for pairs of rels where there is a
525 * suitable join clause (or join order restriction), in order to avoid
526 * unreasonable growth of planning time.
530 int other_level = level - k;
533 * Since make_join_rel(x, y) handles both x,y and y,x cases, we only
534 * need to go as far as the halfway point.
539 foreach(r, joinrels[k])
541 RelOptInfo *old_rel = (RelOptInfo *) lfirst(r);
542 ListCell *other_rels;
546 * We can ignore clauseless joins here, *except* when they
547 * participate in join-order restrictions --- then we might have
548 * to force a bushy join plan.
550 if (old_rel->joininfo == NIL && !old_rel->has_eclass_joins &&
551 !has_join_restriction(root, old_rel))
554 if (k == other_level)
555 other_rels = lnext(r); /* only consider remaining rels */
557 other_rels = list_head(joinrels[other_level]);
559 for_each_cell(r2, other_rels)
561 RelOptInfo *new_rel = (RelOptInfo *) lfirst(r2);
563 if (!bms_overlap(old_rel->relids, new_rel->relids))
566 * OK, we can build a rel of the right level from this
567 * pair of rels. Do so if there is at least one usable
568 * join clause or a relevant join restriction.
570 if (have_relevant_joinclause(root, old_rel, new_rel) ||
571 have_join_order_restriction(root, old_rel, new_rel))
573 (void) my_make_join_rel(root, old_rel, new_rel);
581 * Last-ditch effort: if we failed to find any usable joins so far, force
582 * a set of cartesian-product joins to be generated. This handles the
583 * special case where all the available rels have join clauses but we
584 * cannot use any of those clauses yet. An example is
586 * SELECT * FROM a,b,c WHERE (a.f1 + b.f2 + c.f3) = 0;
588 * The join clause will be usable at level 3, but at level 2 we have no
589 * choice but to make cartesian joins. We consider only left-sided and
590 * right-sided cartesian joins in this case (no bushy).
592 if (joinrels[level] == NIL)
595 * This loop is just like the first one, except we always call
596 * make_rels_by_clauseless_joins().
598 foreach(r, joinrels[level - 1])
600 RelOptInfo *old_rel = (RelOptInfo *) lfirst(r);
601 ListCell *other_rels;
604 other_rels = lnext(r); /* only consider remaining initial
607 other_rels = list_head(joinrels[1]); /* consider all initial
610 make_rels_by_clauseless_joins(root,
616 * When special joins are involved, there may be no legal way
617 * to make an N-way join for some values of N. For example consider
619 * SELECT ... FROM t1 WHERE
620 * x IN (SELECT ... FROM t2,t3 WHERE ...) AND
621 * y IN (SELECT ... FROM t4,t5 WHERE ...)
623 * We will flatten this query to a 5-way join problem, but there are
624 * no 4-way joins that join_is_legal() will consider legal. We have
625 * to accept failure at level 4 and go on to discover a workable
626 * bushy plan at level 5.
628 * However, if there are no special joins then join_is_legal() should
629 * never fail, and so the following sanity check is useful.
632 if (joinrels[level] == NIL && root->join_info_list == NIL)
633 elog(ERROR, "failed to build any %d-way joins", level);
638 * src/backend/optimizer/path/joinrels.c
639 * static make_rels_by_clause_joins() を流用
642 * make_join_rel() の呼び出しをラップする、my_make_join_rel()の呼び出しに変更
645 * make_rels_by_clause_joins
646 * Build joins between the given relation 'old_rel' and other relations
647 * that participate in join clauses that 'old_rel' also participates in
648 * (or participate in join-order restrictions with it).
649 * The join rels are returned in root->join_rel_level[join_cur_level].
651 * Note: at levels above 2 we will generate the same joined relation in
652 * multiple ways --- for example (a join b) join c is the same RelOptInfo as
653 * (b join c) join a, though the second case will add a different set of Paths
654 * to it. This is the reason for using the join_rel_level mechanism, which
655 * automatically ensures that each new joinrel is only added to the list once.
657 * 'old_rel' is the relation entry for the relation to be joined
658 * 'other_rels': the first cell in a linked list containing the other
659 * rels to be considered for joining
661 * Currently, this is only used with initial rels in other_rels, but it
662 * will work for joining to joinrels too.
665 make_rels_by_clause_joins(PlannerInfo *root,
667 ListCell *other_rels)
671 for_each_cell(l, other_rels)
673 RelOptInfo *other_rel = (RelOptInfo *) lfirst(l);
675 if (!bms_overlap(old_rel->relids, other_rel->relids) &&
676 (have_relevant_joinclause(root, old_rel, other_rel) ||
677 have_join_order_restriction(root, old_rel, other_rel)))
679 (void) my_make_join_rel(root, old_rel, other_rel);
685 * src/backend/optimizer/path/joinrels.c
686 * static make_rels_by_clauseless_joins() を流用
689 * make_join_rel() の呼び出しをラップする、my_make_join_rel()の呼び出しに変更
692 * make_rels_by_clauseless_joins
693 * Given a relation 'old_rel' and a list of other relations
694 * 'other_rels', create a join relation between 'old_rel' and each
695 * member of 'other_rels' that isn't already included in 'old_rel'.
696 * The join rels are returned in root->join_rel_level[join_cur_level].
698 * 'old_rel' is the relation entry for the relation to be joined
699 * 'other_rels': the first cell of a linked list containing the
700 * other rels to be considered for joining
702 * Currently, this is only used with initial rels in other_rels, but it would
703 * work for joining to joinrels too.
706 make_rels_by_clauseless_joins(PlannerInfo *root,
708 ListCell *other_rels)
712 for_each_cell(l, other_rels)
714 RelOptInfo *other_rel = (RelOptInfo *) lfirst(l);
716 if (!bms_overlap(other_rel->relids, old_rel->relids))
718 (void) my_make_join_rel(root, old_rel, other_rel);
724 * src/backend/optimizer/path/joinrels.c
725 * static has_join_restriction() を流用
731 * has_join_restriction
732 * Detect whether the specified relation has join-order restrictions
733 * due to being inside an outer join or an IN (sub-SELECT).
735 * Essentially, this tests whether have_join_order_restriction() could
736 * succeed with this rel and some other one. It's OK if we sometimes
737 * say "true" incorrectly. (Therefore, we don't bother with the relatively
738 * expensive has_legal_joinclause test.)
741 has_join_restriction(PlannerInfo *root, RelOptInfo *rel)
745 foreach(l, root->join_info_list)
747 SpecialJoinInfo *sjinfo = (SpecialJoinInfo *) lfirst(l);
749 /* ignore full joins --- other mechanisms preserve their ordering */
750 if (sjinfo->jointype == JOIN_FULL)
753 /* ignore if SJ is already contained in rel */
754 if (bms_is_subset(sjinfo->min_lefthand, rel->relids) &&
755 bms_is_subset(sjinfo->min_righthand, rel->relids))
758 /* restricted if it overlaps LHS or RHS, but doesn't contain SJ */
759 if (bms_overlap(sjinfo->min_lefthand, rel->relids) ||
760 bms_overlap(sjinfo->min_righthand, rel->relids))