From 8241ef0732b65ff8635eb5cc56860b2e9db40ab7 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Tue, 26 Feb 2019 19:25:08 +0900 Subject: [PATCH] Follow core's cobe/havor chages Take in some changes took place in core code. Commit 608b167f9f has largely changed the behavior around CTE. Although I didn't confirmed that the all changes are valid as the test for pg_hint_plan, just took in all plan differences. --- core.c | 90 ++++ expected/pg_hint_plan.out | 428 ++++++++-------- expected/ut-J.out | 832 +++++++++++++++----------------- expected/ut-L.out | 680 +++++++++++++------------- expected/ut-R.out | 830 +++++++++++++++---------------- expected/ut-S.out | 750 ++++++++++++++-------------- output/ut-W.source => expected/ut-W.out | 152 +++--- pg_hint_plan.c | 3 +- pg_stat_statements.c | 20 +- 9 files changed, 1846 insertions(+), 1939 deletions(-) rename output/ut-W.source => expected/ut-W.out (89%) diff --git a/core.c b/core.c index a8c1b90..965b393 100644 --- a/core.c +++ b/core.c @@ -37,6 +37,8 @@ * has_join_restriction() * is_dummy_rel() * restriction_is_constant_false() + * update_child_rel_info() + * build_child_join_sjinfo() * * * Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group @@ -1041,6 +1043,71 @@ restriction_is_constant_false(List *restrictlist, return false; } + +/* + * Set up tlist expressions for the childrel, and add EC members referencing + * the childrel. + */ +static void +update_child_rel_info(PlannerInfo *root, + RelOptInfo *rel, RelOptInfo *childrel) +{ + AppendRelInfo *appinfo = root->append_rel_array[childrel->relid]; + + /* Make child tlist expressions */ + childrel->reltarget->exprs = (List *) + adjust_appendrel_attrs(root, + (Node *) rel->reltarget->exprs, + 1, &appinfo); + + /* Make child entries in the EquivalenceClass as well */ + if (rel->has_eclass_joins || has_useful_pathkeys(root, rel)) + add_child_rel_equivalences(root, appinfo, rel, childrel); + childrel->has_eclass_joins = rel->has_eclass_joins; +} + +/* + * Construct the SpecialJoinInfo for a child-join by translating + * SpecialJoinInfo for the join between parents. left_relids and right_relids + * are the relids of left and right side of the join respectively. + */ +static SpecialJoinInfo * +build_child_join_sjinfo(PlannerInfo *root, SpecialJoinInfo *parent_sjinfo, + Relids left_relids, Relids right_relids) +{ + SpecialJoinInfo *sjinfo = makeNode(SpecialJoinInfo); + AppendRelInfo **left_appinfos; + int left_nappinfos; + AppendRelInfo **right_appinfos; + int right_nappinfos; + + memcpy(sjinfo, parent_sjinfo, sizeof(SpecialJoinInfo)); + left_appinfos = find_appinfos_by_relids(root, left_relids, + &left_nappinfos); + right_appinfos = find_appinfos_by_relids(root, right_relids, + &right_nappinfos); + + sjinfo->min_lefthand = adjust_child_relids(sjinfo->min_lefthand, + left_nappinfos, left_appinfos); + sjinfo->min_righthand = adjust_child_relids(sjinfo->min_righthand, + right_nappinfos, + right_appinfos); + sjinfo->syn_lefthand = adjust_child_relids(sjinfo->syn_lefthand, + left_nappinfos, left_appinfos); + sjinfo->syn_righthand = adjust_child_relids(sjinfo->syn_righthand, + right_nappinfos, + right_appinfos); + sjinfo->semi_rhs_exprs = (List *) adjust_appendrel_attrs(root, + (Node *) sjinfo->semi_rhs_exprs, + right_nappinfos, + right_appinfos); + + pfree(left_appinfos); + pfree(right_appinfos); + + return sjinfo; +} + /* * Assess whether join between given two partitioned relations can be broken * down into joins between matching partitions; a technique called @@ -1066,6 +1133,8 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, RelOptInfo *joinrel, SpecialJoinInfo *parent_sjinfo, List *parent_restrictlist) { + bool rel1_is_simple = IS_SIMPLE_REL(rel1); + bool rel2_is_simple = IS_SIMPLE_REL(rel2); int nparts; int cnt_parts; @@ -1130,6 +1199,27 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, AppendRelInfo **appinfos; int nappinfos; + /* + * If a child table has consider_partitionwise_join=false, it means + * that it's a dummy relation for which we skipped setting up tlist + * expressions and adding EC members in set_append_rel_size(), so do + * that now for use later. + */ + if (rel1_is_simple && !child_rel1->consider_partitionwise_join) + { + Assert(child_rel1->reloptkind == RELOPT_OTHER_MEMBER_REL); + Assert(IS_DUMMY_REL(child_rel1)); + update_child_rel_info(root, rel1, child_rel1); + child_rel1->consider_partitionwise_join = true; + } + if (rel2_is_simple && !child_rel2->consider_partitionwise_join) + { + Assert(child_rel2->reloptkind == RELOPT_OTHER_MEMBER_REL); + Assert(IS_DUMMY_REL(child_rel2)); + update_child_rel_info(root, rel2, child_rel2); + child_rel2->consider_partitionwise_join = true; + } + /* We should never try to join two overlapping sets of rels. */ Assert(!bms_overlap(child_rel1->relids, child_rel2->relids)); child_joinrelids = bms_union(child_rel1->relids, child_rel2->relids); diff --git a/expected/pg_hint_plan.out b/expected/pg_hint_plan.out index 0922aed..01f698d 100644 --- a/expected/pg_hint_plan.out +++ b/expected/pg_hint_plan.out @@ -4476,18 +4476,7 @@ SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t QUERY PLAN ------------------------------------------------------------------------------ Nested Loop - CTE c1_1 - -> Aggregate - -> Merge Join - Merge Cond: (t1_5.id = t3_5.id) - -> Merge Join - Merge Cond: (t1_5.id = t2_5.id) - -> Index Only Scan using t1_pkey on t1 t1_5 - -> Index Only Scan using t2_pkey on t2 t2_5 - -> Sort - Sort Key: t3_5.id - -> Seq Scan on t3 t3_5 - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Merge Join Merge Cond: (t1_2.id = t3_2.id) @@ -4498,7 +4487,7 @@ SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t -> Sort Sort Key: t3_2.id -> Seq Scan on t3 t3_2 - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Merge Join Merge Cond: (t1_4.id = t3_4.id) @@ -4515,20 +4504,29 @@ SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_1 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Index Only Scan using t2_pkey on t2 t2_1 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Seq Scan on t3 t3_1 - Filter: (id = $2) + Filter: (id = $1) -> Index Only Scan using t1_pkey on t1 t1_3 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Index Only Scan using t2_pkey on t2 t2_3 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Seq Scan on t3 t3_3 - Filter: (id = $2) - -> CTE Scan on c1_1 - Filter: (id = $2) -(53 rows) + Filter: (id = $1) + -> Aggregate + Filter: (max(t1_5.id) = $1) + -> Merge Join + Merge Cond: (t1_5.id = t3_5.id) + -> Merge Join + Merge Cond: (t1_5.id = t2_5.id) + -> Index Only Scan using t1_pkey on t1 t1_5 + -> Index Only Scan using t2_pkey on t2 t2_5 + -> Sort + Sort Key: t3_5.id + -> Seq Scan on t3 t3_5 +(51 rows) /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)*/ EXPLAIN (COSTS false) @@ -4556,18 +4554,7 @@ error hint: QUERY PLAN ------------------------------------------------------------------------------ Nested Loop - CTE c1_1 - -> Aggregate - -> Merge Join - Merge Cond: (t1_5.id = t3_5.id) - -> Nested Loop - -> Index Only Scan using t2_pkey on t2 t2_5 - -> Index Only Scan using t1_pkey on t1 t1_5 - Index Cond: (id = t2_5.id) - -> Sort - Sort Key: t3_5.id - -> Seq Scan on t3 t3_5 - InitPlan 2 (returns $3) + InitPlan 1 (returns $1) -> Aggregate -> Merge Join Merge Cond: (t1_2.id = t3_2.id) @@ -4578,7 +4565,7 @@ error hint: -> Sort Sort Key: t3_2.id -> Seq Scan on t3 t3_2 - InitPlan 3 (returns $5) + InitPlan 2 (returns $3) -> Aggregate -> Merge Join Merge Cond: (t1_4.id = t3_4.id) @@ -4595,20 +4582,29 @@ error hint: -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_1 - Filter: (id = $5) + Filter: (id = $3) -> Index Only Scan using t1_pkey on t1 t1_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_3 - Filter: (id = $5) - -> CTE Scan on c1_1 - Filter: (id = $5) -(53 rows) + Filter: (id = $3) + -> Aggregate + Filter: (max(t1_5.id) = $3) + -> Merge Join + Merge Cond: (t1_5.id = t3_5.id) + -> Nested Loop + -> Index Only Scan using t2_pkey on t2 t2_5 + -> Index Only Scan using t1_pkey on t1 t1_5 + Index Cond: (id = t2_5.id) + -> Sort + Sort Key: t3_5.id + -> Seq Scan on t3 t3_5 +(51 rows) /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t1_1 t1_2 t1_4 t1_5)*/ EXPLAIN (COSTS false) @@ -4637,18 +4633,7 @@ error hint: QUERY PLAN ------------------------------------------------------------------------------ Nested Loop - CTE c1_1 - -> Aggregate - -> Merge Join - Merge Cond: (t1_5.id = t3_5.id) - -> Nested Loop - -> Index Only Scan using t2_pkey on t2 t2_5 - -> Index Only Scan using t1_pkey on t1 t1_5 - Index Cond: (id = t2_5.id) - -> Sort - Sort Key: t3_5.id - -> Seq Scan on t3 t3_5 - InitPlan 2 (returns $3) + InitPlan 1 (returns $1) -> Aggregate -> Merge Join Merge Cond: (t1_2.id = t3_2.id) @@ -4659,7 +4644,7 @@ error hint: -> Sort Sort Key: t3_2.id -> Seq Scan on t3 t3_2 - InitPlan 3 (returns $5) + InitPlan 2 (returns $3) -> Aggregate -> Merge Join Merge Cond: (t1_4.id = t3_4.id) @@ -4676,20 +4661,29 @@ error hint: -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_1 - Filter: (id = $5) + Filter: (id = $3) -> Index Only Scan using t1_pkey on t1 t1_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_3 - Filter: (id = $5) - -> CTE Scan on c1_1 - Filter: (id = $5) -(53 rows) + Filter: (id = $3) + -> Aggregate + Filter: (max(t1_5.id) = $3) + -> Merge Join + Merge Cond: (t1_5.id = t3_5.id) + -> Nested Loop + -> Index Only Scan using t2_pkey on t2 t2_5 + -> Index Only Scan using t1_pkey on t1 t1_5 + Index Cond: (id = t2_5.id) + -> Sort + Sort Key: t3_5.id + -> Seq Scan on t3 t3_5 +(51 rows) /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t3_2 t3_5 t2_2 c1_1 t3_4 t3_3 t2_3 t2_4 t1_3 t2_5 t1_2 t3_1 t1_4 t2_1 t1_5 t1_1)*/ EXPLAIN (COSTS false) @@ -4718,18 +4712,7 @@ error hint: QUERY PLAN ------------------------------------------------------------------------------ Nested Loop - CTE c1_1 - -> Aggregate - -> Merge Join - Merge Cond: (t1_5.id = t3_5.id) - -> Nested Loop - -> Index Only Scan using t2_pkey on t2 t2_5 - -> Index Only Scan using t1_pkey on t1 t1_5 - Index Cond: (id = t2_5.id) - -> Sort - Sort Key: t3_5.id - -> Seq Scan on t3 t3_5 - InitPlan 2 (returns $3) + InitPlan 1 (returns $1) -> Aggregate -> Merge Join Merge Cond: (t1_2.id = t3_2.id) @@ -4740,7 +4723,7 @@ error hint: -> Sort Sort Key: t3_2.id -> Seq Scan on t3 t3_2 - InitPlan 3 (returns $5) + InitPlan 2 (returns $3) -> Aggregate -> Merge Join Merge Cond: (t1_4.id = t3_4.id) @@ -4757,20 +4740,29 @@ error hint: -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_1 - Filter: (id = $5) + Filter: (id = $3) -> Index Only Scan using t1_pkey on t1 t1_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_3 - Filter: (id = $5) - -> CTE Scan on c1_1 - Filter: (id = $5) -(53 rows) + Filter: (id = $3) + -> Aggregate + Filter: (max(t1_5.id) = $3) + -> Merge Join + Merge Cond: (t1_5.id = t3_5.id) + -> Nested Loop + -> Index Only Scan using t2_pkey on t2 t2_5 + -> Index Only Scan using t1_pkey on t1 t1_5 + Index Cond: (id = t2_5.id) + -> Sort + Sort Key: t3_5.id + -> Seq Scan on t3 t3_5 +(51 rows) /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(t3_5 t2_5 t1_5)Leading(t3_2 t2_2 t1_2)Leading(t3_4 t2_4 t1_4)Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)*/ EXPLAIN (COSTS false) @@ -4799,21 +4791,10 @@ NestLoop(t1_5 t2_5) duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------ Nested Loop - CTE c1_1 - -> Aggregate - -> Merge Join - Merge Cond: (t2_5.id = t1_5.id) - -> Merge Join - Merge Cond: (t2_5.id = t3_5.id) - -> Index Only Scan using t2_pkey on t2 t2_5 - -> Sort - Sort Key: t3_5.id - -> Seq Scan on t3 t3_5 - -> Index Only Scan using t1_pkey on t1 t1_5 - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Merge Join Merge Cond: (t2_2.id = t1_2.id) @@ -4824,7 +4805,7 @@ error hint: Sort Key: t3_2.id -> Seq Scan on t3 t3_2 -> Index Only Scan using t1_pkey on t1 t1_2 - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Merge Join Merge Cond: (t2_4.id = t1_4.id) @@ -4841,20 +4822,29 @@ error hint: -> Nested Loop -> Nested Loop -> Seq Scan on t3 t3_3 - Filter: (id = $2) - -> CTE Scan on c1_1 - Filter: (id = $2) + Filter: (id = $1) + -> Aggregate + Filter: (max(t1_5.id) = $1) + -> Merge Join + Merge Cond: (t2_5.id = t1_5.id) + -> Merge Join + Merge Cond: (t2_5.id = t3_5.id) + -> Index Only Scan using t2_pkey on t2 t2_5 + -> Sort + Sort Key: t3_5.id + -> Seq Scan on t3 t3_5 + -> Index Only Scan using t1_pkey on t1 t1_5 -> Index Only Scan using t2_pkey on t2 t2_3 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Index Only Scan using t1_pkey on t1 t1_3 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Seq Scan on t3 t3_1 - Filter: (id = $2) + Filter: (id = $1) -> Index Only Scan using t2_pkey on t2 t2_1 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Index Only Scan using t1_pkey on t1 t1_1 - Index Cond: (id = $2) -(53 rows) + Index Cond: (id = $1) +(51 rows) SET from_collapse_limit TO 1; EXPLAIN (COSTS false) @@ -4871,18 +4861,7 @@ SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t QUERY PLAN -------------------------------------------------------------------- Nested Loop - CTE c1_1 - -> Aggregate - -> Merge Join - Merge Cond: (t1_5.id = t3_5.id) - -> Merge Join - Merge Cond: (t1_5.id = t2_5.id) - -> Index Only Scan using t1_pkey on t1 t1_5 - -> Index Only Scan using t2_pkey on t2 t2_5 - -> Sort - Sort Key: t3_5.id - -> Seq Scan on t3 t3_5 - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Merge Join Merge Cond: (t1_2.id = t3_2.id) @@ -4893,7 +4872,7 @@ SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t -> Sort Sort Key: t3_2.id -> Seq Scan on t3 t3_2 - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Merge Join Merge Cond: (t1_4.id = t3_4.id) @@ -4908,22 +4887,31 @@ SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_1 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Index Only Scan using t2_pkey on t2 t2_1 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Seq Scan on t3 t3_1 - Filter: (id = $2) + Filter: (id = $1) -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_3 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Index Only Scan using t2_pkey on t2 t2_3 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Seq Scan on t3 t3_3 - Filter: (id = $2) - -> CTE Scan on c1_1 - Filter: (id = $2) -(53 rows) + Filter: (id = $1) + -> Aggregate + Filter: (max(t1_5.id) = $1) + -> Merge Join + Merge Cond: (t1_5.id = t3_5.id) + -> Merge Join + Merge Cond: (t1_5.id = t2_5.id) + -> Index Only Scan using t1_pkey on t1 t1_5 + -> Index Only Scan using t2_pkey on t2 t2_5 + -> Sort + Sort Key: t3_5.id + -> Seq Scan on t3 t3_5 +(51 rows) /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)*/ EXPLAIN (COSTS false) @@ -4951,18 +4939,7 @@ error hint: QUERY PLAN -------------------------------------------------------------------- Nested Loop - CTE c1_1 - -> Aggregate - -> Merge Join - Merge Cond: (t1_5.id = t3_5.id) - -> Nested Loop - -> Index Only Scan using t2_pkey on t2 t2_5 - -> Index Only Scan using t1_pkey on t1 t1_5 - Index Cond: (id = t2_5.id) - -> Sort - Sort Key: t3_5.id - -> Seq Scan on t3 t3_5 - InitPlan 2 (returns $3) + InitPlan 1 (returns $1) -> Aggregate -> Merge Join Merge Cond: (t1_2.id = t3_2.id) @@ -4973,7 +4950,7 @@ error hint: -> Sort Sort Key: t3_2.id -> Seq Scan on t3 t3_2 - InitPlan 3 (returns $5) + InitPlan 2 (returns $3) -> Aggregate -> Merge Join Merge Cond: (t1_4.id = t3_4.id) @@ -4988,22 +4965,31 @@ error hint: -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_1 - Filter: (id = $5) + Filter: (id = $3) -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_3 - Filter: (id = $5) - -> CTE Scan on c1_1 - Filter: (id = $5) -(53 rows) + Filter: (id = $3) + -> Aggregate + Filter: (max(t1_5.id) = $3) + -> Merge Join + Merge Cond: (t1_5.id = t3_5.id) + -> Nested Loop + -> Index Only Scan using t2_pkey on t2 t2_5 + -> Index Only Scan using t1_pkey on t1 t1_5 + Index Cond: (id = t2_5.id) + -> Sort + Sort Key: t3_5.id + -> Seq Scan on t3 t3_5 +(51 rows) /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t1_1 t1_2 t1_4 t1_5)*/ EXPLAIN (COSTS false) @@ -5032,18 +5018,7 @@ error hint: QUERY PLAN -------------------------------------------------------------------- Nested Loop - CTE c1_1 - -> Aggregate - -> Merge Join - Merge Cond: (t1_5.id = t3_5.id) - -> Nested Loop - -> Index Only Scan using t2_pkey on t2 t2_5 - -> Index Only Scan using t1_pkey on t1 t1_5 - Index Cond: (id = t2_5.id) - -> Sort - Sort Key: t3_5.id - -> Seq Scan on t3 t3_5 - InitPlan 2 (returns $3) + InitPlan 1 (returns $1) -> Aggregate -> Merge Join Merge Cond: (t1_2.id = t3_2.id) @@ -5054,7 +5029,7 @@ error hint: -> Sort Sort Key: t3_2.id -> Seq Scan on t3 t3_2 - InitPlan 3 (returns $5) + InitPlan 2 (returns $3) -> Aggregate -> Merge Join Merge Cond: (t1_4.id = t3_4.id) @@ -5069,22 +5044,31 @@ error hint: -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_1 - Filter: (id = $5) + Filter: (id = $3) -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_3 - Filter: (id = $5) - -> CTE Scan on c1_1 - Filter: (id = $5) -(53 rows) + Filter: (id = $3) + -> Aggregate + Filter: (max(t1_5.id) = $3) + -> Merge Join + Merge Cond: (t1_5.id = t3_5.id) + -> Nested Loop + -> Index Only Scan using t2_pkey on t2 t2_5 + -> Index Only Scan using t1_pkey on t1 t1_5 + Index Cond: (id = t2_5.id) + -> Sort + Sort Key: t3_5.id + -> Seq Scan on t3 t3_5 +(51 rows) /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t3_2 t3_5 t2_2 c1_1 t3_4 t3_3 t2_3 t2_4 t1_3 t2_5 t1_2 t3_1 t1_4 t2_1 t1_5 t1_1)*/ EXPLAIN (COSTS false) @@ -5113,18 +5097,7 @@ error hint: QUERY PLAN -------------------------------------------------------------------- Nested Loop - CTE c1_1 - -> Aggregate - -> Merge Join - Merge Cond: (t1_5.id = t3_5.id) - -> Nested Loop - -> Index Only Scan using t2_pkey on t2 t2_5 - -> Index Only Scan using t1_pkey on t1 t1_5 - Index Cond: (id = t2_5.id) - -> Sort - Sort Key: t3_5.id - -> Seq Scan on t3 t3_5 - InitPlan 2 (returns $3) + InitPlan 1 (returns $1) -> Aggregate -> Merge Join Merge Cond: (t1_2.id = t3_2.id) @@ -5135,7 +5108,7 @@ error hint: -> Sort Sort Key: t3_2.id -> Seq Scan on t3 t3_2 - InitPlan 3 (returns $5) + InitPlan 2 (returns $3) -> Aggregate -> Merge Join Merge Cond: (t1_4.id = t3_4.id) @@ -5150,22 +5123,31 @@ error hint: -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_1 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_1 - Filter: (id = $5) + Filter: (id = $3) -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Index Only Scan using t2_pkey on t2 t2_3 - Index Cond: (id = $5) + Index Cond: (id = $3) -> Seq Scan on t3 t3_3 - Filter: (id = $5) - -> CTE Scan on c1_1 - Filter: (id = $5) -(53 rows) + Filter: (id = $3) + -> Aggregate + Filter: (max(t1_5.id) = $3) + -> Merge Join + Merge Cond: (t1_5.id = t3_5.id) + -> Nested Loop + -> Index Only Scan using t2_pkey on t2 t2_5 + -> Index Only Scan using t1_pkey on t1 t1_5 + Index Cond: (id = t2_5.id) + -> Sort + Sort Key: t3_5.id + -> Seq Scan on t3 t3_5 +(51 rows) /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(t3_5 t2_5 t1_5)Leading(t3_2 t2_2 t1_2)Leading(t3_4 t2_4 t1_4)Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)*/ EXPLAIN (COSTS false) @@ -5194,21 +5176,10 @@ NestLoop(t1_5 t2_5) duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------ Nested Loop - CTE c1_1 - -> Aggregate - -> Merge Join - Merge Cond: (t2_5.id = t1_5.id) - -> Merge Join - Merge Cond: (t2_5.id = t3_5.id) - -> Index Only Scan using t2_pkey on t2 t2_5 - -> Sort - Sort Key: t3_5.id - -> Seq Scan on t3 t3_5 - -> Index Only Scan using t1_pkey on t1 t1_5 - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Merge Join Merge Cond: (t2_2.id = t1_2.id) @@ -5219,7 +5190,7 @@ error hint: Sort Key: t3_2.id -> Seq Scan on t3 t3_2 -> Index Only Scan using t1_pkey on t1 t1_2 - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Merge Join Merge Cond: (t2_4.id = t1_4.id) @@ -5236,20 +5207,29 @@ error hint: -> Nested Loop -> Nested Loop -> Index Only Scan using t1_pkey on t1 t1_3 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Index Only Scan using t2_pkey on t2 t2_3 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Seq Scan on t3 t3_3 - Filter: (id = $2) - -> CTE Scan on c1_1 - Filter: (id = $2) + Filter: (id = $1) + -> Aggregate + Filter: (max(t1_5.id) = $1) + -> Merge Join + Merge Cond: (t2_5.id = t1_5.id) + -> Merge Join + Merge Cond: (t2_5.id = t3_5.id) + -> Index Only Scan using t2_pkey on t2 t2_5 + -> Sort + Sort Key: t3_5.id + -> Seq Scan on t3 t3_5 + -> Index Only Scan using t1_pkey on t1 t1_5 -> Seq Scan on t3 t3_1 - Filter: (id = $2) + Filter: (id = $1) -> Index Only Scan using t2_pkey on t2 t2_1 - Index Cond: (id = $2) + Index Cond: (id = $1) -> Index Only Scan using t1_pkey on t1 t1_1 - Index Cond: (id = $2) -(53 rows) + Index Cond: (id = $1) +(51 rows) -- ambigous error EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id; diff --git a/expected/ut-J.out b/expected/ut-J.out index 49f54fa..48f6345 100644 --- a/expected/ut-J.out +++ b/expected/ut-J.out @@ -676,21 +676,19 @@ error hint: -- No. J-1-6-9 EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Nested Loop - CTE c1 - -> Aggregate - -> Merge Join - Merge Cond: (t1_1.c1 = t2.c1) - -> Index Only Scan using t1_i1 on t1 t1_1 - -> Sort - Sort Key: t2.c1 - -> Seq Scan on t2 - -> CTE Scan on c1 + -> Aggregate + -> Merge Join + Merge Cond: (t1_1.c1 = t2.c1) + -> Index Only Scan using t1_i1 on t1 t1_1 + -> Sort + Sort Key: t2.c1 + -> Seq Scan on t2 -> Index Scan using t1_i1 on t1 - Index Cond: (c1 = c1.c1) -(12 rows) + Index Cond: (c1 = (max(t1_1.c1))) +(10 rows) /*+NestLoop(t1 t2)HashJoin(t1 c1)*/ EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1; @@ -702,20 +700,18 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------- Hash Join - Hash Cond: (t1.c1 = c1.c1) - CTE c1 - -> Aggregate - -> Nested Loop - -> Seq Scan on t2 - -> Index Only Scan using t1_i1 on t1 t1_1 - Index Cond: (c1 = t2.c1) + Hash Cond: (t1.c1 = (max(t1_1.c1))) -> Seq Scan on t1 -> Hash - -> CTE Scan on c1 -(11 rows) + -> Aggregate + -> Nested Loop + -> Seq Scan on t2 + -> Index Only Scan using t1_i1 on t1 t1_1 + Index Cond: (c1 = t2.c1) +(9 rows) -- No. J-1-6-10 EXPLAIN (COSTS false) SELECT * FROM s1.v1 t1, s1.v1 t2 WHERE t1.c1 = t2.c1; @@ -1736,45 +1732,13 @@ SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 AND bmt1.c1 = c1.c1 AND bmt1.c1 = c2.c1 ; - QUERY PLAN ------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - CTE c2 - -> Aggregate - -> Nested Loop - Join Filter: (b2t1.c1 = b2t4.c1) - -> Nested Loop - Join Filter: (b2t1.c1 = b2t3.c1) - -> Nested Loop - Join Filter: (b2t1.c1 = b2t2.c1) - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b2t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b2t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b2t4 - TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c2.c1) + Join Filter: (bmt1.c1 = (max(b2t1.c1))) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -1789,9 +1753,37 @@ AND bmt1.c1 = c2.c1 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 - -> CTE Scan on c2 -(53 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t3.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b2t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b2t4 + TID Cond: (ctid = '(1,1)'::tid) +(49 rows) /*+ Leading(c2 c1 bmt1 bmt2 bmt3 bmt4) @@ -1834,51 +1826,9 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t2.c1 = b1t1.c1) - -> Hash Join - Hash Cond: (b1t4.c1 = b1t2.c1) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Hash - -> Merge Join - Merge Cond: (b1t2.c1 = b1t3.c1) - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Sort - Sort Key: b1t3.c1 - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - CTE c2 - -> Aggregate - -> Nested Loop - Join Filter: (b2t1.c1 = b2t2.c1) - -> Hash Join - Hash Cond: (b2t1.c1 = b2t3.c1) - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Hash - -> Merge Join - Merge Cond: (b2t3.c1 = b2t4.c1) - -> Sort - Sort Key: b2t3.c1 - -> Tid Scan on t3 b2t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Sort - Sort Key: b2t4.c1 - -> Tid Scan on t4 b2t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b2t2 - Filter: (ctid = '(1,1)'::tid) -> Hash Join Hash Cond: (bmt4.c1 = bmt1.c1) -> Tid Scan on t4 bmt4 @@ -1891,25 +1841,63 @@ error hint: -> Nested Loop Join Filter: (bmt1.c1 = bmt2.c1) -> Hash Join - Hash Cond: (bmt1.c1 = c1.c1) + Hash Cond: (bmt1.c1 = (max(b1t1.c1))) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) -> Hash -> Merge Join - Merge Cond: (c1.c1 = c2.c1) + Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1))) -> Sort - Sort Key: c1.c1 - -> CTE Scan on c1 + Sort Key: (max(b1t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b1t2.c1 = b1t1.c1) + -> Hash Join + Hash Cond: (b1t4.c1 = b1t2.c1) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Hash + -> Merge Join + Merge Cond: (b1t2.c1 = b1t3.c1) + -> Sort + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: b1t3.c1 + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) -> Sort - Sort Key: c2.c1 - -> CTE Scan on c2 + Sort Key: (max(b2t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Hash Join + Hash Cond: (b2t1.c1 = b2t3.c1) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Hash + -> Merge Join + Merge Cond: (b2t3.c1 = b2t4.c1) + -> Sort + Sort Key: b2t3.c1 + -> Tid Scan on t3 b2t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: b2t4.c1 + -> Tid Scan on t4 b2t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Sort Sort Key: bmt3.c1 -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -(73 rows) +(69 rows) -- No. J-2-1-8 EXPLAIN (COSTS false) @@ -1929,81 +1917,79 @@ AND bmt1.c1 = c1.c1 AND bmt1.c1 = c2.c1 AND bmt1.c1 = c3.c1 ; - QUERY PLAN ------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - CTE c2 - -> Aggregate - -> Nested Loop - Join Filter: (b2t1.c1 = b2t4.c1) - -> Nested Loop - Join Filter: (b2t1.c1 = b2t3.c1) - -> Nested Loop - Join Filter: (b2t1.c1 = b2t2.c1) - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b2t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b2t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b2t4 - TID Cond: (ctid = '(1,1)'::tid) - CTE c3 - -> Aggregate - -> Nested Loop - Join Filter: (b3t1.c1 = b3t4.c1) - -> Nested Loop - Join Filter: (b3t1.c1 = b3t3.c1) - -> Nested Loop - Join Filter: (b3t1.c1 = b3t2.c1) - -> Tid Scan on t1 b3t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b3t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b3t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b3t4 - TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c3.c1) + Join Filter: (bmt1.c1 = (max(b3t1.c1))) -> Nested Loop - Join Filter: (bmt1.c1 = c2.c1) + Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = bmt3.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) + Join Filter: (bmt1.c1 = bmt2.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt3.c1) - -> Nested Loop - Join Filter: (bmt1.c1 = bmt2.c1) - -> Tid Scan on t1 bmt1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 bmt2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 bmt3 + Join Filter: ((max(b1t1.c1)) = bmt1.c1) + -> Merge Join + Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1))) + -> Sort + Sort Key: (max(b1t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: (max(b2t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t3.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b2t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b2t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 bmt4 + -> Seq Scan on t2 bmt2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 bmt3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 bmt4 + TID Cond: (ctid = '(1,1)'::tid) + -> Aggregate + -> Nested Loop + Join Filter: (b3t1.c1 = b3t4.c1) + -> Nested Loop + Join Filter: (b3t1.c1 = b3t3.c1) + -> Nested Loop + Join Filter: (b3t1.c1 = b3t2.c1) + -> Tid Scan on t1 b3t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b3t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b3t3 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 - -> CTE Scan on c2 - -> CTE Scan on c3 -(72 rows) + -> Tid Scan on t4 b3t4 + TID Cond: (ctid = '(1,1)'::tid) +(70 rows) /*+ Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4) @@ -2057,72 +2043,9 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t2.c1 = b1t1.c1) - -> Hash Join - Hash Cond: (b1t4.c1 = b1t2.c1) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Hash - -> Merge Join - Merge Cond: (b1t2.c1 = b1t3.c1) - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Sort - Sort Key: b1t3.c1 - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - CTE c2 - -> Aggregate - -> Nested Loop - Join Filter: (b2t1.c1 = b2t2.c1) - -> Hash Join - Hash Cond: (b2t1.c1 = b2t3.c1) - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Hash - -> Merge Join - Merge Cond: (b2t3.c1 = b2t4.c1) - -> Sort - Sort Key: b2t3.c1 - -> Tid Scan on t3 b2t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Sort - Sort Key: b2t4.c1 - -> Tid Scan on t4 b2t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b2t2 - Filter: (ctid = '(1,1)'::tid) - CTE c3 - -> Aggregate - -> Nested Loop - Join Filter: (b3t1.c1 = b3t3.c1) - -> Hash Join - Hash Cond: (b3t2.c1 = b3t1.c1) - -> Seq Scan on t2 b3t2 - Filter: (ctid = '(1,1)'::tid) - -> Hash - -> Merge Join - Merge Cond: (b3t1.c1 = b3t4.c1) - -> Sort - Sort Key: b3t1.c1 - -> Tid Scan on t1 b3t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Sort - Sort Key: b3t4.c1 - -> Tid Scan on t4 b3t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b3t3 - TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Hash Join @@ -2135,19 +2058,76 @@ error hint: -> Sort Sort Key: bmt1.c1 -> Nested Loop - Join Filter: (c1.c1 = bmt1.c1) + Join Filter: ((max(b1t1.c1)) = bmt1.c1) -> Hash Join - Hash Cond: (c2.c1 = c1.c1) + Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1))) -> Merge Join - Merge Cond: (c2.c1 = c3.c1) + Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1))) -> Sort - Sort Key: c2.c1 - -> CTE Scan on c2 + Sort Key: (max(b2t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Hash Join + Hash Cond: (b2t1.c1 = b2t3.c1) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Hash + -> Merge Join + Merge Cond: (b2t3.c1 = b2t4.c1) + -> Sort + Sort Key: b2t3.c1 + -> Tid Scan on t3 b2t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: b2t4.c1 + -> Tid Scan on t4 b2t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) -> Sort - Sort Key: c3.c1 - -> CTE Scan on c3 + Sort Key: (max(b3t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b3t1.c1 = b3t3.c1) + -> Hash Join + Hash Cond: (b3t2.c1 = b3t1.c1) + -> Seq Scan on t2 b3t2 + Filter: (ctid = '(1,1)'::tid) + -> Hash + -> Merge Join + Merge Cond: (b3t1.c1 = b3t4.c1) + -> Sort + Sort Key: b3t1.c1 + -> Tid Scan on t1 b3t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: b3t4.c1 + -> Tid Scan on t4 b3t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b3t3 + TID Cond: (ctid = '(1,1)'::tid) -> Hash - -> CTE Scan on c1 + -> Aggregate + -> Nested Loop + Join Filter: (b1t2.c1 = b1t1.c1) + -> Hash Join + Hash Cond: (b1t4.c1 = b1t2.c1) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Hash + -> Merge Join + Merge Cond: (b1t2.c1 = b1t3.c1) + -> Sort + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: b1t3.c1 + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) -> Sort @@ -2156,7 +2136,7 @@ error hint: Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) -(97 rows) +(91 rows) ---- ---- No. J-2-2 the number of the tables per quiry block @@ -2175,37 +2155,35 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1 ) ; - QUERY PLAN ------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------- Aggregate - CTE c1 + InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Tid Scan on t1 b1t1 + -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 4 (returns $3) -> Result InitPlan 3 (returns $2) -> Limit - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 6 (returns $5) - -> Result - InitPlan 5 (returns $4) - -> Limit -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 <> $5) AND (c1 = 1)) - -> CTE Scan on c1 - Filter: (c1 = 1) -(28 rows) + Filter: ((c1 <> $3) AND (c1 = 1)) + -> Result + One-Time Filter: ($4 = 1) + InitPlan 5 (returns $4) + -> Limit + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((c1 IS NOT NULL) AND (c1 = 1)) +(26 rows) /*+ Leading(c1 bmt1) @@ -2238,37 +2216,35 @@ HashJoin(b3t1 c1) duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------ + QUERY PLAN +--------------------------------------------------------------------- Aggregate - CTE c1 + InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Tid Scan on t1 b1t1 + -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 4 (returns $3) -> Result InitPlan 3 (returns $2) -> Limit - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 6 (returns $5) - -> Result - InitPlan 5 (returns $4) - -> Limit -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 <> $5) AND (c1 = 1)) - -> CTE Scan on c1 - Filter: (c1 = 1) -(28 rows) + Filter: ((c1 <> $3) AND (c1 = 1)) + -> Result + One-Time Filter: ($4 = 1) + InitPlan 5 (returns $4) + -> Limit + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((c1 IS NOT NULL) AND (c1 = 1)) +(26 rows) -- No. J-2-2-2 EXPLAIN (COSTS false) @@ -2284,18 +2260,10 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t2.c1) @@ -2303,7 +2271,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3 TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 b2t2 Filter: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t2.c1) @@ -2312,16 +2280,22 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3 -> Seq Scan on t2 b3t2 Filter: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) + Filter: (c1 <> $1) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(35 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) +(33 rows) /*+ Leading(c1 bmt2 bmt1) @@ -2362,22 +2336,10 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Merge Join - Merge Cond: (b1t1.c1 = b1t2.c1) - -> Sort - Sort Key: b1t1.c1 - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Merge Join Merge Cond: (b2t1.c1 = b2t2.c1) @@ -2389,7 +2351,7 @@ error hint: Sort Key: b2t2.c1 -> Seq Scan on t2 b2t2 Filter: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Merge Join Merge Cond: (b3t1.c1 = b3t2.c1) @@ -2405,18 +2367,28 @@ error hint: Hash Cond: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) + Filter: (c1 <> $1) -> Hash -> Merge Join - Merge Cond: (bmt2.c1 = c1.c1) + Merge Cond: (bmt2.c1 = (max(b1t1.c1))) -> Sort Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Sort - Sort Key: c1.c1 - -> CTE Scan on c1 -(52 rows) + Sort Key: (max(b1t1.c1)) + -> Aggregate + -> Merge Join + Merge Cond: (b1t1.c1 = b1t2.c1) + -> Sort + Sort Key: b1t1.c1 + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) +(50 rows) -- No. J-2-2-3 EXPLAIN (COSTS false) @@ -2431,26 +2403,10 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t4.c1) @@ -2466,7 +2422,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 b2t4 TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t4.c1) @@ -2483,7 +2439,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 -> Tid Scan on t4 b3t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -2492,15 +2448,29 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) + Filter: (c1 <> $1) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(67 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) +(65 rows) /*+ Leading(c1 bmt4 bmt3 bmt2 bmt1) @@ -2556,31 +2526,10 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Merge Join - Merge Cond: (b1t2.c1 = b1t1.c1) - -> Sort - Sort Key: b1t2.c1 - -> Nested Loop - Join Filter: (b1t3.c1 = b1t2.c1) - -> Hash Join - Hash Cond: (b1t3.c1 = b1t4.c1) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Hash - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Sort - Sort Key: b1t1.c1 - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Merge Join Merge Cond: (b2t2.c1 = b2t1.c1) @@ -2601,7 +2550,7 @@ error hint: Sort Key: b2t1.c1 -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Merge Join Merge Cond: (b3t2.c1 = b3t1.c1) @@ -2634,22 +2583,41 @@ error hint: TID Cond: (ctid = '(1,1)'::tid) -> Hash -> Merge Join - Merge Cond: (bmt4.c1 = c1.c1) + Merge Cond: (bmt4.c1 = (max(b1t1.c1))) -> Sort Sort Key: bmt4.c1 -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) -> Sort - Sort Key: c1.c1 - -> CTE Scan on c1 + Sort Key: (max(b1t1.c1)) + -> Aggregate + -> Merge Join + Merge Cond: (b1t2.c1 = b1t1.c1) + -> Sort + Sort Key: b1t2.c1 + -> Nested Loop + Join Filter: (b1t3.c1 = b1t2.c1) + -> Hash Join + Hash Cond: (b1t3.c1 = b1t4.c1) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Hash + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: b1t1.c1 + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Sort Sort Key: bmt1.c1 -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) -(91 rows) + Filter: (c1 <> $1) +(89 rows) -- No. J-2-2-4 EXPLAIN (COSTS false) @@ -2664,38 +2632,22 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Limit -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 4 (returns $4) + InitPlan 3 (returns $3) -> Aggregate -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -2704,15 +2656,29 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $4) + Filter: (c1 <> $3) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(46 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) +(44 rows) /*+ Leading(c1 bmt4 bmt3 bmt2 bmt1) @@ -2752,38 +2718,17 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t2.c1 = b1t1.c1) - -> Hash Join - Hash Cond: (b1t2.c1 = b1t3.c1) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Hash - -> Merge Join - Merge Cond: (b1t3.c1 = b1t4.c1) - -> Sort - Sort Key: b1t3.c1 - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Sort - Sort Key: b1t4.c1 - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Limit -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 4 (returns $4) + InitPlan 3 (returns $3) -> Aggregate -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) @@ -2799,22 +2744,41 @@ error hint: TID Cond: (ctid = '(1,1)'::tid) -> Hash -> Merge Join - Merge Cond: (bmt4.c1 = c1.c1) + Merge Cond: (bmt4.c1 = (max(b1t1.c1))) -> Sort Sort Key: bmt4.c1 -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) -> Sort - Sort Key: c1.c1 - -> CTE Scan on c1 + Sort Key: (max(b1t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b1t2.c1 = b1t1.c1) + -> Hash Join + Hash Cond: (b1t2.c1 = b1t3.c1) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Hash + -> Merge Join + Merge Cond: (b1t3.c1 = b1t4.c1) + -> Sort + Sort Key: b1t3.c1 + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: b1t4.c1 + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Sort Sort Key: bmt1.c1 -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $4) -(60 rows) + Filter: (c1 <> $3) +(58 rows) ---- ---- No. J-2-3 RULE or VIEW @@ -4744,8 +4708,8 @@ error hint: \o \! sql/maskout.sh results/ut-J.tmpout - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------- Hash Full Join (cost={inf}..{inf} rows=1000 width=xxx) Hash Cond: (t1.c1 = t2.c1) -> Seq Scan on t1 (cost=xxx..xxx rows=1000 width=xxx) diff --git a/expected/ut-L.out b/expected/ut-L.out index 2e3389d..9aecb8e 100644 --- a/expected/ut-L.out +++ b/expected/ut-L.out @@ -1938,45 +1938,13 @@ SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 AND bmt1.c1 = c1.c1 AND bmt1.c1 = c2.c1 ; - QUERY PLAN ------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - CTE c2 - -> Aggregate - -> Nested Loop - Join Filter: (b2t1.c1 = b2t4.c1) - -> Nested Loop - Join Filter: (b2t1.c1 = b2t3.c1) - -> Nested Loop - Join Filter: (b2t1.c1 = b2t2.c1) - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b2t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b2t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b2t4 - TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c2.c1) + Join Filter: (bmt1.c1 = (max(b2t1.c1))) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -1991,9 +1959,37 @@ AND bmt1.c1 = c2.c1 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 - -> CTE Scan on c2 -(53 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t3.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b2t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b2t4 + TID Cond: (ctid = '(1,1)'::tid) +(49 rows) /*+ Leading(c2 c1 bmt1 bmt2 bmt3 bmt4) @@ -2022,41 +2018,9 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t2.c1 = b1t1.c1) - -> Nested Loop - Join Filter: (b1t2.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t2.c1 = b1t3.c1) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - CTE c2 - -> Aggregate - -> Nested Loop - Join Filter: (b2t1.c1 = b2t2.c1) - -> Nested Loop - Join Filter: (b2t3.c1 = b2t1.c1) - -> Nested Loop - Join Filter: (b2t3.c1 = b2t4.c1) - -> Tid Scan on t3 b2t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b2t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b2t2 - Filter: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -2064,11 +2028,43 @@ error hint: -> Nested Loop Join Filter: (bmt1.c1 = bmt2.c1) -> Nested Loop - Join Filter: (c1.c1 = bmt1.c1) - -> Nested Loop - Join Filter: (c1.c1 = c2.c1) - -> CTE Scan on c1 - -> CTE Scan on c2 + Join Filter: ((max(b1t1.c1)) = bmt1.c1) + -> Merge Join + Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1))) + -> Sort + Sort Key: (max(b1t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b1t2.c1 = b1t1.c1) + -> Nested Loop + Join Filter: (b1t2.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t2.c1 = b1t3.c1) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: (max(b2t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Nested Loop + Join Filter: (b2t3.c1 = b2t1.c1) + -> Nested Loop + Join Filter: (b2t3.c1 = b2t4.c1) + -> Tid Scan on t3 b2t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b2t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 bmt2 @@ -2097,81 +2093,79 @@ AND bmt1.c1 = c1.c1 AND bmt1.c1 = c2.c1 AND bmt1.c1 = c3.c1 ; - QUERY PLAN ------------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - CTE c2 - -> Aggregate - -> Nested Loop - Join Filter: (b2t1.c1 = b2t4.c1) - -> Nested Loop - Join Filter: (b2t1.c1 = b2t3.c1) - -> Nested Loop - Join Filter: (b2t1.c1 = b2t2.c1) - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b2t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b2t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b2t4 - TID Cond: (ctid = '(1,1)'::tid) - CTE c3 - -> Aggregate - -> Nested Loop - Join Filter: (b3t1.c1 = b3t4.c1) - -> Nested Loop - Join Filter: (b3t1.c1 = b3t3.c1) - -> Nested Loop - Join Filter: (b3t1.c1 = b3t2.c1) - -> Tid Scan on t1 b3t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b3t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b3t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b3t4 - TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c3.c1) + Join Filter: (bmt1.c1 = (max(b3t1.c1))) -> Nested Loop - Join Filter: (bmt1.c1 = c2.c1) + Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = bmt3.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt4.c1) + Join Filter: (bmt1.c1 = bmt2.c1) -> Nested Loop - Join Filter: (bmt1.c1 = bmt3.c1) - -> Nested Loop - Join Filter: (bmt1.c1 = bmt2.c1) - -> Tid Scan on t1 bmt1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 bmt2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 bmt3 + Join Filter: ((max(b1t1.c1)) = bmt1.c1) + -> Merge Join + Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1))) + -> Sort + Sort Key: (max(b1t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: (max(b2t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t4.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t3.c1) + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b2t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b2t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 bmt4 + -> Seq Scan on t2 bmt2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 bmt3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 bmt4 + TID Cond: (ctid = '(1,1)'::tid) + -> Aggregate + -> Nested Loop + Join Filter: (b3t1.c1 = b3t4.c1) + -> Nested Loop + Join Filter: (b3t1.c1 = b3t3.c1) + -> Nested Loop + Join Filter: (b3t1.c1 = b3t2.c1) + -> Tid Scan on t1 b3t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b3t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b3t3 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 - -> CTE Scan on c2 - -> CTE Scan on c3 -(72 rows) + -> Tid Scan on t4 b3t4 + TID Cond: (ctid = '(1,1)'::tid) +(70 rows) /*+ Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4) @@ -2206,57 +2200,9 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t2.c1 = b1t1.c1) - -> Nested Loop - Join Filter: (b1t2.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t2.c1 = b1t3.c1) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - CTE c2 - -> Aggregate - -> Nested Loop - Join Filter: (b2t1.c1 = b2t2.c1) - -> Nested Loop - Join Filter: (b2t3.c1 = b2t1.c1) - -> Nested Loop - Join Filter: (b2t3.c1 = b2t4.c1) - -> Tid Scan on t3 b2t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b2t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b2t2 - Filter: (ctid = '(1,1)'::tid) - CTE c3 - -> Aggregate - -> Nested Loop - Join Filter: (b3t1.c1 = b3t3.c1) - -> Nested Loop - Join Filter: (b3t1.c1 = b3t2.c1) - -> Nested Loop - Join Filter: (b3t1.c1 = b3t4.c1) - -> Tid Scan on t1 b3t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b3t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b3t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b3t3 - TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -2264,14 +2210,60 @@ error hint: -> Nested Loop Join Filter: (bmt1.c1 = bmt2.c1) -> Nested Loop - Join Filter: (c1.c1 = bmt1.c1) + Join Filter: ((max(b1t1.c1)) = bmt1.c1) -> Nested Loop - Join Filter: (c2.c1 = c1.c1) - -> Nested Loop - Join Filter: (c2.c1 = c3.c1) - -> CTE Scan on c2 - -> CTE Scan on c3 - -> CTE Scan on c1 + Join Filter: ((max(b2t1.c1)) = (max(b1t1.c1))) + -> Merge Join + Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1))) + -> Sort + Sort Key: (max(b2t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b2t1.c1 = b2t2.c1) + -> Nested Loop + Join Filter: (b2t3.c1 = b2t1.c1) + -> Nested Loop + Join Filter: (b2t3.c1 = b2t4.c1) + -> Tid Scan on t3 b2t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b2t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t1 b2t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b2t2 + Filter: (ctid = '(1,1)'::tid) + -> Sort + Sort Key: (max(b3t1.c1)) + -> Aggregate + -> Nested Loop + Join Filter: (b3t1.c1 = b3t3.c1) + -> Nested Loop + Join Filter: (b3t1.c1 = b3t2.c1) + -> Nested Loop + Join Filter: (b3t1.c1 = b3t4.c1) + -> Tid Scan on t1 b3t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b3t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b3t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b3t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Aggregate + -> Nested Loop + Join Filter: (b1t2.c1 = b1t1.c1) + -> Nested Loop + Join Filter: (b1t2.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t2.c1 = b1t3.c1) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 bmt2 @@ -2280,7 +2272,7 @@ error hint: TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) -(72 rows) +(70 rows) ---- ---- No. L-2-2 the number of the tables per quiry block @@ -2301,33 +2293,26 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1 QUERY PLAN ----------------------------------------------------------------- Aggregate - CTE c1 + InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Tid Scan on t1 b1t1 + -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 4 (returns $3) -> Result InitPlan 3 (returns $2) -> Limit - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 6 (returns $5) - -> Result - InitPlan 5 (returns $4) - -> Limit -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 <> $5) AND (c1 = 1)) - -> CTE Scan on c1 -(27 rows) + Filter: ((c1 <> $3) AND (c1 = 1)) + -> Result +(20 rows) /*+ Leading(c1 bmt1) @@ -2354,33 +2339,26 @@ error hint: QUERY PLAN ----------------------------------------------------------------- Aggregate - CTE c1 + InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Tid Scan on t1 b1t1 + -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 4 (returns $3) -> Result InitPlan 3 (returns $2) -> Limit - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 6 (returns $5) - -> Result - InitPlan 5 (returns $4) - -> Limit -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 <> $5) AND (c1 = 1)) - -> CTE Scan on c1 -(27 rows) + Filter: ((c1 <> $3) AND (c1 = 1)) + -> Result +(20 rows) -- No. L-2-2-2 EXPLAIN (COSTS false) @@ -2396,18 +2374,10 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t2.c1) @@ -2415,7 +2385,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3 TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 b2t2 Filter: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t2.c1) @@ -2424,16 +2394,22 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3 -> Seq Scan on t2 b3t2 Filter: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) + Filter: (c1 <> $1) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(35 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) +(33 rows) /*+ Leading(c1 bmt2 bmt1) @@ -2464,18 +2440,10 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t2.c1) @@ -2483,7 +2451,7 @@ error hint: TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 b2t2 Filter: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t2.c1) @@ -2494,14 +2462,20 @@ error hint: -> Nested Loop Join Filter: (bmt2.c1 = bmt1.c1) -> Nested Loop - Join Filter: (bmt2.c1 = c1.c1) + Join Filter: (bmt2.c1 = (max(b1t1.c1))) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) -(35 rows) + Filter: (c1 <> $1) +(33 rows) -- No. L-2-2-3 EXPLAIN (COSTS false) @@ -2516,26 +2490,10 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t4.c1) @@ -2551,7 +2509,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 b2t4 TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t4.c1) @@ -2568,7 +2526,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 -> Tid Scan on t4 b3t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -2577,15 +2535,29 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) + Filter: (c1 <> $1) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(67 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) +(65 rows) /*+ Leading(c1 bmt4 bmt3 bmt2 bmt1) @@ -2615,26 +2587,10 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t2.c1 = b1t1.c1) - -> Nested Loop - Join Filter: (b1t3.c1 = b1t2.c1) - -> Nested Loop - Join Filter: (b1t3.c1 = b1t4.c1) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop Join Filter: (b2t2.c1 = b2t1.c1) @@ -2650,7 +2606,7 @@ error hint: Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b3t2.c1 = b3t1.c1) @@ -2673,18 +2629,32 @@ error hint: -> Nested Loop Join Filter: (bmt4.c1 = bmt3.c1) -> Nested Loop - Join Filter: (bmt4.c1 = c1.c1) + Join Filter: (bmt4.c1 = (max(b1t1.c1))) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 + -> Aggregate + -> Nested Loop + Join Filter: (b1t2.c1 = b1t1.c1) + -> Nested Loop + Join Filter: (b1t3.c1 = b1t2.c1) + -> Nested Loop + Join Filter: (b1t3.c1 = b1t4.c1) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) -(67 rows) + Filter: (c1 <> $1) +(65 rows) -- No. L-2-2-4 EXPLAIN (COSTS false) @@ -2699,38 +2669,22 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Limit -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 4 (returns $4) + InitPlan 3 (returns $3) -> Aggregate -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -2739,15 +2693,29 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $4) + Filter: (c1 <> $3) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(46 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) +(44 rows) /*+ Leading(c1 bmt4 bmt3 bmt2 bmt1) @@ -2773,33 +2741,17 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t2.c1 = b1t1.c1) - -> Nested Loop - Join Filter: (b1t3.c1 = b1t2.c1) - -> Nested Loop - Join Filter: (b1t3.c1 = b1t4.c1) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Limit -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 4 (returns $4) + InitPlan 3 (returns $3) -> Aggregate -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) @@ -2810,18 +2762,32 @@ error hint: -> Nested Loop Join Filter: (bmt4.c1 = bmt3.c1) -> Nested Loop - Join Filter: (bmt4.c1 = c1.c1) + Join Filter: (bmt4.c1 = (max(b1t1.c1))) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 + -> Aggregate + -> Nested Loop + Join Filter: (b1t2.c1 = b1t1.c1) + -> Nested Loop + Join Filter: (b1t3.c1 = b1t2.c1) + -> Nested Loop + Join Filter: (b1t3.c1 = b1t4.c1) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $4) -(46 rows) + Filter: (c1 <> $3) +(44 rows) ---- ---- No. L-2-3 RULE or VIEW diff --git a/expected/ut-R.out b/expected/ut-R.out index a268d6d..29290c9 100644 --- a/expected/ut-R.out +++ b/expected/ut-R.out @@ -747,20 +747,18 @@ error hint: EXPLAIN WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1; \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN --------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------ Nested Loop (cost=xxx..xxx rows=1 width=xxx) - CTE c1 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Merge Join (cost=xxx..xxx rows=100 width=xxx) - Merge Cond: (t1_1.c1 = t2.c1) - -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: t2.c1 - -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx) - -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Merge Join (cost=xxx..xxx rows=100 width=xxx) + Merge Cond: (t1_1.c1 = t2.c1) + -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx) + -> Sort (cost=xxx..xxx rows=100 width=xxx) + Sort Key: t2.c1 + -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = c1.c1) + Index Cond: (c1 = (max(t1_1.c1))) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)Rows(t1 c1 +1)*/ @@ -775,20 +773,18 @@ error hint: \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN --------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------ Nested Loop (cost=xxx..xxx rows=2 width=xxx) - CTE c1 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (t1_1.c1 = t2.c1) - -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: t2.c1 - -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx) - -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Merge Join (cost=xxx..xxx rows=1 width=xxx) + Merge Cond: (t1_1.c1 = t2.c1) + -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx) + -> Sort (cost=xxx..xxx rows=100 width=xxx) + Sort Key: t2.c1 + -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = c1.c1) + Index Cond: (c1 = (max(t1_1.c1))) -- No. R-1-6-10 \o results/ut-R.tmpout @@ -2019,38 +2015,9 @@ error hint: \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN --------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=xxx..xxx rows=1 width=xxx) - CTE c1 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) - Join Filter: (b1t2.c1 = b1t1.c1) - -> Hash Join (cost=xxx..xxx rows=100 width=xxx) - Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=100 width=xxx) - -> Merge Join (cost=xxx..xxx rows=100 width=xxx) - Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b1t3.c1) - CTE c2 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) - -> Hash Join (cost=xxx..xxx rows=1000 width=xxx) - Hash Cond: (b2t3.c1 = b2t1.c1) - -> Merge Join (cost=xxx..xxx rows=1130 width=xxx) - Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=1000 width=xxx) - -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b2t1.c1) -> Hash Join (cost=xxx..xxx rows=1 width=xxx) Hash Cond: (bmt4.c1 = bmt1.c1) -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx) @@ -2062,17 +2029,42 @@ error hint: Sort Key: bmt1.c1 -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (bmt1.c1 = c1.c1) + Hash Cond: (bmt1.c1 = (max(b1t1.c1))) -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx) -> Hash (cost=xxx..xxx rows=1 width=xxx) -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (c1.c1 = c2.c1) + Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1))) -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: c1.c1 - -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx) + Sort Key: (max(b1t1.c1)) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) + Join Filter: (b1t2.c1 = b1t1.c1) + -> Hash Join (cost=xxx..xxx rows=100 width=xxx) + Hash Cond: (b1t4.c1 = b1t2.c1) + -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=100 width=xxx) + -> Merge Join (cost=xxx..xxx rows=100 width=xxx) + Merge Cond: (b1t3.c1 = b1t2.c1) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Sort (cost=xxx..xxx rows=100 width=xxx) + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: c2.c1 - -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx) + Sort Key: (max(b2t1.c1)) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) + -> Hash Join (cost=xxx..xxx rows=1000 width=xxx) + Hash Cond: (b2t3.c1 = b2t1.c1) + -> Merge Join (cost=xxx..xxx rows=1130 width=xxx) + Merge Cond: (b2t3.c1 = b2t4.c1) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=1000 width=xxx) + -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) + -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b2t1.c1) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) @@ -2133,38 +2125,9 @@ error hint: \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN --------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=xxx..xxx rows=1 width=xxx) - CTE c1 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) - Join Filter: (b1t2.c1 = b1t1.c1) - -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=1 width=xxx) - -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b1t3.c1) - CTE c2 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) - -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (b2t1.c1 = b2t3.c1) - -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Hash (cost=xxx..xxx rows=1 width=xxx) - -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b2t1.c1) -> Hash Join (cost=xxx..xxx rows=1 width=xxx) Hash Cond: (bmt4.c1 = bmt1.c1) -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx) @@ -2176,17 +2139,42 @@ error hint: Sort Key: bmt1.c1 -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (bmt1.c1 = c1.c1) + Hash Cond: (bmt1.c1 = (max(b1t1.c1))) -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx) -> Hash (cost=xxx..xxx rows=1 width=xxx) -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (c1.c1 = c2.c1) + Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1))) -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: c1.c1 - -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx) + Sort Key: (max(b1t1.c1)) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) + Join Filter: (b1t2.c1 = b1t1.c1) + -> Hash Join (cost=xxx..xxx rows=1 width=xxx) + Hash Cond: (b1t4.c1 = b1t2.c1) + -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=1 width=xxx) + -> Merge Join (cost=xxx..xxx rows=1 width=xxx) + Merge Cond: (b1t3.c1 = b1t2.c1) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Sort (cost=xxx..xxx rows=100 width=xxx) + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: c2.c1 - -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx) + Sort Key: (max(b2t1.c1)) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) + -> Hash Join (cost=xxx..xxx rows=1 width=xxx) + Hash Cond: (b2t1.c1 = b2t3.c1) + -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) + -> Hash (cost=xxx..xxx rows=1 width=xxx) + -> Merge Join (cost=xxx..xxx rows=1 width=xxx) + Merge Cond: (b2t3.c1 = b2t4.c1) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b2t1.c1) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) @@ -2245,51 +2233,9 @@ error hint: \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN --------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=xxx..xxx rows=1 width=xxx) - CTE c1 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) - Join Filter: (b1t2.c1 = b1t1.c1) - -> Hash Join (cost=xxx..xxx rows=100 width=xxx) - Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=100 width=xxx) - -> Merge Join (cost=xxx..xxx rows=100 width=xxx) - Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b1t3.c1) - CTE c2 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) - -> Hash Join (cost=xxx..xxx rows=1000 width=xxx) - Hash Cond: (b2t3.c1 = b2t1.c1) - -> Merge Join (cost=xxx..xxx rows=1130 width=xxx) - Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=1000 width=xxx) - -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b2t1.c1) - CTE c3 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) - -> Hash Join (cost=xxx..xxx rows=100 width=xxx) - Hash Cond: (b3t1.c1 = b3t2.c1) - -> Merge Join (cost=xxx..xxx rows=1000 width=xxx) - Merge Cond: (b3t1.c1 = b3t4.c1) - -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=100 width=xxx) - -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx) - -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b3t1.c1) -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) -> Hash Join (cost=xxx..xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) @@ -2300,21 +2246,57 @@ error hint: -> Sort (cost=xxx..xxx rows=1 width=xxx) Sort Key: bmt1.c1 -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) - Join Filter: (c1.c1 = bmt1.c1) + Join Filter: ((max(b1t1.c1)) = bmt1.c1) -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (c2.c1 = c1.c1) + Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1))) -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (c2.c1 = c3.c1) + Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1))) -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: c2.c1 - -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx) + Sort Key: (max(b2t1.c1)) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) + -> Hash Join (cost=xxx..xxx rows=1000 width=xxx) + Hash Cond: (b2t3.c1 = b2t1.c1) + -> Merge Join (cost=xxx..xxx rows=1130 width=xxx) + Merge Cond: (b2t3.c1 = b2t4.c1) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=1000 width=xxx) + -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) + -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b2t1.c1) -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: c3.c1 - -> CTE Scan on c3 (cost=xxx..xxx rows=1 width=xxx) + Sort Key: (max(b3t1.c1)) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) + -> Hash Join (cost=xxx..xxx rows=100 width=xxx) + Hash Cond: (b3t1.c1 = b3t2.c1) + -> Merge Join (cost=xxx..xxx rows=1000 width=xxx) + Merge Cond: (b3t1.c1 = b3t4.c1) + -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx) + -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=100 width=xxx) + -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b3t1.c1) -> Hash (cost=xxx..xxx rows=1 width=xxx) - -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) + Join Filter: (b1t2.c1 = b1t1.c1) + -> Hash Join (cost=xxx..xxx rows=100 width=xxx) + Hash Cond: (b1t4.c1 = b1t2.c1) + -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=100 width=xxx) + -> Merge Join (cost=xxx..xxx rows=100 width=xxx) + Merge Cond: (b1t3.c1 = b1t2.c1) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Sort (cost=xxx..xxx rows=100 width=xxx) + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = c2.c1) + Index Cond: (c1 = (max(b2t1.c1))) -> Sort (cost=xxx..xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx) @@ -2394,51 +2376,9 @@ error hint: \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN --------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=xxx..xxx rows=1 width=xxx) - CTE c1 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) - Join Filter: (b1t2.c1 = b1t1.c1) - -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=1 width=xxx) - -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b1t3.c1) - CTE c2 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) - -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (b2t1.c1 = b2t3.c1) - -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Hash (cost=xxx..xxx rows=1 width=xxx) - -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b2t1.c1) - CTE c3 - -> Aggregate (cost=xxx..xxx rows=1 width=xxx) - -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) - -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (b3t1.c1 = b3t2.c1) - -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (b3t1.c1 = b3t4.c1) - -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=100 width=xxx) - -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx) - -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b3t1.c1) -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) -> Hash Join (cost=xxx..xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) @@ -2449,21 +2389,57 @@ error hint: -> Sort (cost=xxx..xxx rows=1 width=xxx) Sort Key: bmt1.c1 -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) - Join Filter: (c1.c1 = bmt1.c1) + Join Filter: ((max(b1t1.c1)) = bmt1.c1) -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (c2.c1 = c1.c1) + Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1))) -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (c2.c1 = c3.c1) + Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1))) -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: c2.c1 - -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx) + Sort Key: (max(b2t1.c1)) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) + -> Hash Join (cost=xxx..xxx rows=1 width=xxx) + Hash Cond: (b2t1.c1 = b2t3.c1) + -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) + -> Hash (cost=xxx..xxx rows=1 width=xxx) + -> Merge Join (cost=xxx..xxx rows=1 width=xxx) + Merge Cond: (b2t3.c1 = b2t4.c1) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b2t1.c1) -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: c3.c1 - -> CTE Scan on c3 (cost=xxx..xxx rows=1 width=xxx) + Sort Key: (max(b3t1.c1)) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) + -> Hash Join (cost=xxx..xxx rows=1 width=xxx) + Hash Cond: (b3t1.c1 = b3t2.c1) + -> Merge Join (cost=xxx..xxx rows=1 width=xxx) + Merge Cond: (b3t1.c1 = b3t4.c1) + -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx) + -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=100 width=xxx) + -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b3t1.c1) -> Hash (cost=xxx..xxx rows=1 width=xxx) - -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx) + -> Aggregate (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) + Join Filter: (b1t2.c1 = b1t1.c1) + -> Hash Join (cost=xxx..xxx rows=1 width=xxx) + Hash Cond: (b1t4.c1 = b1t2.c1) + -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=1 width=xxx) + -> Merge Join (cost=xxx..xxx rows=1 width=xxx) + Merge Cond: (b1t3.c1 = b1t2.c1) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Sort (cost=xxx..xxx rows=100 width=xxx) + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = c2.c1) + Index Cond: (c1 = (max(b2t1.c1))) -> Sort (cost=xxx..xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx) @@ -2492,8 +2468,8 @@ SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1 ); LOG: pg_hint_plan: used hint: -Leading(c1 bmt1) not used hint: +Leading(c1 bmt1) duplication hint: error hint: @@ -2502,20 +2478,17 @@ error hint: QUERY PLAN ---------------------------------------------------------------------------------- Nested Loop (cost=xxx..xxx rows=1 width=xxx) - CTE c1 - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = 1) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = 1) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = 1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = 1) - Filter: (c1 <> $2) - -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx) - Filter: (c1 = 1) + Filter: (c1 <> $1) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = 1) \o results/ut-R.tmpout /*+ @@ -2539,12 +2512,12 @@ SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1 ); LOG: pg_hint_plan: used hint: -Leading(c1 bmt1) -Rows(bmt1 c1 #1) not used hint: +Leading(c1 bmt1) Rows(b1t1 c1 #1) Rows(b2t1 c1 #1) Rows(b3t1 c1 #1) +Rows(bmt1 c1 #1) duplication hint: error hint: @@ -2553,20 +2526,17 @@ error hint: QUERY PLAN ---------------------------------------------------------------------------------- Nested Loop (cost=xxx..xxx rows=1 width=xxx) - CTE c1 - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = 1) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = 1) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = 1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = 1) - Filter: (c1 <> $2) - -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx) - Filter: (c1 = 1) + Filter: (c1 <> $1) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = 1) -- No. R-2-2-2 \o results/ut-R.tmpout @@ -2598,54 +2568,48 @@ used hint: MergeJoin(b1t1 b1t2) MergeJoin(b2t1 b2t2) MergeJoin(b3t1 b3t2) -MergeJoin(bmt2 c1) -HashJoin(bmt1 bmt2 c1) -Leading(c1 bmt2 bmt1) Leading(b1t2 b1t1) Leading(b2t2 b2t1) Leading(b3t2 b3t1) not used hint: +MergeJoin(bmt2 c1) +HashJoin(bmt1 bmt2 c1) +Leading(c1 bmt2 bmt1) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN --------------------------------------------------------------------------------------------- - Hash Join (cost=xxx..xxx rows=10 width=xxx) - Hash Cond: (bmt1.c1 = bmt2.c1) - CTE c1 - -> Merge Join (cost=xxx..xxx rows=100 width=xxx) - Merge Cond: (b1t1.c1 = b1t2.c1) - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) - InitPlan 2 (returns $1) + QUERY PLAN +------------------------------------------------------------------------------------------------ + Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt2.c1) + InitPlan 1 (returns $0) -> Merge Join (cost=xxx..xxx rows=100 width=xxx) Merge Cond: (b2t1.c1 = b2t2.c1) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) -> Sort (cost=xxx..xxx rows=100 width=xxx) Sort Key: b2t2.c1 -> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=100 width=xxx) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Merge Join (cost=xxx..xxx rows=100 width=xxx) Merge Cond: (b3t1.c1 = b3t2.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx) -> Sort (cost=xxx..xxx rows=100 width=xxx) Sort Key: b3t2.c1 -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx) - -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx) - Filter: (c1 <> $2) - -> Hash (cost=xxx..xxx rows=100 width=xxx) + -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx) -> Merge Join (cost=xxx..xxx rows=100 width=xxx) - Merge Cond: (bmt2.c1 = c1.c1) + Merge Cond: (b1t1.c1 = b1t2.c1) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx) -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: bmt2.c1 - -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: c1.c1 - -> CTE Scan on c1 (cost=xxx..xxx rows=100 width=xxx) + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) + -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t1.c1) + Filter: (c1 <> $1) + -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t1.c1) \o results/ut-R.tmpout /*+ @@ -2682,59 +2646,53 @@ used hint: MergeJoin(b1t1 b1t2) MergeJoin(b2t1 b2t2) MergeJoin(b3t1 b3t2) -MergeJoin(bmt2 c1) -HashJoin(bmt1 bmt2 c1) -Leading(c1 bmt2 bmt1) Leading(b1t2 b1t1) Leading(b2t2 b2t1) Leading(b3t2 b3t1) Rows(b1t1 b1t2 #1) Rows(b2t1 b2t2 #1) Rows(b3t1 b3t2 #1) +not used hint: +MergeJoin(bmt2 c1) +HashJoin(bmt1 bmt2 c1) +Leading(c1 bmt2 bmt1) Rows(bmt2 c1 #1) Rows(bmt1 bmt2 c1 #1) -not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN --------------------------------------------------------------------------------------------- - Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (bmt1.c1 = bmt2.c1) - CTE c1 - -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (b1t1.c1 = b1t2.c1) - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) - InitPlan 2 (returns $1) + QUERY PLAN +------------------------------------------------------------------------------------------------ + Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt2.c1) + InitPlan 1 (returns $0) -> Merge Join (cost=xxx..xxx rows=1 width=xxx) Merge Cond: (b2t1.c1 = b2t2.c1) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) -> Sort (cost=xxx..xxx rows=100 width=xxx) Sort Key: b2t2.c1 -> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=100 width=xxx) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Merge Join (cost=xxx..xxx rows=1 width=xxx) Merge Cond: (b3t1.c1 = b3t2.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx) -> Sort (cost=xxx..xxx rows=100 width=xxx) Sort Key: b3t2.c1 -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx) - -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx) - Filter: (c1 <> $2) - -> Hash (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx) -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (bmt2.c1 = c1.c1) + Merge Cond: (b1t1.c1 = b1t2.c1) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx) -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: bmt2.c1 - -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx) - -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: c1.c1 - -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx) + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) + -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t1.c1) + Filter: (c1 <> $1) + -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t1.c1) -- No. R-2-2-3 \o results/ut-R.tmpout @@ -2773,45 +2731,31 @@ used hint: HashJoin(b1t3 b1t4) HashJoin(b2t3 b2t4) HashJoin(b3t3 b3t4) -MergeJoin(bmt4 c1) NestLoop(b1t2 b1t3 b1t4) NestLoop(b2t2 b2t3 b2t4) NestLoop(b3t2 b3t3 b3t4) -HashJoin(bmt3 bmt4 c1) MergeJoin(b1t1 b1t2 b1t3 b1t4) MergeJoin(b2t1 b2t2 b2t3 b2t4) MergeJoin(b3t1 b3t2 b3t3 b3t4) -NestLoop(bmt2 bmt3 bmt4 c1) -MergeJoin(bmt1 bmt2 bmt3 bmt4 c1) -Leading(c1 bmt4 bmt3 bmt2 bmt1) Leading(b1t4 b1t3 b1t2 b1t1) Leading(b2t4 b2t3 b2t2 b2t1) Leading(b3t4 b3t3 b3t2 b3t1) not used hint: +MergeJoin(bmt4 c1) +HashJoin(bmt3 bmt4 c1) +NestLoop(bmt2 bmt3 bmt4 c1) +MergeJoin(bmt1 bmt2 bmt3 bmt4 c1) +Leading(c1 bmt4 bmt3 bmt2 bmt1) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- - Merge Join (cost=xxx..xxx rows=10 width=xxx) - Merge Cond: (bmt1.c1 = bmt2.c1) - CTE c1 - -> Merge Join (cost=xxx..xxx rows=100 width=xxx) - Merge Cond: (b1t1.c1 = b1t2.c1) - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: b1t2.c1 - -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) - -> Hash Join (cost=xxx..xxx rows=1130 width=xxx) - Hash Cond: (b1t3.c1 = b1t4.c1) - -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=1130 width=xxx) - -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b1t3.c1) - InitPlan 2 (returns $3) + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------- + Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt4.c1) + InitPlan 1 (returns $1) -> Merge Join (cost=xxx..xxx rows=100 width=xxx) Merge Cond: (b2t1.c1 = b2t2.c1) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) @@ -2825,7 +2769,7 @@ error hint: -> Seq Scan on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = b2t3.c1) - InitPlan 3 (returns $5) + InitPlan 2 (returns $3) -> Merge Join (cost=xxx..xxx rows=100 width=xxx) Merge Cond: (b3t1.c1 = b3t2.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx) @@ -2839,23 +2783,34 @@ error hint: -> Seq Scan on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = b3t3.c1) - -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx) - Filter: (c1 <> $5) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: bmt2.c1 - -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) - -> Hash Join (cost=xxx..xxx rows=100 width=xxx) - Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=100 width=xxx) - -> Merge Join (cost=xxx..xxx rows=100 width=xxx) - Merge Cond: (bmt4.c1 = c1.c1) - -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: c1.c1 - -> CTE Scan on c1 (cost=xxx..xxx rows=100 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt3.c1) + -> Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt2.c1) + -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx) + Join Filter: (b1t1.c1 = bmt1.c1) + -> Merge Join (cost=xxx..xxx rows=100 width=xxx) + Merge Cond: (b1t1.c1 = b1t2.c1) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx) + -> Sort (cost=xxx..xxx rows=100 width=xxx) + Sort Key: b1t2.c1 + -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) + -> Hash Join (cost=xxx..xxx rows=1130 width=xxx) + Hash Cond: (b1t3.c1 = b1t4.c1) + -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=1130 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + Filter: (c1 <> $3) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = bmt3.c1) + Index Cond: (c1 = b1t3.c1) + -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = bmt3.c1) \o results/ut-R.tmpout /*+ @@ -2906,58 +2861,44 @@ used hint: HashJoin(b1t3 b1t4) HashJoin(b2t3 b2t4) HashJoin(b3t3 b3t4) -MergeJoin(bmt4 c1) NestLoop(b1t2 b1t3 b1t4) NestLoop(b2t2 b2t3 b2t4) NestLoop(b3t2 b3t3 b3t4) -HashJoin(bmt3 bmt4 c1) MergeJoin(b1t1 b1t2 b1t3 b1t4) MergeJoin(b2t1 b2t2 b2t3 b2t4) MergeJoin(b3t1 b3t2 b3t3 b3t4) -NestLoop(bmt2 bmt3 bmt4 c1) -MergeJoin(bmt1 bmt2 bmt3 bmt4 c1) -Leading(c1 bmt4 bmt3 bmt2 bmt1) Leading(b1t4 b1t3 b1t2 b1t1) Leading(b2t4 b2t3 b2t2 b2t1) Leading(b3t4 b3t3 b3t2 b3t1) Rows(b1t3 b1t4 #1) Rows(b2t3 b2t4 #1) Rows(b3t3 b3t4 #1) -Rows(bmt4 c1 #1) Rows(b1t2 b1t3 b1t4 #1) Rows(b2t2 b2t3 b2t4 #1) Rows(b3t2 b3t3 b3t4 #1) -Rows(bmt3 bmt4 c1 #1) Rows(b1t1 b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t2 b2t3 b2t4 #1) Rows(b3t1 b3t2 b3t3 b3t4 #1) +not used hint: +MergeJoin(bmt4 c1) +HashJoin(bmt3 bmt4 c1) +NestLoop(bmt2 bmt3 bmt4 c1) +MergeJoin(bmt1 bmt2 bmt3 bmt4 c1) +Leading(c1 bmt4 bmt3 bmt2 bmt1) +Rows(bmt4 c1 #1) +Rows(bmt3 bmt4 c1 #1) Rows(bmt2 bmt3 bmt4 c1 #1) Rows(bmt1 bmt2 bmt3 bmt4 c1 #1) -not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- - Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (bmt1.c1 = bmt2.c1) - CTE c1 - -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (b1t1.c1 = b1t2.c1) - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: b1t2.c1 - -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) - -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (b1t3.c1 = b1t4.c1) - -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=1130 width=xxx) - -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b1t3.c1) - InitPlan 2 (returns $3) + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------- + Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt4.c1) + InitPlan 1 (returns $1) -> Merge Join (cost=xxx..xxx rows=1 width=xxx) Merge Cond: (b2t1.c1 = b2t2.c1) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx) @@ -2971,7 +2912,7 @@ error hint: -> Seq Scan on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = b2t3.c1) - InitPlan 3 (returns $5) + InitPlan 2 (returns $3) -> Merge Join (cost=xxx..xxx rows=1 width=xxx) Merge Cond: (b3t1.c1 = b3t2.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx) @@ -2985,23 +2926,34 @@ error hint: -> Seq Scan on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = b3t3.c1) - -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx) - Filter: (c1 <> $5) - -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: bmt2.c1 - -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) - -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=1 width=xxx) - -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (bmt4.c1 = c1.c1) - -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx) - -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: c1.c1 - -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt3.c1) + -> Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt2.c1) + -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx) + Join Filter: (b1t1.c1 = bmt1.c1) + -> Merge Join (cost=xxx..xxx rows=1 width=xxx) + Merge Cond: (b1t1.c1 = b1t2.c1) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx) + -> Sort (cost=xxx..xxx rows=1 width=xxx) + Sort Key: b1t2.c1 + -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) + -> Hash Join (cost=xxx..xxx rows=1 width=xxx) + Hash Cond: (b1t3.c1 = b1t4.c1) + -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=1130 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + Filter: (c1 <> $3) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = bmt3.c1) + Index Cond: (c1 = b1t3.c1) + -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = bmt3.c1) -- No. R-2-2-4 \o results/ut-R.tmpout @@ -3030,59 +2982,56 @@ SELECT b3t1.c1 FROM s1.t1 b3t1 LOG: pg_hint_plan: used hint: MergeJoin(b1t3 b1t4) -MergeJoin(bmt4 c1) HashJoin(b1t2 b1t3 b1t4) -HashJoin(bmt3 bmt4 c1) NestLoop(b1t1 b1t2 b1t3 b1t4) +Leading(b1t4 b1t3 b1t2 b1t1) +not used hint: +MergeJoin(bmt4 c1) +HashJoin(bmt3 bmt4 c1) NestLoop(bmt2 bmt3 bmt4 c1) MergeJoin(bmt1 bmt2 bmt3 bmt4 c1) Leading(c1 bmt4 bmt3 bmt2 bmt1) -Leading(b1t4 b1t3 b1t2 b1t1) -not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- - Merge Join (cost=xxx..xxx rows=10 width=xxx) - Merge Cond: (bmt1.c1 = bmt2.c1) - CTE c1 - -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) - Join Filter: (b1t2.c1 = b1t1.c1) - -> Hash Join (cost=xxx..xxx rows=100 width=xxx) - Hash Cond: (b1t3.c1 = b1t2.c1) - -> Merge Join (cost=xxx..xxx rows=1130 width=xxx) - Merge Cond: (b1t3.c1 = b1t4.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=100 width=xxx) - -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b1t3.c1) - InitPlan 2 (returns $2) + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------ + Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt4.c1) + InitPlan 1 (returns $0) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = 1) - InitPlan 3 (returns $3) + InitPlan 2 (returns $1) -> Seq Scan on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx) - Filter: (c1 <> $3) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: bmt2.c1 - -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) - -> Hash Join (cost=xxx..xxx rows=100 width=xxx) - Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=100 width=xxx) - -> Merge Join (cost=xxx..xxx rows=100 width=xxx) - Merge Cond: (bmt4.c1 = c1.c1) - -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx) - -> Sort (cost=xxx..xxx rows=100 width=xxx) - Sort Key: c1.c1 - -> CTE Scan on c1 (cost=xxx..xxx rows=100 width=xxx) - -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = bmt3.c1) + -> Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt3.c1) + -> Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt2.c1 = bmt1.c1) + -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx) + Join Filter: (b1t1.c1 = bmt2.c1) + -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx) + -> Materialize (cost=xxx..xxx rows=100 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=100 width=xxx) + Join Filter: (b1t2.c1 = b1t1.c1) + -> Hash Join (cost=xxx..xxx rows=100 width=xxx) + Hash Cond: (b1t3.c1 = b1t2.c1) + -> Merge Join (cost=xxx..xxx rows=1130 width=xxx) + Merge Cond: (b1t3.c1 = b1t4.c1) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=100 width=xxx) + -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + Filter: (c1 <> $1) + -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = bmt3.c1) \o results/ut-R.tmpout /*+ @@ -3117,66 +3066,63 @@ SELECT b3t1.c1 FROM s1.t1 b3t1 LOG: pg_hint_plan: used hint: MergeJoin(b1t3 b1t4) -MergeJoin(bmt4 c1) HashJoin(b1t2 b1t3 b1t4) -HashJoin(bmt3 bmt4 c1) NestLoop(b1t1 b1t2 b1t3 b1t4) +Leading(b1t4 b1t3 b1t2 b1t1) +Rows(b1t3 b1t4 #1) +Rows(b1t2 b1t3 b1t4 #1) +Rows(b1t1 b1t2 b1t3 b1t4 #1) +not used hint: +MergeJoin(bmt4 c1) +HashJoin(bmt3 bmt4 c1) NestLoop(bmt2 bmt3 bmt4 c1) MergeJoin(bmt1 bmt2 bmt3 bmt4 c1) Leading(c1 bmt4 bmt3 bmt2 bmt1) -Leading(b1t4 b1t3 b1t2 b1t1) -Rows(b1t3 b1t4 #1) Rows(bmt4 c1 #1) -Rows(b1t2 b1t3 b1t4 #1) Rows(bmt3 bmt4 c1 #1) -Rows(b1t1 b1t2 b1t3 b1t4 #1) Rows(bmt2 bmt3 bmt4 c1 #1) Rows(bmt1 bmt2 bmt3 bmt4 c1 #1) -not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout - QUERY PLAN ------------------------------------------------------------------------------------------------------------------- - Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (bmt1.c1 = bmt2.c1) - CTE c1 - -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) - Join Filter: (b1t2.c1 = b1t1.c1) - -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (b1t3.c1 = b1t2.c1) - -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (b1t3.c1 = b1t4.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) - -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=100 width=xxx) - -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) - -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = b1t3.c1) - InitPlan 2 (returns $2) + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------ + Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt4.c1) + InitPlan 1 (returns $0) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c1 = 1) - InitPlan 3 (returns $3) + InitPlan 2 (returns $1) -> Seq Scan on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx) - -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx) - Filter: (c1 <> $3) - -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: bmt2.c1 - -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) - -> Hash Join (cost=xxx..xxx rows=1 width=xxx) - Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx) - -> Hash (cost=xxx..xxx rows=1 width=xxx) - -> Merge Join (cost=xxx..xxx rows=1 width=xxx) - Merge Cond: (bmt4.c1 = c1.c1) - -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx) - -> Sort (cost=xxx..xxx rows=1 width=xxx) - Sort Key: c1.c1 - -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx) - -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx) - Index Cond: (c1 = bmt3.c1) + -> Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt1.c1 = bmt3.c1) + -> Nested Loop (cost=xxx..xxx rows=10 width=xxx) + Join Filter: (bmt2.c1 = bmt1.c1) + -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx) + Join Filter: (b1t1.c1 = bmt2.c1) + -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx) + -> Materialize (cost=xxx..xxx rows=1 width=xxx) + -> Nested Loop (cost=xxx..xxx rows=1 width=xxx) + Join Filter: (b1t2.c1 = b1t1.c1) + -> Hash Join (cost=xxx..xxx rows=1 width=xxx) + Hash Cond: (b1t3.c1 = b1t2.c1) + -> Merge Join (cost=xxx..xxx rows=1 width=xxx) + Merge Cond: (b1t3.c1 = b1t4.c1) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx) + -> Hash (cost=xxx..xxx rows=100 width=xxx) + -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx) + -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + Filter: (c1 <> $1) + -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = b1t3.c1) + -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx) + Index Cond: (c1 = bmt3.c1) ---- ---- No. R-2-3 RULE or VIEW diff --git a/expected/ut-S.out b/expected/ut-S.out index e8dae59..00a30ce 100644 --- a/expected/ut-S.out +++ b/expected/ut-S.out @@ -540,20 +540,18 @@ error hint: -- No. S-1-5-9 EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = 1 AND t1.c1 = c1.c1; - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Nested Loop - CTE c1 - -> Result - InitPlan 1 (returns $0) - -> Limit - -> Index Only Scan using t1_i1 on t1 t1_1 - Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) - -> CTE Scan on c1 - Filter: (c1 = 1) -(11 rows) + -> Result + One-Time Filter: ($0 = 1) + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan using t1_i1 on t1 t1_1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) +(9 rows) /*+SeqScan(c1)*/ EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1) @@ -565,20 +563,18 @@ SeqScan(c1) duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Nested Loop - CTE c1 - -> Result - InitPlan 1 (returns $0) - -> Limit - -> Index Only Scan using t1_i1 on t1 t1_1 - Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) -> Index Scan using t1_i1 on t1 Index Cond: (c1 = 1) - -> CTE Scan on c1 - Filter: (c1 = 1) -(11 rows) + -> Result + One-Time Filter: ($0 = 1) + InitPlan 1 (returns $0) + -> Limit + -> Index Only Scan using t1_i1 on t1 t1_1 + Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) +(9 rows) -- No. S-1-5-10 EXPLAIN (COSTS false) SELECT * FROM s1.v1 WHERE v1.c1 = 1; @@ -1233,38 +1229,36 @@ SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1 ; - QUERY PLAN ------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - -> Merge Join - Merge Cond: (b1t1.c1 = b1t2.c1) - -> Merge Join - Merge Cond: (b1t1.c1 = b1t3.c1) - -> Index Only Scan using t1_i1 on t1 b1t1 - -> Index Only Scan using t3_i1 on t3 b1t3 - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - -> Index Only Scan using t4_i1 on t4 b1t4 - Index Cond: (c1 = b1t1.c1) -> Nested Loop -> Nested Loop -> Nested Loop -> Hash Join - Hash Cond: (bmt2.c1 = c1.c1) + Hash Cond: (bmt2.c1 = (max(b1t1.c1))) -> Seq Scan on t2 bmt2 -> Hash - -> CTE Scan on c1 + -> Aggregate + -> Nested Loop + -> Merge Join + Merge Cond: (b1t1.c1 = b1t2.c1) + -> Merge Join + Merge Cond: (b1t1.c1 = b1t3.c1) + -> Index Only Scan using t1_i1 on t1 b1t1 + -> Index Only Scan using t3_i1 on t3 b1t3 + -> Sort + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 + -> Index Only Scan using t4_i1 on t4 b1t4 + Index Cond: (c1 = b1t1.c1) -> Index Only Scan using t1_i1 on t1 bmt1 Index Cond: (c1 = bmt2.c1) -> Index Only Scan using t3_i1 on t3 bmt3 Index Cond: (c1 = bmt1.c1) -> Index Only Scan using t4_i1 on t4 bmt4 Index Cond: (c1 = bmt1.c1) -(29 rows) +(27 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) @@ -1296,43 +1290,45 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------ Aggregate - CTE c1 - -> Aggregate - -> Hash Join - Hash Cond: (b1t1.c1 = b1t2.c1) - -> Seq Scan on t1 b1t1 - -> Hash - -> Nested Loop - Join Filter: (b1t2.c1 = b1t3.c1) - -> Nested Loop - -> Seq Scan on t2 b1t2 - -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t2.c1) - -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t2.c1) - -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t4.c1) -> Hash Join Hash Cond: (bmt4.c1 = bmt1.c1) -> Seq Scan on t4 bmt4 -> Hash -> Nested Loop - -> Nested Loop - -> Hash Join - Hash Cond: (bmt1.c1 = c1.c1) - -> Seq Scan on t1 bmt1 - -> Hash - -> CTE Scan on c1 - -> Index Scan using t2_pkey on t2 bmt2 - Index Cond: (c1 = bmt1.c1) + -> Merge Join + Merge Cond: (bmt1.c1 = (max(b1t1.c1))) + -> Sort + Sort Key: bmt1.c1 + -> Hash Join + Hash Cond: (bmt1.c1 = bmt2.c1) + -> Seq Scan on t1 bmt1 + -> Hash + -> Index Scan using t2_pkey on t2 bmt2 + -> Sort + Sort Key: (max(b1t1.c1)) + -> Aggregate + -> Hash Join + Hash Cond: (b1t1.c1 = b1t2.c1) + -> Seq Scan on t1 b1t1 + -> Hash + -> Nested Loop + Join Filter: (b1t2.c1 = b1t3.c1) + -> Nested Loop + -> Seq Scan on t2 b1t2 + -> Bitmap Heap Scan on t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) + -> Index Scan using t3_pkey on t3 b1t3 + Index Cond: (c1 = b1t4.c1) -> Bitmap Heap Scan on t3 bmt3 Recheck Cond: (c1 = bmt1.c1) -> Bitmap Index Scan on t3_pkey Index Cond: (c1 = bmt1.c1) -(34 rows) +(36 rows) -- No. S-2-1-8 EXPLAIN (COSTS false) @@ -1348,55 +1344,51 @@ SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 AND bmt1.c1 = c1.c1 AND bmt1.c1 = c2.c1 ; - QUERY PLAN ------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - -> Merge Join - Merge Cond: (b1t1.c1 = b1t2.c1) - -> Merge Join - Merge Cond: (b1t1.c1 = b1t3.c1) - -> Index Only Scan using t1_i1 on t1 b1t1 - -> Index Only Scan using t3_i1 on t3 b1t3 - -> Sort - Sort Key: b1t2.c1 - -> Seq Scan on t2 b1t2 - -> Index Only Scan using t4_i1 on t4 b1t4 - Index Cond: (c1 = b1t1.c1) - CTE c2 - -> Aggregate - -> Nested Loop - -> Merge Join - Merge Cond: (b2t1.c1 = b2t2.c1) - -> Merge Join - Merge Cond: (b2t1.c1 = b2t3.c1) - -> Index Only Scan using t1_i1 on t1 b2t1 - -> Index Only Scan using t3_i1 on t3 b2t3 - -> Sort - Sort Key: b2t2.c1 - -> Seq Scan on t2 b2t2 - -> Index Only Scan using t4_i1 on t4 b2t4 - Index Cond: (c1 = b2t1.c1) -> Nested Loop - Join Filter: (bmt1.c1 = c2.c1) + Join Filter: (bmt1.c1 = (max(b2t1.c1))) -> Nested Loop -> Nested Loop -> Nested Loop -> Hash Join - Hash Cond: (bmt2.c1 = c1.c1) + Hash Cond: (bmt2.c1 = (max(b1t1.c1))) -> Seq Scan on t2 bmt2 -> Hash - -> CTE Scan on c1 + -> Aggregate + -> Nested Loop + -> Merge Join + Merge Cond: (b1t1.c1 = b1t2.c1) + -> Merge Join + Merge Cond: (b1t1.c1 = b1t3.c1) + -> Index Only Scan using t1_i1 on t1 b1t1 + -> Index Only Scan using t3_i1 on t3 b1t3 + -> Sort + Sort Key: b1t2.c1 + -> Seq Scan on t2 b1t2 + -> Index Only Scan using t4_i1 on t4 b1t4 + Index Cond: (c1 = b1t1.c1) -> Index Only Scan using t1_i1 on t1 bmt1 Index Cond: (c1 = bmt2.c1) -> Index Only Scan using t3_i1 on t3 bmt3 Index Cond: (c1 = bmt1.c1) -> Index Only Scan using t4_i1 on t4 bmt4 Index Cond: (c1 = bmt1.c1) - -> CTE Scan on c2 -(46 rows) + -> Aggregate + -> Nested Loop + -> Merge Join + Merge Cond: (b2t1.c1 = b2t2.c1) + -> Merge Join + Merge Cond: (b2t1.c1 = b2t3.c1) + -> Index Only Scan using t1_i1 on t1 b2t1 + -> Index Only Scan using t3_i1 on t3 b2t3 + -> Sort + Sort Key: b2t2.c1 + -> Seq Scan on t2 b2t2 + -> Index Only Scan using t4_i1 on t4 b2t4 + Index Cond: (c1 = b2t1.c1) +(42 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) @@ -1439,63 +1431,62 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Hash Join - Hash Cond: (b1t1.c1 = b1t2.c1) - -> Seq Scan on t1 b1t1 - -> Hash - -> Nested Loop - Join Filter: (b1t2.c1 = b1t3.c1) - -> Nested Loop - -> Seq Scan on t2 b1t2 - -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t2.c1) - -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t2.c1) - -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t4.c1) - CTE c2 - -> Aggregate - -> Hash Join - Hash Cond: (b2t3.c1 = b2t1.c1) - -> Seq Scan on t3 b2t3 - -> Hash - -> Merge Join - Merge Cond: (b2t1.c1 = b2t2.c1) - -> Nested Loop - -> Index Scan using t4_pkey on t4 b2t4 - -> Bitmap Heap Scan on t1 b2t1 - Recheck Cond: (c1 = b2t4.c1) - -> Bitmap Index Scan on t1_pkey - Index Cond: (c1 = b2t4.c1) - -> Sort - Sort Key: b2t2.c1 - -> Seq Scan on t2 b2t2 -> Hash Join Hash Cond: (bmt4.c1 = bmt1.c1) -> Seq Scan on t4 bmt4 -> Hash -> Nested Loop - Join Filter: (bmt1.c1 = c2.c1) -> Nested Loop -> Nested Loop - -> Hash Join - Hash Cond: (bmt1.c1 = c1.c1) - -> Seq Scan on t1 bmt1 - -> Hash - -> CTE Scan on c1 - -> Index Scan using t2_pkey on t2 bmt2 - Index Cond: (c1 = bmt1.c1) - -> Bitmap Heap Scan on t3 bmt3 - Recheck Cond: (c1 = bmt1.c1) - -> Bitmap Index Scan on t3_pkey - Index Cond: (c1 = bmt1.c1) - -> CTE Scan on c2 -(54 rows) + Join Filter: ((max(b1t1.c1)) = bmt1.c1) + -> Merge Join + Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1))) + -> Sort + Sort Key: (max(b1t1.c1)) + -> Aggregate + -> Hash Join + Hash Cond: (b1t1.c1 = b1t2.c1) + -> Seq Scan on t1 b1t1 + -> Hash + -> Nested Loop + Join Filter: (b1t2.c1 = b1t3.c1) + -> Nested Loop + -> Seq Scan on t2 b1t2 + -> Bitmap Heap Scan on t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) + -> Index Scan using t3_pkey on t3 b1t3 + Index Cond: (c1 = b1t4.c1) + -> Sort + Sort Key: (max(b2t1.c1)) + -> Aggregate + -> Hash Join + Hash Cond: (b2t3.c1 = b2t1.c1) + -> Seq Scan on t3 b2t3 + -> Hash + -> Merge Join + Merge Cond: (b2t1.c1 = b2t2.c1) + -> Nested Loop + -> Index Scan using t4_pkey on t4 b2t4 + -> Bitmap Heap Scan on t1 b2t1 + Recheck Cond: (c1 = b2t4.c1) + -> Bitmap Index Scan on t1_pkey + Index Cond: (c1 = b2t4.c1) + -> Sort + Sort Key: b2t2.c1 + -> Seq Scan on t2 b2t2 + -> Seq Scan on t1 bmt1 + -> Index Scan using t2_pkey on t2 bmt2 + Index Cond: (c1 = bmt1.c1) + -> Bitmap Heap Scan on t3 bmt3 + Recheck Cond: (c1 = bmt1.c1) + -> Bitmap Index Scan on t3_pkey + Index Cond: (c1 = bmt1.c1) +(53 rows) ---- ---- No. S-2-2 the number of the tables per quiry block @@ -1516,33 +1507,26 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1 QUERY PLAN ----------------------------------------------------------------- Aggregate - CTE c1 + InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Tid Scan on t1 b1t1 + -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) InitPlan 4 (returns $3) -> Result InitPlan 3 (returns $2) -> Limit - -> Tid Scan on t1 b2t1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 6 (returns $5) - -> Result - InitPlan 5 (returns $4) - -> Limit -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 <> $5) AND (c1 = 1)) - -> CTE Scan on c1 -(27 rows) + Filter: ((c1 <> $3) AND (c1 = 1)) + -> Result +(20 rows) /*+SeqScan(bmt1) TidScan(b1t1) @@ -1578,34 +1562,27 @@ error hint: QUERY PLAN --------------------------------------------------------------------------- Aggregate - CTE c1 + InitPlan 2 (returns $1) -> Result InitPlan 1 (returns $0) -> Limit - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 4 (returns $3) - -> Result - InitPlan 3 (returns $2) - -> Limit -> Bitmap Heap Scan on t1 b2t1 Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1)) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t1_pkey Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 6 (returns $5) + InitPlan 4 (returns $3) -> Result - InitPlan 5 (returns $4) + InitPlan 3 (returns $2) -> Limit -> Index Scan using t1_pkey on t1 b3t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) Filter: (ctid = '(1,1)'::tid) -> Nested Loop -> Seq Scan on t1 bmt1 - Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid) AND (c1 = 1)) - -> CTE Scan on c1 -(28 rows) + Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid) AND (c1 = 1)) + -> Result +(21 rows) -- No. S-2-2-2 EXPLAIN (COSTS false) @@ -1620,18 +1597,10 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t2.c1) @@ -1639,7 +1608,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3 TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on t2 b2t2 Filter: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t2.c1) @@ -1652,11 +1621,17 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3 Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) + Filter: (c1 <> $1) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(34 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) +(32 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey) TidScan(b1t1)SeqScan(b1t2) @@ -1696,15 +1671,7 @@ error hint: QUERY PLAN -------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $2) + InitPlan 1 (returns $1) -> Aggregate -> Nested Loop -> Tid Scan on t2 b2t2 @@ -1714,7 +1681,7 @@ error hint: Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t1_pkey Index Cond: (c1 = b2t2.c1) - InitPlan 3 (returns $4) + InitPlan 2 (returns $3) -> Aggregate -> Nested Loop -> Index Scan using t1_pkey on t1 b3t1 @@ -1728,11 +1695,17 @@ error hint: -> Nested Loop Join Filter: (bmt1.c1 = bmt2.c1) -> Seq Scan on t1 bmt1 - Filter: ((c1 <> $4) AND (ctid = '(1,1)'::tid)) + Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid)) -> Index Scan using t2_pkey on t2 bmt2 Filter: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(37 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) +(35 rows) -- No. S-2-2-3 EXPLAIN (COSTS false) @@ -1747,26 +1720,10 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t4.c1) @@ -1782,7 +1739,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 b2t4 TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t4.c1) @@ -1799,7 +1756,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 -> Tid Scan on t4 b3t4 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -1808,15 +1765,29 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $2) + Filter: (c1 <> $1) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(67 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) +(65 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) @@ -1835,12 +1806,12 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)' ) ; -LOG: available indexes for IndexScan(b1t3): t3_pkey -LOG: available indexes for BitmapScan(b1t4): t4_pkey LOG: available indexes for BitmapScan(b2t1): t1_pkey LOG: available indexes for IndexScan(b2t4): t4_pkey LOG: available indexes for IndexScan(b3t1): t1_pkey LOG: available indexes for BitmapScan(b3t2): t2_pkey +LOG: available indexes for IndexScan(b1t3): t3_pkey +LOG: available indexes for BitmapScan(b1t4): t4_pkey LOG: available indexes for IndexScan(bmt2): t2_pkey LOG: available indexes for BitmapScan(bmt3): t3_pkey LOG: pg_hint_plan: @@ -1868,27 +1839,7 @@ error hint: QUERY PLAN -------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t2.c1) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t2.c1) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t2.c1) - InitPlan 2 (returns $5) + InitPlan 1 (returns $2) -> Aggregate -> Nested Loop Join Filter: (b2t1.c1 = b2t4.c1) @@ -1908,7 +1859,7 @@ error hint: -> Index Scan using t4_pkey on t4 b2t4 Index Cond: (c1 = b2t2.c1) Filter: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $8) + InitPlan 2 (returns $5) -> Aggregate -> Nested Loop Join Filter: (b3t1.c1 = b3t2.c1) @@ -1929,7 +1880,7 @@ error hint: -> Bitmap Index Scan on t2_pkey Index Cond: (c1 = b3t3.c1) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt3.c1) -> Nested Loop @@ -1937,7 +1888,7 @@ error hint: -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Seq Scan on t1 bmt1 - Filter: ((c1 <> $8) AND (ctid = '(1,1)'::tid)) + Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid)) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) -> Index Scan using t2_pkey on t2 bmt2 @@ -1948,8 +1899,26 @@ error hint: Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t3_pkey Index Cond: (c1 = bmt2.c1) - -> CTE Scan on c1 -(82 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t3_pkey on t3 b1t3 + Index Cond: (c1 = b1t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) +(80 rows) -- No. S-2-2-4 EXPLAIN (COSTS false) @@ -1964,38 +1933,22 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' ) ; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on t3 b1t3 - TID Cond: (ctid = '(1,1)'::tid) - -> Tid Scan on t4 b1t4 - TID Cond: (ctid = '(1,1)'::tid) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Limit -> Tid Scan on t1 b2t1 TID Cond: (ctid = '(1,1)'::tid) Filter: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 4 (returns $4) + InitPlan 3 (returns $3) -> Aggregate -> Tid Scan on t1 b3t1 TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Nested Loop @@ -2004,15 +1957,29 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' Join Filter: (bmt1.c1 = bmt2.c1) -> Tid Scan on t1 bmt1 TID Cond: (ctid = '(1,1)'::tid) - Filter: (c1 <> $4) + Filter: (c1 <> $3) -> Seq Scan on t2 bmt2 Filter: (ctid = '(1,1)'::tid) -> Tid Scan on t3 bmt3 TID Cond: (ctid = '(1,1)'::tid) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) - -> CTE Scan on c1 -(46 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on t3 b1t3 + TID Cond: (ctid = '(1,1)'::tid) + -> Tid Scan on t4 b1t4 + TID Cond: (ctid = '(1,1)'::tid) +(44 rows) /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4) TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey) @@ -2031,12 +1998,12 @@ AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' ) ; -LOG: available indexes for IndexScan(b1t3): t3_pkey -LOG: available indexes for BitmapScan(b1t4): t4_pkey LOG: available indexes for BitmapScan(b2t1): t1_pkey LOG: available indexes for BitmapScan(b2t1): t1_pkey LOG: available indexes for IndexScan(b3t1): t1_pkey LOG: available indexes for IndexScan(b3t1): t1_pkey +LOG: available indexes for IndexScan(b1t3): t3_pkey +LOG: available indexes for BitmapScan(b1t4): t4_pkey LOG: available indexes for IndexScan(bmt2): t2_pkey LOG: available indexes for BitmapScan(bmt3): t3_pkey LOG: pg_hint_plan: @@ -2058,44 +2025,24 @@ error hint: QUERY PLAN -------------------------------------------------------------------------------- Aggregate - CTE c1 - -> Aggregate - -> Nested Loop - Join Filter: (b1t1.c1 = b1t4.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t3.c1) - -> Nested Loop - Join Filter: (b1t1.c1 = b1t2.c1) - -> Tid Scan on t1 b1t1 - TID Cond: (ctid = '(1,1)'::tid) - -> Seq Scan on t2 b1t2 - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using t3_pkey on t3 b1t3 - Index Cond: (c1 = b1t2.c1) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Heap Scan on t4 b1t4 - Recheck Cond: (c1 = b1t2.c1) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on t4_pkey - Index Cond: (c1 = b1t2.c1) - InitPlan 3 (returns $4) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $3) + InitPlan 1 (returns $0) -> Limit -> Bitmap Heap Scan on t1 b2t1 Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1)) Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t1_pkey Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 5 (returns $6) + InitPlan 4 (returns $3) -> Result - InitPlan 4 (returns $5) + InitPlan 3 (returns $2) -> Limit -> Index Scan Backward using t1_pkey on t1 b3t1 Index Cond: (c1 IS NOT NULL) Filter: (ctid = '(1,1)'::tid) -> Nested Loop - Join Filter: (bmt1.c1 = c1.c1) + Join Filter: (bmt1.c1 = (max(b1t1.c1))) -> Nested Loop Join Filter: (bmt1.c1 = bmt3.c1) -> Nested Loop @@ -2103,7 +2050,7 @@ error hint: -> Nested Loop Join Filter: (bmt1.c1 = bmt4.c1) -> Seq Scan on t1 bmt1 - Filter: ((c1 <> $6) AND (ctid = '(1,1)'::tid)) + Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid)) -> Tid Scan on t4 bmt4 TID Cond: (ctid = '(1,1)'::tid) -> Index Scan using t2_pkey on t2 bmt2 @@ -2114,8 +2061,26 @@ error hint: Filter: (ctid = '(1,1)'::tid) -> Bitmap Index Scan on t3_pkey Index Cond: (c1 = bmt2.c1) - -> CTE Scan on c1 -(58 rows) + -> Aggregate + -> Nested Loop + Join Filter: (b1t1.c1 = b1t4.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t3.c1) + -> Nested Loop + Join Filter: (b1t1.c1 = b1t2.c1) + -> Tid Scan on t1 b1t1 + TID Cond: (ctid = '(1,1)'::tid) + -> Seq Scan on t2 b1t2 + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using t3_pkey on t3 b1t3 + Index Cond: (c1 = b1t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Heap Scan on t4 b1t4 + Recheck Cond: (c1 = b1t2.c1) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on t4_pkey + Index Cond: (c1 = b1t2.c1) +(56 rows) ---- ---- No. S-2-3 RULE or VIEW @@ -3902,11 +3867,11 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_expr on ti1 Index Cond: ((c1 < 100) = true) - Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-6 @@ -4356,8 +4321,7 @@ error hint: ----------------------------------- Index Scan using ti1_expr on ti1 Index Cond: ((c1 < 100) = true) - Filter: (c1 < 100) -(3 rows) +(2 rows) -- No. S-3-4-28 EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1'; @@ -4523,8 +4487,8 @@ error hint: \o \! sql/maskout.sh results/ut-S.tmpout - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) @@ -4541,8 +4505,8 @@ error hint: \o \! sql/maskout.sh results/ut-S.tmpout - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) @@ -4559,8 +4523,8 @@ error hint: \o \! sql/maskout.sh results/ut-S.tmpout - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) @@ -4577,8 +4541,8 @@ error hint: \o \! sql/maskout.sh results/ut-S.tmpout - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) @@ -4595,8 +4559,8 @@ error hint: \o \! sql/maskout.sh results/ut-S.tmpout - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) @@ -4613,8 +4577,8 @@ error hint: \o \! sql/maskout.sh results/ut-S.tmpout - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 100) @@ -4637,8 +4601,8 @@ error hint: \o \! sql/maskout.sh results/ut-S.tmpout - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Seq Scan on t1 (cost={inf}..{inf} rows=1 width=xxx) Filter: (c1 = 1) @@ -4795,24 +4759,17 @@ SELECT max(b3t1.c1), ( SELECT max(b2t1.c1) FROM s1.t1 b2t1 JOIN s1.t2 b2t2 ON(b2t1.c1 = b2t2.c1) WHERE b2t1.c1 = 1 ) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = ( SELECT max(b4t1.c1) FROM s1.t1 b4t1 JOIN s1.t2 b4t2 ON(b4t1.c1 = b4t2.c1) WHERE b4t1.c1 = 1); - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------- Aggregate - CTE cte1 - -> Aggregate - -> Nested Loop - -> Index Only Scan using t1_i1 on t1 b1t1 - Index Cond: (c1 = 1) - -> Seq Scan on t2 b1t2 - Filter: (c1 = 1) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop -> Index Only Scan using t1_i1 on t1 b2t1 Index Cond: (c1 = 1) -> Seq Scan on t2 b2t2 Filter: (c1 = 1) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop -> Index Only Scan using t1_i1 on t1 b4t1 @@ -4822,12 +4779,17 @@ SELECT max(b4t1.c1) FROM s1.t1 b4t1 JOIN s1.t2 b4t2 ON(b4t1.c1 = b4t2.c1) WHERE -> Nested Loop -> Nested Loop -> Index Only Scan using t1_i1 on t1 b3t1 - Index Cond: (c1 = $2) + Index Cond: (c1 = $1) -> Seq Scan on t2 b3t2 - Filter: (c1 = $2) - -> CTE Scan on cte1 - Filter: (c1 = $2) -(30 rows) + Filter: (c1 = $1) + -> Aggregate + Filter: (max(b1t1.c1) = $1) + -> Nested Loop + -> Index Only Scan using t1_i1 on t1 b1t1 + Index Cond: (c1 = 1) + -> Seq Scan on t2 b1t2 + Filter: (c1 = 1) +(28 rows) /*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1) TidScan(b1t2)SeqScan(b2t2)IndexScan(b3t2 t2_pkey)BitmapScan(b4t2 t2_pkey) @@ -4860,21 +4822,14 @@ error hint: QUERY PLAN --------------------------------------------------------- Aggregate - CTE cte1 - -> Aggregate - -> Nested Loop - -> Seq Scan on t1 b1t1 - Filter: (c1 = 1) - -> Seq Scan on t2 b1t2 - Filter: (c1 = 1) - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Aggregate -> Nested Loop -> Index Scan using t1_pkey on t1 b2t1 Index Cond: (c1 = 1) -> Seq Scan on t2 b2t2 Filter: (c1 = 1) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Aggregate -> Nested Loop -> Seq Scan on t1 b4t1 @@ -4886,14 +4841,19 @@ error hint: -> Nested Loop -> Nested Loop -> Bitmap Heap Scan on t1 b3t1 - Recheck Cond: (c1 = $2) + Recheck Cond: (c1 = $1) -> Bitmap Index Scan on t1_pkey - Index Cond: (c1 = $2) + Index Cond: (c1 = $1) -> Index Scan using t2_pkey on t2 b3t2 - Index Cond: (c1 = $2) - -> CTE Scan on cte1 - Filter: (c1 = $2) -(34 rows) + Index Cond: (c1 = $1) + -> Aggregate + Filter: (max(b1t1.c1) = $1) + -> Nested Loop + -> Seq Scan on t1 b1t1 + Filter: (c1 = 1) + -> Seq Scan on t2 b1t2 + Filter: (c1 = 1) +(32 rows) -- No. S-3-7-3 EXPLAIN (COSTS false) @@ -4906,34 +4866,32 @@ SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1); QUERY PLAN --------------------------------------------------------------------- Aggregate - CTE cte1 - -> Aggregate - -> Nested Loop - -> Index Only Scan using t1_i1 on t1 b1t1 - Index Cond: (c1 = 1) - -> Seq Scan on t2 b1t2 - Filter: (c1 = 1) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Limit -> Index Only Scan using t1_i1 on t1 b2t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 5 (returns $4) + InitPlan 4 (returns $3) -> Result - InitPlan 4 (returns $3) + InitPlan 3 (returns $2) -> Limit -> Index Only Scan using t1_i1 on t1 b4t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Nested Loop -> Index Only Scan using t1_i1 on t1 b3t1 - Index Cond: (c1 = $4) + Index Cond: (c1 = $3) -> Seq Scan on t2 b3t2 - Filter: (c1 = $4) - -> CTE Scan on cte1 - Filter: (c1 = $4) -(28 rows) + Filter: (c1 = $3) + -> Aggregate + Filter: (max(b1t1.c1) = $3) + -> Nested Loop + -> Index Only Scan using t1_i1 on t1 b1t1 + Index Cond: (c1 = 1) + -> Seq Scan on t2 b1t2 + Filter: (c1 = 1) +(26 rows) /*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1) TidScan(b1t2)IndexScan(b3t2 t2_pkey) @@ -4964,36 +4922,34 @@ error hint: QUERY PLAN --------------------------------------------------------------------- Aggregate - CTE cte1 - -> Aggregate - -> Nested Loop - -> Seq Scan on t1 b1t1 - Filter: (c1 = 1) - -> Seq Scan on t2 b1t2 - Filter: (c1 = 1) - InitPlan 3 (returns $2) + InitPlan 2 (returns $1) -> Result - InitPlan 2 (returns $1) + InitPlan 1 (returns $0) -> Limit -> Index Scan using t1_pkey on t1 b2t1 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1)) - InitPlan 5 (returns $4) + InitPlan 4 (returns $3) -> Result - InitPlan 4 (returns $3) + InitPlan 3 (returns $2) -> Limit -> Seq Scan on t1 b4t1 Filter: ((c1 IS NOT NULL) AND (c1 = 1)) -> Nested Loop -> Nested Loop -> Bitmap Heap Scan on t1 b3t1 - Recheck Cond: (c1 = $4) + Recheck Cond: (c1 = $3) -> Bitmap Index Scan on t1_pkey - Index Cond: (c1 = $4) + Index Cond: (c1 = $3) -> Index Scan using t2_pkey on t2 b3t2 - Index Cond: (c1 = $4) - -> CTE Scan on cte1 - Filter: (c1 = $4) -(30 rows) + Index Cond: (c1 = $3) + -> Aggregate + Filter: (max(b1t1.c1) = $3) + -> Nested Loop + -> Seq Scan on t1 b1t1 + Filter: (c1 = 1) + -> Seq Scan on t2 b1t2 + Filter: (c1 = 1) +(28 rows) ---- ---- No. S-3-8 inheritance table select/update type @@ -5319,8 +5275,8 @@ error hint: \o \! sql/maskout.sh results/ut-S.tmpout - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------ Append (cost=xxx..xxx rows=4 width=xxx) -> Index Scan using p1_i2 on p1 (cost=xxx..xxx rows=1 width=xxx) Index Cond: (c2 = 1) diff --git a/output/ut-W.source b/expected/ut-W.out similarity index 89% rename from output/ut-W.source rename to expected/ut-W.out index 688c8b8..bf5126b 100644 --- a/output/ut-W.source +++ b/expected/ut-W.out @@ -677,33 +677,33 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------ - Gather - Workers Planned: 8 - -> Parallel Hash Join - Hash Cond: (p1.id = p2.id) - -> Append - -> Parallel Seq Scan on p1 - -> Parallel Seq Scan on p1_c1 - -> Parallel Seq Scan on p1_c2 - -> Parallel Seq Scan on p1_c3 - -> Parallel Seq Scan on p1_c4 - -> Parallel Seq Scan on p1_c1_c1 - -> Parallel Seq Scan on p1_c1_c2 - -> Parallel Seq Scan on p1_c3_c1 - -> Parallel Seq Scan on p1_c3_c2 - -> Parallel Hash + QUERY PLAN +-------------------------------------------------------------- + Hash Join + Hash Cond: (p2.id = p1.id) + -> Append + -> Index Scan using p2_id2_val on p2 + -> Index Scan using p2_c1_id2_val on p2_c1 + -> Index Scan using p2_c2_id2_val on p2_c2 + -> Index Scan using p2_c3_id_val_idx on p2_c3 + -> Index Scan using p2_c4_id_val_idx on p2_c4 + -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 + -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 + -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 + -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 + -> Hash + -> Gather + Workers Planned: 8 -> Append - -> Parallel Index Scan using p2_id2_val on p2 - -> Parallel Index Scan using p2_c1_id2_val on p2_c1 - -> Parallel Index Scan using p2_c2_id2_val on p2_c2 - -> Parallel Index Scan using p2_c3_id_val_idx on p2_c3 - -> Parallel Index Scan using p2_c4_id_val_idx on p2_c4 - -> Parallel Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 - -> Parallel Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 - -> Parallel Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 - -> Parallel Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 + -> Parallel Seq Scan on p1 + -> Parallel Seq Scan on p1_c1 + -> Parallel Seq Scan on p1_c2 + -> Parallel Seq Scan on p1_c3 + -> Parallel Seq Scan on p1_c4 + -> Parallel Seq Scan on p1_c1_c1 + -> Parallel Seq Scan on p1_c1_c2 + -> Parallel Seq Scan on p1_c3_c1 + -> Parallel Seq Scan on p1_c3_c2 (25 rows) SET enable_parallel_append to true; @@ -717,33 +717,33 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------ - Gather - Workers Planned: 8 - -> Parallel Hash Join - Hash Cond: (p1.id = p2_c2.id) - -> Parallel Append - -> Seq Scan on p1 - -> Seq Scan on p1_c1 - -> Seq Scan on p1_c3 - -> Parallel Seq Scan on p1_c2 - -> Parallel Seq Scan on p1_c4 - -> Parallel Seq Scan on p1_c1_c1 - -> Parallel Seq Scan on p1_c1_c2 - -> Parallel Seq Scan on p1_c3_c1 - -> Parallel Seq Scan on p1_c3_c2 - -> Parallel Hash + QUERY PLAN +-------------------------------------------------------------- + Hash Join + Hash Cond: (p2.id = p1.id) + -> Append + -> Index Scan using p2_id2_val on p2 + -> Index Scan using p2_c1_id2_val on p2_c1 + -> Index Scan using p2_c2_id2_val on p2_c2 + -> Index Scan using p2_c3_id_val_idx on p2_c3 + -> Index Scan using p2_c4_id_val_idx on p2_c4 + -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 + -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 + -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 + -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 + -> Hash + -> Gather + Workers Planned: 8 -> Parallel Append - -> Parallel Index Scan using p2_c2_id2_val on p2_c2 - -> Parallel Index Scan using p2_c4_id_val_idx on p2_c4 - -> Parallel Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 - -> Parallel Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 - -> Parallel Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 - -> Parallel Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 - -> Parallel Index Scan using p2_id2_val on p2 - -> Parallel Index Scan using p2_c1_id2_val on p2_c1 - -> Parallel Index Scan using p2_c3_id_val_idx on p2_c3 + -> Seq Scan on p1 + -> Seq Scan on p1_c1 + -> Seq Scan on p1_c3 + -> Parallel Seq Scan on p1_c2 + -> Parallel Seq Scan on p1_c4 + -> Parallel Seq Scan on p1_c1_c1 + -> Parallel Seq Scan on p1_c1_c2 + -> Parallel Seq Scan on p1_c3_c1 + -> Parallel Seq Scan on p1_c3_c2 (25 rows) -- Parallel sequential scan @@ -1246,34 +1246,32 @@ error hint: -> Sample Scan on p1_c1_c1 s1 Sampling: system ('10'::real) -> Foreign Scan on ft1 - Foreign File: @abs_srcdir@/data/data.csv - -> CTE Scan on cte1 - CTE cte1 - -> Gather - Workers Planned: 5 - -> Parallel Append - -> Seq Scan on p1 - Filter: ((id % 2) = 0) - -> Seq Scan on p1_c1 - Filter: ((id % 2) = 0) - -> Seq Scan on p1_c3 - Filter: ((id % 2) = 0) - -> Parallel Seq Scan on p1_c2 - Filter: ((id % 2) = 0) - -> Parallel Seq Scan on p1_c4 - Filter: ((id % 2) = 0) - -> Parallel Seq Scan on p1_c1_c1 - Filter: ((id % 2) = 0) - -> Parallel Seq Scan on p1_c1_c2 - Filter: ((id % 2) = 0) - -> Parallel Seq Scan on p1_c3_c1 - Filter: ((id % 2) = 0) - -> Parallel Seq Scan on p1_c3_c2 - Filter: ((id % 2) = 0) + Foreign File: /home/horiguti/work/pg_hint_plan/pg_hint_plan/data/data.csv + -> Gather + Workers Planned: 5 + -> Parallel Append + -> Seq Scan on p1 + Filter: ((id % 2) = 0) + -> Seq Scan on p1_c1 + Filter: ((id % 2) = 0) + -> Seq Scan on p1_c3 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c2 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c4 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c1_c1 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c1_c2 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c3_c1 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c3_c2 + Filter: ((id % 2) = 0) -> Function Scan on pg_stat_statements -> Subquery Scan on "*SELECT* 5" -> Values Scan on "*VALUES*" -(33 rows) +(31 rows) ALTER SYSTEM SET session_preload_libraries TO DEFAULT; SELECT pg_reload_conf(); diff --git a/pg_hint_plan.c b/pg_hint_plan.c index 794aac4..4e2f5e5 100644 --- a/pg_hint_plan.c +++ b/pg_hint_plan.c @@ -12,6 +12,7 @@ #include "postgres.h" #include "access/genam.h" #include "access/heapam.h" +#include "access/relation.h" #include "catalog/pg_collation.h" #include "catalog/pg_index.h" #include "commands/prepare.h" @@ -19,12 +20,12 @@ #include "miscadmin.h" #include "nodes/nodeFuncs.h" #include "nodes/params.h" -#include "nodes/relation.h" #include "optimizer/appendinfo.h" #include "optimizer/clauses.h" #include "optimizer/cost.h" #include "optimizer/geqo.h" #include "optimizer/joininfo.h" +#include "optimizer/optimizer.h" #include "optimizer/pathnode.h" #include "optimizer/paths.h" #include "optimizer/plancat.h" diff --git a/pg_stat_statements.c b/pg_stat_statements.c index 25bf905..b104dd6 100644 --- a/pg_stat_statements.c +++ b/pg_stat_statements.c @@ -48,8 +48,10 @@ AppendJumble(pgssJumbleState *jstate, const unsigned char *item, Size size) if (jumble_len >= JUMBLE_SIZE) { - uint32 start_hash = hash_any(jumble, JUMBLE_SIZE); + uint64 start_hash; + start_hash = DatumGetUInt64(hash_any_extended(jumble, + JUMBLE_SIZE, 0)); memcpy(jumble, &start_hash, sizeof(start_hash)); jumble_len = sizeof(start_hash); } @@ -152,6 +154,8 @@ JumbleRangeTable(pgssJumbleState *jstate, List *rtable) case RTE_NAMEDTUPLESTORE: APP_JUMB_STRING(rte->enrname); break; + case RTE_RESULT: + break; default: elog(ERROR, "unrecognized RTE kind: %d", (int) rte->rtekind); break; @@ -253,14 +257,14 @@ JumbleExpr(pgssJumbleState *jstate, Node *node) JumbleExpr(jstate, (Node *) expr->aggfilter); } break; - case T_ArrayRef: + case T_SubscriptingRef: { - ArrayRef *aref = (ArrayRef *) node; + SubscriptingRef *sbsref = (SubscriptingRef *) node; - JumbleExpr(jstate, (Node *) aref->refupperindexpr); - JumbleExpr(jstate, (Node *) aref->reflowerindexpr); - JumbleExpr(jstate, (Node *) aref->refexpr); - JumbleExpr(jstate, (Node *) aref->refassgnexpr); + JumbleExpr(jstate, (Node *) sbsref->refupperindexpr); + JumbleExpr(jstate, (Node *) sbsref->reflowerindexpr); + JumbleExpr(jstate, (Node *) sbsref->refexpr); + JumbleExpr(jstate, (Node *) sbsref->refassgnexpr); } break; case T_FuncExpr: @@ -354,6 +358,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node) APP_JUMB(acexpr->resulttype); JumbleExpr(jstate, (Node *) acexpr->arg); + JumbleExpr(jstate, (Node *) acexpr->elemexpr); } break; case T_ConvertRowtypeExpr: @@ -600,6 +605,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node) /* we store the string name because RTE_CTE RTEs need it */ APP_JUMB_STRING(cte->ctename); + APP_JUMB(cte->ctematerialized); JumbleQuery(jstate, castNode(Query, cte->ctequery)); } break; -- 2.11.0