From 9f444026f40b7561a311ca9ea881ebcf29e7852f Mon Sep 17 00:00:00 2001 From: Mitsuru Hasegawa Date: Mon, 2 Apr 2012 20:45:41 +0900 Subject: [PATCH] =?utf8?q?9.1=E5=AF=BE=E5=BF=9C?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- Makefile | 12 +- core.c | 435 ++++++++++++++++++++++++++++++ expected/base_plan-9.1.out | 76 ++++++ expected/base_plan-9.2.out | 76 ++++++ expected/pg_hint_plan.out | 660 ++++++++++++++++++++------------------------- pg_hint_plan.c | 522 ++++------------------------------- sql/base_plan.sql | 12 + sql/pg_hint_plan.sql | 264 +++--------------- 8 files changed, 1000 insertions(+), 1057 deletions(-) create mode 100644 core.c create mode 100644 expected/base_plan-9.1.out create mode 100644 expected/base_plan-9.2.out create mode 100644 sql/base_plan.sql diff --git a/Makefile b/Makefile index 5b156cd..3fa0bf3 100644 --- a/Makefile +++ b/Makefile @@ -5,7 +5,7 @@ # MODULES = pg_hint_plan -REGRESS = init pg_hint_plan +REGRESS = init base_plan pg_hint_plan ifdef USE_PGXS PG_CONFIG = pg_config @@ -17,3 +17,13 @@ top_builddir = ../.. include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif + +expected/base_plan.out: expected/base_plan-$(MAJORVERSION).out + cp expected/base_plan-$(MAJORVERSION).out expected/base_plan.out + +.PHONY: subclean +clean: subclean +subclean: + rm -f expected/base_plan.out + +installcheck: expected/base_plan.out diff --git a/core.c b/core.c new file mode 100644 index 0000000..59b2a2a --- /dev/null +++ b/core.c @@ -0,0 +1,435 @@ +/* + * PostgreSQL 本体から流用した関数 + * + * src/backend/optimizer/path/allpaths.c + * standard_join_search() + * set_plain_rel_pathlist() + * + * src/backend/optimizer/path/joinrels.c: + * join_search_one_level() + * make_rels_by_clause_joins() + * make_rels_by_clauseless_joins() + * has_join_restriction() + */ + +/* + * standard_join_search + * Find possible joinpaths for a query by successively finding ways + * to join component relations into join relations. + * + * 'levels_needed' is the number of iterations needed, ie, the number of + * independent jointree items in the query. This is > 1. + * + * 'initial_rels' is a list of RelOptInfo nodes for each independent + * jointree item. These are the components to be joined together. + * Note that levels_needed == list_length(initial_rels). + * + * Returns the final level of join relations, i.e., the relation that is + * the result of joining all the original relations together. + * At least one implementation path must be provided for this relation and + * all required sub-relations. + * + * To support loadable plugins that modify planner behavior by changing the + * join searching algorithm, we provide a hook variable that lets a plugin + * replace or supplement this function. Any such hook must return the same + * final join relation as the standard code would, but it might have a + * different set of implementation paths attached, and only the sub-joinrels + * needed for these paths need have been instantiated. + * + * Note to plugin authors: the functions invoked during standard_join_search() + * modify root->join_rel_list and root->join_rel_hash. If you want to do more + * than one join-order search, you'll probably need to save and restore the + * original states of those data structures. See geqo_eval() for an example. + */ +RelOptInfo * +standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels) +{ + int lev; + RelOptInfo *rel; + + /* + * This function cannot be invoked recursively within any one planning + * problem, so join_rel_level[] can't be in use already. + */ + Assert(root->join_rel_level == NULL); + + /* + * We employ a simple "dynamic programming" algorithm: we first find all + * ways to build joins of two jointree items, then all ways to build joins + * of three items (from two-item joins and single items), then four-item + * joins, and so on until we have considered all ways to join all the + * items into one rel. + * + * root->join_rel_level[j] is a list of all the j-item rels. Initially we + * set root->join_rel_level[1] to represent all the single-jointree-item + * relations. + */ + root->join_rel_level = (List **) palloc0((levels_needed + 1) * sizeof(List *)); + + root->join_rel_level[1] = initial_rels; + + for (lev = 2; lev <= levels_needed; lev++) + { + ListCell *lc; + + /* + * Determine all possible pairs of relations to be joined at this + * level, and build paths for making each one from every available + * pair of lower-level relations. + */ + join_search_one_level(root, lev); + + /* + * Do cleanup work on each just-processed rel. + */ + foreach(lc, root->join_rel_level[lev]) + { + rel = (RelOptInfo *) lfirst(lc); + + /* Find and save the cheapest paths for this rel */ + set_cheapest(rel); + +#ifdef OPTIMIZER_DEBUG + debug_print_rel(root, rel); +#endif + } + } + + /* + * We should have a single rel at the final level. + */ + if (root->join_rel_level[levels_needed] == NIL) + elog(ERROR, "failed to build any %d-way joins", levels_needed); + Assert(list_length(root->join_rel_level[levels_needed]) == 1); + + rel = (RelOptInfo *) linitial(root->join_rel_level[levels_needed]); + + root->join_rel_level = NULL; + + return rel; +} + +/* + * set_plain_rel_pathlist + * Build access paths for a plain relation (no subquery, no inheritance) + */ +static void +set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) +{ + /* Consider sequential scan */ + add_path(rel, create_seqscan_path(root, rel)); + + /* Consider index scans */ + create_index_paths(root, rel); + + /* Consider TID scans */ + create_tidscan_paths(root, rel); + + /* Now find the cheapest of the paths for this rel */ + set_cheapest(rel); +} + +/* + * join_search_one_level + * Consider ways to produce join relations containing exactly 'level' + * jointree items. (This is one step of the dynamic-programming method + * embodied in standard_join_search.) Join rel nodes for each feasible + * combination of lower-level rels are created and returned in a list. + * Implementation paths are created for each such joinrel, too. + * + * level: level of rels we want to make this time + * root->join_rel_level[j], 1 <= j < level, is a list of rels containing j items + * + * The result is returned in root->join_rel_level[level]. + */ +void +join_search_one_level(PlannerInfo *root, int level) +{ + List **joinrels = root->join_rel_level; + ListCell *r; + int k; + + Assert(joinrels[level] == NIL); + + /* Set join_cur_level so that new joinrels are added to proper list */ + root->join_cur_level = level; + + /* + * First, consider left-sided and right-sided plans, in which rels of + * exactly level-1 member relations are joined against initial relations. + * We prefer to join using join clauses, but if we find a rel of level-1 + * members that has no join clauses, we will generate Cartesian-product + * joins against all initial rels not already contained in it. + * + * In the first pass (level == 2), we try to join each initial rel to each + * initial rel that appears later in joinrels[1]. (The mirror-image joins + * are handled automatically by make_join_rel.) In later passes, we try + * to join rels of size level-1 from joinrels[level-1] to each initial rel + * in joinrels[1]. + */ + foreach(r, joinrels[level - 1]) + { + RelOptInfo *old_rel = (RelOptInfo *) lfirst(r); + ListCell *other_rels; + + if (level == 2) + other_rels = lnext(r); /* only consider remaining initial + * rels */ + else + other_rels = list_head(joinrels[1]); /* consider all initial + * rels */ + + if (old_rel->joininfo != NIL || old_rel->has_eclass_joins || + has_join_restriction(root, old_rel)) + { + /* + * Note that if all available join clauses for this rel require + * more than one other rel, we will fail to make any joins against + * it here. In most cases that's OK; it'll be considered by + * "bushy plan" join code in a higher-level pass where we have + * those other rels collected into a join rel. + * + * See also the last-ditch case below. + */ + make_rels_by_clause_joins(root, + old_rel, + other_rels); + } + else + { + /* + * Oops, we have a relation that is not joined to any other + * relation, either directly or by join-order restrictions. + * Cartesian product time. + */ + make_rels_by_clauseless_joins(root, + old_rel, + other_rels); + } + } + + /* + * Now, consider "bushy plans" in which relations of k initial rels are + * joined to relations of level-k initial rels, for 2 <= k <= level-2. + * + * We only consider bushy-plan joins for pairs of rels where there is a + * suitable join clause (or join order restriction), in order to avoid + * unreasonable growth of planning time. + */ + for (k = 2;; k++) + { + int other_level = level - k; + + /* + * Since make_join_rel(x, y) handles both x,y and y,x cases, we only + * need to go as far as the halfway point. + */ + if (k > other_level) + break; + + foreach(r, joinrels[k]) + { + RelOptInfo *old_rel = (RelOptInfo *) lfirst(r); + ListCell *other_rels; + ListCell *r2; + + /* + * We can ignore clauseless joins here, *except* when they + * participate in join-order restrictions --- then we might have + * to force a bushy join plan. + */ + if (old_rel->joininfo == NIL && !old_rel->has_eclass_joins && + !has_join_restriction(root, old_rel)) + continue; + + if (k == other_level) + other_rels = lnext(r); /* only consider remaining rels */ + else + other_rels = list_head(joinrels[other_level]); + + for_each_cell(r2, other_rels) + { + RelOptInfo *new_rel = (RelOptInfo *) lfirst(r2); + + if (!bms_overlap(old_rel->relids, new_rel->relids)) + { + /* + * OK, we can build a rel of the right level from this + * pair of rels. Do so if there is at least one usable + * join clause or a relevant join restriction. + */ + if (have_relevant_joinclause(root, old_rel, new_rel) || + have_join_order_restriction(root, old_rel, new_rel)) + { + (void) make_join_rel(root, old_rel, new_rel); + } + } + } + } + } + + /* + * Last-ditch effort: if we failed to find any usable joins so far, force + * a set of cartesian-product joins to be generated. This handles the + * special case where all the available rels have join clauses but we + * cannot use any of those clauses yet. An example is + * + * SELECT * FROM a,b,c WHERE (a.f1 + b.f2 + c.f3) = 0; + * + * The join clause will be usable at level 3, but at level 2 we have no + * choice but to make cartesian joins. We consider only left-sided and + * right-sided cartesian joins in this case (no bushy). + */ + if (joinrels[level] == NIL) + { + /* + * This loop is just like the first one, except we always call + * make_rels_by_clauseless_joins(). + */ + foreach(r, joinrels[level - 1]) + { + RelOptInfo *old_rel = (RelOptInfo *) lfirst(r); + ListCell *other_rels; + + if (level == 2) + other_rels = lnext(r); /* only consider remaining initial + * rels */ + else + other_rels = list_head(joinrels[1]); /* consider all initial + * rels */ + + make_rels_by_clauseless_joins(root, + old_rel, + other_rels); + } + + /*---------- + * When special joins are involved, there may be no legal way + * to make an N-way join for some values of N. For example consider + * + * SELECT ... FROM t1 WHERE + * x IN (SELECT ... FROM t2,t3 WHERE ...) AND + * y IN (SELECT ... FROM t4,t5 WHERE ...) + * + * We will flatten this query to a 5-way join problem, but there are + * no 4-way joins that join_is_legal() will consider legal. We have + * to accept failure at level 4 and go on to discover a workable + * bushy plan at level 5. + * + * However, if there are no special joins then join_is_legal() should + * never fail, and so the following sanity check is useful. + *---------- + */ + if (joinrels[level] == NIL && root->join_info_list == NIL) + elog(ERROR, "failed to build any %d-way joins", level); + } +} + +/* + * make_rels_by_clause_joins + * Build joins between the given relation 'old_rel' and other relations + * that participate in join clauses that 'old_rel' also participates in + * (or participate in join-order restrictions with it). + * The join rels are returned in root->join_rel_level[join_cur_level]. + * + * Note: at levels above 2 we will generate the same joined relation in + * multiple ways --- for example (a join b) join c is the same RelOptInfo as + * (b join c) join a, though the second case will add a different set of Paths + * to it. This is the reason for using the join_rel_level mechanism, which + * automatically ensures that each new joinrel is only added to the list once. + * + * 'old_rel' is the relation entry for the relation to be joined + * 'other_rels': the first cell in a linked list containing the other + * rels to be considered for joining + * + * Currently, this is only used with initial rels in other_rels, but it + * will work for joining to joinrels too. + */ +static void +make_rels_by_clause_joins(PlannerInfo *root, + RelOptInfo *old_rel, + ListCell *other_rels) +{ + ListCell *l; + + for_each_cell(l, other_rels) + { + RelOptInfo *other_rel = (RelOptInfo *) lfirst(l); + + if (!bms_overlap(old_rel->relids, other_rel->relids) && + (have_relevant_joinclause(root, old_rel, other_rel) || + have_join_order_restriction(root, old_rel, other_rel))) + { + (void) make_join_rel(root, old_rel, other_rel); + } + } +} + +/* + * make_rels_by_clauseless_joins + * Given a relation 'old_rel' and a list of other relations + * 'other_rels', create a join relation between 'old_rel' and each + * member of 'other_rels' that isn't already included in 'old_rel'. + * The join rels are returned in root->join_rel_level[join_cur_level]. + * + * 'old_rel' is the relation entry for the relation to be joined + * 'other_rels': the first cell of a linked list containing the + * other rels to be considered for joining + * + * Currently, this is only used with initial rels in other_rels, but it would + * work for joining to joinrels too. + */ +static void +make_rels_by_clauseless_joins(PlannerInfo *root, + RelOptInfo *old_rel, + ListCell *other_rels) +{ + ListCell *l; + + for_each_cell(l, other_rels) + { + RelOptInfo *other_rel = (RelOptInfo *) lfirst(l); + + if (!bms_overlap(other_rel->relids, old_rel->relids)) + { + (void) make_join_rel(root, old_rel, other_rel); + } + } +} + +/* + * has_join_restriction + * Detect whether the specified relation has join-order restrictions + * due to being inside an outer join or an IN (sub-SELECT). + * + * Essentially, this tests whether have_join_order_restriction() could + * succeed with this rel and some other one. It's OK if we sometimes + * say "true" incorrectly. (Therefore, we don't bother with the relatively + * expensive has_legal_joinclause test.) + */ +static bool +has_join_restriction(PlannerInfo *root, RelOptInfo *rel) +{ + ListCell *l; + + foreach(l, root->join_info_list) + { + SpecialJoinInfo *sjinfo = (SpecialJoinInfo *) lfirst(l); + + /* ignore full joins --- other mechanisms preserve their ordering */ + if (sjinfo->jointype == JOIN_FULL) + continue; + + /* ignore if SJ is already contained in rel */ + if (bms_is_subset(sjinfo->min_lefthand, rel->relids) && + bms_is_subset(sjinfo->min_righthand, rel->relids)) + continue; + + /* restricted if it overlaps LHS or RHS, but doesn't contain SJ */ + if (bms_overlap(sjinfo->min_lefthand, rel->relids) || + bms_overlap(sjinfo->min_righthand, rel->relids)) + return true; + } + + return false; +} diff --git a/expected/base_plan-9.1.out b/expected/base_plan-9.1.out new file mode 100644 index 0000000..9f4f4a4 --- /dev/null +++ b/expected/base_plan-9.1.out @@ -0,0 +1,76 @@ +-- query type 1 +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +-------------------------------------- + Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 +(4 rows) + +-- query type 2 +EXPLAIN (COSTS false) SELECT * FROM t1, t4 WHERE t1.val < 10; + QUERY PLAN +----------------------------------------- + Nested Loop + -> Bitmap Heap Scan on t1 + Recheck Cond: (val < 10) + -> Bitmap Index Scan on t1_val + Index Cond: (val < 10) + -> Materialize + -> Seq Scan on t4 +(7 rows) + +-- query type 3 +EXPLAIN (COSTS false) SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)'; + QUERY PLAN +--------------------------------------------- + Merge Join + Merge Cond: (t3.id = t4.id) + -> Index Scan using t3_pkey on t3 + -> Sort + Sort Key: t4.id + -> Seq Scan on t4 + Filter: (ctid = '(1,1)'::tid) +(7 rows) + +-- query type 4 +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)'; + QUERY PLAN +----------------------------------------- + Nested Loop + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Index Scan using t2_pkey on t2 + Index Cond: (id = t1.id) +(5 rows) + +-- query type 5 +EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val; + QUERY PLAN +-------------------------------- + Hash Join + Hash Cond: (t1.val = t3.val) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t3 +(5 rows) + +-- query type 6 +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; + QUERY PLAN +-------------------------------------------------- + Merge Join + Merge Cond: (t1.id = t4.id) + -> Merge Join + Merge Cond: (t1.id = t3.id) + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 + -> Index Scan using t3_pkey on t3 + -> Sort + Sort Key: t4.id + -> Seq Scan on t4 +(12 rows) + diff --git a/expected/base_plan-9.2.out b/expected/base_plan-9.2.out new file mode 100644 index 0000000..a474d8a --- /dev/null +++ b/expected/base_plan-9.2.out @@ -0,0 +1,76 @@ +-- query type 1 +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +-------------------------------------- + Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 +(4 rows) + +-- query type 2 +EXPLAIN (COSTS false) SELECT * FROM t1, t4 WHERE t1.val < 10; + QUERY PLAN +----------------------------------------- + Nested Loop + -> Bitmap Heap Scan on t1 + Recheck Cond: (val < 10) + -> Bitmap Index Scan on t1_val + Index Cond: (val < 10) + -> Materialize + -> Seq Scan on t4 +(7 rows) + +-- query type 3 +EXPLAIN (COSTS false) SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)'; + QUERY PLAN +--------------------------------------------- + Merge Join + Merge Cond: (t3.id = t4.id) + -> Index Scan using t3_pkey on t3 + -> Sort + Sort Key: t4.id + -> Seq Scan on t4 + Filter: (ctid = '(1,1)'::tid) +(7 rows) + +-- query type 4 +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)'; + QUERY PLAN +----------------------------------------- + Nested Loop + -> Tid Scan on t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Index Scan using t2_pkey on t2 + Index Cond: (id = t1.id) +(5 rows) + +-- query type 5 +EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val; + QUERY PLAN +-------------------------------- + Hash Join + Hash Cond: (t1.val = t3.val) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t3 +(5 rows) + +-- query type 6 +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; + QUERY PLAN +-------------------------------------------------- + Nested Loop + -> Merge Join + Merge Cond: (t1.id = t4.id) + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 + -> Sort + Sort Key: t4.id + -> Seq Scan on t4 + -> Index Scan using t3_pkey on t3 + Index Cond: (id = t1.id) +(12 rows) + diff --git a/expected/pg_hint_plan.out b/expected/pg_hint_plan.out index 24ce446..5acd1ab 100644 --- a/expected/pg_hint_plan.out +++ b/expected/pg_hint_plan.out @@ -1,100 +1,100 @@ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------------------- - Merge Join (cost=0.00..90.08 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +-------------------------------------- + Merge Join Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 (4 rows) -EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val; - QUERY PLAN ------------------------------------------------------------------------------------ - Merge Join (cost=0.00..1377.64 rows=100000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val; + QUERY PLAN +------------------------------------------- + Merge Join Merge Cond: (t2.val = t1.val) - -> Index Scan using t2_val on t2 (cost=0.00..55.12 rows=1000 width=8) - -> Materialize (cost=0.00..475.21 rows=10000 width=8) - -> Index Scan using t1_val on t1 (cost=0.00..450.21 rows=10000 width=8) + -> Index Scan using t2_val on t2 + -> Materialize + -> Index Scan using t1_val on t1 (5 rows) LOAD 'pg_hint_plan'; -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------------------- - Merge Join (cost=0.00..90.08 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +-------------------------------------- + Merge Join Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 (4 rows) -EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val; - QUERY PLAN ------------------------------------------------------------------------------------ - Merge Join (cost=0.00..1377.64 rows=100000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val; + QUERY PLAN +------------------------------------------- + Merge Join Merge Cond: (t2.val = t1.val) - -> Index Scan using t2_val on t2 (cost=0.00..55.12 rows=1000 width=8) - -> Materialize (cost=0.00..475.21 rows=10000 width=8) - -> Index Scan using t1_val on t1 (cost=0.00..450.21 rows=10000 width=8) + -> Index Scan using t2_val on t2 + -> Materialize + -> Index Scan using t1_val on t1 (5 rows) /* Test (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; INFO: hint syntax error at or near "Test (t1 t2) " DETAIL: Keyword "Test" does not exist. - QUERY PLAN ------------------------------------------------------------------------------- - Merge Join (cost=0.00..90.08 rows=1000 width=16) + QUERY PLAN +-------------------------------------- + Merge Join Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 (4 rows) SET pg_hint_plan.enable TO off; /* Test (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------------------- - Merge Join (cost=0.00..90.08 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +-------------------------------------- + Merge Join Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 (4 rows) SET pg_hint_plan.enable TO on; /*Set(enable_indexscan off)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------- - Hash Join (cost=27.50..220.00 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +------------------------------ + Hash Join Hash Cond: (t1.id = t2.id) - -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) - -> Hash (cost=15.00..15.00 rows=1000 width=8) - -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t2 (5 rows) /* Set(enable_indexscan off) Set(enable_hashjoin off) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN --------------------------------------------------------------------- - Merge Join (cost=874.21..894.21 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +------------------------------- + Merge Join Merge Cond: (t1.id = t2.id) - -> Sort (cost=809.39..834.39 rows=10000 width=8) + -> Sort Sort Key: t1.id - -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) - -> Sort (cost=64.83..67.33 rows=1000 width=8) + -> Seq Scan on t1 + -> Sort Sort Key: t2.id - -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8) + -> Seq Scan on t2 (8 rows) /* Set ( enable_indexscan off ) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------- - Hash Join (cost=27.50..220.00 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +------------------------------ + Hash Join Hash Cond: (t1.id = t2.id) - -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) - -> Hash (cost=15.00..15.00 rows=1000 width=8) - -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t2 (5 rows) /* @@ -104,455 +104,393 @@ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; off ) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------- - Hash Join (cost=27.50..220.00 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +------------------------------ + Hash Join Hash Cond: (t1.id = t2.id) - -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) - -> Hash (cost=15.00..15.00 rows=1000 width=8) - -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t2 (5 rows) /* Set(enable_indexscan off)Set(enable_nestloop off)Set(enable_mergejoin off) Set(enable_seqscan off) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------------------------------------------ - Hash Join (cost=20000000055.75..20000000421.51 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +-------------------------------------------- + Hash Join Hash Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=10000000000.00..10000000318.26 rows=10000 width=8) - -> Hash (cost=10000000043.25..10000000043.25 rows=1000 width=8) - -> Index Scan using t2_pkey on t2 (cost=10000000000.00..10000000043.25 rows=1000 width=8) + -> Index Scan using t1_pkey on t1 + -> Hash + -> Index Scan using t2_pkey on t2 (5 rows) /*Set(work_mem "1M")*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; INFO: invalid value for parameter "work_mem": "1M" HINT: Valid units for this parameter are "kB", "MB", and "GB". - QUERY PLAN ------------------------------------------------------------------------------- - Merge Join (cost=0.00..90.08 rows=1000 width=16) + QUERY PLAN +-------------------------------------- + Merge Join Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 (4 rows) /*Set(work_mem "1MB")*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------------------- - Merge Join (cost=0.00..90.08 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +-------------------------------------- + Merge Join Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 (4 rows) /*Set(work_mem TO "1MB")*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; INFO: hint syntax error at or near ""1MB")" DETAIL: Closed parenthesis is necessary. - QUERY PLAN ------------------------------------------------------------------------------- - Merge Join (cost=0.00..90.08 rows=1000 width=16) + QUERY PLAN +-------------------------------------- + Merge Join Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) -(4 rows) - -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------------------- - Merge Join (cost=0.00..90.08 rows=1000 width=16) - Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 (4 rows) /*SeqScan(t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; INFO: hint syntax error at or near "t2)" DETAIL: Closed parenthesis is necessary. - QUERY PLAN ------------------------------------------------------------------------------- - Merge Join (cost=0.00..90.08 rows=1000 width=16) + QUERY PLAN +-------------------------------------- + Merge Join Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 (4 rows) /*SeqScan(t1)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------- - Hash Join (cost=27.50..220.00 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +------------------------------ + Hash Join Hash Cond: (t1.id = t2.id) - -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) - -> Hash (cost=15.00..15.00 rows=1000 width=8) - -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t2 (5 rows) /*SeqScan(t1)IndexScan(t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ----------------------------------------------------------------------------------- - Hash Join (cost=55.75..248.25 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +-------------------------------------------- + Hash Join Hash Cond: (t1.id = t2.id) - -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) - -> Hash (cost=43.25..43.25 rows=1000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) + -> Seq Scan on t1 + -> Hash + -> Index Scan using t2_pkey on t2 (5 rows) /*BitmapScan(t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ----------------------------------------------------------------------------- - Nested Loop (cost=0.26..42987.50 rows=1000 width=16) - -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) - -> Bitmap Heap Scan on t2 (cost=0.26..4.27 rows=1 width=8) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +------------------------------------------ + Nested Loop + -> Seq Scan on t1 + -> Bitmap Heap Scan on t2 Recheck Cond: (id = t1.id) - -> Bitmap Index Scan on t2_pkey (cost=0.00..0.26 rows=1 width=0) + -> Bitmap Index Scan on t2_pkey Index Cond: (id = t1.id) (6 rows) /*BitmapScan(t2)NoSeqScan(t1)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------------------- - Nested Loop (cost=0.26..43160.76 rows=1000 width=16) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Bitmap Heap Scan on t2 (cost=0.26..4.27 rows=1 width=8) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +------------------------------------------ + Nested Loop + -> Index Scan using t1_pkey on t1 + -> Bitmap Heap Scan on t2 Recheck Cond: (id = t1.id) - -> Bitmap Index Scan on t2_pkey (cost=0.00..0.26 rows=1 width=0) + -> Bitmap Index Scan on t2_pkey Index Cond: (id = t1.id) (6 rows) /*NoIndexScan(t1)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------- - Hash Join (cost=27.50..220.00 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +------------------------------ + Hash Join Hash Cond: (t1.id = t2.id) - -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) - -> Hash (cost=15.00..15.00 rows=1000 width=8) - -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t2 (5 rows) -EXPLAIN SELECT * FROM t1, t4 WHERE t1.val < 10; - QUERY PLAN -------------------------------------------------------------------------------- - Nested Loop (cost=20.01..203.64 rows=10000 width=16) - -> Bitmap Heap Scan on t1 (cost=20.01..77.51 rows=1000 width=8) - Recheck Cond: (val < 10) - -> Bitmap Index Scan on t1_val (cost=0.00..19.76 rows=1000 width=0) - Index Cond: (val < 10) - -> Materialize (cost=0.00..1.15 rows=10 width=8) - -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8) -(7 rows) - /*NoBitmapScan(t1)*/ -EXPLAIN SELECT * FROM t1, t4 WHERE t1.val < 10; - QUERY PLAN ---------------------------------------------------------------- - Nested Loop (cost=0.00..296.12 rows=10000 width=16) - -> Seq Scan on t1 (cost=0.00..170.00 rows=1000 width=8) +EXPLAIN (COSTS false) SELECT * FROM t1, t4 WHERE t1.val < 10; + QUERY PLAN +---------------------------- + Nested Loop + -> Seq Scan on t1 Filter: (val < 10) - -> Materialize (cost=0.00..1.15 rows=10 width=8) - -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8) + -> Materialize + -> Seq Scan on t4 (5 rows) -EXPLAIN SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)'; - QUERY PLAN ---------------------------------------------------------------------------- - Merge Join (cost=1.14..2.41 rows=1 width=16) - Merge Cond: (t3.id = t4.id) - -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8) - -> Sort (cost=1.14..1.14 rows=1 width=8) - Sort Key: t4.id - -> Seq Scan on t4 (cost=0.00..1.12 rows=1 width=8) - Filter: (ctid = '(1,1)'::tid) -(7 rows) - /*TidScan(t4)*/ -EXPLAIN SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)'; - QUERY PLAN ---------------------------------------------------------------------------- - Merge Join (cost=4.02..5.30 rows=1 width=16) +EXPLAIN (COSTS false) SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)'; + QUERY PLAN +----------------------------------------------- + Merge Join Merge Cond: (t3.id = t4.id) - -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8) - -> Sort (cost=4.02..4.03 rows=1 width=8) + -> Index Scan using t3_pkey on t3 + -> Sort Sort Key: t4.id - -> Tid Scan on t4 (cost=0.00..4.01 rows=1 width=8) + -> Tid Scan on t4 TID Cond: (ctid = '(1,1)'::tid) (7 rows) -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)'; - QUERY PLAN ------------------------------------------------------------------------- - Nested Loop (cost=0.00..12.29 rows=1 width=16) - -> Tid Scan on t1 (cost=0.00..4.01 rows=1 width=8) - TID Cond: (ctid = '(1,1)'::tid) - -> Index Scan using t2_pkey on t2 (cost=0.00..8.27 rows=1 width=8) - Index Cond: (id = t1.id) -(5 rows) - /*NoTidScan(t1)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)'; - QUERY PLAN ------------------------------------------------------------------------- - Nested Loop (cost=0.00..178.28 rows=1 width=16) - -> Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)'; + QUERY PLAN +--------------------------------------- + Nested Loop + -> Seq Scan on t1 Filter: (ctid = '(1,1)'::tid) - -> Index Scan using t2_pkey on t2 (cost=0.00..8.27 rows=1 width=8) + -> Index Scan using t2_pkey on t2 Index Cond: (id = t1.id) (5 rows) -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------------------- - Merge Join (cost=0.00..90.08 rows=1000 width=16) - Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) -(4 rows) - /*HashJoin(t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------- - Hash Join (cost=27.50..220.00 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +------------------------------ + Hash Join Hash Cond: (t1.id = t2.id) - -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) - -> Hash (cost=15.00..15.00 rows=1000 width=8) - -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t2 (5 rows) /*NestLoop(t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------------- - Nested Loop (cost=0.00..607.00 rows=1000 width=16) - -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8) - -> Index Scan using t1_pkey on t1 (cost=0.00..0.58 rows=1 width=8) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +-------------------------------------- + Nested Loop + -> Seq Scan on t2 + -> Index Scan using t1_pkey on t1 Index Cond: (id = t2.id) (4 rows) /*NoMergeJoin(t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; - QUERY PLAN ------------------------------------------------------------------- - Hash Join (cost=27.50..220.00 rows=1000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + QUERY PLAN +------------------------------ + Hash Join Hash Cond: (t1.id = t2.id) - -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) - -> Hash (cost=15.00..15.00 rows=1000 width=8) - -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8) -(5 rows) - -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; - QUERY PLAN ----------------------------------------------------------------- - Hash Join (cost=3.25..285.75 rows=10000 width=16) - Hash Cond: (t1.val = t3.val) - -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8) - -> Hash (cost=2.00..2.00 rows=100 width=8) - -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t2 (5 rows) /*MergeJoin(t1 t3)*/ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; - QUERY PLAN ------------------------------------------------------------------------------ - Merge Join (cost=10.07..630.03 rows=10000 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val; + QUERY PLAN +------------------------------------- + Merge Join Merge Cond: (t1.val = t3.val) - -> Index Scan using t1_val on t1 (cost=0.00..450.21 rows=10000 width=8) - -> Sort (cost=5.32..5.57 rows=100 width=8) + -> Index Scan using t1_val on t1 + -> Sort Sort Key: t3.val - -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) + -> Seq Scan on t3 (6 rows) /*NestLoop(t1 t3)*/ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; - QUERY PLAN -------------------------------------------------------------------------- - Nested Loop (cost=0.00..628.20 rows=10000 width=16) - -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) - -> Index Scan using t1_val on t1 (cost=0.00..5.01 rows=100 width=8) +EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val; + QUERY PLAN +------------------------------------- + Nested Loop + -> Seq Scan on t3 + -> Index Scan using t1_val on t1 Index Cond: (val = t3.val) (4 rows) /*NoHashJoin(t1 t3)*/ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; - QUERY PLAN -------------------------------------------------------------------------- - Nested Loop (cost=0.00..628.20 rows=10000 width=16) - -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) - -> Index Scan using t1_val on t1 (cost=0.00..5.01 rows=100 width=8) +EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val; + QUERY PLAN +------------------------------------- + Nested Loop + -> Seq Scan on t3 + -> Index Scan using t1_val on t1 Index Cond: (val = t3.val) (4 rows) -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; - QUERY PLAN ------------------------------------------------------------------------------------------- - Nested Loop (cost=1.27..1.70 rows=1 width=32) - -> Merge Join (cost=1.27..1.42 rows=1 width=24) - Merge Cond: (t1.id = t4.id) - -> Merge Join (cost=0.00..90.08 rows=1000 width=16) - Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) - -> Sort (cost=1.27..1.29 rows=10 width=8) - Sort Key: t4.id - -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8) - -> Index Scan using t3_pkey on t3 (cost=0.00..0.27 rows=1 width=8) - Index Cond: (id = t1.id) -(12 rows) - /*MergeJoin(t4 t1 t2 t3)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; - QUERY PLAN ------------------------------------------------------------------------------------------- - Merge Join (cost=1.27..2.83 rows=1 width=32) +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; + QUERY PLAN +-------------------------------------------------- + Merge Join Merge Cond: (t1.id = t4.id) - -> Merge Join (cost=0.00..15.03 rows=10 width=24) + -> Merge Join Merge Cond: (t1.id = t3.id) - -> Merge Join (cost=0.00..90.08 rows=1000 width=16) + -> Merge Join Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) - -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8) - -> Sort (cost=1.27..1.29 rows=10 width=8) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 + -> Index Scan using t3_pkey on t3 + -> Sort Sort Key: t4.id - -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8) + -> Seq Scan on t4 (12 rows) /*HashJoin(t3 t4 t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; - QUERY PLAN ------------------------------------------------------------------------------------------------- - Hash Join (cost=1.43..3.82 rows=1 width=32) +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; + QUERY PLAN +-------------------------------------------------------- + Hash Join Hash Cond: (t3.id = t1.id) - -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) - -> Hash (cost=1.42..1.42 rows=1 width=24) - -> Merge Join (cost=1.27..1.42 rows=1 width=24) + -> Seq Scan on t3 + -> Hash + -> Merge Join Merge Cond: (t1.id = t4.id) - -> Merge Join (cost=0.00..90.08 rows=1000 width=16) + -> Merge Join Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) - -> Sort (cost=1.27..1.29 rows=10 width=8) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 + -> Sort Sort Key: t4.id - -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8) + -> Seq Scan on t4 (13 rows) +/*NestLoop(t2 t3 t4 t1) IndexScan(t3)*/ +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; + QUERY PLAN +-------------------------------------------------- + Nested Loop + -> Merge Join + Merge Cond: (t1.id = t4.id) + -> Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 + -> Sort + Sort Key: t4.id + -> Seq Scan on t4 + -> Index Scan using t3_pkey on t3 + Index Cond: (id = t1.id) +(12 rows) + /*NoNestLoop(t4 t1 t3 t2)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; - QUERY PLAN ------------------------------------------------------------------------------------------- - Merge Join (cost=1.27..2.83 rows=1 width=32) +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; + QUERY PLAN +-------------------------------------------------- + Merge Join Merge Cond: (t1.id = t4.id) - -> Merge Join (cost=0.00..15.03 rows=10 width=24) + -> Merge Join Merge Cond: (t1.id = t3.id) - -> Merge Join (cost=0.00..90.08 rows=1000 width=16) + -> Merge Join Merge Cond: (t1.id = t2.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) - -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8) - -> Sort (cost=1.27..1.29 rows=10 width=8) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 + -> Index Scan using t3_pkey on t3 + -> Sort Sort Key: t4.id - -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8) + -> Seq Scan on t4 (12 rows) /*Leading(t3 t4)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; - QUERY PLAN ---------------------------------------------------------------------------------------- - Nested Loop (cost=20000000001.27..20000000010.09 rows=1 width=32) - -> Nested Loop (cost=10000000001.27..10000000009.50 rows=1 width=24) - -> Merge Join (cost=1.27..2.68 rows=10 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; + QUERY PLAN +-------------------------------------------------- + Nested Loop + -> Nested Loop + -> Merge Join Merge Cond: (t3.id = t4.id) - -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8) - -> Sort (cost=1.27..1.29 rows=10 width=8) + -> Index Scan using t3_pkey on t3 + -> Sort Sort Key: t4.id - -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..0.67 rows=1 width=8) + -> Seq Scan on t4 + -> Index Scan using t2_pkey on t2 Index Cond: (id = t3.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..0.58 rows=1 width=8) + -> Index Scan using t1_pkey on t1 Index Cond: (id = t2.id) (12 rows) /*Leading(t3 t4 t1)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; - QUERY PLAN ---------------------------------------------------------------------------------------- - Nested Loop (cost=10000000001.27..10000000006.52 rows=1 width=32) - -> Merge Join (cost=1.27..6.23 rows=1 width=24) +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; + QUERY PLAN +-------------------------------------------------- + Nested Loop + -> Merge Join Merge Cond: (t3.id = t1.id) - -> Merge Join (cost=1.27..2.68 rows=10 width=16) + -> Merge Join Merge Cond: (t3.id = t4.id) - -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8) - -> Sort (cost=1.27..1.29 rows=10 width=8) + -> Index Scan using t3_pkey on t3 + -> Sort Sort Key: t4.id - -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..0.27 rows=1 width=8) + -> Seq Scan on t4 + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 Index Cond: (id = t1.id) (12 rows) /*Leading(t3 t4 t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; - QUERY PLAN ---------------------------------------------------------------------------------------- - Nested Loop (cost=1.27..6.52 rows=1 width=32) - -> Merge Join (cost=1.27..6.23 rows=1 width=24) +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; + QUERY PLAN +-------------------------------------------------- + Nested Loop + -> Merge Join Merge Cond: (t3.id = t1.id) - -> Merge Join (cost=1.27..2.68 rows=10 width=16) + -> Merge Join Merge Cond: (t3.id = t4.id) - -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8) - -> Sort (cost=1.27..1.29 rows=10 width=8) + -> Index Scan using t3_pkey on t3 + -> Sort Sort Key: t4.id - -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..0.27 rows=1 width=8) + -> Seq Scan on t4 + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 Index Cond: (id = t1.id) (12 rows) /*Leading(t3 t4 t1 t2 t1)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; INFO: hint syntax error at or near "Leading(t3 t4 t1 t2 t1)" DETAIL: In Leading hint, specified relation name 4 or less. - QUERY PLAN ---------------------------------------------------------------------------------------- - Nested Loop (cost=1.27..6.52 rows=1 width=32) - -> Merge Join (cost=1.27..6.23 rows=1 width=24) + QUERY PLAN +-------------------------------------------------- + Nested Loop + -> Merge Join Merge Cond: (t3.id = t1.id) - -> Merge Join (cost=1.27..2.68 rows=10 width=16) + -> Merge Join Merge Cond: (t3.id = t4.id) - -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8) - -> Sort (cost=1.27..1.29 rows=10 width=8) + -> Index Scan using t3_pkey on t3 + -> Sort Sort Key: t4.id - -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8) - -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..0.27 rows=1 width=8) + -> Seq Scan on t4 + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 Index Cond: (id = t1.id) (12 rows) /*Leading(t3 t4 t4)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; - QUERY PLAN ---------------------------------------------------------------------------------------- - Nested Loop (cost=20000000001.27..20000000010.09 rows=1 width=32) - -> Nested Loop (cost=10000000001.27..10000000009.50 rows=1 width=24) - -> Merge Join (cost=1.27..2.68 rows=10 width=16) +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; + QUERY PLAN +-------------------------------------------------- + Nested Loop + -> Nested Loop + -> Merge Join Merge Cond: (t3.id = t4.id) - -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8) - -> Sort (cost=1.27..1.29 rows=10 width=8) + -> Index Scan using t3_pkey on t3 + -> Sort Sort Key: t4.id - -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8) - -> Index Scan using t2_pkey on t2 (cost=0.00..0.67 rows=1 width=8) + -> Seq Scan on t4 + -> Index Scan using t2_pkey on t2 Index Cond: (id = t3.id) - -> Index Scan using t1_pkey on t1 (cost=0.00..0.58 rows=1 width=8) + -> Index Scan using t1_pkey on t1 Index Cond: (id = t2.id) (12 rows) -\q diff --git a/pg_hint_plan.c b/pg_hint_plan.c index 6e0717e..502c7da 100644 --- a/pg_hint_plan.c +++ b/pg_hint_plan.c @@ -23,7 +23,7 @@ PG_MODULE_MAGIC; #endif -#if PG_VERSION_NUM != 90200 +#if PG_VERSION_NUM < 90100 #error unsupported PostgreSQL version #endif @@ -155,7 +155,8 @@ static const char *ParseSet(PlanHint *plan, Query *parse, char *keyword, const c static const char *Ordered(PlanHint *plan, Query *parse, char *keyword, const char *str); #endif -static void my_join_search_one_level(PlannerInfo *root, int level); +RelOptInfo *standard_join_search_org(PlannerInfo *root, int levels_needed, List *initial_rels); +void pg_hint_plan_join_search_one_level(PlannerInfo *root, int level); static void make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels); static void make_rels_by_clauseless_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels); static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel); @@ -617,6 +618,49 @@ JoinHintCmpIsOrder(const void *a, const void *b) return JoinHintCmp(a, b, true); } +#if PG_VERSION_NUM < 90200 +static int +set_config_option_wrapper(const char *name, const char *value, + GucContext context, GucSource source, + GucAction action, bool changeVal, int elevel) +{ + int result = 0; + MemoryContext ccxt = CurrentMemoryContext; + + PG_TRY(); + { + result = set_config_option(name, value, context, source, + action, changeVal); + } + PG_CATCH(); + { + ErrorData *errdata; + MemoryContext ecxt; + + if (elevel >= ERROR) + PG_RE_THROW(); + + ecxt = MemoryContextSwitchTo(ccxt); + errdata = CopyErrorData(); + ereport(elevel, (errcode(errdata->sqlerrcode), + errmsg("%s", errdata->message), + errdata->detail ? errdetail("%s", errdata->detail) : 0, + errdata->hint ? errhint("%s", errdata->hint) : 0)); + FreeErrorData(errdata); + + MemoryContextSwitchTo(ecxt); + } + PG_END_TRY(); + + return result; +} + +#define set_config_option(name, value, context, source, \ + action, changeVal, elevel) \ + set_config_option_wrapper(name, value, context, source, \ + action, changeVal, elevel) +#endif + static int set_config_options(List *options, GucContext context) { @@ -1616,7 +1660,6 @@ pg_hint_plan_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2 return rel; } -static RelOptInfo *_standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels); static RelOptInfo * pg_hint_plan_join_search(PlannerInfo *root, int levels_needed, List *initial_rels) { @@ -1631,476 +1674,13 @@ pg_hint_plan_join_search(PlannerInfo *root, int levels_needed, List *initial_rel return standard_join_search(root, levels_needed, initial_rels); } - return _standard_join_search(root, levels_needed, initial_rels); -} - -/* - * PostgreSQL 本体から流用した関数 - */ - -/* - * src/backend/optimizer/path/allpaths.c - * export standard_join_search() を流用 - * - * 変更箇所 - * rebuild_join_hints() と rebuild_scan_path() の呼び出しを追加 - */ -/* - * standard_join_search - * Find possible joinpaths for a query by successively finding ways - * to join component relations into join relations. - * - * 'levels_needed' is the number of iterations needed, ie, the number of - * independent jointree items in the query. This is > 1. - * - * 'initial_rels' is a list of RelOptInfo nodes for each independent - * jointree item. These are the components to be joined together. - * Note that levels_needed == list_length(initial_rels). - * - * Returns the final level of join relations, i.e., the relation that is - * the result of joining all the original relations together. - * At least one implementation path must be provided for this relation and - * all required sub-relations. - * - * To support loadable plugins that modify planner behavior by changing the - * join searching algorithm, we provide a hook variable that lets a plugin - * replace or supplement this function. Any such hook must return the same - * final join relation as the standard code would, but it might have a - * different set of implementation paths attached, and only the sub-joinrels - * needed for these paths need have been instantiated. - * - * Note to plugin authors: the functions invoked during standard_join_search() - * modify root->join_rel_list and root->join_rel_hash. If you want to do more - * than one join-order search, you'll probably need to save and restore the - * original states of those data structures. See geqo_eval() for an example. - */ -static RelOptInfo * -_standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels) -{ - int lev; - RelOptInfo *rel; - - /* - * This function cannot be invoked recursively within any one planning - * problem, so join_rel_level[] can't be in use already. - */ - Assert(root->join_rel_level == NULL); - - /* - * We employ a simple "dynamic programming" algorithm: we first find all - * ways to build joins of two jointree items, then all ways to build joins - * of three items (from two-item joins and single items), then four-item - * joins, and so on until we have considered all ways to join all the - * items into one rel. - * - * root->join_rel_level[j] is a list of all the j-item rels. Initially we - * set root->join_rel_level[1] to represent all the single-jointree-item - * relations. - */ - root->join_rel_level = (List **) palloc0((levels_needed + 1) * sizeof(List *)); - - root->join_rel_level[1] = initial_rels; - rebuild_join_hints(global, root, levels_needed, initial_rels); rebuild_scan_path(global, root, levels_needed, initial_rels); - for (lev = 2; lev <= levels_needed; lev++) - { - ListCell *lc; - - /* - * Determine all possible pairs of relations to be joined at this - * level, and build paths for making each one from every available - * pair of lower-level relations. - */ - my_join_search_one_level(root, lev); - - /* - * Do cleanup work on each just-processed rel. - */ - foreach(lc, root->join_rel_level[lev]) - { - rel = (RelOptInfo *) lfirst(lc); - - /* Find and save the cheapest paths for this rel */ - set_cheapest(rel); - -#ifdef OPTIMIZER_DEBUG - debug_print_rel(root, rel); -#endif - } - } - - /* - * We should have a single rel at the final level. - */ - if (root->join_rel_level[levels_needed] == NIL) - elog(ERROR, "failed to build any %d-way joins", levels_needed); - Assert(list_length(root->join_rel_level[levels_needed]) == 1); - - rel = (RelOptInfo *) linitial(root->join_rel_level[levels_needed]); - - root->join_rel_level = NULL; - - return rel; -} - -/* - * src/backend/optimizer/path/joinrels.c - * join_search_one_level() を流用 - * - * 変更箇所 - * make_join_rel() の呼び出しをラップする、pg_hint_plan_make_join_rel()の呼び出しに変更 - */ -/* - * join_search_one_level - * Consider ways to produce join relations containing exactly 'level' - * jointree items. (This is one step of the dynamic-programming method - * embodied in standard_join_search.) Join rel nodes for each feasible - * combination of lower-level rels are created and returned in a list. - * Implementation paths are created for each such joinrel, too. - * - * level: level of rels we want to make this time - * root->join_rel_level[j], 1 <= j < level, is a list of rels containing j items - * - * The result is returned in root->join_rel_level[level]. - */ -static void -my_join_search_one_level(PlannerInfo *root, int level) -{ - List **joinrels = root->join_rel_level; - ListCell *r; - int k; - - Assert(joinrels[level] == NIL); - - /* Set join_cur_level so that new joinrels are added to proper list */ - root->join_cur_level = level; - - /* - * First, consider left-sided and right-sided plans, in which rels of - * exactly level-1 member relations are joined against initial relations. - * We prefer to join using join clauses, but if we find a rel of level-1 - * members that has no join clauses, we will generate Cartesian-product - * joins against all initial rels not already contained in it. - * - * In the first pass (level == 2), we try to join each initial rel to each - * initial rel that appears later in joinrels[1]. (The mirror-image joins - * are handled automatically by make_join_rel.) In later passes, we try - * to join rels of size level-1 from joinrels[level-1] to each initial rel - * in joinrels[1]. - */ - foreach(r, joinrels[level - 1]) - { - RelOptInfo *old_rel = (RelOptInfo *) lfirst(r); - ListCell *other_rels; - - if (level == 2) - other_rels = lnext(r); /* only consider remaining initial - * rels */ - else - other_rels = list_head(joinrels[1]); /* consider all initial - * rels */ - - if (old_rel->joininfo != NIL || old_rel->has_eclass_joins || - has_join_restriction(root, old_rel)) - { - /* - * Note that if all available join clauses for this rel require - * more than one other rel, we will fail to make any joins against - * it here. In most cases that's OK; it'll be considered by - * "bushy plan" join code in a higher-level pass where we have - * those other rels collected into a join rel. - * - * See also the last-ditch case below. - */ - make_rels_by_clause_joins(root, - old_rel, - other_rels); - } - else - { - /* - * Oops, we have a relation that is not joined to any other - * relation, either directly or by join-order restrictions. - * Cartesian product time. - */ - make_rels_by_clauseless_joins(root, - old_rel, - other_rels); - } - } - - /* - * Now, consider "bushy plans" in which relations of k initial rels are - * joined to relations of level-k initial rels, for 2 <= k <= level-2. - * - * We only consider bushy-plan joins for pairs of rels where there is a - * suitable join clause (or join order restriction), in order to avoid - * unreasonable growth of planning time. - */ - for (k = 2;; k++) - { - int other_level = level - k; - - /* - * Since make_join_rel(x, y) handles both x,y and y,x cases, we only - * need to go as far as the halfway point. - */ - if (k > other_level) - break; - - foreach(r, joinrels[k]) - { - RelOptInfo *old_rel = (RelOptInfo *) lfirst(r); - ListCell *other_rels; - ListCell *r2; - - /* - * We can ignore clauseless joins here, *except* when they - * participate in join-order restrictions --- then we might have - * to force a bushy join plan. - */ - if (old_rel->joininfo == NIL && !old_rel->has_eclass_joins && - !has_join_restriction(root, old_rel)) - continue; - - if (k == other_level) - other_rels = lnext(r); /* only consider remaining rels */ - else - other_rels = list_head(joinrels[other_level]); - - for_each_cell(r2, other_rels) - { - RelOptInfo *new_rel = (RelOptInfo *) lfirst(r2); - - if (!bms_overlap(old_rel->relids, new_rel->relids)) - { - /* - * OK, we can build a rel of the right level from this - * pair of rels. Do so if there is at least one usable - * join clause or a relevant join restriction. - */ - if (have_relevant_joinclause(root, old_rel, new_rel) || - have_join_order_restriction(root, old_rel, new_rel)) - { - (void) pg_hint_plan_make_join_rel(root, old_rel, new_rel); - } - } - } - } - } - - /* - * Last-ditch effort: if we failed to find any usable joins so far, force - * a set of cartesian-product joins to be generated. This handles the - * special case where all the available rels have join clauses but we - * cannot use any of those clauses yet. An example is - * - * SELECT * FROM a,b,c WHERE (a.f1 + b.f2 + c.f3) = 0; - * - * The join clause will be usable at level 3, but at level 2 we have no - * choice but to make cartesian joins. We consider only left-sided and - * right-sided cartesian joins in this case (no bushy). - */ - if (joinrels[level] == NIL) - { - /* - * This loop is just like the first one, except we always call - * make_rels_by_clauseless_joins(). - */ - foreach(r, joinrels[level - 1]) - { - RelOptInfo *old_rel = (RelOptInfo *) lfirst(r); - ListCell *other_rels; - - if (level == 2) - other_rels = lnext(r); /* only consider remaining initial - * rels */ - else - other_rels = list_head(joinrels[1]); /* consider all initial - * rels */ - - make_rels_by_clauseless_joins(root, - old_rel, - other_rels); - } - - /*---------- - * When special joins are involved, there may be no legal way - * to make an N-way join for some values of N. For example consider - * - * SELECT ... FROM t1 WHERE - * x IN (SELECT ... FROM t2,t3 WHERE ...) AND - * y IN (SELECT ... FROM t4,t5 WHERE ...) - * - * We will flatten this query to a 5-way join problem, but there are - * no 4-way joins that join_is_legal() will consider legal. We have - * to accept failure at level 4 and go on to discover a workable - * bushy plan at level 5. - * - * However, if there are no special joins then join_is_legal() should - * never fail, and so the following sanity check is useful. - *---------- - */ - if (joinrels[level] == NIL && root->join_info_list == NIL) - elog(ERROR, "failed to build any %d-way joins", level); - } + return standard_join_search_org(root, levels_needed, initial_rels); } -/* - * src/backend/optimizer/path/joinrels.c - * static make_rels_by_clause_joins() を流用 - * - * 変更箇所 - * make_join_rel() の呼び出しをラップする、pg_hint_plan_make_join_rel()の呼び出しに変更 - */ -/* - * make_rels_by_clause_joins - * Build joins between the given relation 'old_rel' and other relations - * that participate in join clauses that 'old_rel' also participates in - * (or participate in join-order restrictions with it). - * The join rels are returned in root->join_rel_level[join_cur_level]. - * - * Note: at levels above 2 we will generate the same joined relation in - * multiple ways --- for example (a join b) join c is the same RelOptInfo as - * (b join c) join a, though the second case will add a different set of Paths - * to it. This is the reason for using the join_rel_level mechanism, which - * automatically ensures that each new joinrel is only added to the list once. - * - * 'old_rel' is the relation entry for the relation to be joined - * 'other_rels': the first cell in a linked list containing the other - * rels to be considered for joining - * - * Currently, this is only used with initial rels in other_rels, but it - * will work for joining to joinrels too. - */ -static void -make_rels_by_clause_joins(PlannerInfo *root, - RelOptInfo *old_rel, - ListCell *other_rels) -{ - ListCell *l; - - for_each_cell(l, other_rels) - { - RelOptInfo *other_rel = (RelOptInfo *) lfirst(l); - - if (!bms_overlap(old_rel->relids, other_rel->relids) && - (have_relevant_joinclause(root, old_rel, other_rel) || - have_join_order_restriction(root, old_rel, other_rel))) - { - (void) pg_hint_plan_make_join_rel(root, old_rel, other_rel); - } - } -} - -/* - * src/backend/optimizer/path/joinrels.c - * static make_rels_by_clauseless_joins() を流用 - * - * 変更箇所 - * make_join_rel() の呼び出しをラップする、pg_hint_plan_make_join_rel()の呼び出しに変更 - */ -/* - * make_rels_by_clauseless_joins - * Given a relation 'old_rel' and a list of other relations - * 'other_rels', create a join relation between 'old_rel' and each - * member of 'other_rels' that isn't already included in 'old_rel'. - * The join rels are returned in root->join_rel_level[join_cur_level]. - * - * 'old_rel' is the relation entry for the relation to be joined - * 'other_rels': the first cell of a linked list containing the - * other rels to be considered for joining - * - * Currently, this is only used with initial rels in other_rels, but it would - * work for joining to joinrels too. - */ -static void -make_rels_by_clauseless_joins(PlannerInfo *root, - RelOptInfo *old_rel, - ListCell *other_rels) -{ - ListCell *l; - - for_each_cell(l, other_rels) - { - RelOptInfo *other_rel = (RelOptInfo *) lfirst(l); - - if (!bms_overlap(other_rel->relids, old_rel->relids)) - { - (void) pg_hint_plan_make_join_rel(root, old_rel, other_rel); - } - } -} - -/* - * src/backend/optimizer/path/joinrels.c - * static has_join_restriction() を流用 - * - * 変更箇所 - * なし - */ -/* - * has_join_restriction - * Detect whether the specified relation has join-order restrictions - * due to being inside an outer join or an IN (sub-SELECT). - * - * Essentially, this tests whether have_join_order_restriction() could - * succeed with this rel and some other one. It's OK if we sometimes - * say "true" incorrectly. (Therefore, we don't bother with the relatively - * expensive has_legal_joinclause test.) - */ -static bool -has_join_restriction(PlannerInfo *root, RelOptInfo *rel) -{ - ListCell *l; - - foreach(l, root->join_info_list) - { - SpecialJoinInfo *sjinfo = (SpecialJoinInfo *) lfirst(l); - - /* ignore full joins --- other mechanisms preserve their ordering */ - if (sjinfo->jointype == JOIN_FULL) - continue; - - /* ignore if SJ is already contained in rel */ - if (bms_is_subset(sjinfo->min_lefthand, rel->relids) && - bms_is_subset(sjinfo->min_righthand, rel->relids)) - continue; - - /* restricted if it overlaps LHS or RHS, but doesn't contain SJ */ - if (bms_overlap(sjinfo->min_lefthand, rel->relids) || - bms_overlap(sjinfo->min_righthand, rel->relids)) - return true; - } - - return false; -} - -/* - * src/backend/optimizer/path/allpaths.c - * static set_plain_rel_pathlist() を流用 - * - * 変更箇所 - * なし - */ -/* - * set_plain_rel_pathlist - * Build access paths for a plain relation (no subquery, no inheritance) - */ -static void -set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) -{ - /* Consider sequential scan */ - add_path(rel, create_seqscan_path(root, rel)); - - /* Consider index scans */ - create_index_paths(root, rel); - - /* Consider TID scans */ - create_tidscan_paths(root, rel); - - /* Now find the cheapest of the paths for this rel */ - set_cheapest(rel); -} +#define standard_join_search standard_join_search_org +#define join_search_one_level pg_hint_plan_join_search_one_level +#define make_join_rel pg_hint_plan_make_join_rel +#include "core.c" diff --git a/sql/base_plan.sql b/sql/base_plan.sql new file mode 100644 index 0000000..4e02a84 --- /dev/null +++ b/sql/base_plan.sql @@ -0,0 +1,12 @@ +-- query type 1 +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; +-- query type 2 +EXPLAIN (COSTS false) SELECT * FROM t1, t4 WHERE t1.val < 10; +-- query type 3 +EXPLAIN (COSTS false) SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)'; +-- query type 4 +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)'; +-- query type 5 +EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val; +-- query type 6 +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; diff --git a/sql/pg_hint_plan.sql b/sql/pg_hint_plan.sql index c4173e6..4ca6f90 100644 --- a/sql/pg_hint_plan.sql +++ b/sql/pg_hint_plan.sql @@ -1,25 +1,25 @@ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; -EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val; LOAD 'pg_hint_plan'; -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; -EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val; /* Test (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; SET pg_hint_plan.enable TO off; /* Test (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; SET pg_hint_plan.enable TO on; /*Set(enable_indexscan off)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /* Set(enable_indexscan off) Set(enable_hashjoin off) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /* Set ( enable_indexscan off ) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /* Set ( @@ -27,252 +27,68 @@ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; off ) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /* Set(enable_indexscan off)Set(enable_nestloop off)Set(enable_mergejoin off) Set(enable_seqscan off) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*Set(work_mem "1M")*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*Set(work_mem "1MB")*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*Set(work_mem TO "1MB")*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*SeqScan(t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*SeqScan(t1)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*SeqScan(t1)IndexScan(t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*BitmapScan(t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*BitmapScan(t2)NoSeqScan(t1)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*NoIndexScan(t1)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; -EXPLAIN SELECT * FROM t1, t4 WHERE t1.val < 10; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; + /*NoBitmapScan(t1)*/ -EXPLAIN SELECT * FROM t1, t4 WHERE t1.val < 10; -EXPLAIN SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)'; +EXPLAIN (COSTS false) SELECT * FROM t1, t4 WHERE t1.val < 10; /*TidScan(t4)*/ -EXPLAIN SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)'; -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)'; +EXPLAIN (COSTS false) SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)'; /*NoTidScan(t1)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)'; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)'; -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*HashJoin(t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*NestLoop(t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; /*NoMergeJoin(t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; /*MergeJoin(t1 t3)*/ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; +EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val; /*NestLoop(t1 t3)*/ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; +EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val; /*NoHashJoin(t1 t3)*/ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; +EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val; -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; /*MergeJoin(t4 t1 t2 t3)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; /*HashJoin(t3 t4 t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; +/*NestLoop(t2 t3 t4 t1) IndexScan(t3)*/ +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; /*NoNestLoop(t4 t1 t3 t2)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; /*Leading(t3 t4)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; /*Leading(t3 t4 t1)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; /*Leading(t3 t4 t1 t2)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; /*Leading(t3 t4 t1 t2 t1)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; /*Leading(t3 t4 t4)*/ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; -\q - -SET pg_hint_plan.debug_print TO true; -/* NestLoop (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; -/* NestLoop (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val; - -/* HashJoin (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; -/* HashJoin (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val; - -/* MergeJoin (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; -/* MergeJoin (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val; - -/* NoMergeJoin (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; -/* NoHashJoin (t1 t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val; - -EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id; -EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val = t2.val AND t2.val = t3.val; - -/* NestLoop (t1 t2 t3) NestLoop (t1 t3) */ -EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id; -/* NestLoop (t1 t2 t3) NestLoop (t1 t3) */ -EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val = t2.val AND t2.val = t3.val; - -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -SET enable_mergejoin TO off; -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -SET enable_mergejoin TO on; - -SET join_collapse_limit TO 10; -EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -SET join_collapse_limit TO 1; -EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -EXPLAIN SELECT * FROM t2 CROSS JOIN t3 CROSS JOIN t4 CROSS JOIN t1 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -EXPLAIN SELECT * FROM t1 CROSS JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t2.id = t3.id) JOIN t4 ON (t3.id = t4.id); - -EXPLAIN SELECT * FROM v2; -EXPLAIN SELECT * FROM v3 v_3; -EXPLAIN SELECT * FROM v2 v_2, v3 v_3 WHERE v_2.t1_id = v_3.t1_id; - -EXPLAIN SELECT * FROM t1 t_1, v2 v_2 WHERE t_1.id = v_2.t1_id; -SET from_collapse_limit TO 1; -EXPLAIN SELECT * FROM t1 t_1, v2 v_2 WHERE t_1.id = v_2.t1_id; - -/* test */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; -/* HashJoin(t1 t2) MergeJoin(t_1 t_2) NestLoop(t2 t1)*/ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; -SET join_collapse_limit TO 10; -/* Set(join_collapse_limit "1") */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; -/* Set(join_collapse_limit "10") */ EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -SHOW join_collapse_limit; -/* Set(join_collapse_limit "1") */ EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -SHOW join_collapse_limit; -/* Set(join_collapse_limit "1") */ EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -SHOW join_collapse_limit; - -/* Set(cursor_tuple_fraction "1") */ EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t2.val = 1 ORDER BY t1.id; -/* Set(cursor_tuple_fraction "0.5") */ EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t2.val = 1 ORDER BY t1.id; -/* Set (cursor_tuple_fraction "0.4") */ EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t2.val = 1 ORDER BY t1.id; - -/* Leading (t1 t2 t3 t4) */EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -/* Leading (t4 t3 t2 t1) */EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; - -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id; -/* SeqScan (t1) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id; -/* SeqScan (t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id; -/* SeqScan (t1) SeqScan (t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id; -/* BitmapScan(t1) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id; -/* BitmapScan(t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id; -/* BitmapScan (t1) BitmapScan (t2) */ -EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id; --- TID SCAN試験 -EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)'; -/* TidScan(t4) */ -EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)'; -/* IndexScan(t1) TidScan(t4)*/ -EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)'; --- NO～試験 -EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)'; -/* NoTidScan(t1) */ -EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)'; -/* NoSeqScan(t4) NoTidScan(t1) */ -EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)'; -/* NoSeqScan(t4) NoTidScan(t1) NoNestLoop(t1 t4)*/ -EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)'; - -EXPLAIN SELECT * FROM t1, t5 WHERE t1.id = t5.id AND t1.id < 1000 AND t5.val < 10; -/* NoIndexScan(t1) */ -EXPLAIN SELECT * FROM t1, t5 WHERE t1.id = t5.id AND t1.id < 1000 AND t5.val < 10; -/* NoBitmapScan(t5) NoIndexScan(t1) */ -EXPLAIN SELECT * FROM t1, t5 WHERE t1.id = t5.id AND t1.id < 1000 AND t5.val < 10; - ---EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id; ---EXPLAIN SELECT * FROM t1, t4 WHERE t1.id = t4.id; - -EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id; -/* IndexScan (t1) */ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id; -/* IndexScan (t3) */ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id; -/* SeqScan (t1) IndexScan (t3) */ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id; -/* SeqScan (t3) IndexScan (t1) */ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id; -/* IndexScan (t1) IndexScan (t3) */ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id; - -EXPLAIN SELECT * FROM t1 WHERE id < 10000; -/* IndexScan (t1) */ -EXPLAIN SELECT * FROM t1 WHERE id < 10000; - -EXPLAIN SELECT * FROM t1 WHERE id = 1; -/* SeqScan (t1) */ -EXPLAIN SELECT * FROM t1 WHERE id = 1; - -EXPLAIN SELECT * FROM t1, t4 WHERE t1.id > 100; -/* IndexScan (t1) */ -EXPLAIN SELECT * FROM t1, t4 WHERE t1.id > 100; -/* IndexScan (t4) */ -EXPLAIN SELECT * FROM t1, t4 WHERE t1.id > 100; -/* Set (enable_seqscan off) Set (enable_indexscan off) */ -EXPLAIN SELECT * FROM t1, t4 WHERE t1.id > 100; - -CREATE INDEX t1_val ON t1 (val); -CREATE INDEX t1_val_id ON t1 (val, id); -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; -/* IndexScan (t1 t1_val_id) */ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; -/* IndexScan (t1 t1_pkey) */ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; -/* IndexScan (t1 t1_val_id t1_val) */ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; -/* IndexScan (t1 t2_val) */ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; - -SET pg_hint_plan.debug_print TO true; -/* HashJoin(a b c A B C z y x Z Y X) HashJoin (t1 t3) MergeJoin(a b c A B C z y x Z Y X) MergeJoin (t3 t1) NestLoop(a b c A B C z y x Z Y X) */ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val; -CREATE INDEX t3_val ON t3 (val); -/* Leading(t3) IndexScan(t4) Set (cursor_tuple_fraction "1.0") Leading(t3 t1 t4) HashJoin(t3 t1) BitmapScan(t3) HashJoin(a b c) IndexScan(t2) MergeJoin(A B C) MergeJoin (B C A) NestLoop(b c a) MergeJoin(c a b) IndexScan(t3) NestLoop(C A B) IndexScan(t1) SeqScan(t1) SeqScan(t1) SeqScan(t1) MergeJoin(t3 t1) Leading(t2 t1 t4)*/ -EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val ORDER BY t3.val; - -EXPLAIN SELECT * FROM t1 WHERE t1.val < 10000; -/* Set(enable_seqscan "off") */ -EXPLAIN SELECT * FROM t1 WHERE t1.val < 10000; -/* IndexScan(t1) */ -EXPLAIN SELECT * FROM t1 WHERE t1.val < 10000; - -EXPLAIN SELECT * FROM v1 WHERE v1.val < 10000; -/* Set(enable_seqscan "off") */ -EXPLAIN SELECT * FROM v1 WHERE v1.val < 10000; -/* IndexScan(t1) */ -EXPLAIN SELECT * FROM v1 WHERE v1.val < 10000; - -EXPLAIN SELECT * FROM v4; -/* Set(enable_seqscan "off") */ -EXPLAIN SELECT * FROM v4; -/* BitmapScan(t2) */ -EXPLAIN SELECT * FROM v2; -/* BitmapScan(t_3) */ -EXPLAIN SELECT * FROM v4; - -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; -/* NestLoop(t1 t2) NoNestLoop(t1 t2 t4) Leading(t1 t2 t3 t4) */ -EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id; +EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; -- 2.11.0