• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revision8241ef0732b65ff8635eb5cc56860b2e9db40ab7 (tree)
Time2019-02-26 19:25:08
AuthorKyotaro Horiguchi <horiguchi.kyotaro@lab....>
CommiterKyotaro Horiguchi

Log Message

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.

Change Summary

Incremental Difference

--- a/core.c
+++ b/core.c
@@ -37,6 +37,8 @@
3737 * has_join_restriction()
3838 * is_dummy_rel()
3939 * restriction_is_constant_false()
40+ * update_child_rel_info()
41+ * build_child_join_sjinfo()
4042 *
4143 *
4244 * Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
@@ -1041,6 +1043,71 @@ restriction_is_constant_false(List *restrictlist,
10411043 return false;
10421044 }
10431045
1046+
1047+/*
1048+ * Set up tlist expressions for the childrel, and add EC members referencing
1049+ * the childrel.
1050+ */
1051+static void
1052+update_child_rel_info(PlannerInfo *root,
1053+ RelOptInfo *rel, RelOptInfo *childrel)
1054+{
1055+ AppendRelInfo *appinfo = root->append_rel_array[childrel->relid];
1056+
1057+ /* Make child tlist expressions */
1058+ childrel->reltarget->exprs = (List *)
1059+ adjust_appendrel_attrs(root,
1060+ (Node *) rel->reltarget->exprs,
1061+ 1, &appinfo);
1062+
1063+ /* Make child entries in the EquivalenceClass as well */
1064+ if (rel->has_eclass_joins || has_useful_pathkeys(root, rel))
1065+ add_child_rel_equivalences(root, appinfo, rel, childrel);
1066+ childrel->has_eclass_joins = rel->has_eclass_joins;
1067+}
1068+
1069+/*
1070+ * Construct the SpecialJoinInfo for a child-join by translating
1071+ * SpecialJoinInfo for the join between parents. left_relids and right_relids
1072+ * are the relids of left and right side of the join respectively.
1073+ */
1074+static SpecialJoinInfo *
1075+build_child_join_sjinfo(PlannerInfo *root, SpecialJoinInfo *parent_sjinfo,
1076+ Relids left_relids, Relids right_relids)
1077+{
1078+ SpecialJoinInfo *sjinfo = makeNode(SpecialJoinInfo);
1079+ AppendRelInfo **left_appinfos;
1080+ int left_nappinfos;
1081+ AppendRelInfo **right_appinfos;
1082+ int right_nappinfos;
1083+
1084+ memcpy(sjinfo, parent_sjinfo, sizeof(SpecialJoinInfo));
1085+ left_appinfos = find_appinfos_by_relids(root, left_relids,
1086+ &left_nappinfos);
1087+ right_appinfos = find_appinfos_by_relids(root, right_relids,
1088+ &right_nappinfos);
1089+
1090+ sjinfo->min_lefthand = adjust_child_relids(sjinfo->min_lefthand,
1091+ left_nappinfos, left_appinfos);
1092+ sjinfo->min_righthand = adjust_child_relids(sjinfo->min_righthand,
1093+ right_nappinfos,
1094+ right_appinfos);
1095+ sjinfo->syn_lefthand = adjust_child_relids(sjinfo->syn_lefthand,
1096+ left_nappinfos, left_appinfos);
1097+ sjinfo->syn_righthand = adjust_child_relids(sjinfo->syn_righthand,
1098+ right_nappinfos,
1099+ right_appinfos);
1100+ sjinfo->semi_rhs_exprs = (List *) adjust_appendrel_attrs(root,
1101+ (Node *) sjinfo->semi_rhs_exprs,
1102+ right_nappinfos,
1103+ right_appinfos);
1104+
1105+ pfree(left_appinfos);
1106+ pfree(right_appinfos);
1107+
1108+ return sjinfo;
1109+}
1110+
10441111 /*
10451112 * Assess whether join between given two partitioned relations can be broken
10461113 * down into joins between matching partitions; a technique called
@@ -1066,6 +1133,8 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
10661133 RelOptInfo *joinrel, SpecialJoinInfo *parent_sjinfo,
10671134 List *parent_restrictlist)
10681135 {
1136+ bool rel1_is_simple = IS_SIMPLE_REL(rel1);
1137+ bool rel2_is_simple = IS_SIMPLE_REL(rel2);
10691138 int nparts;
10701139 int cnt_parts;
10711140
@@ -1130,6 +1199,27 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
11301199 AppendRelInfo **appinfos;
11311200 int nappinfos;
11321201
1202+ /*
1203+ * If a child table has consider_partitionwise_join=false, it means
1204+ * that it's a dummy relation for which we skipped setting up tlist
1205+ * expressions and adding EC members in set_append_rel_size(), so do
1206+ * that now for use later.
1207+ */
1208+ if (rel1_is_simple && !child_rel1->consider_partitionwise_join)
1209+ {
1210+ Assert(child_rel1->reloptkind == RELOPT_OTHER_MEMBER_REL);
1211+ Assert(IS_DUMMY_REL(child_rel1));
1212+ update_child_rel_info(root, rel1, child_rel1);
1213+ child_rel1->consider_partitionwise_join = true;
1214+ }
1215+ if (rel2_is_simple && !child_rel2->consider_partitionwise_join)
1216+ {
1217+ Assert(child_rel2->reloptkind == RELOPT_OTHER_MEMBER_REL);
1218+ Assert(IS_DUMMY_REL(child_rel2));
1219+ update_child_rel_info(root, rel2, child_rel2);
1220+ child_rel2->consider_partitionwise_join = true;
1221+ }
1222+
11331223 /* We should never try to join two overlapping sets of rels. */
11341224 Assert(!bms_overlap(child_rel1->relids, child_rel2->relids));
11351225 child_joinrelids = bms_union(child_rel1->relids, child_rel2->relids);
--- 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
44764476 QUERY PLAN
44774477 ------------------------------------------------------------------------------
44784478 Nested Loop
4479- CTE c1_1
4480- -> Aggregate
4481- -> Merge Join
4482- Merge Cond: (t1_5.id = t3_5.id)
4483- -> Merge Join
4484- Merge Cond: (t1_5.id = t2_5.id)
4485- -> Index Only Scan using t1_pkey on t1 t1_5
4486- -> Index Only Scan using t2_pkey on t2 t2_5
4487- -> Sort
4488- Sort Key: t3_5.id
4489- -> Seq Scan on t3 t3_5
4490- InitPlan 2 (returns $1)
4479+ InitPlan 1 (returns $0)
44914480 -> Aggregate
44924481 -> Merge Join
44934482 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
44984487 -> Sort
44994488 Sort Key: t3_2.id
45004489 -> Seq Scan on t3 t3_2
4501- InitPlan 3 (returns $2)
4490+ InitPlan 2 (returns $1)
45024491 -> Aggregate
45034492 -> Merge Join
45044493 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
45154504 -> Nested Loop
45164505 -> Nested Loop
45174506 -> Index Only Scan using t1_pkey on t1 t1_1
4518- Index Cond: (id = $2)
4507+ Index Cond: (id = $1)
45194508 -> Index Only Scan using t2_pkey on t2 t2_1
4520- Index Cond: (id = $2)
4509+ Index Cond: (id = $1)
45214510 -> Seq Scan on t3 t3_1
4522- Filter: (id = $2)
4511+ Filter: (id = $1)
45234512 -> Index Only Scan using t1_pkey on t1 t1_3
4524- Index Cond: (id = $2)
4513+ Index Cond: (id = $1)
45254514 -> Index Only Scan using t2_pkey on t2 t2_3
4526- Index Cond: (id = $2)
4515+ Index Cond: (id = $1)
45274516 -> Seq Scan on t3 t3_3
4528- Filter: (id = $2)
4529- -> CTE Scan on c1_1
4530- Filter: (id = $2)
4531-(53 rows)
4517+ Filter: (id = $1)
4518+ -> Aggregate
4519+ Filter: (max(t1_5.id) = $1)
4520+ -> Merge Join
4521+ Merge Cond: (t1_5.id = t3_5.id)
4522+ -> Merge Join
4523+ Merge Cond: (t1_5.id = t2_5.id)
4524+ -> Index Only Scan using t1_pkey on t1 t1_5
4525+ -> Index Only Scan using t2_pkey on t2 t2_5
4526+ -> Sort
4527+ Sort Key: t3_5.id
4528+ -> Seq Scan on t3 t3_5
4529+(51 rows)
45324530
45334531 /*+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)*/
45344532 EXPLAIN (COSTS false)
@@ -4556,18 +4554,7 @@ error hint:
45564554 QUERY PLAN
45574555 ------------------------------------------------------------------------------
45584556 Nested Loop
4559- CTE c1_1
4560- -> Aggregate
4561- -> Merge Join
4562- Merge Cond: (t1_5.id = t3_5.id)
4563- -> Nested Loop
4564- -> Index Only Scan using t2_pkey on t2 t2_5
4565- -> Index Only Scan using t1_pkey on t1 t1_5
4566- Index Cond: (id = t2_5.id)
4567- -> Sort
4568- Sort Key: t3_5.id
4569- -> Seq Scan on t3 t3_5
4570- InitPlan 2 (returns $3)
4557+ InitPlan 1 (returns $1)
45714558 -> Aggregate
45724559 -> Merge Join
45734560 Merge Cond: (t1_2.id = t3_2.id)
@@ -4578,7 +4565,7 @@ error hint:
45784565 -> Sort
45794566 Sort Key: t3_2.id
45804567 -> Seq Scan on t3 t3_2
4581- InitPlan 3 (returns $5)
4568+ InitPlan 2 (returns $3)
45824569 -> Aggregate
45834570 -> Merge Join
45844571 Merge Cond: (t1_4.id = t3_4.id)
@@ -4595,20 +4582,29 @@ error hint:
45954582 -> Nested Loop
45964583 -> Nested Loop
45974584 -> Index Only Scan using t1_pkey on t1 t1_1
4598- Index Cond: (id = $5)
4585+ Index Cond: (id = $3)
45994586 -> Index Only Scan using t2_pkey on t2 t2_1
4600- Index Cond: (id = $5)
4587+ Index Cond: (id = $3)
46014588 -> Seq Scan on t3 t3_1
4602- Filter: (id = $5)
4589+ Filter: (id = $3)
46034590 -> Index Only Scan using t1_pkey on t1 t1_3
4604- Index Cond: (id = $5)
4591+ Index Cond: (id = $3)
46054592 -> Index Only Scan using t2_pkey on t2 t2_3
4606- Index Cond: (id = $5)
4593+ Index Cond: (id = $3)
46074594 -> Seq Scan on t3 t3_3
4608- Filter: (id = $5)
4609- -> CTE Scan on c1_1
4610- Filter: (id = $5)
4611-(53 rows)
4595+ Filter: (id = $3)
4596+ -> Aggregate
4597+ Filter: (max(t1_5.id) = $3)
4598+ -> Merge Join
4599+ Merge Cond: (t1_5.id = t3_5.id)
4600+ -> Nested Loop
4601+ -> Index Only Scan using t2_pkey on t2 t2_5
4602+ -> Index Only Scan using t1_pkey on t1 t1_5
4603+ Index Cond: (id = t2_5.id)
4604+ -> Sort
4605+ Sort Key: t3_5.id
4606+ -> Seq Scan on t3 t3_5
4607+(51 rows)
46124608
46134609 /*+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)*/
46144610 EXPLAIN (COSTS false)
@@ -4637,18 +4633,7 @@ error hint:
46374633 QUERY PLAN
46384634 ------------------------------------------------------------------------------
46394635 Nested Loop
4640- CTE c1_1
4641- -> Aggregate
4642- -> Merge Join
4643- Merge Cond: (t1_5.id = t3_5.id)
4644- -> Nested Loop
4645- -> Index Only Scan using t2_pkey on t2 t2_5
4646- -> Index Only Scan using t1_pkey on t1 t1_5
4647- Index Cond: (id = t2_5.id)
4648- -> Sort
4649- Sort Key: t3_5.id
4650- -> Seq Scan on t3 t3_5
4651- InitPlan 2 (returns $3)
4636+ InitPlan 1 (returns $1)
46524637 -> Aggregate
46534638 -> Merge Join
46544639 Merge Cond: (t1_2.id = t3_2.id)
@@ -4659,7 +4644,7 @@ error hint:
46594644 -> Sort
46604645 Sort Key: t3_2.id
46614646 -> Seq Scan on t3 t3_2
4662- InitPlan 3 (returns $5)
4647+ InitPlan 2 (returns $3)
46634648 -> Aggregate
46644649 -> Merge Join
46654650 Merge Cond: (t1_4.id = t3_4.id)
@@ -4676,20 +4661,29 @@ error hint:
46764661 -> Nested Loop
46774662 -> Nested Loop
46784663 -> Index Only Scan using t1_pkey on t1 t1_1
4679- Index Cond: (id = $5)
4664+ Index Cond: (id = $3)
46804665 -> Index Only Scan using t2_pkey on t2 t2_1
4681- Index Cond: (id = $5)
4666+ Index Cond: (id = $3)
46824667 -> Seq Scan on t3 t3_1
4683- Filter: (id = $5)
4668+ Filter: (id = $3)
46844669 -> Index Only Scan using t1_pkey on t1 t1_3
4685- Index Cond: (id = $5)
4670+ Index Cond: (id = $3)
46864671 -> Index Only Scan using t2_pkey on t2 t2_3
4687- Index Cond: (id = $5)
4672+ Index Cond: (id = $3)
46884673 -> Seq Scan on t3 t3_3
4689- Filter: (id = $5)
4690- -> CTE Scan on c1_1
4691- Filter: (id = $5)
4692-(53 rows)
4674+ Filter: (id = $3)
4675+ -> Aggregate
4676+ Filter: (max(t1_5.id) = $3)
4677+ -> Merge Join
4678+ Merge Cond: (t1_5.id = t3_5.id)
4679+ -> Nested Loop
4680+ -> Index Only Scan using t2_pkey on t2 t2_5
4681+ -> Index Only Scan using t1_pkey on t1 t1_5
4682+ Index Cond: (id = t2_5.id)
4683+ -> Sort
4684+ Sort Key: t3_5.id
4685+ -> Seq Scan on t3 t3_5
4686+(51 rows)
46934687
46944688 /*+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)*/
46954689 EXPLAIN (COSTS false)
@@ -4718,18 +4712,7 @@ error hint:
47184712 QUERY PLAN
47194713 ------------------------------------------------------------------------------
47204714 Nested Loop
4721- CTE c1_1
4722- -> Aggregate
4723- -> Merge Join
4724- Merge Cond: (t1_5.id = t3_5.id)
4725- -> Nested Loop
4726- -> Index Only Scan using t2_pkey on t2 t2_5
4727- -> Index Only Scan using t1_pkey on t1 t1_5
4728- Index Cond: (id = t2_5.id)
4729- -> Sort
4730- Sort Key: t3_5.id
4731- -> Seq Scan on t3 t3_5
4732- InitPlan 2 (returns $3)
4715+ InitPlan 1 (returns $1)
47334716 -> Aggregate
47344717 -> Merge Join
47354718 Merge Cond: (t1_2.id = t3_2.id)
@@ -4740,7 +4723,7 @@ error hint:
47404723 -> Sort
47414724 Sort Key: t3_2.id
47424725 -> Seq Scan on t3 t3_2
4743- InitPlan 3 (returns $5)
4726+ InitPlan 2 (returns $3)
47444727 -> Aggregate
47454728 -> Merge Join
47464729 Merge Cond: (t1_4.id = t3_4.id)
@@ -4757,20 +4740,29 @@ error hint:
47574740 -> Nested Loop
47584741 -> Nested Loop
47594742 -> Index Only Scan using t1_pkey on t1 t1_1
4760- Index Cond: (id = $5)
4743+ Index Cond: (id = $3)
47614744 -> Index Only Scan using t2_pkey on t2 t2_1
4762- Index Cond: (id = $5)
4745+ Index Cond: (id = $3)
47634746 -> Seq Scan on t3 t3_1
4764- Filter: (id = $5)
4747+ Filter: (id = $3)
47654748 -> Index Only Scan using t1_pkey on t1 t1_3
4766- Index Cond: (id = $5)
4749+ Index Cond: (id = $3)
47674750 -> Index Only Scan using t2_pkey on t2 t2_3
4768- Index Cond: (id = $5)
4751+ Index Cond: (id = $3)
47694752 -> Seq Scan on t3 t3_3
4770- Filter: (id = $5)
4771- -> CTE Scan on c1_1
4772- Filter: (id = $5)
4773-(53 rows)
4753+ Filter: (id = $3)
4754+ -> Aggregate
4755+ Filter: (max(t1_5.id) = $3)
4756+ -> Merge Join
4757+ Merge Cond: (t1_5.id = t3_5.id)
4758+ -> Nested Loop
4759+ -> Index Only Scan using t2_pkey on t2 t2_5
4760+ -> Index Only Scan using t1_pkey on t1 t1_5
4761+ Index Cond: (id = t2_5.id)
4762+ -> Sort
4763+ Sort Key: t3_5.id
4764+ -> Seq Scan on t3 t3_5
4765+(51 rows)
47744766
47754767 /*+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)*/
47764768 EXPLAIN (COSTS false)
@@ -4799,21 +4791,10 @@ NestLoop(t1_5 t2_5)
47994791 duplication hint:
48004792 error hint:
48014793
4802- QUERY PLAN
4803-------------------------------------------------------------------------
4794+ QUERY PLAN
4795+------------------------------------------------------------------------------------------------
48044796 Nested Loop
4805- CTE c1_1
4806- -> Aggregate
4807- -> Merge Join
4808- Merge Cond: (t2_5.id = t1_5.id)
4809- -> Merge Join
4810- Merge Cond: (t2_5.id = t3_5.id)
4811- -> Index Only Scan using t2_pkey on t2 t2_5
4812- -> Sort
4813- Sort Key: t3_5.id
4814- -> Seq Scan on t3 t3_5
4815- -> Index Only Scan using t1_pkey on t1 t1_5
4816- InitPlan 2 (returns $1)
4797+ InitPlan 1 (returns $0)
48174798 -> Aggregate
48184799 -> Merge Join
48194800 Merge Cond: (t2_2.id = t1_2.id)
@@ -4824,7 +4805,7 @@ error hint:
48244805 Sort Key: t3_2.id
48254806 -> Seq Scan on t3 t3_2
48264807 -> Index Only Scan using t1_pkey on t1 t1_2
4827- InitPlan 3 (returns $2)
4808+ InitPlan 2 (returns $1)
48284809 -> Aggregate
48294810 -> Merge Join
48304811 Merge Cond: (t2_4.id = t1_4.id)
@@ -4841,20 +4822,29 @@ error hint:
48414822 -> Nested Loop
48424823 -> Nested Loop
48434824 -> Seq Scan on t3 t3_3
4844- Filter: (id = $2)
4845- -> CTE Scan on c1_1
4846- Filter: (id = $2)
4825+ Filter: (id = $1)
4826+ -> Aggregate
4827+ Filter: (max(t1_5.id) = $1)
4828+ -> Merge Join
4829+ Merge Cond: (t2_5.id = t1_5.id)
4830+ -> Merge Join
4831+ Merge Cond: (t2_5.id = t3_5.id)
4832+ -> Index Only Scan using t2_pkey on t2 t2_5
4833+ -> Sort
4834+ Sort Key: t3_5.id
4835+ -> Seq Scan on t3 t3_5
4836+ -> Index Only Scan using t1_pkey on t1 t1_5
48474837 -> Index Only Scan using t2_pkey on t2 t2_3
4848- Index Cond: (id = $2)
4838+ Index Cond: (id = $1)
48494839 -> Index Only Scan using t1_pkey on t1 t1_3
4850- Index Cond: (id = $2)
4840+ Index Cond: (id = $1)
48514841 -> Seq Scan on t3 t3_1
4852- Filter: (id = $2)
4842+ Filter: (id = $1)
48534843 -> Index Only Scan using t2_pkey on t2 t2_1
4854- Index Cond: (id = $2)
4844+ Index Cond: (id = $1)
48554845 -> Index Only Scan using t1_pkey on t1 t1_1
4856- Index Cond: (id = $2)
4857-(53 rows)
4846+ Index Cond: (id = $1)
4847+(51 rows)
48584848
48594849 SET from_collapse_limit TO 1;
48604850 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
48714861 QUERY PLAN
48724862 --------------------------------------------------------------------
48734863 Nested Loop
4874- CTE c1_1
4875- -> Aggregate
4876- -> Merge Join
4877- Merge Cond: (t1_5.id = t3_5.id)
4878- -> Merge Join
4879- Merge Cond: (t1_5.id = t2_5.id)
4880- -> Index Only Scan using t1_pkey on t1 t1_5
4881- -> Index Only Scan using t2_pkey on t2 t2_5
4882- -> Sort
4883- Sort Key: t3_5.id
4884- -> Seq Scan on t3 t3_5
4885- InitPlan 2 (returns $1)
4864+ InitPlan 1 (returns $0)
48864865 -> Aggregate
48874866 -> Merge Join
48884867 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
48934872 -> Sort
48944873 Sort Key: t3_2.id
48954874 -> Seq Scan on t3 t3_2
4896- InitPlan 3 (returns $2)
4875+ InitPlan 2 (returns $1)
48974876 -> Aggregate
48984877 -> Merge Join
48994878 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
49084887 -> Nested Loop
49094888 -> Nested Loop
49104889 -> Index Only Scan using t1_pkey on t1 t1_1
4911- Index Cond: (id = $2)
4890+ Index Cond: (id = $1)
49124891 -> Index Only Scan using t2_pkey on t2 t2_1
4913- Index Cond: (id = $2)
4892+ Index Cond: (id = $1)
49144893 -> Seq Scan on t3 t3_1
4915- Filter: (id = $2)
4894+ Filter: (id = $1)
49164895 -> Nested Loop
49174896 -> Nested Loop
49184897 -> Index Only Scan using t1_pkey on t1 t1_3
4919- Index Cond: (id = $2)
4898+ Index Cond: (id = $1)
49204899 -> Index Only Scan using t2_pkey on t2 t2_3
4921- Index Cond: (id = $2)
4900+ Index Cond: (id = $1)
49224901 -> Seq Scan on t3 t3_3
4923- Filter: (id = $2)
4924- -> CTE Scan on c1_1
4925- Filter: (id = $2)
4926-(53 rows)
4902+ Filter: (id = $1)
4903+ -> Aggregate
4904+ Filter: (max(t1_5.id) = $1)
4905+ -> Merge Join
4906+ Merge Cond: (t1_5.id = t3_5.id)
4907+ -> Merge Join
4908+ Merge Cond: (t1_5.id = t2_5.id)
4909+ -> Index Only Scan using t1_pkey on t1 t1_5
4910+ -> Index Only Scan using t2_pkey on t2 t2_5
4911+ -> Sort
4912+ Sort Key: t3_5.id
4913+ -> Seq Scan on t3 t3_5
4914+(51 rows)
49274915
49284916 /*+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)*/
49294917 EXPLAIN (COSTS false)
@@ -4951,18 +4939,7 @@ error hint:
49514939 QUERY PLAN
49524940 --------------------------------------------------------------------
49534941 Nested Loop
4954- CTE c1_1
4955- -> Aggregate
4956- -> Merge Join
4957- Merge Cond: (t1_5.id = t3_5.id)
4958- -> Nested Loop
4959- -> Index Only Scan using t2_pkey on t2 t2_5
4960- -> Index Only Scan using t1_pkey on t1 t1_5
4961- Index Cond: (id = t2_5.id)
4962- -> Sort
4963- Sort Key: t3_5.id
4964- -> Seq Scan on t3 t3_5
4965- InitPlan 2 (returns $3)
4942+ InitPlan 1 (returns $1)
49664943 -> Aggregate
49674944 -> Merge Join
49684945 Merge Cond: (t1_2.id = t3_2.id)
@@ -4973,7 +4950,7 @@ error hint:
49734950 -> Sort
49744951 Sort Key: t3_2.id
49754952 -> Seq Scan on t3 t3_2
4976- InitPlan 3 (returns $5)
4953+ InitPlan 2 (returns $3)
49774954 -> Aggregate
49784955 -> Merge Join
49794956 Merge Cond: (t1_4.id = t3_4.id)
@@ -4988,22 +4965,31 @@ error hint:
49884965 -> Nested Loop
49894966 -> Nested Loop
49904967 -> Index Only Scan using t1_pkey on t1 t1_1
4991- Index Cond: (id = $5)
4968+ Index Cond: (id = $3)
49924969 -> Index Only Scan using t2_pkey on t2 t2_1
4993- Index Cond: (id = $5)
4970+ Index Cond: (id = $3)
49944971 -> Seq Scan on t3 t3_1
4995- Filter: (id = $5)
4972+ Filter: (id = $3)
49964973 -> Nested Loop
49974974 -> Nested Loop
49984975 -> Index Only Scan using t1_pkey on t1 t1_3
4999- Index Cond: (id = $5)
4976+ Index Cond: (id = $3)
50004977 -> Index Only Scan using t2_pkey on t2 t2_3
5001- Index Cond: (id = $5)
4978+ Index Cond: (id = $3)
50024979 -> Seq Scan on t3 t3_3
5003- Filter: (id = $5)
5004- -> CTE Scan on c1_1
5005- Filter: (id = $5)
5006-(53 rows)
4980+ Filter: (id = $3)
4981+ -> Aggregate
4982+ Filter: (max(t1_5.id) = $3)
4983+ -> Merge Join
4984+ Merge Cond: (t1_5.id = t3_5.id)
4985+ -> Nested Loop
4986+ -> Index Only Scan using t2_pkey on t2 t2_5
4987+ -> Index Only Scan using t1_pkey on t1 t1_5
4988+ Index Cond: (id = t2_5.id)
4989+ -> Sort
4990+ Sort Key: t3_5.id
4991+ -> Seq Scan on t3 t3_5
4992+(51 rows)
50074993
50084994 /*+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)*/
50094995 EXPLAIN (COSTS false)
@@ -5032,18 +5018,7 @@ error hint:
50325018 QUERY PLAN
50335019 --------------------------------------------------------------------
50345020 Nested Loop
5035- CTE c1_1
5036- -> Aggregate
5037- -> Merge Join
5038- Merge Cond: (t1_5.id = t3_5.id)
5039- -> Nested Loop
5040- -> Index Only Scan using t2_pkey on t2 t2_5
5041- -> Index Only Scan using t1_pkey on t1 t1_5
5042- Index Cond: (id = t2_5.id)
5043- -> Sort
5044- Sort Key: t3_5.id
5045- -> Seq Scan on t3 t3_5
5046- InitPlan 2 (returns $3)
5021+ InitPlan 1 (returns $1)
50475022 -> Aggregate
50485023 -> Merge Join
50495024 Merge Cond: (t1_2.id = t3_2.id)
@@ -5054,7 +5029,7 @@ error hint:
50545029 -> Sort
50555030 Sort Key: t3_2.id
50565031 -> Seq Scan on t3 t3_2
5057- InitPlan 3 (returns $5)
5032+ InitPlan 2 (returns $3)
50585033 -> Aggregate
50595034 -> Merge Join
50605035 Merge Cond: (t1_4.id = t3_4.id)
@@ -5069,22 +5044,31 @@ error hint:
50695044 -> Nested Loop
50705045 -> Nested Loop
50715046 -> Index Only Scan using t1_pkey on t1 t1_1
5072- Index Cond: (id = $5)
5047+ Index Cond: (id = $3)
50735048 -> Index Only Scan using t2_pkey on t2 t2_1
5074- Index Cond: (id = $5)
5049+ Index Cond: (id = $3)
50755050 -> Seq Scan on t3 t3_1
5076- Filter: (id = $5)
5051+ Filter: (id = $3)
50775052 -> Nested Loop
50785053 -> Nested Loop
50795054 -> Index Only Scan using t1_pkey on t1 t1_3
5080- Index Cond: (id = $5)
5055+ Index Cond: (id = $3)
50815056 -> Index Only Scan using t2_pkey on t2 t2_3
5082- Index Cond: (id = $5)
5057+ Index Cond: (id = $3)
50835058 -> Seq Scan on t3 t3_3
5084- Filter: (id = $5)
5085- -> CTE Scan on c1_1
5086- Filter: (id = $5)
5087-(53 rows)
5059+ Filter: (id = $3)
5060+ -> Aggregate
5061+ Filter: (max(t1_5.id) = $3)
5062+ -> Merge Join
5063+ Merge Cond: (t1_5.id = t3_5.id)
5064+ -> Nested Loop
5065+ -> Index Only Scan using t2_pkey on t2 t2_5
5066+ -> Index Only Scan using t1_pkey on t1 t1_5
5067+ Index Cond: (id = t2_5.id)
5068+ -> Sort
5069+ Sort Key: t3_5.id
5070+ -> Seq Scan on t3 t3_5
5071+(51 rows)
50885072
50895073 /*+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)*/
50905074 EXPLAIN (COSTS false)
@@ -5113,18 +5097,7 @@ error hint:
51135097 QUERY PLAN
51145098 --------------------------------------------------------------------
51155099 Nested Loop
5116- CTE c1_1
5117- -> Aggregate
5118- -> Merge Join
5119- Merge Cond: (t1_5.id = t3_5.id)
5120- -> Nested Loop
5121- -> Index Only Scan using t2_pkey on t2 t2_5
5122- -> Index Only Scan using t1_pkey on t1 t1_5
5123- Index Cond: (id = t2_5.id)
5124- -> Sort
5125- Sort Key: t3_5.id
5126- -> Seq Scan on t3 t3_5
5127- InitPlan 2 (returns $3)
5100+ InitPlan 1 (returns $1)
51285101 -> Aggregate
51295102 -> Merge Join
51305103 Merge Cond: (t1_2.id = t3_2.id)
@@ -5135,7 +5108,7 @@ error hint:
51355108 -> Sort
51365109 Sort Key: t3_2.id
51375110 -> Seq Scan on t3 t3_2
5138- InitPlan 3 (returns $5)
5111+ InitPlan 2 (returns $3)
51395112 -> Aggregate
51405113 -> Merge Join
51415114 Merge Cond: (t1_4.id = t3_4.id)
@@ -5150,22 +5123,31 @@ error hint:
51505123 -> Nested Loop
51515124 -> Nested Loop
51525125 -> Index Only Scan using t1_pkey on t1 t1_1
5153- Index Cond: (id = $5)
5126+ Index Cond: (id = $3)
51545127 -> Index Only Scan using t2_pkey on t2 t2_1
5155- Index Cond: (id = $5)
5128+ Index Cond: (id = $3)
51565129 -> Seq Scan on t3 t3_1
5157- Filter: (id = $5)
5130+ Filter: (id = $3)
51585131 -> Nested Loop
51595132 -> Nested Loop
51605133 -> Index Only Scan using t1_pkey on t1 t1_3
5161- Index Cond: (id = $5)
5134+ Index Cond: (id = $3)
51625135 -> Index Only Scan using t2_pkey on t2 t2_3
5163- Index Cond: (id = $5)
5136+ Index Cond: (id = $3)
51645137 -> Seq Scan on t3 t3_3
5165- Filter: (id = $5)
5166- -> CTE Scan on c1_1
5167- Filter: (id = $5)
5168-(53 rows)
5138+ Filter: (id = $3)
5139+ -> Aggregate
5140+ Filter: (max(t1_5.id) = $3)
5141+ -> Merge Join
5142+ Merge Cond: (t1_5.id = t3_5.id)
5143+ -> Nested Loop
5144+ -> Index Only Scan using t2_pkey on t2 t2_5
5145+ -> Index Only Scan using t1_pkey on t1 t1_5
5146+ Index Cond: (id = t2_5.id)
5147+ -> Sort
5148+ Sort Key: t3_5.id
5149+ -> Seq Scan on t3 t3_5
5150+(51 rows)
51695151
51705152 /*+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)*/
51715153 EXPLAIN (COSTS false)
@@ -5194,21 +5176,10 @@ NestLoop(t1_5 t2_5)
51945176 duplication hint:
51955177 error hint:
51965178
5197- QUERY PLAN
5198-------------------------------------------------------------------------------
5179+ QUERY PLAN
5180+------------------------------------------------------------------------------------
51995181 Nested Loop
5200- CTE c1_1
5201- -> Aggregate
5202- -> Merge Join
5203- Merge Cond: (t2_5.id = t1_5.id)
5204- -> Merge Join
5205- Merge Cond: (t2_5.id = t3_5.id)
5206- -> Index Only Scan using t2_pkey on t2 t2_5
5207- -> Sort
5208- Sort Key: t3_5.id
5209- -> Seq Scan on t3 t3_5
5210- -> Index Only Scan using t1_pkey on t1 t1_5
5211- InitPlan 2 (returns $1)
5182+ InitPlan 1 (returns $0)
52125183 -> Aggregate
52135184 -> Merge Join
52145185 Merge Cond: (t2_2.id = t1_2.id)
@@ -5219,7 +5190,7 @@ error hint:
52195190 Sort Key: t3_2.id
52205191 -> Seq Scan on t3 t3_2
52215192 -> Index Only Scan using t1_pkey on t1 t1_2
5222- InitPlan 3 (returns $2)
5193+ InitPlan 2 (returns $1)
52235194 -> Aggregate
52245195 -> Merge Join
52255196 Merge Cond: (t2_4.id = t1_4.id)
@@ -5236,20 +5207,29 @@ error hint:
52365207 -> Nested Loop
52375208 -> Nested Loop
52385209 -> Index Only Scan using t1_pkey on t1 t1_3
5239- Index Cond: (id = $2)
5210+ Index Cond: (id = $1)
52405211 -> Index Only Scan using t2_pkey on t2 t2_3
5241- Index Cond: (id = $2)
5212+ Index Cond: (id = $1)
52425213 -> Seq Scan on t3 t3_3
5243- Filter: (id = $2)
5244- -> CTE Scan on c1_1
5245- Filter: (id = $2)
5214+ Filter: (id = $1)
5215+ -> Aggregate
5216+ Filter: (max(t1_5.id) = $1)
5217+ -> Merge Join
5218+ Merge Cond: (t2_5.id = t1_5.id)
5219+ -> Merge Join
5220+ Merge Cond: (t2_5.id = t3_5.id)
5221+ -> Index Only Scan using t2_pkey on t2 t2_5
5222+ -> Sort
5223+ Sort Key: t3_5.id
5224+ -> Seq Scan on t3 t3_5
5225+ -> Index Only Scan using t1_pkey on t1 t1_5
52465226 -> Seq Scan on t3 t3_1
5247- Filter: (id = $2)
5227+ Filter: (id = $1)
52485228 -> Index Only Scan using t2_pkey on t2 t2_1
5249- Index Cond: (id = $2)
5229+ Index Cond: (id = $1)
52505230 -> Index Only Scan using t1_pkey on t1 t1_1
5251- Index Cond: (id = $2)
5252-(53 rows)
5231+ Index Cond: (id = $1)
5232+(51 rows)
52535233
52545234 -- ambigous error
52555235 EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
--- a/expected/ut-J.out
+++ b/expected/ut-J.out
@@ -676,21 +676,19 @@ error hint:
676676
677677 -- No. J-1-6-9
678678 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;
679- QUERY PLAN
680-------------------------------------------------------------
679+ QUERY PLAN
680+----------------------------------------------------------
681681 Nested Loop
682- CTE c1
683- -> Aggregate
684- -> Merge Join
685- Merge Cond: (t1_1.c1 = t2.c1)
686- -> Index Only Scan using t1_i1 on t1 t1_1
687- -> Sort
688- Sort Key: t2.c1
689- -> Seq Scan on t2
690- -> CTE Scan on c1
682+ -> Aggregate
683+ -> Merge Join
684+ Merge Cond: (t1_1.c1 = t2.c1)
685+ -> Index Only Scan using t1_i1 on t1 t1_1
686+ -> Sort
687+ Sort Key: t2.c1
688+ -> Seq Scan on t2
691689 -> Index Scan using t1_i1 on t1
692- Index Cond: (c1 = c1.c1)
693-(12 rows)
690+ Index Cond: (c1 = (max(t1_1.c1)))
691+(10 rows)
694692
695693 /*+NestLoop(t1 t2)HashJoin(t1 c1)*/
696694 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:
702700 duplication hint:
703701 error hint:
704702
705- QUERY PLAN
706-------------------------------------------------------------
703+ QUERY PLAN
704+----------------------------------------------------------------
707705 Hash Join
708- Hash Cond: (t1.c1 = c1.c1)
709- CTE c1
710- -> Aggregate
711- -> Nested Loop
712- -> Seq Scan on t2
713- -> Index Only Scan using t1_i1 on t1 t1_1
714- Index Cond: (c1 = t2.c1)
706+ Hash Cond: (t1.c1 = (max(t1_1.c1)))
715707 -> Seq Scan on t1
716708 -> Hash
717- -> CTE Scan on c1
718-(11 rows)
709+ -> Aggregate
710+ -> Nested Loop
711+ -> Seq Scan on t2
712+ -> Index Only Scan using t1_i1 on t1 t1_1
713+ Index Cond: (c1 = t2.c1)
714+(9 rows)
719715
720716 -- No. J-1-6-10
721717 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
17361732 AND bmt1.c1 = c1.c1
17371733 AND bmt1.c1 = c2.c1
17381734 ;
1739- QUERY PLAN
1740------------------------------------------------------------------------
1735+ QUERY PLAN
1736+-----------------------------------------------------------------------------
17411737 Aggregate
1742- CTE c1
1743- -> Aggregate
1744- -> Nested Loop
1745- Join Filter: (b1t1.c1 = b1t4.c1)
1746- -> Nested Loop
1747- Join Filter: (b1t1.c1 = b1t3.c1)
1748- -> Nested Loop
1749- Join Filter: (b1t1.c1 = b1t2.c1)
1750- -> Tid Scan on t1 b1t1
1751- TID Cond: (ctid = '(1,1)'::tid)
1752- -> Seq Scan on t2 b1t2
1753- Filter: (ctid = '(1,1)'::tid)
1754- -> Tid Scan on t3 b1t3
1755- TID Cond: (ctid = '(1,1)'::tid)
1756- -> Tid Scan on t4 b1t4
1757- TID Cond: (ctid = '(1,1)'::tid)
1758- CTE c2
1759- -> Aggregate
1760- -> Nested Loop
1761- Join Filter: (b2t1.c1 = b2t4.c1)
1762- -> Nested Loop
1763- Join Filter: (b2t1.c1 = b2t3.c1)
1764- -> Nested Loop
1765- Join Filter: (b2t1.c1 = b2t2.c1)
1766- -> Tid Scan on t1 b2t1
1767- TID Cond: (ctid = '(1,1)'::tid)
1768- -> Seq Scan on t2 b2t2
1769- Filter: (ctid = '(1,1)'::tid)
1770- -> Tid Scan on t3 b2t3
1771- TID Cond: (ctid = '(1,1)'::tid)
1772- -> Tid Scan on t4 b2t4
1773- TID Cond: (ctid = '(1,1)'::tid)
17741738 -> Nested Loop
1775- Join Filter: (bmt1.c1 = c2.c1)
1739+ Join Filter: (bmt1.c1 = (max(b2t1.c1)))
17761740 -> Nested Loop
1777- Join Filter: (bmt1.c1 = c1.c1)
1741+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
17781742 -> Nested Loop
17791743 Join Filter: (bmt1.c1 = bmt4.c1)
17801744 -> Nested Loop
@@ -1789,9 +1753,37 @@ AND bmt1.c1 = c2.c1
17891753 TID Cond: (ctid = '(1,1)'::tid)
17901754 -> Tid Scan on t4 bmt4
17911755 TID Cond: (ctid = '(1,1)'::tid)
1792- -> CTE Scan on c1
1793- -> CTE Scan on c2
1794-(53 rows)
1756+ -> Aggregate
1757+ -> Nested Loop
1758+ Join Filter: (b1t1.c1 = b1t4.c1)
1759+ -> Nested Loop
1760+ Join Filter: (b1t1.c1 = b1t3.c1)
1761+ -> Nested Loop
1762+ Join Filter: (b1t1.c1 = b1t2.c1)
1763+ -> Tid Scan on t1 b1t1
1764+ TID Cond: (ctid = '(1,1)'::tid)
1765+ -> Seq Scan on t2 b1t2
1766+ Filter: (ctid = '(1,1)'::tid)
1767+ -> Tid Scan on t3 b1t3
1768+ TID Cond: (ctid = '(1,1)'::tid)
1769+ -> Tid Scan on t4 b1t4
1770+ TID Cond: (ctid = '(1,1)'::tid)
1771+ -> Aggregate
1772+ -> Nested Loop
1773+ Join Filter: (b2t1.c1 = b2t4.c1)
1774+ -> Nested Loop
1775+ Join Filter: (b2t1.c1 = b2t3.c1)
1776+ -> Nested Loop
1777+ Join Filter: (b2t1.c1 = b2t2.c1)
1778+ -> Tid Scan on t1 b2t1
1779+ TID Cond: (ctid = '(1,1)'::tid)
1780+ -> Seq Scan on t2 b2t2
1781+ Filter: (ctid = '(1,1)'::tid)
1782+ -> Tid Scan on t3 b2t3
1783+ TID Cond: (ctid = '(1,1)'::tid)
1784+ -> Tid Scan on t4 b2t4
1785+ TID Cond: (ctid = '(1,1)'::tid)
1786+(49 rows)
17951787
17961788 /*+
17971789 Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
@@ -1834,51 +1826,9 @@ not used hint:
18341826 duplication hint:
18351827 error hint:
18361828
1837- QUERY PLAN
1838--------------------------------------------------------------------------------
1829+ QUERY PLAN
1830+-----------------------------------------------------------------------------------------------------------------------------------
18391831 Aggregate
1840- CTE c1
1841- -> Aggregate
1842- -> Nested Loop
1843- Join Filter: (b1t2.c1 = b1t1.c1)
1844- -> Hash Join
1845- Hash Cond: (b1t4.c1 = b1t2.c1)
1846- -> Tid Scan on t4 b1t4
1847- TID Cond: (ctid = '(1,1)'::tid)
1848- -> Hash
1849- -> Merge Join
1850- Merge Cond: (b1t2.c1 = b1t3.c1)
1851- -> Sort
1852- Sort Key: b1t2.c1
1853- -> Seq Scan on t2 b1t2
1854- Filter: (ctid = '(1,1)'::tid)
1855- -> Sort
1856- Sort Key: b1t3.c1
1857- -> Tid Scan on t3 b1t3
1858- TID Cond: (ctid = '(1,1)'::tid)
1859- -> Tid Scan on t1 b1t1
1860- TID Cond: (ctid = '(1,1)'::tid)
1861- CTE c2
1862- -> Aggregate
1863- -> Nested Loop
1864- Join Filter: (b2t1.c1 = b2t2.c1)
1865- -> Hash Join
1866- Hash Cond: (b2t1.c1 = b2t3.c1)
1867- -> Tid Scan on t1 b2t1
1868- TID Cond: (ctid = '(1,1)'::tid)
1869- -> Hash
1870- -> Merge Join
1871- Merge Cond: (b2t3.c1 = b2t4.c1)
1872- -> Sort
1873- Sort Key: b2t3.c1
1874- -> Tid Scan on t3 b2t3
1875- TID Cond: (ctid = '(1,1)'::tid)
1876- -> Sort
1877- Sort Key: b2t4.c1
1878- -> Tid Scan on t4 b2t4
1879- TID Cond: (ctid = '(1,1)'::tid)
1880- -> Seq Scan on t2 b2t2
1881- Filter: (ctid = '(1,1)'::tid)
18821832 -> Hash Join
18831833 Hash Cond: (bmt4.c1 = bmt1.c1)
18841834 -> Tid Scan on t4 bmt4
@@ -1891,25 +1841,63 @@ error hint:
18911841 -> Nested Loop
18921842 Join Filter: (bmt1.c1 = bmt2.c1)
18931843 -> Hash Join
1894- Hash Cond: (bmt1.c1 = c1.c1)
1844+ Hash Cond: (bmt1.c1 = (max(b1t1.c1)))
18951845 -> Tid Scan on t1 bmt1
18961846 TID Cond: (ctid = '(1,1)'::tid)
18971847 -> Hash
18981848 -> Merge Join
1899- Merge Cond: (c1.c1 = c2.c1)
1849+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
19001850 -> Sort
1901- Sort Key: c1.c1
1902- -> CTE Scan on c1
1851+ Sort Key: (max(b1t1.c1))
1852+ -> Aggregate
1853+ -> Nested Loop
1854+ Join Filter: (b1t2.c1 = b1t1.c1)
1855+ -> Hash Join
1856+ Hash Cond: (b1t4.c1 = b1t2.c1)
1857+ -> Tid Scan on t4 b1t4
1858+ TID Cond: (ctid = '(1,1)'::tid)
1859+ -> Hash
1860+ -> Merge Join
1861+ Merge Cond: (b1t2.c1 = b1t3.c1)
1862+ -> Sort
1863+ Sort Key: b1t2.c1
1864+ -> Seq Scan on t2 b1t2
1865+ Filter: (ctid = '(1,1)'::tid)
1866+ -> Sort
1867+ Sort Key: b1t3.c1
1868+ -> Tid Scan on t3 b1t3
1869+ TID Cond: (ctid = '(1,1)'::tid)
1870+ -> Tid Scan on t1 b1t1
1871+ TID Cond: (ctid = '(1,1)'::tid)
19031872 -> Sort
1904- Sort Key: c2.c1
1905- -> CTE Scan on c2
1873+ Sort Key: (max(b2t1.c1))
1874+ -> Aggregate
1875+ -> Nested Loop
1876+ Join Filter: (b2t1.c1 = b2t2.c1)
1877+ -> Hash Join
1878+ Hash Cond: (b2t1.c1 = b2t3.c1)
1879+ -> Tid Scan on t1 b2t1
1880+ TID Cond: (ctid = '(1,1)'::tid)
1881+ -> Hash
1882+ -> Merge Join
1883+ Merge Cond: (b2t3.c1 = b2t4.c1)
1884+ -> Sort
1885+ Sort Key: b2t3.c1
1886+ -> Tid Scan on t3 b2t3
1887+ TID Cond: (ctid = '(1,1)'::tid)
1888+ -> Sort
1889+ Sort Key: b2t4.c1
1890+ -> Tid Scan on t4 b2t4
1891+ TID Cond: (ctid = '(1,1)'::tid)
1892+ -> Seq Scan on t2 b2t2
1893+ Filter: (ctid = '(1,1)'::tid)
19061894 -> Seq Scan on t2 bmt2
19071895 Filter: (ctid = '(1,1)'::tid)
19081896 -> Sort
19091897 Sort Key: bmt3.c1
19101898 -> Tid Scan on t3 bmt3
19111899 TID Cond: (ctid = '(1,1)'::tid)
1912-(73 rows)
1900+(69 rows)
19131901
19141902 -- No. J-2-1-8
19151903 EXPLAIN (COSTS false)
@@ -1929,81 +1917,79 @@ AND bmt1.c1 = c1.c1
19291917 AND bmt1.c1 = c2.c1
19301918 AND bmt1.c1 = c3.c1
19311919 ;
1932- QUERY PLAN
1933------------------------------------------------------------------------------
1920+ QUERY PLAN
1921+-----------------------------------------------------------------------------------------------------------
19341922 Aggregate
1935- CTE c1
1936- -> Aggregate
1937- -> Nested Loop
1938- Join Filter: (b1t1.c1 = b1t4.c1)
1939- -> Nested Loop
1940- Join Filter: (b1t1.c1 = b1t3.c1)
1941- -> Nested Loop
1942- Join Filter: (b1t1.c1 = b1t2.c1)
1943- -> Tid Scan on t1 b1t1
1944- TID Cond: (ctid = '(1,1)'::tid)
1945- -> Seq Scan on t2 b1t2
1946- Filter: (ctid = '(1,1)'::tid)
1947- -> Tid Scan on t3 b1t3
1948- TID Cond: (ctid = '(1,1)'::tid)
1949- -> Tid Scan on t4 b1t4
1950- TID Cond: (ctid = '(1,1)'::tid)
1951- CTE c2
1952- -> Aggregate
1953- -> Nested Loop
1954- Join Filter: (b2t1.c1 = b2t4.c1)
1955- -> Nested Loop
1956- Join Filter: (b2t1.c1 = b2t3.c1)
1957- -> Nested Loop
1958- Join Filter: (b2t1.c1 = b2t2.c1)
1959- -> Tid Scan on t1 b2t1
1960- TID Cond: (ctid = '(1,1)'::tid)
1961- -> Seq Scan on t2 b2t2
1962- Filter: (ctid = '(1,1)'::tid)
1963- -> Tid Scan on t3 b2t3
1964- TID Cond: (ctid = '(1,1)'::tid)
1965- -> Tid Scan on t4 b2t4
1966- TID Cond: (ctid = '(1,1)'::tid)
1967- CTE c3
1968- -> Aggregate
1969- -> Nested Loop
1970- Join Filter: (b3t1.c1 = b3t4.c1)
1971- -> Nested Loop
1972- Join Filter: (b3t1.c1 = b3t3.c1)
1973- -> Nested Loop
1974- Join Filter: (b3t1.c1 = b3t2.c1)
1975- -> Tid Scan on t1 b3t1
1976- TID Cond: (ctid = '(1,1)'::tid)
1977- -> Seq Scan on t2 b3t2
1978- Filter: (ctid = '(1,1)'::tid)
1979- -> Tid Scan on t3 b3t3
1980- TID Cond: (ctid = '(1,1)'::tid)
1981- -> Tid Scan on t4 b3t4
1982- TID Cond: (ctid = '(1,1)'::tid)
19831923 -> Nested Loop
1984- Join Filter: (bmt1.c1 = c3.c1)
1924+ Join Filter: (bmt1.c1 = (max(b3t1.c1)))
19851925 -> Nested Loop
1986- Join Filter: (bmt1.c1 = c2.c1)
1926+ Join Filter: (bmt1.c1 = bmt4.c1)
19871927 -> Nested Loop
1988- Join Filter: (bmt1.c1 = c1.c1)
1928+ Join Filter: (bmt1.c1 = bmt3.c1)
19891929 -> Nested Loop
1990- Join Filter: (bmt1.c1 = bmt4.c1)
1930+ Join Filter: (bmt1.c1 = bmt2.c1)
19911931 -> Nested Loop
1992- Join Filter: (bmt1.c1 = bmt3.c1)
1993- -> Nested Loop
1994- Join Filter: (bmt1.c1 = bmt2.c1)
1995- -> Tid Scan on t1 bmt1
1996- TID Cond: (ctid = '(1,1)'::tid)
1997- -> Seq Scan on t2 bmt2
1998- Filter: (ctid = '(1,1)'::tid)
1999- -> Tid Scan on t3 bmt3
1932+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
1933+ -> Merge Join
1934+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
1935+ -> Sort
1936+ Sort Key: (max(b1t1.c1))
1937+ -> Aggregate
1938+ -> Nested Loop
1939+ Join Filter: (b1t1.c1 = b1t4.c1)
1940+ -> Nested Loop
1941+ Join Filter: (b1t1.c1 = b1t3.c1)
1942+ -> Nested Loop
1943+ Join Filter: (b1t1.c1 = b1t2.c1)
1944+ -> Tid Scan on t1 b1t1
1945+ TID Cond: (ctid = '(1,1)'::tid)
1946+ -> Seq Scan on t2 b1t2
1947+ Filter: (ctid = '(1,1)'::tid)
1948+ -> Tid Scan on t3 b1t3
1949+ TID Cond: (ctid = '(1,1)'::tid)
1950+ -> Tid Scan on t4 b1t4
1951+ TID Cond: (ctid = '(1,1)'::tid)
1952+ -> Sort
1953+ Sort Key: (max(b2t1.c1))
1954+ -> Aggregate
1955+ -> Nested Loop
1956+ Join Filter: (b2t1.c1 = b2t4.c1)
1957+ -> Nested Loop
1958+ Join Filter: (b2t1.c1 = b2t3.c1)
1959+ -> Nested Loop
1960+ Join Filter: (b2t1.c1 = b2t2.c1)
1961+ -> Tid Scan on t1 b2t1
1962+ TID Cond: (ctid = '(1,1)'::tid)
1963+ -> Seq Scan on t2 b2t2
1964+ Filter: (ctid = '(1,1)'::tid)
1965+ -> Tid Scan on t3 b2t3
1966+ TID Cond: (ctid = '(1,1)'::tid)
1967+ -> Tid Scan on t4 b2t4
1968+ TID Cond: (ctid = '(1,1)'::tid)
1969+ -> Tid Scan on t1 bmt1
20001970 TID Cond: (ctid = '(1,1)'::tid)
2001- -> Tid Scan on t4 bmt4
1971+ -> Seq Scan on t2 bmt2
1972+ Filter: (ctid = '(1,1)'::tid)
1973+ -> Tid Scan on t3 bmt3
1974+ TID Cond: (ctid = '(1,1)'::tid)
1975+ -> Tid Scan on t4 bmt4
1976+ TID Cond: (ctid = '(1,1)'::tid)
1977+ -> Aggregate
1978+ -> Nested Loop
1979+ Join Filter: (b3t1.c1 = b3t4.c1)
1980+ -> Nested Loop
1981+ Join Filter: (b3t1.c1 = b3t3.c1)
1982+ -> Nested Loop
1983+ Join Filter: (b3t1.c1 = b3t2.c1)
1984+ -> Tid Scan on t1 b3t1
1985+ TID Cond: (ctid = '(1,1)'::tid)
1986+ -> Seq Scan on t2 b3t2
1987+ Filter: (ctid = '(1,1)'::tid)
1988+ -> Tid Scan on t3 b3t3
20021989 TID Cond: (ctid = '(1,1)'::tid)
2003- -> CTE Scan on c1
2004- -> CTE Scan on c2
2005- -> CTE Scan on c3
2006-(72 rows)
1990+ -> Tid Scan on t4 b3t4
1991+ TID Cond: (ctid = '(1,1)'::tid)
1992+(70 rows)
20071993
20081994 /*+
20091995 Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
@@ -2057,72 +2043,9 @@ not used hint:
20572043 duplication hint:
20582044 error hint:
20592045
2060- QUERY PLAN
2061--------------------------------------------------------------------------------
2046+ QUERY PLAN
2047+-----------------------------------------------------------------------------------------------------------------------------------
20622048 Aggregate
2063- CTE c1
2064- -> Aggregate
2065- -> Nested Loop
2066- Join Filter: (b1t2.c1 = b1t1.c1)
2067- -> Hash Join
2068- Hash Cond: (b1t4.c1 = b1t2.c1)
2069- -> Tid Scan on t4 b1t4
2070- TID Cond: (ctid = '(1,1)'::tid)
2071- -> Hash
2072- -> Merge Join
2073- Merge Cond: (b1t2.c1 = b1t3.c1)
2074- -> Sort
2075- Sort Key: b1t2.c1
2076- -> Seq Scan on t2 b1t2
2077- Filter: (ctid = '(1,1)'::tid)
2078- -> Sort
2079- Sort Key: b1t3.c1
2080- -> Tid Scan on t3 b1t3
2081- TID Cond: (ctid = '(1,1)'::tid)
2082- -> Tid Scan on t1 b1t1
2083- TID Cond: (ctid = '(1,1)'::tid)
2084- CTE c2
2085- -> Aggregate
2086- -> Nested Loop
2087- Join Filter: (b2t1.c1 = b2t2.c1)
2088- -> Hash Join
2089- Hash Cond: (b2t1.c1 = b2t3.c1)
2090- -> Tid Scan on t1 b2t1
2091- TID Cond: (ctid = '(1,1)'::tid)
2092- -> Hash
2093- -> Merge Join
2094- Merge Cond: (b2t3.c1 = b2t4.c1)
2095- -> Sort
2096- Sort Key: b2t3.c1
2097- -> Tid Scan on t3 b2t3
2098- TID Cond: (ctid = '(1,1)'::tid)
2099- -> Sort
2100- Sort Key: b2t4.c1
2101- -> Tid Scan on t4 b2t4
2102- TID Cond: (ctid = '(1,1)'::tid)
2103- -> Seq Scan on t2 b2t2
2104- Filter: (ctid = '(1,1)'::tid)
2105- CTE c3
2106- -> Aggregate
2107- -> Nested Loop
2108- Join Filter: (b3t1.c1 = b3t3.c1)
2109- -> Hash Join
2110- Hash Cond: (b3t2.c1 = b3t1.c1)
2111- -> Seq Scan on t2 b3t2
2112- Filter: (ctid = '(1,1)'::tid)
2113- -> Hash
2114- -> Merge Join
2115- Merge Cond: (b3t1.c1 = b3t4.c1)
2116- -> Sort
2117- Sort Key: b3t1.c1
2118- -> Tid Scan on t1 b3t1
2119- TID Cond: (ctid = '(1,1)'::tid)
2120- -> Sort
2121- Sort Key: b3t4.c1
2122- -> Tid Scan on t4 b3t4
2123- TID Cond: (ctid = '(1,1)'::tid)
2124- -> Tid Scan on t3 b3t3
2125- TID Cond: (ctid = '(1,1)'::tid)
21262049 -> Nested Loop
21272050 Join Filter: (bmt1.c1 = bmt4.c1)
21282051 -> Hash Join
@@ -2135,19 +2058,76 @@ error hint:
21352058 -> Sort
21362059 Sort Key: bmt1.c1
21372060 -> Nested Loop
2138- Join Filter: (c1.c1 = bmt1.c1)
2061+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
21392062 -> Hash Join
2140- Hash Cond: (c2.c1 = c1.c1)
2063+ Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1)))
21412064 -> Merge Join
2142- Merge Cond: (c2.c1 = c3.c1)
2065+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
21432066 -> Sort
2144- Sort Key: c2.c1
2145- -> CTE Scan on c2
2067+ Sort Key: (max(b2t1.c1))
2068+ -> Aggregate
2069+ -> Nested Loop
2070+ Join Filter: (b2t1.c1 = b2t2.c1)
2071+ -> Hash Join
2072+ Hash Cond: (b2t1.c1 = b2t3.c1)
2073+ -> Tid Scan on t1 b2t1
2074+ TID Cond: (ctid = '(1,1)'::tid)
2075+ -> Hash
2076+ -> Merge Join
2077+ Merge Cond: (b2t3.c1 = b2t4.c1)
2078+ -> Sort
2079+ Sort Key: b2t3.c1
2080+ -> Tid Scan on t3 b2t3
2081+ TID Cond: (ctid = '(1,1)'::tid)
2082+ -> Sort
2083+ Sort Key: b2t4.c1
2084+ -> Tid Scan on t4 b2t4
2085+ TID Cond: (ctid = '(1,1)'::tid)
2086+ -> Seq Scan on t2 b2t2
2087+ Filter: (ctid = '(1,1)'::tid)
21462088 -> Sort
2147- Sort Key: c3.c1
2148- -> CTE Scan on c3
2089+ Sort Key: (max(b3t1.c1))
2090+ -> Aggregate
2091+ -> Nested Loop
2092+ Join Filter: (b3t1.c1 = b3t3.c1)
2093+ -> Hash Join
2094+ Hash Cond: (b3t2.c1 = b3t1.c1)
2095+ -> Seq Scan on t2 b3t2
2096+ Filter: (ctid = '(1,1)'::tid)
2097+ -> Hash
2098+ -> Merge Join
2099+ Merge Cond: (b3t1.c1 = b3t4.c1)
2100+ -> Sort
2101+ Sort Key: b3t1.c1
2102+ -> Tid Scan on t1 b3t1
2103+ TID Cond: (ctid = '(1,1)'::tid)
2104+ -> Sort
2105+ Sort Key: b3t4.c1
2106+ -> Tid Scan on t4 b3t4
2107+ TID Cond: (ctid = '(1,1)'::tid)
2108+ -> Tid Scan on t3 b3t3
2109+ TID Cond: (ctid = '(1,1)'::tid)
21492110 -> Hash
2150- -> CTE Scan on c1
2111+ -> Aggregate
2112+ -> Nested Loop
2113+ Join Filter: (b1t2.c1 = b1t1.c1)
2114+ -> Hash Join
2115+ Hash Cond: (b1t4.c1 = b1t2.c1)
2116+ -> Tid Scan on t4 b1t4
2117+ TID Cond: (ctid = '(1,1)'::tid)
2118+ -> Hash
2119+ -> Merge Join
2120+ Merge Cond: (b1t2.c1 = b1t3.c1)
2121+ -> Sort
2122+ Sort Key: b1t2.c1
2123+ -> Seq Scan on t2 b1t2
2124+ Filter: (ctid = '(1,1)'::tid)
2125+ -> Sort
2126+ Sort Key: b1t3.c1
2127+ -> Tid Scan on t3 b1t3
2128+ TID Cond: (ctid = '(1,1)'::tid)
2129+ -> Tid Scan on t1 b1t1
2130+ TID Cond: (ctid = '(1,1)'::tid)
21512131 -> Tid Scan on t1 bmt1
21522132 TID Cond: (ctid = '(1,1)'::tid)
21532133 -> Sort
@@ -2156,7 +2136,7 @@ error hint:
21562136 Filter: (ctid = '(1,1)'::tid)
21572137 -> Tid Scan on t4 bmt4
21582138 TID Cond: (ctid = '(1,1)'::tid)
2159-(97 rows)
2139+(91 rows)
21602140
21612141 ----
21622142 ---- No. J-2-2 the number of the tables per quiry block
@@ -2175,37 +2155,35 @@ AND bmt1.c1 <> (
21752155 SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1
21762156 )
21772157 ;
2178- QUERY PLAN
2179------------------------------------------------------------------
2158+ QUERY PLAN
2159+---------------------------------------------------------------------
21802160 Aggregate
2181- CTE c1
2161+ InitPlan 2 (returns $1)
21822162 -> Result
21832163 InitPlan 1 (returns $0)
21842164 -> Limit
2185- -> Tid Scan on t1 b1t1
2165+ -> Tid Scan on t1 b2t1
21862166 TID Cond: (ctid = '(1,1)'::tid)
21872167 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
21882168 InitPlan 4 (returns $3)
21892169 -> Result
21902170 InitPlan 3 (returns $2)
21912171 -> Limit
2192- -> Tid Scan on t1 b2t1
2193- TID Cond: (ctid = '(1,1)'::tid)
2194- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
2195- InitPlan 6 (returns $5)
2196- -> Result
2197- InitPlan 5 (returns $4)
2198- -> Limit
21992172 -> Tid Scan on t1 b3t1
22002173 TID Cond: (ctid = '(1,1)'::tid)
22012174 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
22022175 -> Nested Loop
22032176 -> Tid Scan on t1 bmt1
22042177 TID Cond: (ctid = '(1,1)'::tid)
2205- Filter: ((c1 <> $5) AND (c1 = 1))
2206- -> CTE Scan on c1
2207- Filter: (c1 = 1)
2208-(28 rows)
2178+ Filter: ((c1 <> $3) AND (c1 = 1))
2179+ -> Result
2180+ One-Time Filter: ($4 = 1)
2181+ InitPlan 5 (returns $4)
2182+ -> Limit
2183+ -> Tid Scan on t1 b1t1
2184+ TID Cond: (ctid = '(1,1)'::tid)
2185+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
2186+(26 rows)
22092187
22102188 /*+
22112189 Leading(c1 bmt1)
@@ -2238,37 +2216,35 @@ HashJoin(b3t1 c1)
22382216 duplication hint:
22392217 error hint:
22402218
2241- QUERY PLAN
2242------------------------------------------------------------------
2219+ QUERY PLAN
2220+---------------------------------------------------------------------
22432221 Aggregate
2244- CTE c1
2222+ InitPlan 2 (returns $1)
22452223 -> Result
22462224 InitPlan 1 (returns $0)
22472225 -> Limit
2248- -> Tid Scan on t1 b1t1
2226+ -> Tid Scan on t1 b2t1
22492227 TID Cond: (ctid = '(1,1)'::tid)
22502228 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
22512229 InitPlan 4 (returns $3)
22522230 -> Result
22532231 InitPlan 3 (returns $2)
22542232 -> Limit
2255- -> Tid Scan on t1 b2t1
2256- TID Cond: (ctid = '(1,1)'::tid)
2257- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
2258- InitPlan 6 (returns $5)
2259- -> Result
2260- InitPlan 5 (returns $4)
2261- -> Limit
22622233 -> Tid Scan on t1 b3t1
22632234 TID Cond: (ctid = '(1,1)'::tid)
22642235 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
22652236 -> Nested Loop
22662237 -> Tid Scan on t1 bmt1
22672238 TID Cond: (ctid = '(1,1)'::tid)
2268- Filter: ((c1 <> $5) AND (c1 = 1))
2269- -> CTE Scan on c1
2270- Filter: (c1 = 1)
2271-(28 rows)
2239+ Filter: ((c1 <> $3) AND (c1 = 1))
2240+ -> Result
2241+ One-Time Filter: ($4 = 1)
2242+ InitPlan 5 (returns $4)
2243+ -> Limit
2244+ -> Tid Scan on t1 b1t1
2245+ TID Cond: (ctid = '(1,1)'::tid)
2246+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
2247+(26 rows)
22722248
22732249 -- No. J-2-2-2
22742250 EXPLAIN (COSTS false)
@@ -2284,18 +2260,10 @@ AND bmt1.c1 <> (
22842260 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)'
22852261 )
22862262 ;
2287- QUERY PLAN
2288--------------------------------------------------------
2263+ QUERY PLAN
2264+-----------------------------------------------------------
22892265 Aggregate
2290- CTE c1
2291- -> Aggregate
2292- -> Nested Loop
2293- Join Filter: (b1t1.c1 = b1t2.c1)
2294- -> Tid Scan on t1 b1t1
2295- TID Cond: (ctid = '(1,1)'::tid)
2296- -> Seq Scan on t2 b1t2
2297- Filter: (ctid = '(1,1)'::tid)
2298- InitPlan 2 (returns $1)
2266+ InitPlan 1 (returns $0)
22992267 -> Aggregate
23002268 -> Nested Loop
23012269 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
23032271 TID Cond: (ctid = '(1,1)'::tid)
23042272 -> Seq Scan on t2 b2t2
23052273 Filter: (ctid = '(1,1)'::tid)
2306- InitPlan 3 (returns $2)
2274+ InitPlan 2 (returns $1)
23072275 -> Aggregate
23082276 -> Nested Loop
23092277 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
23122280 -> Seq Scan on t2 b3t2
23132281 Filter: (ctid = '(1,1)'::tid)
23142282 -> Nested Loop
2315- Join Filter: (bmt1.c1 = c1.c1)
2283+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
23162284 -> Nested Loop
23172285 Join Filter: (bmt1.c1 = bmt2.c1)
23182286 -> Tid Scan on t1 bmt1
23192287 TID Cond: (ctid = '(1,1)'::tid)
2320- Filter: (c1 <> $2)
2288+ Filter: (c1 <> $1)
23212289 -> Seq Scan on t2 bmt2
23222290 Filter: (ctid = '(1,1)'::tid)
2323- -> CTE Scan on c1
2324-(35 rows)
2291+ -> Aggregate
2292+ -> Nested Loop
2293+ Join Filter: (b1t1.c1 = b1t2.c1)
2294+ -> Tid Scan on t1 b1t1
2295+ TID Cond: (ctid = '(1,1)'::tid)
2296+ -> Seq Scan on t2 b1t2
2297+ Filter: (ctid = '(1,1)'::tid)
2298+(33 rows)
23252299
23262300 /*+
23272301 Leading(c1 bmt2 bmt1)
@@ -2362,22 +2336,10 @@ not used hint:
23622336 duplication hint:
23632337 error hint:
23642338
2365- QUERY PLAN
2366----------------------------------------------------------------
2339+ QUERY PLAN
2340+-----------------------------------------------------------------------------------
23672341 Aggregate
2368- CTE c1
2369- -> Aggregate
2370- -> Merge Join
2371- Merge Cond: (b1t1.c1 = b1t2.c1)
2372- -> Sort
2373- Sort Key: b1t1.c1
2374- -> Tid Scan on t1 b1t1
2375- TID Cond: (ctid = '(1,1)'::tid)
2376- -> Sort
2377- Sort Key: b1t2.c1
2378- -> Seq Scan on t2 b1t2
2379- Filter: (ctid = '(1,1)'::tid)
2380- InitPlan 2 (returns $1)
2342+ InitPlan 1 (returns $0)
23812343 -> Aggregate
23822344 -> Merge Join
23832345 Merge Cond: (b2t1.c1 = b2t2.c1)
@@ -2389,7 +2351,7 @@ error hint:
23892351 Sort Key: b2t2.c1
23902352 -> Seq Scan on t2 b2t2
23912353 Filter: (ctid = '(1,1)'::tid)
2392- InitPlan 3 (returns $2)
2354+ InitPlan 2 (returns $1)
23932355 -> Aggregate
23942356 -> Merge Join
23952357 Merge Cond: (b3t1.c1 = b3t2.c1)
@@ -2405,18 +2367,28 @@ error hint:
24052367 Hash Cond: (bmt1.c1 = bmt2.c1)
24062368 -> Tid Scan on t1 bmt1
24072369 TID Cond: (ctid = '(1,1)'::tid)
2408- Filter: (c1 <> $2)
2370+ Filter: (c1 <> $1)
24092371 -> Hash
24102372 -> Merge Join
2411- Merge Cond: (bmt2.c1 = c1.c1)
2373+ Merge Cond: (bmt2.c1 = (max(b1t1.c1)))
24122374 -> Sort
24132375 Sort Key: bmt2.c1
24142376 -> Seq Scan on t2 bmt2
24152377 Filter: (ctid = '(1,1)'::tid)
24162378 -> Sort
2417- Sort Key: c1.c1
2418- -> CTE Scan on c1
2419-(52 rows)
2379+ Sort Key: (max(b1t1.c1))
2380+ -> Aggregate
2381+ -> Merge Join
2382+ Merge Cond: (b1t1.c1 = b1t2.c1)
2383+ -> Sort
2384+ Sort Key: b1t1.c1
2385+ -> Tid Scan on t1 b1t1
2386+ TID Cond: (ctid = '(1,1)'::tid)
2387+ -> Sort
2388+ Sort Key: b1t2.c1
2389+ -> Seq Scan on t2 b1t2
2390+ Filter: (ctid = '(1,1)'::tid)
2391+(50 rows)
24202392
24212393 -- No. J-2-2-3
24222394 EXPLAIN (COSTS false)
@@ -2431,26 +2403,10 @@ AND bmt1.c1 <> (
24312403 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)'
24322404 )
24332405 ;
2434- QUERY PLAN
2435--------------------------------------------------------------------
2406+ QUERY PLAN
2407+-----------------------------------------------------------------------
24362408 Aggregate
2437- CTE c1
2438- -> Aggregate
2439- -> Nested Loop
2440- Join Filter: (b1t1.c1 = b1t4.c1)
2441- -> Nested Loop
2442- Join Filter: (b1t1.c1 = b1t3.c1)
2443- -> Nested Loop
2444- Join Filter: (b1t1.c1 = b1t2.c1)
2445- -> Tid Scan on t1 b1t1
2446- TID Cond: (ctid = '(1,1)'::tid)
2447- -> Seq Scan on t2 b1t2
2448- Filter: (ctid = '(1,1)'::tid)
2449- -> Tid Scan on t3 b1t3
2450- TID Cond: (ctid = '(1,1)'::tid)
2451- -> Tid Scan on t4 b1t4
2452- TID Cond: (ctid = '(1,1)'::tid)
2453- InitPlan 2 (returns $1)
2409+ InitPlan 1 (returns $0)
24542410 -> Aggregate
24552411 -> Nested Loop
24562412 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
24662422 TID Cond: (ctid = '(1,1)'::tid)
24672423 -> Tid Scan on t4 b2t4
24682424 TID Cond: (ctid = '(1,1)'::tid)
2469- InitPlan 3 (returns $2)
2425+ InitPlan 2 (returns $1)
24702426 -> Aggregate
24712427 -> Nested Loop
24722428 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
24832439 -> Tid Scan on t4 b3t4
24842440 TID Cond: (ctid = '(1,1)'::tid)
24852441 -> Nested Loop
2486- Join Filter: (bmt1.c1 = c1.c1)
2442+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
24872443 -> Nested Loop
24882444 Join Filter: (bmt1.c1 = bmt4.c1)
24892445 -> 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
24922448 Join Filter: (bmt1.c1 = bmt2.c1)
24932449 -> Tid Scan on t1 bmt1
24942450 TID Cond: (ctid = '(1,1)'::tid)
2495- Filter: (c1 <> $2)
2451+ Filter: (c1 <> $1)
24962452 -> Seq Scan on t2 bmt2
24972453 Filter: (ctid = '(1,1)'::tid)
24982454 -> Tid Scan on t3 bmt3
24992455 TID Cond: (ctid = '(1,1)'::tid)
25002456 -> Tid Scan on t4 bmt4
25012457 TID Cond: (ctid = '(1,1)'::tid)
2502- -> CTE Scan on c1
2503-(67 rows)
2458+ -> Aggregate
2459+ -> Nested Loop
2460+ Join Filter: (b1t1.c1 = b1t4.c1)
2461+ -> Nested Loop
2462+ Join Filter: (b1t1.c1 = b1t3.c1)
2463+ -> Nested Loop
2464+ Join Filter: (b1t1.c1 = b1t2.c1)
2465+ -> Tid Scan on t1 b1t1
2466+ TID Cond: (ctid = '(1,1)'::tid)
2467+ -> Seq Scan on t2 b1t2
2468+ Filter: (ctid = '(1,1)'::tid)
2469+ -> Tid Scan on t3 b1t3
2470+ TID Cond: (ctid = '(1,1)'::tid)
2471+ -> Tid Scan on t4 b1t4
2472+ TID Cond: (ctid = '(1,1)'::tid)
2473+(65 rows)
25042474
25052475 /*+
25062476 Leading(c1 bmt4 bmt3 bmt2 bmt1)
@@ -2556,31 +2526,10 @@ not used hint:
25562526 duplication hint:
25572527 error hint:
25582528
2559- QUERY PLAN
2560------------------------------------------------------------------------------------
2529+ QUERY PLAN
2530+-----------------------------------------------------------------------------------------------------------------------
25612531 Aggregate
2562- CTE c1
2563- -> Aggregate
2564- -> Merge Join
2565- Merge Cond: (b1t2.c1 = b1t1.c1)
2566- -> Sort
2567- Sort Key: b1t2.c1
2568- -> Nested Loop
2569- Join Filter: (b1t3.c1 = b1t2.c1)
2570- -> Hash Join
2571- Hash Cond: (b1t3.c1 = b1t4.c1)
2572- -> Tid Scan on t3 b1t3
2573- TID Cond: (ctid = '(1,1)'::tid)
2574- -> Hash
2575- -> Tid Scan on t4 b1t4
2576- TID Cond: (ctid = '(1,1)'::tid)
2577- -> Seq Scan on t2 b1t2
2578- Filter: (ctid = '(1,1)'::tid)
2579- -> Sort
2580- Sort Key: b1t1.c1
2581- -> Tid Scan on t1 b1t1
2582- TID Cond: (ctid = '(1,1)'::tid)
2583- InitPlan 2 (returns $1)
2532+ InitPlan 1 (returns $0)
25842533 -> Aggregate
25852534 -> Merge Join
25862535 Merge Cond: (b2t2.c1 = b2t1.c1)
@@ -2601,7 +2550,7 @@ error hint:
26012550 Sort Key: b2t1.c1
26022551 -> Tid Scan on t1 b2t1
26032552 TID Cond: (ctid = '(1,1)'::tid)
2604- InitPlan 3 (returns $2)
2553+ InitPlan 2 (returns $1)
26052554 -> Aggregate
26062555 -> Merge Join
26072556 Merge Cond: (b3t2.c1 = b3t1.c1)
@@ -2634,22 +2583,41 @@ error hint:
26342583 TID Cond: (ctid = '(1,1)'::tid)
26352584 -> Hash
26362585 -> Merge Join
2637- Merge Cond: (bmt4.c1 = c1.c1)
2586+ Merge Cond: (bmt4.c1 = (max(b1t1.c1)))
26382587 -> Sort
26392588 Sort Key: bmt4.c1
26402589 -> Tid Scan on t4 bmt4
26412590 TID Cond: (ctid = '(1,1)'::tid)
26422591 -> Sort
2643- Sort Key: c1.c1
2644- -> CTE Scan on c1
2592+ Sort Key: (max(b1t1.c1))
2593+ -> Aggregate
2594+ -> Merge Join
2595+ Merge Cond: (b1t2.c1 = b1t1.c1)
2596+ -> Sort
2597+ Sort Key: b1t2.c1
2598+ -> Nested Loop
2599+ Join Filter: (b1t3.c1 = b1t2.c1)
2600+ -> Hash Join
2601+ Hash Cond: (b1t3.c1 = b1t4.c1)
2602+ -> Tid Scan on t3 b1t3
2603+ TID Cond: (ctid = '(1,1)'::tid)
2604+ -> Hash
2605+ -> Tid Scan on t4 b1t4
2606+ TID Cond: (ctid = '(1,1)'::tid)
2607+ -> Seq Scan on t2 b1t2
2608+ Filter: (ctid = '(1,1)'::tid)
2609+ -> Sort
2610+ Sort Key: b1t1.c1
2611+ -> Tid Scan on t1 b1t1
2612+ TID Cond: (ctid = '(1,1)'::tid)
26452613 -> Seq Scan on t2 bmt2
26462614 Filter: (ctid = '(1,1)'::tid)
26472615 -> Sort
26482616 Sort Key: bmt1.c1
26492617 -> Tid Scan on t1 bmt1
26502618 TID Cond: (ctid = '(1,1)'::tid)
2651- Filter: (c1 <> $2)
2652-(91 rows)
2619+ Filter: (c1 <> $1)
2620+(89 rows)
26532621
26542622 -- No. J-2-2-4
26552623 EXPLAIN (COSTS false)
@@ -2664,38 +2632,22 @@ AND bmt1.c1 <> (
26642632 SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
26652633 )
26662634 ;
2667- QUERY PLAN
2668--------------------------------------------------------------------
2635+ QUERY PLAN
2636+-----------------------------------------------------------------------
26692637 Aggregate
2670- CTE c1
2671- -> Aggregate
2672- -> Nested Loop
2673- Join Filter: (b1t1.c1 = b1t4.c1)
2674- -> Nested Loop
2675- Join Filter: (b1t1.c1 = b1t3.c1)
2676- -> Nested Loop
2677- Join Filter: (b1t1.c1 = b1t2.c1)
2678- -> Tid Scan on t1 b1t1
2679- TID Cond: (ctid = '(1,1)'::tid)
2680- -> Seq Scan on t2 b1t2
2681- Filter: (ctid = '(1,1)'::tid)
2682- -> Tid Scan on t3 b1t3
2683- TID Cond: (ctid = '(1,1)'::tid)
2684- -> Tid Scan on t4 b1t4
2685- TID Cond: (ctid = '(1,1)'::tid)
2686- InitPlan 3 (returns $2)
2638+ InitPlan 2 (returns $1)
26872639 -> Result
2688- InitPlan 2 (returns $1)
2640+ InitPlan 1 (returns $0)
26892641 -> Limit
26902642 -> Tid Scan on t1 b2t1
26912643 TID Cond: (ctid = '(1,1)'::tid)
26922644 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
2693- InitPlan 4 (returns $4)
2645+ InitPlan 3 (returns $3)
26942646 -> Aggregate
26952647 -> Tid Scan on t1 b3t1
26962648 TID Cond: (ctid = '(1,1)'::tid)
26972649 -> Nested Loop
2698- Join Filter: (bmt1.c1 = c1.c1)
2650+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
26992651 -> Nested Loop
27002652 Join Filter: (bmt1.c1 = bmt4.c1)
27012653 -> Nested Loop
@@ -2704,15 +2656,29 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
27042656 Join Filter: (bmt1.c1 = bmt2.c1)
27052657 -> Tid Scan on t1 bmt1
27062658 TID Cond: (ctid = '(1,1)'::tid)
2707- Filter: (c1 <> $4)
2659+ Filter: (c1 <> $3)
27082660 -> Seq Scan on t2 bmt2
27092661 Filter: (ctid = '(1,1)'::tid)
27102662 -> Tid Scan on t3 bmt3
27112663 TID Cond: (ctid = '(1,1)'::tid)
27122664 -> Tid Scan on t4 bmt4
27132665 TID Cond: (ctid = '(1,1)'::tid)
2714- -> CTE Scan on c1
2715-(46 rows)
2666+ -> Aggregate
2667+ -> Nested Loop
2668+ Join Filter: (b1t1.c1 = b1t4.c1)
2669+ -> Nested Loop
2670+ Join Filter: (b1t1.c1 = b1t3.c1)
2671+ -> Nested Loop
2672+ Join Filter: (b1t1.c1 = b1t2.c1)
2673+ -> Tid Scan on t1 b1t1
2674+ TID Cond: (ctid = '(1,1)'::tid)
2675+ -> Seq Scan on t2 b1t2
2676+ Filter: (ctid = '(1,1)'::tid)
2677+ -> Tid Scan on t3 b1t3
2678+ TID Cond: (ctid = '(1,1)'::tid)
2679+ -> Tid Scan on t4 b1t4
2680+ TID Cond: (ctid = '(1,1)'::tid)
2681+(44 rows)
27162682
27172683 /*+
27182684 Leading(c1 bmt4 bmt3 bmt2 bmt1)
@@ -2752,38 +2718,17 @@ not used hint:
27522718 duplication hint:
27532719 error hint:
27542720
2755- QUERY PLAN
2756------------------------------------------------------------------------------------
2721+ QUERY PLAN
2722+-----------------------------------------------------------------------------------------------------------------------
27572723 Aggregate
2758- CTE c1
2759- -> Aggregate
2760- -> Nested Loop
2761- Join Filter: (b1t2.c1 = b1t1.c1)
2762- -> Hash Join
2763- Hash Cond: (b1t2.c1 = b1t3.c1)
2764- -> Seq Scan on t2 b1t2
2765- Filter: (ctid = '(1,1)'::tid)
2766- -> Hash
2767- -> Merge Join
2768- Merge Cond: (b1t3.c1 = b1t4.c1)
2769- -> Sort
2770- Sort Key: b1t3.c1
2771- -> Tid Scan on t3 b1t3
2772- TID Cond: (ctid = '(1,1)'::tid)
2773- -> Sort
2774- Sort Key: b1t4.c1
2775- -> Tid Scan on t4 b1t4
2776- TID Cond: (ctid = '(1,1)'::tid)
2777- -> Tid Scan on t1 b1t1
2778- TID Cond: (ctid = '(1,1)'::tid)
2779- InitPlan 3 (returns $2)
2724+ InitPlan 2 (returns $1)
27802725 -> Result
2781- InitPlan 2 (returns $1)
2726+ InitPlan 1 (returns $0)
27822727 -> Limit
27832728 -> Tid Scan on t1 b2t1
27842729 TID Cond: (ctid = '(1,1)'::tid)
27852730 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
2786- InitPlan 4 (returns $4)
2731+ InitPlan 3 (returns $3)
27872732 -> Aggregate
27882733 -> Tid Scan on t1 b3t1
27892734 TID Cond: (ctid = '(1,1)'::tid)
@@ -2799,22 +2744,41 @@ error hint:
27992744 TID Cond: (ctid = '(1,1)'::tid)
28002745 -> Hash
28012746 -> Merge Join
2802- Merge Cond: (bmt4.c1 = c1.c1)
2747+ Merge Cond: (bmt4.c1 = (max(b1t1.c1)))
28032748 -> Sort
28042749 Sort Key: bmt4.c1
28052750 -> Tid Scan on t4 bmt4
28062751 TID Cond: (ctid = '(1,1)'::tid)
28072752 -> Sort
2808- Sort Key: c1.c1
2809- -> CTE Scan on c1
2753+ Sort Key: (max(b1t1.c1))
2754+ -> Aggregate
2755+ -> Nested Loop
2756+ Join Filter: (b1t2.c1 = b1t1.c1)
2757+ -> Hash Join
2758+ Hash Cond: (b1t2.c1 = b1t3.c1)
2759+ -> Seq Scan on t2 b1t2
2760+ Filter: (ctid = '(1,1)'::tid)
2761+ -> Hash
2762+ -> Merge Join
2763+ Merge Cond: (b1t3.c1 = b1t4.c1)
2764+ -> Sort
2765+ Sort Key: b1t3.c1
2766+ -> Tid Scan on t3 b1t3
2767+ TID Cond: (ctid = '(1,1)'::tid)
2768+ -> Sort
2769+ Sort Key: b1t4.c1
2770+ -> Tid Scan on t4 b1t4
2771+ TID Cond: (ctid = '(1,1)'::tid)
2772+ -> Tid Scan on t1 b1t1
2773+ TID Cond: (ctid = '(1,1)'::tid)
28102774 -> Seq Scan on t2 bmt2
28112775 Filter: (ctid = '(1,1)'::tid)
28122776 -> Sort
28132777 Sort Key: bmt1.c1
28142778 -> Tid Scan on t1 bmt1
28152779 TID Cond: (ctid = '(1,1)'::tid)
2816- Filter: (c1 <> $4)
2817-(60 rows)
2780+ Filter: (c1 <> $3)
2781+(58 rows)
28182782
28192783 ----
28202784 ---- No. J-2-3 RULE or VIEW
@@ -4744,8 +4708,8 @@ error hint:
47444708
47454709 \o
47464710 \! sql/maskout.sh results/ut-J.tmpout
4747- QUERY PLAN
4748--------------------------------------------------------------------------------
4711+ QUERY PLAN
4712+--------------------------------------------------------------------------
47494713 Hash Full Join (cost={inf}..{inf} rows=1000 width=xxx)
47504714 Hash Cond: (t1.c1 = t2.c1)
47514715 -> Seq Scan on t1 (cost=xxx..xxx rows=1000 width=xxx)
--- 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
19381938 AND bmt1.c1 = c1.c1
19391939 AND bmt1.c1 = c2.c1
19401940 ;
1941- QUERY PLAN
1942------------------------------------------------------------------------
1941+ QUERY PLAN
1942+-----------------------------------------------------------------------------
19431943 Aggregate
1944- CTE c1
1945- -> Aggregate
1946- -> Nested Loop
1947- Join Filter: (b1t1.c1 = b1t4.c1)
1948- -> Nested Loop
1949- Join Filter: (b1t1.c1 = b1t3.c1)
1950- -> Nested Loop
1951- Join Filter: (b1t1.c1 = b1t2.c1)
1952- -> Tid Scan on t1 b1t1
1953- TID Cond: (ctid = '(1,1)'::tid)
1954- -> Seq Scan on t2 b1t2
1955- Filter: (ctid = '(1,1)'::tid)
1956- -> Tid Scan on t3 b1t3
1957- TID Cond: (ctid = '(1,1)'::tid)
1958- -> Tid Scan on t4 b1t4
1959- TID Cond: (ctid = '(1,1)'::tid)
1960- CTE c2
1961- -> Aggregate
1962- -> Nested Loop
1963- Join Filter: (b2t1.c1 = b2t4.c1)
1964- -> Nested Loop
1965- Join Filter: (b2t1.c1 = b2t3.c1)
1966- -> Nested Loop
1967- Join Filter: (b2t1.c1 = b2t2.c1)
1968- -> Tid Scan on t1 b2t1
1969- TID Cond: (ctid = '(1,1)'::tid)
1970- -> Seq Scan on t2 b2t2
1971- Filter: (ctid = '(1,1)'::tid)
1972- -> Tid Scan on t3 b2t3
1973- TID Cond: (ctid = '(1,1)'::tid)
1974- -> Tid Scan on t4 b2t4
1975- TID Cond: (ctid = '(1,1)'::tid)
19761944 -> Nested Loop
1977- Join Filter: (bmt1.c1 = c2.c1)
1945+ Join Filter: (bmt1.c1 = (max(b2t1.c1)))
19781946 -> Nested Loop
1979- Join Filter: (bmt1.c1 = c1.c1)
1947+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
19801948 -> Nested Loop
19811949 Join Filter: (bmt1.c1 = bmt4.c1)
19821950 -> Nested Loop
@@ -1991,9 +1959,37 @@ AND bmt1.c1 = c2.c1
19911959 TID Cond: (ctid = '(1,1)'::tid)
19921960 -> Tid Scan on t4 bmt4
19931961 TID Cond: (ctid = '(1,1)'::tid)
1994- -> CTE Scan on c1
1995- -> CTE Scan on c2
1996-(53 rows)
1962+ -> Aggregate
1963+ -> Nested Loop
1964+ Join Filter: (b1t1.c1 = b1t4.c1)
1965+ -> Nested Loop
1966+ Join Filter: (b1t1.c1 = b1t3.c1)
1967+ -> Nested Loop
1968+ Join Filter: (b1t1.c1 = b1t2.c1)
1969+ -> Tid Scan on t1 b1t1
1970+ TID Cond: (ctid = '(1,1)'::tid)
1971+ -> Seq Scan on t2 b1t2
1972+ Filter: (ctid = '(1,1)'::tid)
1973+ -> Tid Scan on t3 b1t3
1974+ TID Cond: (ctid = '(1,1)'::tid)
1975+ -> Tid Scan on t4 b1t4
1976+ TID Cond: (ctid = '(1,1)'::tid)
1977+ -> Aggregate
1978+ -> Nested Loop
1979+ Join Filter: (b2t1.c1 = b2t4.c1)
1980+ -> Nested Loop
1981+ Join Filter: (b2t1.c1 = b2t3.c1)
1982+ -> Nested Loop
1983+ Join Filter: (b2t1.c1 = b2t2.c1)
1984+ -> Tid Scan on t1 b2t1
1985+ TID Cond: (ctid = '(1,1)'::tid)
1986+ -> Seq Scan on t2 b2t2
1987+ Filter: (ctid = '(1,1)'::tid)
1988+ -> Tid Scan on t3 b2t3
1989+ TID Cond: (ctid = '(1,1)'::tid)
1990+ -> Tid Scan on t4 b2t4
1991+ TID Cond: (ctid = '(1,1)'::tid)
1992+(49 rows)
19971993
19981994 /*+
19991995 Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
@@ -2022,41 +2018,9 @@ not used hint:
20222018 duplication hint:
20232019 error hint:
20242020
2025- QUERY PLAN
2026--------------------------------------------------------------------
2021+ QUERY PLAN
2022+-----------------------------------------------------------------------------------------------------
20272023 Aggregate
2028- CTE c1
2029- -> Aggregate
2030- -> Nested Loop
2031- Join Filter: (b1t2.c1 = b1t1.c1)
2032- -> Nested Loop
2033- Join Filter: (b1t2.c1 = b1t4.c1)
2034- -> Nested Loop
2035- Join Filter: (b1t2.c1 = b1t3.c1)
2036- -> Seq Scan on t2 b1t2
2037- Filter: (ctid = '(1,1)'::tid)
2038- -> Tid Scan on t3 b1t3
2039- TID Cond: (ctid = '(1,1)'::tid)
2040- -> Tid Scan on t4 b1t4
2041- TID Cond: (ctid = '(1,1)'::tid)
2042- -> Tid Scan on t1 b1t1
2043- TID Cond: (ctid = '(1,1)'::tid)
2044- CTE c2
2045- -> Aggregate
2046- -> Nested Loop
2047- Join Filter: (b2t1.c1 = b2t2.c1)
2048- -> Nested Loop
2049- Join Filter: (b2t3.c1 = b2t1.c1)
2050- -> Nested Loop
2051- Join Filter: (b2t3.c1 = b2t4.c1)
2052- -> Tid Scan on t3 b2t3
2053- TID Cond: (ctid = '(1,1)'::tid)
2054- -> Tid Scan on t4 b2t4
2055- TID Cond: (ctid = '(1,1)'::tid)
2056- -> Tid Scan on t1 b2t1
2057- TID Cond: (ctid = '(1,1)'::tid)
2058- -> Seq Scan on t2 b2t2
2059- Filter: (ctid = '(1,1)'::tid)
20602024 -> Nested Loop
20612025 Join Filter: (bmt1.c1 = bmt4.c1)
20622026 -> Nested Loop
@@ -2064,11 +2028,43 @@ error hint:
20642028 -> Nested Loop
20652029 Join Filter: (bmt1.c1 = bmt2.c1)
20662030 -> Nested Loop
2067- Join Filter: (c1.c1 = bmt1.c1)
2068- -> Nested Loop
2069- Join Filter: (c1.c1 = c2.c1)
2070- -> CTE Scan on c1
2071- -> CTE Scan on c2
2031+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
2032+ -> Merge Join
2033+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
2034+ -> Sort
2035+ Sort Key: (max(b1t1.c1))
2036+ -> Aggregate
2037+ -> Nested Loop
2038+ Join Filter: (b1t2.c1 = b1t1.c1)
2039+ -> Nested Loop
2040+ Join Filter: (b1t2.c1 = b1t4.c1)
2041+ -> Nested Loop
2042+ Join Filter: (b1t2.c1 = b1t3.c1)
2043+ -> Seq Scan on t2 b1t2
2044+ Filter: (ctid = '(1,1)'::tid)
2045+ -> Tid Scan on t3 b1t3
2046+ TID Cond: (ctid = '(1,1)'::tid)
2047+ -> Tid Scan on t4 b1t4
2048+ TID Cond: (ctid = '(1,1)'::tid)
2049+ -> Tid Scan on t1 b1t1
2050+ TID Cond: (ctid = '(1,1)'::tid)
2051+ -> Sort
2052+ Sort Key: (max(b2t1.c1))
2053+ -> Aggregate
2054+ -> Nested Loop
2055+ Join Filter: (b2t1.c1 = b2t2.c1)
2056+ -> Nested Loop
2057+ Join Filter: (b2t3.c1 = b2t1.c1)
2058+ -> Nested Loop
2059+ Join Filter: (b2t3.c1 = b2t4.c1)
2060+ -> Tid Scan on t3 b2t3
2061+ TID Cond: (ctid = '(1,1)'::tid)
2062+ -> Tid Scan on t4 b2t4
2063+ TID Cond: (ctid = '(1,1)'::tid)
2064+ -> Tid Scan on t1 b2t1
2065+ TID Cond: (ctid = '(1,1)'::tid)
2066+ -> Seq Scan on t2 b2t2
2067+ Filter: (ctid = '(1,1)'::tid)
20722068 -> Tid Scan on t1 bmt1
20732069 TID Cond: (ctid = '(1,1)'::tid)
20742070 -> Seq Scan on t2 bmt2
@@ -2097,81 +2093,79 @@ AND bmt1.c1 = c1.c1
20972093 AND bmt1.c1 = c2.c1
20982094 AND bmt1.c1 = c3.c1
20992095 ;
2100- QUERY PLAN
2101------------------------------------------------------------------------------
2096+ QUERY PLAN
2097+-----------------------------------------------------------------------------------------------------------
21022098 Aggregate
2103- CTE c1
2104- -> Aggregate
2105- -> Nested Loop
2106- Join Filter: (b1t1.c1 = b1t4.c1)
2107- -> Nested Loop
2108- Join Filter: (b1t1.c1 = b1t3.c1)
2109- -> Nested Loop
2110- Join Filter: (b1t1.c1 = b1t2.c1)
2111- -> Tid Scan on t1 b1t1
2112- TID Cond: (ctid = '(1,1)'::tid)
2113- -> Seq Scan on t2 b1t2
2114- Filter: (ctid = '(1,1)'::tid)
2115- -> Tid Scan on t3 b1t3
2116- TID Cond: (ctid = '(1,1)'::tid)
2117- -> Tid Scan on t4 b1t4
2118- TID Cond: (ctid = '(1,1)'::tid)
2119- CTE c2
2120- -> Aggregate
2121- -> Nested Loop
2122- Join Filter: (b2t1.c1 = b2t4.c1)
2123- -> Nested Loop
2124- Join Filter: (b2t1.c1 = b2t3.c1)
2125- -> Nested Loop
2126- Join Filter: (b2t1.c1 = b2t2.c1)
2127- -> Tid Scan on t1 b2t1
2128- TID Cond: (ctid = '(1,1)'::tid)
2129- -> Seq Scan on t2 b2t2
2130- Filter: (ctid = '(1,1)'::tid)
2131- -> Tid Scan on t3 b2t3
2132- TID Cond: (ctid = '(1,1)'::tid)
2133- -> Tid Scan on t4 b2t4
2134- TID Cond: (ctid = '(1,1)'::tid)
2135- CTE c3
2136- -> Aggregate
2137- -> Nested Loop
2138- Join Filter: (b3t1.c1 = b3t4.c1)
2139- -> Nested Loop
2140- Join Filter: (b3t1.c1 = b3t3.c1)
2141- -> Nested Loop
2142- Join Filter: (b3t1.c1 = b3t2.c1)
2143- -> Tid Scan on t1 b3t1
2144- TID Cond: (ctid = '(1,1)'::tid)
2145- -> Seq Scan on t2 b3t2
2146- Filter: (ctid = '(1,1)'::tid)
2147- -> Tid Scan on t3 b3t3
2148- TID Cond: (ctid = '(1,1)'::tid)
2149- -> Tid Scan on t4 b3t4
2150- TID Cond: (ctid = '(1,1)'::tid)
21512099 -> Nested Loop
2152- Join Filter: (bmt1.c1 = c3.c1)
2100+ Join Filter: (bmt1.c1 = (max(b3t1.c1)))
21532101 -> Nested Loop
2154- Join Filter: (bmt1.c1 = c2.c1)
2102+ Join Filter: (bmt1.c1 = bmt4.c1)
21552103 -> Nested Loop
2156- Join Filter: (bmt1.c1 = c1.c1)
2104+ Join Filter: (bmt1.c1 = bmt3.c1)
21572105 -> Nested Loop
2158- Join Filter: (bmt1.c1 = bmt4.c1)
2106+ Join Filter: (bmt1.c1 = bmt2.c1)
21592107 -> Nested Loop
2160- Join Filter: (bmt1.c1 = bmt3.c1)
2161- -> Nested Loop
2162- Join Filter: (bmt1.c1 = bmt2.c1)
2163- -> Tid Scan on t1 bmt1
2164- TID Cond: (ctid = '(1,1)'::tid)
2165- -> Seq Scan on t2 bmt2
2166- Filter: (ctid = '(1,1)'::tid)
2167- -> Tid Scan on t3 bmt3
2108+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
2109+ -> Merge Join
2110+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
2111+ -> Sort
2112+ Sort Key: (max(b1t1.c1))
2113+ -> Aggregate
2114+ -> Nested Loop
2115+ Join Filter: (b1t1.c1 = b1t4.c1)
2116+ -> Nested Loop
2117+ Join Filter: (b1t1.c1 = b1t3.c1)
2118+ -> Nested Loop
2119+ Join Filter: (b1t1.c1 = b1t2.c1)
2120+ -> Tid Scan on t1 b1t1
2121+ TID Cond: (ctid = '(1,1)'::tid)
2122+ -> Seq Scan on t2 b1t2
2123+ Filter: (ctid = '(1,1)'::tid)
2124+ -> Tid Scan on t3 b1t3
2125+ TID Cond: (ctid = '(1,1)'::tid)
2126+ -> Tid Scan on t4 b1t4
2127+ TID Cond: (ctid = '(1,1)'::tid)
2128+ -> Sort
2129+ Sort Key: (max(b2t1.c1))
2130+ -> Aggregate
2131+ -> Nested Loop
2132+ Join Filter: (b2t1.c1 = b2t4.c1)
2133+ -> Nested Loop
2134+ Join Filter: (b2t1.c1 = b2t3.c1)
2135+ -> Nested Loop
2136+ Join Filter: (b2t1.c1 = b2t2.c1)
2137+ -> Tid Scan on t1 b2t1
2138+ TID Cond: (ctid = '(1,1)'::tid)
2139+ -> Seq Scan on t2 b2t2
2140+ Filter: (ctid = '(1,1)'::tid)
2141+ -> Tid Scan on t3 b2t3
2142+ TID Cond: (ctid = '(1,1)'::tid)
2143+ -> Tid Scan on t4 b2t4
2144+ TID Cond: (ctid = '(1,1)'::tid)
2145+ -> Tid Scan on t1 bmt1
21682146 TID Cond: (ctid = '(1,1)'::tid)
2169- -> Tid Scan on t4 bmt4
2147+ -> Seq Scan on t2 bmt2
2148+ Filter: (ctid = '(1,1)'::tid)
2149+ -> Tid Scan on t3 bmt3
2150+ TID Cond: (ctid = '(1,1)'::tid)
2151+ -> Tid Scan on t4 bmt4
2152+ TID Cond: (ctid = '(1,1)'::tid)
2153+ -> Aggregate
2154+ -> Nested Loop
2155+ Join Filter: (b3t1.c1 = b3t4.c1)
2156+ -> Nested Loop
2157+ Join Filter: (b3t1.c1 = b3t3.c1)
2158+ -> Nested Loop
2159+ Join Filter: (b3t1.c1 = b3t2.c1)
2160+ -> Tid Scan on t1 b3t1
2161+ TID Cond: (ctid = '(1,1)'::tid)
2162+ -> Seq Scan on t2 b3t2
2163+ Filter: (ctid = '(1,1)'::tid)
2164+ -> Tid Scan on t3 b3t3
21702165 TID Cond: (ctid = '(1,1)'::tid)
2171- -> CTE Scan on c1
2172- -> CTE Scan on c2
2173- -> CTE Scan on c3
2174-(72 rows)
2166+ -> Tid Scan on t4 b3t4
2167+ TID Cond: (ctid = '(1,1)'::tid)
2168+(70 rows)
21752169
21762170 /*+
21772171 Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
@@ -2206,57 +2200,9 @@ not used hint:
22062200 duplication hint:
22072201 error hint:
22082202
2209- QUERY PLAN
2210---------------------------------------------------------------------
2203+ QUERY PLAN
2204+-----------------------------------------------------------------------------------------------------------
22112205 Aggregate
2212- CTE c1
2213- -> Aggregate
2214- -> Nested Loop
2215- Join Filter: (b1t2.c1 = b1t1.c1)
2216- -> Nested Loop
2217- Join Filter: (b1t2.c1 = b1t4.c1)
2218- -> Nested Loop
2219- Join Filter: (b1t2.c1 = b1t3.c1)
2220- -> Seq Scan on t2 b1t2
2221- Filter: (ctid = '(1,1)'::tid)
2222- -> Tid Scan on t3 b1t3
2223- TID Cond: (ctid = '(1,1)'::tid)
2224- -> Tid Scan on t4 b1t4
2225- TID Cond: (ctid = '(1,1)'::tid)
2226- -> Tid Scan on t1 b1t1
2227- TID Cond: (ctid = '(1,1)'::tid)
2228- CTE c2
2229- -> Aggregate
2230- -> Nested Loop
2231- Join Filter: (b2t1.c1 = b2t2.c1)
2232- -> Nested Loop
2233- Join Filter: (b2t3.c1 = b2t1.c1)
2234- -> Nested Loop
2235- Join Filter: (b2t3.c1 = b2t4.c1)
2236- -> Tid Scan on t3 b2t3
2237- TID Cond: (ctid = '(1,1)'::tid)
2238- -> Tid Scan on t4 b2t4
2239- TID Cond: (ctid = '(1,1)'::tid)
2240- -> Tid Scan on t1 b2t1
2241- TID Cond: (ctid = '(1,1)'::tid)
2242- -> Seq Scan on t2 b2t2
2243- Filter: (ctid = '(1,1)'::tid)
2244- CTE c3
2245- -> Aggregate
2246- -> Nested Loop
2247- Join Filter: (b3t1.c1 = b3t3.c1)
2248- -> Nested Loop
2249- Join Filter: (b3t1.c1 = b3t2.c1)
2250- -> Nested Loop
2251- Join Filter: (b3t1.c1 = b3t4.c1)
2252- -> Tid Scan on t1 b3t1
2253- TID Cond: (ctid = '(1,1)'::tid)
2254- -> Tid Scan on t4 b3t4
2255- TID Cond: (ctid = '(1,1)'::tid)
2256- -> Seq Scan on t2 b3t2
2257- Filter: (ctid = '(1,1)'::tid)
2258- -> Tid Scan on t3 b3t3
2259- TID Cond: (ctid = '(1,1)'::tid)
22602206 -> Nested Loop
22612207 Join Filter: (bmt1.c1 = bmt4.c1)
22622208 -> Nested Loop
@@ -2264,14 +2210,60 @@ error hint:
22642210 -> Nested Loop
22652211 Join Filter: (bmt1.c1 = bmt2.c1)
22662212 -> Nested Loop
2267- Join Filter: (c1.c1 = bmt1.c1)
2213+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
22682214 -> Nested Loop
2269- Join Filter: (c2.c1 = c1.c1)
2270- -> Nested Loop
2271- Join Filter: (c2.c1 = c3.c1)
2272- -> CTE Scan on c2
2273- -> CTE Scan on c3
2274- -> CTE Scan on c1
2215+ Join Filter: ((max(b2t1.c1)) = (max(b1t1.c1)))
2216+ -> Merge Join
2217+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
2218+ -> Sort
2219+ Sort Key: (max(b2t1.c1))
2220+ -> Aggregate
2221+ -> Nested Loop
2222+ Join Filter: (b2t1.c1 = b2t2.c1)
2223+ -> Nested Loop
2224+ Join Filter: (b2t3.c1 = b2t1.c1)
2225+ -> Nested Loop
2226+ Join Filter: (b2t3.c1 = b2t4.c1)
2227+ -> Tid Scan on t3 b2t3
2228+ TID Cond: (ctid = '(1,1)'::tid)
2229+ -> Tid Scan on t4 b2t4
2230+ TID Cond: (ctid = '(1,1)'::tid)
2231+ -> Tid Scan on t1 b2t1
2232+ TID Cond: (ctid = '(1,1)'::tid)
2233+ -> Seq Scan on t2 b2t2
2234+ Filter: (ctid = '(1,1)'::tid)
2235+ -> Sort
2236+ Sort Key: (max(b3t1.c1))
2237+ -> Aggregate
2238+ -> Nested Loop
2239+ Join Filter: (b3t1.c1 = b3t3.c1)
2240+ -> Nested Loop
2241+ Join Filter: (b3t1.c1 = b3t2.c1)
2242+ -> Nested Loop
2243+ Join Filter: (b3t1.c1 = b3t4.c1)
2244+ -> Tid Scan on t1 b3t1
2245+ TID Cond: (ctid = '(1,1)'::tid)
2246+ -> Tid Scan on t4 b3t4
2247+ TID Cond: (ctid = '(1,1)'::tid)
2248+ -> Seq Scan on t2 b3t2
2249+ Filter: (ctid = '(1,1)'::tid)
2250+ -> Tid Scan on t3 b3t3
2251+ TID Cond: (ctid = '(1,1)'::tid)
2252+ -> Aggregate
2253+ -> Nested Loop
2254+ Join Filter: (b1t2.c1 = b1t1.c1)
2255+ -> Nested Loop
2256+ Join Filter: (b1t2.c1 = b1t4.c1)
2257+ -> Nested Loop
2258+ Join Filter: (b1t2.c1 = b1t3.c1)
2259+ -> Seq Scan on t2 b1t2
2260+ Filter: (ctid = '(1,1)'::tid)
2261+ -> Tid Scan on t3 b1t3
2262+ TID Cond: (ctid = '(1,1)'::tid)
2263+ -> Tid Scan on t4 b1t4
2264+ TID Cond: (ctid = '(1,1)'::tid)
2265+ -> Tid Scan on t1 b1t1
2266+ TID Cond: (ctid = '(1,1)'::tid)
22752267 -> Tid Scan on t1 bmt1
22762268 TID Cond: (ctid = '(1,1)'::tid)
22772269 -> Seq Scan on t2 bmt2
@@ -2280,7 +2272,7 @@ error hint:
22802272 TID Cond: (ctid = '(1,1)'::tid)
22812273 -> Tid Scan on t4 bmt4
22822274 TID Cond: (ctid = '(1,1)'::tid)
2283-(72 rows)
2275+(70 rows)
22842276
22852277 ----
22862278 ---- 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
23012293 QUERY PLAN
23022294 -----------------------------------------------------------------
23032295 Aggregate
2304- CTE c1
2296+ InitPlan 2 (returns $1)
23052297 -> Result
23062298 InitPlan 1 (returns $0)
23072299 -> Limit
2308- -> Tid Scan on t1 b1t1
2300+ -> Tid Scan on t1 b2t1
23092301 TID Cond: (ctid = '(1,1)'::tid)
23102302 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
23112303 InitPlan 4 (returns $3)
23122304 -> Result
23132305 InitPlan 3 (returns $2)
23142306 -> Limit
2315- -> Tid Scan on t1 b2t1
2316- TID Cond: (ctid = '(1,1)'::tid)
2317- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
2318- InitPlan 6 (returns $5)
2319- -> Result
2320- InitPlan 5 (returns $4)
2321- -> Limit
23222307 -> Tid Scan on t1 b3t1
23232308 TID Cond: (ctid = '(1,1)'::tid)
23242309 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
23252310 -> Nested Loop
23262311 -> Tid Scan on t1 bmt1
23272312 TID Cond: (ctid = '(1,1)'::tid)
2328- Filter: ((c1 <> $5) AND (c1 = 1))
2329- -> CTE Scan on c1
2330-(27 rows)
2313+ Filter: ((c1 <> $3) AND (c1 = 1))
2314+ -> Result
2315+(20 rows)
23312316
23322317 /*+
23332318 Leading(c1 bmt1)
@@ -2354,33 +2339,26 @@ error hint:
23542339 QUERY PLAN
23552340 -----------------------------------------------------------------
23562341 Aggregate
2357- CTE c1
2342+ InitPlan 2 (returns $1)
23582343 -> Result
23592344 InitPlan 1 (returns $0)
23602345 -> Limit
2361- -> Tid Scan on t1 b1t1
2346+ -> Tid Scan on t1 b2t1
23622347 TID Cond: (ctid = '(1,1)'::tid)
23632348 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
23642349 InitPlan 4 (returns $3)
23652350 -> Result
23662351 InitPlan 3 (returns $2)
23672352 -> Limit
2368- -> Tid Scan on t1 b2t1
2369- TID Cond: (ctid = '(1,1)'::tid)
2370- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
2371- InitPlan 6 (returns $5)
2372- -> Result
2373- InitPlan 5 (returns $4)
2374- -> Limit
23752353 -> Tid Scan on t1 b3t1
23762354 TID Cond: (ctid = '(1,1)'::tid)
23772355 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
23782356 -> Nested Loop
23792357 -> Tid Scan on t1 bmt1
23802358 TID Cond: (ctid = '(1,1)'::tid)
2381- Filter: ((c1 <> $5) AND (c1 = 1))
2382- -> CTE Scan on c1
2383-(27 rows)
2359+ Filter: ((c1 <> $3) AND (c1 = 1))
2360+ -> Result
2361+(20 rows)
23842362
23852363 -- No. L-2-2-2
23862364 EXPLAIN (COSTS false)
@@ -2396,18 +2374,10 @@ AND bmt1.c1 <> (
23962374 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)'
23972375 )
23982376 ;
2399- QUERY PLAN
2400--------------------------------------------------------
2377+ QUERY PLAN
2378+-----------------------------------------------------------
24012379 Aggregate
2402- CTE c1
2403- -> Aggregate
2404- -> Nested Loop
2405- Join Filter: (b1t1.c1 = b1t2.c1)
2406- -> Tid Scan on t1 b1t1
2407- TID Cond: (ctid = '(1,1)'::tid)
2408- -> Seq Scan on t2 b1t2
2409- Filter: (ctid = '(1,1)'::tid)
2410- InitPlan 2 (returns $1)
2380+ InitPlan 1 (returns $0)
24112381 -> Aggregate
24122382 -> Nested Loop
24132383 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
24152385 TID Cond: (ctid = '(1,1)'::tid)
24162386 -> Seq Scan on t2 b2t2
24172387 Filter: (ctid = '(1,1)'::tid)
2418- InitPlan 3 (returns $2)
2388+ InitPlan 2 (returns $1)
24192389 -> Aggregate
24202390 -> Nested Loop
24212391 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
24242394 -> Seq Scan on t2 b3t2
24252395 Filter: (ctid = '(1,1)'::tid)
24262396 -> Nested Loop
2427- Join Filter: (bmt1.c1 = c1.c1)
2397+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
24282398 -> Nested Loop
24292399 Join Filter: (bmt1.c1 = bmt2.c1)
24302400 -> Tid Scan on t1 bmt1
24312401 TID Cond: (ctid = '(1,1)'::tid)
2432- Filter: (c1 <> $2)
2402+ Filter: (c1 <> $1)
24332403 -> Seq Scan on t2 bmt2
24342404 Filter: (ctid = '(1,1)'::tid)
2435- -> CTE Scan on c1
2436-(35 rows)
2405+ -> Aggregate
2406+ -> Nested Loop
2407+ Join Filter: (b1t1.c1 = b1t2.c1)
2408+ -> Tid Scan on t1 b1t1
2409+ TID Cond: (ctid = '(1,1)'::tid)
2410+ -> Seq Scan on t2 b1t2
2411+ Filter: (ctid = '(1,1)'::tid)
2412+(33 rows)
24372413
24382414 /*+
24392415 Leading(c1 bmt2 bmt1)
@@ -2464,18 +2440,10 @@ not used hint:
24642440 duplication hint:
24652441 error hint:
24662442
2467- QUERY PLAN
2468--------------------------------------------------------
2443+ QUERY PLAN
2444+-----------------------------------------------------------------
24692445 Aggregate
2470- CTE c1
2471- -> Aggregate
2472- -> Nested Loop
2473- Join Filter: (b1t1.c1 = b1t2.c1)
2474- -> Tid Scan on t1 b1t1
2475- TID Cond: (ctid = '(1,1)'::tid)
2476- -> Seq Scan on t2 b1t2
2477- Filter: (ctid = '(1,1)'::tid)
2478- InitPlan 2 (returns $1)
2446+ InitPlan 1 (returns $0)
24792447 -> Aggregate
24802448 -> Nested Loop
24812449 Join Filter: (b2t1.c1 = b2t2.c1)
@@ -2483,7 +2451,7 @@ error hint:
24832451 TID Cond: (ctid = '(1,1)'::tid)
24842452 -> Seq Scan on t2 b2t2
24852453 Filter: (ctid = '(1,1)'::tid)
2486- InitPlan 3 (returns $2)
2454+ InitPlan 2 (returns $1)
24872455 -> Aggregate
24882456 -> Nested Loop
24892457 Join Filter: (b3t1.c1 = b3t2.c1)
@@ -2494,14 +2462,20 @@ error hint:
24942462 -> Nested Loop
24952463 Join Filter: (bmt2.c1 = bmt1.c1)
24962464 -> Nested Loop
2497- Join Filter: (bmt2.c1 = c1.c1)
2465+ Join Filter: (bmt2.c1 = (max(b1t1.c1)))
24982466 -> Seq Scan on t2 bmt2
24992467 Filter: (ctid = '(1,1)'::tid)
2500- -> CTE Scan on c1
2468+ -> Aggregate
2469+ -> Nested Loop
2470+ Join Filter: (b1t1.c1 = b1t2.c1)
2471+ -> Tid Scan on t1 b1t1
2472+ TID Cond: (ctid = '(1,1)'::tid)
2473+ -> Seq Scan on t2 b1t2
2474+ Filter: (ctid = '(1,1)'::tid)
25012475 -> Tid Scan on t1 bmt1
25022476 TID Cond: (ctid = '(1,1)'::tid)
2503- Filter: (c1 <> $2)
2504-(35 rows)
2477+ Filter: (c1 <> $1)
2478+(33 rows)
25052479
25062480 -- No. L-2-2-3
25072481 EXPLAIN (COSTS false)
@@ -2516,26 +2490,10 @@ AND bmt1.c1 <> (
25162490 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)'
25172491 )
25182492 ;
2519- QUERY PLAN
2520--------------------------------------------------------------------
2493+ QUERY PLAN
2494+-----------------------------------------------------------------------
25212495 Aggregate
2522- CTE c1
2523- -> Aggregate
2524- -> Nested Loop
2525- Join Filter: (b1t1.c1 = b1t4.c1)
2526- -> Nested Loop
2527- Join Filter: (b1t1.c1 = b1t3.c1)
2528- -> Nested Loop
2529- Join Filter: (b1t1.c1 = b1t2.c1)
2530- -> Tid Scan on t1 b1t1
2531- TID Cond: (ctid = '(1,1)'::tid)
2532- -> Seq Scan on t2 b1t2
2533- Filter: (ctid = '(1,1)'::tid)
2534- -> Tid Scan on t3 b1t3
2535- TID Cond: (ctid = '(1,1)'::tid)
2536- -> Tid Scan on t4 b1t4
2537- TID Cond: (ctid = '(1,1)'::tid)
2538- InitPlan 2 (returns $1)
2496+ InitPlan 1 (returns $0)
25392497 -> Aggregate
25402498 -> Nested Loop
25412499 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
25512509 TID Cond: (ctid = '(1,1)'::tid)
25522510 -> Tid Scan on t4 b2t4
25532511 TID Cond: (ctid = '(1,1)'::tid)
2554- InitPlan 3 (returns $2)
2512+ InitPlan 2 (returns $1)
25552513 -> Aggregate
25562514 -> Nested Loop
25572515 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
25682526 -> Tid Scan on t4 b3t4
25692527 TID Cond: (ctid = '(1,1)'::tid)
25702528 -> Nested Loop
2571- Join Filter: (bmt1.c1 = c1.c1)
2529+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
25722530 -> Nested Loop
25732531 Join Filter: (bmt1.c1 = bmt4.c1)
25742532 -> 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
25772535 Join Filter: (bmt1.c1 = bmt2.c1)
25782536 -> Tid Scan on t1 bmt1
25792537 TID Cond: (ctid = '(1,1)'::tid)
2580- Filter: (c1 <> $2)
2538+ Filter: (c1 <> $1)
25812539 -> Seq Scan on t2 bmt2
25822540 Filter: (ctid = '(1,1)'::tid)
25832541 -> Tid Scan on t3 bmt3
25842542 TID Cond: (ctid = '(1,1)'::tid)
25852543 -> Tid Scan on t4 bmt4
25862544 TID Cond: (ctid = '(1,1)'::tid)
2587- -> CTE Scan on c1
2588-(67 rows)
2545+ -> Aggregate
2546+ -> Nested Loop
2547+ Join Filter: (b1t1.c1 = b1t4.c1)
2548+ -> Nested Loop
2549+ Join Filter: (b1t1.c1 = b1t3.c1)
2550+ -> Nested Loop
2551+ Join Filter: (b1t1.c1 = b1t2.c1)
2552+ -> Tid Scan on t1 b1t1
2553+ TID Cond: (ctid = '(1,1)'::tid)
2554+ -> Seq Scan on t2 b1t2
2555+ Filter: (ctid = '(1,1)'::tid)
2556+ -> Tid Scan on t3 b1t3
2557+ TID Cond: (ctid = '(1,1)'::tid)
2558+ -> Tid Scan on t4 b1t4
2559+ TID Cond: (ctid = '(1,1)'::tid)
2560+(65 rows)
25892561
25902562 /*+
25912563 Leading(c1 bmt4 bmt3 bmt2 bmt1)
@@ -2615,26 +2587,10 @@ not used hint:
26152587 duplication hint:
26162588 error hint:
26172589
2618- QUERY PLAN
2619--------------------------------------------------------------------
2590+ QUERY PLAN
2591+-----------------------------------------------------------------------------------------
26202592 Aggregate
2621- CTE c1
2622- -> Aggregate
2623- -> Nested Loop
2624- Join Filter: (b1t2.c1 = b1t1.c1)
2625- -> Nested Loop
2626- Join Filter: (b1t3.c1 = b1t2.c1)
2627- -> Nested Loop
2628- Join Filter: (b1t3.c1 = b1t4.c1)
2629- -> Tid Scan on t3 b1t3
2630- TID Cond: (ctid = '(1,1)'::tid)
2631- -> Tid Scan on t4 b1t4
2632- TID Cond: (ctid = '(1,1)'::tid)
2633- -> Seq Scan on t2 b1t2
2634- Filter: (ctid = '(1,1)'::tid)
2635- -> Tid Scan on t1 b1t1
2636- TID Cond: (ctid = '(1,1)'::tid)
2637- InitPlan 2 (returns $1)
2593+ InitPlan 1 (returns $0)
26382594 -> Aggregate
26392595 -> Nested Loop
26402596 Join Filter: (b2t2.c1 = b2t1.c1)
@@ -2650,7 +2606,7 @@ error hint:
26502606 Filter: (ctid = '(1,1)'::tid)
26512607 -> Tid Scan on t1 b2t1
26522608 TID Cond: (ctid = '(1,1)'::tid)
2653- InitPlan 3 (returns $2)
2609+ InitPlan 2 (returns $1)
26542610 -> Aggregate
26552611 -> Nested Loop
26562612 Join Filter: (b3t2.c1 = b3t1.c1)
@@ -2673,18 +2629,32 @@ error hint:
26732629 -> Nested Loop
26742630 Join Filter: (bmt4.c1 = bmt3.c1)
26752631 -> Nested Loop
2676- Join Filter: (bmt4.c1 = c1.c1)
2632+ Join Filter: (bmt4.c1 = (max(b1t1.c1)))
26772633 -> Tid Scan on t4 bmt4
26782634 TID Cond: (ctid = '(1,1)'::tid)
2679- -> CTE Scan on c1
2635+ -> Aggregate
2636+ -> Nested Loop
2637+ Join Filter: (b1t2.c1 = b1t1.c1)
2638+ -> Nested Loop
2639+ Join Filter: (b1t3.c1 = b1t2.c1)
2640+ -> Nested Loop
2641+ Join Filter: (b1t3.c1 = b1t4.c1)
2642+ -> Tid Scan on t3 b1t3
2643+ TID Cond: (ctid = '(1,1)'::tid)
2644+ -> Tid Scan on t4 b1t4
2645+ TID Cond: (ctid = '(1,1)'::tid)
2646+ -> Seq Scan on t2 b1t2
2647+ Filter: (ctid = '(1,1)'::tid)
2648+ -> Tid Scan on t1 b1t1
2649+ TID Cond: (ctid = '(1,1)'::tid)
26802650 -> Tid Scan on t3 bmt3
26812651 TID Cond: (ctid = '(1,1)'::tid)
26822652 -> Seq Scan on t2 bmt2
26832653 Filter: (ctid = '(1,1)'::tid)
26842654 -> Tid Scan on t1 bmt1
26852655 TID Cond: (ctid = '(1,1)'::tid)
2686- Filter: (c1 <> $2)
2687-(67 rows)
2656+ Filter: (c1 <> $1)
2657+(65 rows)
26882658
26892659 -- No. L-2-2-4
26902660 EXPLAIN (COSTS false)
@@ -2699,38 +2669,22 @@ AND bmt1.c1 <> (
26992669 SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
27002670 )
27012671 ;
2702- QUERY PLAN
2703--------------------------------------------------------------------
2672+ QUERY PLAN
2673+-----------------------------------------------------------------------
27042674 Aggregate
2705- CTE c1
2706- -> Aggregate
2707- -> Nested Loop
2708- Join Filter: (b1t1.c1 = b1t4.c1)
2709- -> Nested Loop
2710- Join Filter: (b1t1.c1 = b1t3.c1)
2711- -> Nested Loop
2712- Join Filter: (b1t1.c1 = b1t2.c1)
2713- -> Tid Scan on t1 b1t1
2714- TID Cond: (ctid = '(1,1)'::tid)
2715- -> Seq Scan on t2 b1t2
2716- Filter: (ctid = '(1,1)'::tid)
2717- -> Tid Scan on t3 b1t3
2718- TID Cond: (ctid = '(1,1)'::tid)
2719- -> Tid Scan on t4 b1t4
2720- TID Cond: (ctid = '(1,1)'::tid)
2721- InitPlan 3 (returns $2)
2675+ InitPlan 2 (returns $1)
27222676 -> Result
2723- InitPlan 2 (returns $1)
2677+ InitPlan 1 (returns $0)
27242678 -> Limit
27252679 -> Tid Scan on t1 b2t1
27262680 TID Cond: (ctid = '(1,1)'::tid)
27272681 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
2728- InitPlan 4 (returns $4)
2682+ InitPlan 3 (returns $3)
27292683 -> Aggregate
27302684 -> Tid Scan on t1 b3t1
27312685 TID Cond: (ctid = '(1,1)'::tid)
27322686 -> Nested Loop
2733- Join Filter: (bmt1.c1 = c1.c1)
2687+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
27342688 -> Nested Loop
27352689 Join Filter: (bmt1.c1 = bmt4.c1)
27362690 -> Nested Loop
@@ -2739,15 +2693,29 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
27392693 Join Filter: (bmt1.c1 = bmt2.c1)
27402694 -> Tid Scan on t1 bmt1
27412695 TID Cond: (ctid = '(1,1)'::tid)
2742- Filter: (c1 <> $4)
2696+ Filter: (c1 <> $3)
27432697 -> Seq Scan on t2 bmt2
27442698 Filter: (ctid = '(1,1)'::tid)
27452699 -> Tid Scan on t3 bmt3
27462700 TID Cond: (ctid = '(1,1)'::tid)
27472701 -> Tid Scan on t4 bmt4
27482702 TID Cond: (ctid = '(1,1)'::tid)
2749- -> CTE Scan on c1
2750-(46 rows)
2703+ -> Aggregate
2704+ -> Nested Loop
2705+ Join Filter: (b1t1.c1 = b1t4.c1)
2706+ -> Nested Loop
2707+ Join Filter: (b1t1.c1 = b1t3.c1)
2708+ -> Nested Loop
2709+ Join Filter: (b1t1.c1 = b1t2.c1)
2710+ -> Tid Scan on t1 b1t1
2711+ TID Cond: (ctid = '(1,1)'::tid)
2712+ -> Seq Scan on t2 b1t2
2713+ Filter: (ctid = '(1,1)'::tid)
2714+ -> Tid Scan on t3 b1t3
2715+ TID Cond: (ctid = '(1,1)'::tid)
2716+ -> Tid Scan on t4 b1t4
2717+ TID Cond: (ctid = '(1,1)'::tid)
2718+(44 rows)
27512719
27522720 /*+
27532721 Leading(c1 bmt4 bmt3 bmt2 bmt1)
@@ -2773,33 +2741,17 @@ not used hint:
27732741 duplication hint:
27742742 error hint:
27752743
2776- QUERY PLAN
2777--------------------------------------------------------------------
2744+ QUERY PLAN
2745+-----------------------------------------------------------------------------------------
27782746 Aggregate
2779- CTE c1
2780- -> Aggregate
2781- -> Nested Loop
2782- Join Filter: (b1t2.c1 = b1t1.c1)
2783- -> Nested Loop
2784- Join Filter: (b1t3.c1 = b1t2.c1)
2785- -> Nested Loop
2786- Join Filter: (b1t3.c1 = b1t4.c1)
2787- -> Tid Scan on t3 b1t3
2788- TID Cond: (ctid = '(1,1)'::tid)
2789- -> Tid Scan on t4 b1t4
2790- TID Cond: (ctid = '(1,1)'::tid)
2791- -> Seq Scan on t2 b1t2
2792- Filter: (ctid = '(1,1)'::tid)
2793- -> Tid Scan on t1 b1t1
2794- TID Cond: (ctid = '(1,1)'::tid)
2795- InitPlan 3 (returns $2)
2747+ InitPlan 2 (returns $1)
27962748 -> Result
2797- InitPlan 2 (returns $1)
2749+ InitPlan 1 (returns $0)
27982750 -> Limit
27992751 -> Tid Scan on t1 b2t1
28002752 TID Cond: (ctid = '(1,1)'::tid)
28012753 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
2802- InitPlan 4 (returns $4)
2754+ InitPlan 3 (returns $3)
28032755 -> Aggregate
28042756 -> Tid Scan on t1 b3t1
28052757 TID Cond: (ctid = '(1,1)'::tid)
@@ -2810,18 +2762,32 @@ error hint:
28102762 -> Nested Loop
28112763 Join Filter: (bmt4.c1 = bmt3.c1)
28122764 -> Nested Loop
2813- Join Filter: (bmt4.c1 = c1.c1)
2765+ Join Filter: (bmt4.c1 = (max(b1t1.c1)))
28142766 -> Tid Scan on t4 bmt4
28152767 TID Cond: (ctid = '(1,1)'::tid)
2816- -> CTE Scan on c1
2768+ -> Aggregate
2769+ -> Nested Loop
2770+ Join Filter: (b1t2.c1 = b1t1.c1)
2771+ -> Nested Loop
2772+ Join Filter: (b1t3.c1 = b1t2.c1)
2773+ -> Nested Loop
2774+ Join Filter: (b1t3.c1 = b1t4.c1)
2775+ -> Tid Scan on t3 b1t3
2776+ TID Cond: (ctid = '(1,1)'::tid)
2777+ -> Tid Scan on t4 b1t4
2778+ TID Cond: (ctid = '(1,1)'::tid)
2779+ -> Seq Scan on t2 b1t2
2780+ Filter: (ctid = '(1,1)'::tid)
2781+ -> Tid Scan on t1 b1t1
2782+ TID Cond: (ctid = '(1,1)'::tid)
28172783 -> Tid Scan on t3 bmt3
28182784 TID Cond: (ctid = '(1,1)'::tid)
28192785 -> Seq Scan on t2 bmt2
28202786 Filter: (ctid = '(1,1)'::tid)
28212787 -> Tid Scan on t1 bmt1
28222788 TID Cond: (ctid = '(1,1)'::tid)
2823- Filter: (c1 <> $4)
2824-(46 rows)
2789+ Filter: (c1 <> $3)
2790+(44 rows)
28252791
28262792 ----
28272793 ---- No. L-2-3 RULE or VIEW
--- a/expected/ut-R.out
+++ b/expected/ut-R.out
@@ -747,20 +747,18 @@ error hint:
747747 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;
748748 \o
749749 \! sql/maskout.sh results/ut-R.tmpout
750- QUERY PLAN
751---------------------------------------------------------------------------------------------------
750+ QUERY PLAN
751+------------------------------------------------------------------------------------------------
752752 Nested Loop (cost=xxx..xxx rows=1 width=xxx)
753- CTE c1
754- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
755- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
756- Merge Cond: (t1_1.c1 = t2.c1)
757- -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
758- -> Sort (cost=xxx..xxx rows=100 width=xxx)
759- Sort Key: t2.c1
760- -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
761- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
753+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
754+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
755+ Merge Cond: (t1_1.c1 = t2.c1)
756+ -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
757+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
758+ Sort Key: t2.c1
759+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
762760 -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
763- Index Cond: (c1 = c1.c1)
761+ Index Cond: (c1 = (max(t1_1.c1)))
764762
765763 \o results/ut-R.tmpout
766764 /*+Rows(t1 t2 #1)Rows(t1 c1 +1)*/
@@ -775,20 +773,18 @@ error hint:
775773
776774 \o
777775 \! sql/maskout.sh results/ut-R.tmpout
778- QUERY PLAN
779---------------------------------------------------------------------------------------------------
776+ QUERY PLAN
777+------------------------------------------------------------------------------------------------
780778 Nested Loop (cost=xxx..xxx rows=2 width=xxx)
781- CTE c1
782- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
783- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
784- Merge Cond: (t1_1.c1 = t2.c1)
785- -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
786- -> Sort (cost=xxx..xxx rows=100 width=xxx)
787- Sort Key: t2.c1
788- -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
789- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
779+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
780+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
781+ Merge Cond: (t1_1.c1 = t2.c1)
782+ -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
783+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
784+ Sort Key: t2.c1
785+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
790786 -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
791- Index Cond: (c1 = c1.c1)
787+ Index Cond: (c1 = (max(t1_1.c1)))
792788
793789 -- No. R-1-6-10
794790 \o results/ut-R.tmpout
@@ -2019,38 +2015,9 @@ error hint:
20192015
20202016 \o
20212017 \! sql/maskout.sh results/ut-R.tmpout
2022- QUERY PLAN
2023---------------------------------------------------------------------------------------------------------------------
2018+ QUERY PLAN
2019+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20242020 Aggregate (cost=xxx..xxx rows=1 width=xxx)
2025- CTE c1
2026- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2027- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2028- Join Filter: (b1t2.c1 = b1t1.c1)
2029- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
2030- Hash Cond: (b1t4.c1 = b1t2.c1)
2031- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2032- -> Hash (cost=xxx..xxx rows=100 width=xxx)
2033- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
2034- Merge Cond: (b1t3.c1 = b1t2.c1)
2035- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2036- -> Sort (cost=xxx..xxx rows=100 width=xxx)
2037- Sort Key: b1t2.c1
2038- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2039- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2040- Index Cond: (c1 = b1t3.c1)
2041- CTE c2
2042- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2043- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2044- -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
2045- Hash Cond: (b2t3.c1 = b2t1.c1)
2046- -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
2047- Merge Cond: (b2t3.c1 = b2t4.c1)
2048- -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
2049- -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
2050- -> Hash (cost=xxx..xxx rows=1000 width=xxx)
2051- -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
2052- -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
2053- Index Cond: (c1 = b2t1.c1)
20542021 -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
20552022 Hash Cond: (bmt4.c1 = bmt1.c1)
20562023 -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
@@ -2062,17 +2029,42 @@ error hint:
20622029 Sort Key: bmt1.c1
20632030 -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
20642031 -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2065- Hash Cond: (bmt1.c1 = c1.c1)
2032+ Hash Cond: (bmt1.c1 = (max(b1t1.c1)))
20662033 -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
20672034 -> Hash (cost=xxx..xxx rows=1 width=xxx)
20682035 -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2069- Merge Cond: (c1.c1 = c2.c1)
2036+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
20702037 -> Sort (cost=xxx..xxx rows=1 width=xxx)
2071- Sort Key: c1.c1
2072- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
2038+ Sort Key: (max(b1t1.c1))
2039+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2040+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2041+ Join Filter: (b1t2.c1 = b1t1.c1)
2042+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
2043+ Hash Cond: (b1t4.c1 = b1t2.c1)
2044+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2045+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
2046+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
2047+ Merge Cond: (b1t3.c1 = b1t2.c1)
2048+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2049+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
2050+ Sort Key: b1t2.c1
2051+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2052+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2053+ Index Cond: (c1 = b1t3.c1)
20732054 -> Sort (cost=xxx..xxx rows=1 width=xxx)
2074- Sort Key: c2.c1
2075- -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx)
2055+ Sort Key: (max(b2t1.c1))
2056+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2057+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2058+ -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
2059+ Hash Cond: (b2t3.c1 = b2t1.c1)
2060+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
2061+ Merge Cond: (b2t3.c1 = b2t4.c1)
2062+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
2063+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
2064+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
2065+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
2066+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
2067+ Index Cond: (c1 = b2t1.c1)
20762068 -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
20772069 Index Cond: (c1 = bmt1.c1)
20782070
@@ -2133,38 +2125,9 @@ error hint:
21332125
21342126 \o
21352127 \! sql/maskout.sh results/ut-R.tmpout
2136- QUERY PLAN
2137---------------------------------------------------------------------------------------------------------------------
2128+ QUERY PLAN
2129+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
21382130 Aggregate (cost=xxx..xxx rows=1 width=xxx)
2139- CTE c1
2140- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2141- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2142- Join Filter: (b1t2.c1 = b1t1.c1)
2143- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2144- Hash Cond: (b1t4.c1 = b1t2.c1)
2145- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2146- -> Hash (cost=xxx..xxx rows=1 width=xxx)
2147- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2148- Merge Cond: (b1t3.c1 = b1t2.c1)
2149- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2150- -> Sort (cost=xxx..xxx rows=100 width=xxx)
2151- Sort Key: b1t2.c1
2152- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2153- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2154- Index Cond: (c1 = b1t3.c1)
2155- CTE c2
2156- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2157- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2158- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2159- Hash Cond: (b2t1.c1 = b2t3.c1)
2160- -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
2161- -> Hash (cost=xxx..xxx rows=1 width=xxx)
2162- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2163- Merge Cond: (b2t3.c1 = b2t4.c1)
2164- -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
2165- -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
2166- -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
2167- Index Cond: (c1 = b2t1.c1)
21682131 -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
21692132 Hash Cond: (bmt4.c1 = bmt1.c1)
21702133 -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
@@ -2176,17 +2139,42 @@ error hint:
21762139 Sort Key: bmt1.c1
21772140 -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
21782141 -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2179- Hash Cond: (bmt1.c1 = c1.c1)
2142+ Hash Cond: (bmt1.c1 = (max(b1t1.c1)))
21802143 -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
21812144 -> Hash (cost=xxx..xxx rows=1 width=xxx)
21822145 -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2183- Merge Cond: (c1.c1 = c2.c1)
2146+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
21842147 -> Sort (cost=xxx..xxx rows=1 width=xxx)
2185- Sort Key: c1.c1
2186- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
2148+ Sort Key: (max(b1t1.c1))
2149+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2150+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2151+ Join Filter: (b1t2.c1 = b1t1.c1)
2152+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2153+ Hash Cond: (b1t4.c1 = b1t2.c1)
2154+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2155+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
2156+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2157+ Merge Cond: (b1t3.c1 = b1t2.c1)
2158+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2159+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
2160+ Sort Key: b1t2.c1
2161+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2162+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2163+ Index Cond: (c1 = b1t3.c1)
21872164 -> Sort (cost=xxx..xxx rows=1 width=xxx)
2188- Sort Key: c2.c1
2189- -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx)
2165+ Sort Key: (max(b2t1.c1))
2166+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2167+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2168+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2169+ Hash Cond: (b2t1.c1 = b2t3.c1)
2170+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
2171+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
2172+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2173+ Merge Cond: (b2t3.c1 = b2t4.c1)
2174+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
2175+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
2176+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
2177+ Index Cond: (c1 = b2t1.c1)
21902178 -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
21912179 Index Cond: (c1 = bmt1.c1)
21922180
@@ -2245,51 +2233,9 @@ error hint:
22452233
22462234 \o
22472235 \! sql/maskout.sh results/ut-R.tmpout
2248- QUERY PLAN
2249---------------------------------------------------------------------------------------------------------------------
2236+ QUERY PLAN
2237+------------------------------------------------------------------------------------------------------------------------------------------------------------------
22502238 Aggregate (cost=xxx..xxx rows=1 width=xxx)
2251- CTE c1
2252- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2253- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2254- Join Filter: (b1t2.c1 = b1t1.c1)
2255- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
2256- Hash Cond: (b1t4.c1 = b1t2.c1)
2257- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2258- -> Hash (cost=xxx..xxx rows=100 width=xxx)
2259- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
2260- Merge Cond: (b1t3.c1 = b1t2.c1)
2261- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2262- -> Sort (cost=xxx..xxx rows=100 width=xxx)
2263- Sort Key: b1t2.c1
2264- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2265- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2266- Index Cond: (c1 = b1t3.c1)
2267- CTE c2
2268- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2269- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2270- -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
2271- Hash Cond: (b2t3.c1 = b2t1.c1)
2272- -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
2273- Merge Cond: (b2t3.c1 = b2t4.c1)
2274- -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
2275- -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
2276- -> Hash (cost=xxx..xxx rows=1000 width=xxx)
2277- -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
2278- -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
2279- Index Cond: (c1 = b2t1.c1)
2280- CTE c3
2281- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2282- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2283- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
2284- Hash Cond: (b3t1.c1 = b3t2.c1)
2285- -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
2286- Merge Cond: (b3t1.c1 = b3t4.c1)
2287- -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
2288- -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
2289- -> Hash (cost=xxx..xxx rows=100 width=xxx)
2290- -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
2291- -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
2292- Index Cond: (c1 = b3t1.c1)
22932239 -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
22942240 -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
22952241 Hash Cond: (bmt3.c1 = bmt1.c1)
@@ -2300,21 +2246,57 @@ error hint:
23002246 -> Sort (cost=xxx..xxx rows=1 width=xxx)
23012247 Sort Key: bmt1.c1
23022248 -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2303- Join Filter: (c1.c1 = bmt1.c1)
2249+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
23042250 -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2305- Hash Cond: (c2.c1 = c1.c1)
2251+ Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1)))
23062252 -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2307- Merge Cond: (c2.c1 = c3.c1)
2253+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
23082254 -> Sort (cost=xxx..xxx rows=1 width=xxx)
2309- Sort Key: c2.c1
2310- -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx)
2255+ Sort Key: (max(b2t1.c1))
2256+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2257+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2258+ -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
2259+ Hash Cond: (b2t3.c1 = b2t1.c1)
2260+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
2261+ Merge Cond: (b2t3.c1 = b2t4.c1)
2262+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
2263+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
2264+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
2265+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
2266+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
2267+ Index Cond: (c1 = b2t1.c1)
23112268 -> Sort (cost=xxx..xxx rows=1 width=xxx)
2312- Sort Key: c3.c1
2313- -> CTE Scan on c3 (cost=xxx..xxx rows=1 width=xxx)
2269+ Sort Key: (max(b3t1.c1))
2270+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2271+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2272+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
2273+ Hash Cond: (b3t1.c1 = b3t2.c1)
2274+ -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
2275+ Merge Cond: (b3t1.c1 = b3t4.c1)
2276+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
2277+ -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
2278+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
2279+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
2280+ -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
2281+ Index Cond: (c1 = b3t1.c1)
23142282 -> Hash (cost=xxx..xxx rows=1 width=xxx)
2315- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
2283+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2284+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2285+ Join Filter: (b1t2.c1 = b1t1.c1)
2286+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
2287+ Hash Cond: (b1t4.c1 = b1t2.c1)
2288+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2289+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
2290+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
2291+ Merge Cond: (b1t3.c1 = b1t2.c1)
2292+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2293+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
2294+ Sort Key: b1t2.c1
2295+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2296+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2297+ Index Cond: (c1 = b1t3.c1)
23162298 -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
2317- Index Cond: (c1 = c2.c1)
2299+ Index Cond: (c1 = (max(b2t1.c1)))
23182300 -> Sort (cost=xxx..xxx rows=100 width=xxx)
23192301 Sort Key: bmt2.c1
23202302 -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
@@ -2394,51 +2376,9 @@ error hint:
23942376
23952377 \o
23962378 \! sql/maskout.sh results/ut-R.tmpout
2397- QUERY PLAN
2398---------------------------------------------------------------------------------------------------------------------
2379+ QUERY PLAN
2380+------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23992381 Aggregate (cost=xxx..xxx rows=1 width=xxx)
2400- CTE c1
2401- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2402- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2403- Join Filter: (b1t2.c1 = b1t1.c1)
2404- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2405- Hash Cond: (b1t4.c1 = b1t2.c1)
2406- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2407- -> Hash (cost=xxx..xxx rows=1 width=xxx)
2408- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2409- Merge Cond: (b1t3.c1 = b1t2.c1)
2410- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2411- -> Sort (cost=xxx..xxx rows=100 width=xxx)
2412- Sort Key: b1t2.c1
2413- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2414- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2415- Index Cond: (c1 = b1t3.c1)
2416- CTE c2
2417- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2418- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2419- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2420- Hash Cond: (b2t1.c1 = b2t3.c1)
2421- -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
2422- -> Hash (cost=xxx..xxx rows=1 width=xxx)
2423- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2424- Merge Cond: (b2t3.c1 = b2t4.c1)
2425- -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
2426- -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
2427- -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
2428- Index Cond: (c1 = b2t1.c1)
2429- CTE c3
2430- -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2431- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2432- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2433- Hash Cond: (b3t1.c1 = b3t2.c1)
2434- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2435- Merge Cond: (b3t1.c1 = b3t4.c1)
2436- -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
2437- -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
2438- -> Hash (cost=xxx..xxx rows=100 width=xxx)
2439- -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
2440- -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
2441- Index Cond: (c1 = b3t1.c1)
24422382 -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
24432383 -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
24442384 Hash Cond: (bmt3.c1 = bmt1.c1)
@@ -2449,21 +2389,57 @@ error hint:
24492389 -> Sort (cost=xxx..xxx rows=1 width=xxx)
24502390 Sort Key: bmt1.c1
24512391 -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2452- Join Filter: (c1.c1 = bmt1.c1)
2392+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
24532393 -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2454- Hash Cond: (c2.c1 = c1.c1)
2394+ Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1)))
24552395 -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2456- Merge Cond: (c2.c1 = c3.c1)
2396+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
24572397 -> Sort (cost=xxx..xxx rows=1 width=xxx)
2458- Sort Key: c2.c1
2459- -> CTE Scan on c2 (cost=xxx..xxx rows=1 width=xxx)
2398+ Sort Key: (max(b2t1.c1))
2399+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2400+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2401+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2402+ Hash Cond: (b2t1.c1 = b2t3.c1)
2403+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
2404+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
2405+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2406+ Merge Cond: (b2t3.c1 = b2t4.c1)
2407+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
2408+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
2409+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
2410+ Index Cond: (c1 = b2t1.c1)
24602411 -> Sort (cost=xxx..xxx rows=1 width=xxx)
2461- Sort Key: c3.c1
2462- -> CTE Scan on c3 (cost=xxx..xxx rows=1 width=xxx)
2412+ Sort Key: (max(b3t1.c1))
2413+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2414+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2415+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2416+ Hash Cond: (b3t1.c1 = b3t2.c1)
2417+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2418+ Merge Cond: (b3t1.c1 = b3t4.c1)
2419+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
2420+ -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
2421+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
2422+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
2423+ -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
2424+ Index Cond: (c1 = b3t1.c1)
24632425 -> Hash (cost=xxx..xxx rows=1 width=xxx)
2464- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
2426+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
2427+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2428+ Join Filter: (b1t2.c1 = b1t1.c1)
2429+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2430+ Hash Cond: (b1t4.c1 = b1t2.c1)
2431+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2432+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
2433+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2434+ Merge Cond: (b1t3.c1 = b1t2.c1)
2435+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2436+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
2437+ Sort Key: b1t2.c1
2438+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2439+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2440+ Index Cond: (c1 = b1t3.c1)
24652441 -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
2466- Index Cond: (c1 = c2.c1)
2442+ Index Cond: (c1 = (max(b2t1.c1)))
24672443 -> Sort (cost=xxx..xxx rows=100 width=xxx)
24682444 Sort Key: bmt2.c1
24692445 -> 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
24922468 );
24932469 LOG: pg_hint_plan:
24942470 used hint:
2495-Leading(c1 bmt1)
24962471 not used hint:
2472+Leading(c1 bmt1)
24972473 duplication hint:
24982474 error hint:
24992475
@@ -2502,20 +2478,17 @@ error hint:
25022478 QUERY PLAN
25032479 ----------------------------------------------------------------------------------
25042480 Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2505- CTE c1
2506- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2507- Index Cond: (c1 = 1)
2508- InitPlan 2 (returns $1)
2481+ InitPlan 1 (returns $0)
25092482 -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
25102483 Index Cond: (c1 = 1)
2511- InitPlan 3 (returns $2)
2484+ InitPlan 2 (returns $1)
25122485 -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1 width=xxx)
25132486 Index Cond: (c1 = 1)
25142487 -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
25152488 Index Cond: (c1 = 1)
2516- Filter: (c1 <> $2)
2517- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
2518- Filter: (c1 = 1)
2489+ Filter: (c1 <> $1)
2490+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2491+ Index Cond: (c1 = 1)
25192492
25202493 \o results/ut-R.tmpout
25212494 /*+
@@ -2539,12 +2512,12 @@ SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1
25392512 );
25402513 LOG: pg_hint_plan:
25412514 used hint:
2542-Leading(c1 bmt1)
2543-Rows(bmt1 c1 #1)
25442515 not used hint:
2516+Leading(c1 bmt1)
25452517 Rows(b1t1 c1 #1)
25462518 Rows(b2t1 c1 #1)
25472519 Rows(b3t1 c1 #1)
2520+Rows(bmt1 c1 #1)
25482521 duplication hint:
25492522 error hint:
25502523
@@ -2553,20 +2526,17 @@ error hint:
25532526 QUERY PLAN
25542527 ----------------------------------------------------------------------------------
25552528 Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2556- CTE c1
2557- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2558- Index Cond: (c1 = 1)
2559- InitPlan 2 (returns $1)
2529+ InitPlan 1 (returns $0)
25602530 -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
25612531 Index Cond: (c1 = 1)
2562- InitPlan 3 (returns $2)
2532+ InitPlan 2 (returns $1)
25632533 -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1 width=xxx)
25642534 Index Cond: (c1 = 1)
25652535 -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
25662536 Index Cond: (c1 = 1)
2567- Filter: (c1 <> $2)
2568- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
2569- Filter: (c1 = 1)
2537+ Filter: (c1 <> $1)
2538+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
2539+ Index Cond: (c1 = 1)
25702540
25712541 -- No. R-2-2-2
25722542 \o results/ut-R.tmpout
@@ -2598,54 +2568,48 @@ used hint:
25982568 MergeJoin(b1t1 b1t2)
25992569 MergeJoin(b2t1 b2t2)
26002570 MergeJoin(b3t1 b3t2)
2601-MergeJoin(bmt2 c1)
2602-HashJoin(bmt1 bmt2 c1)
2603-Leading(c1 bmt2 bmt1)
26042571 Leading(b1t2 b1t1)
26052572 Leading(b2t2 b2t1)
26062573 Leading(b3t2 b3t1)
26072574 not used hint:
2575+MergeJoin(bmt2 c1)
2576+HashJoin(bmt1 bmt2 c1)
2577+Leading(c1 bmt2 bmt1)
26082578 duplication hint:
26092579 error hint:
26102580
26112581 \o
26122582 \! sql/maskout.sh results/ut-R.tmpout
2613- QUERY PLAN
2614---------------------------------------------------------------------------------------------
2615- Hash Join (cost=xxx..xxx rows=10 width=xxx)
2616- Hash Cond: (bmt1.c1 = bmt2.c1)
2617- CTE c1
2618- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
2619- Merge Cond: (b1t1.c1 = b1t2.c1)
2620- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
2621- -> Sort (cost=xxx..xxx rows=100 width=xxx)
2622- Sort Key: b1t2.c1
2623- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2624- InitPlan 2 (returns $1)
2583+ QUERY PLAN
2584+------------------------------------------------------------------------------------------------
2585+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
2586+ Join Filter: (bmt1.c1 = bmt2.c1)
2587+ InitPlan 1 (returns $0)
26252588 -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
26262589 Merge Cond: (b2t1.c1 = b2t2.c1)
26272590 -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
26282591 -> Sort (cost=xxx..xxx rows=100 width=xxx)
26292592 Sort Key: b2t2.c1
26302593 -> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=100 width=xxx)
2631- InitPlan 3 (returns $2)
2594+ InitPlan 2 (returns $1)
26322595 -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
26332596 Merge Cond: (b3t1.c1 = b3t2.c1)
26342597 -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
26352598 -> Sort (cost=xxx..xxx rows=100 width=xxx)
26362599 Sort Key: b3t2.c1
26372600 -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
2638- -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
2639- Filter: (c1 <> $2)
2640- -> Hash (cost=xxx..xxx rows=100 width=xxx)
2601+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
26412602 -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
2642- Merge Cond: (bmt2.c1 = c1.c1)
2603+ Merge Cond: (b1t1.c1 = b1t2.c1)
2604+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
26432605 -> Sort (cost=xxx..xxx rows=100 width=xxx)
2644- Sort Key: bmt2.c1
2645- -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
2646- -> Sort (cost=xxx..xxx rows=100 width=xxx)
2647- Sort Key: c1.c1
2648- -> CTE Scan on c1 (cost=xxx..xxx rows=100 width=xxx)
2606+ Sort Key: b1t2.c1
2607+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2608+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
2609+ Index Cond: (c1 = b1t1.c1)
2610+ Filter: (c1 <> $1)
2611+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
2612+ Index Cond: (c1 = b1t1.c1)
26492613
26502614 \o results/ut-R.tmpout
26512615 /*+
@@ -2682,59 +2646,53 @@ used hint:
26822646 MergeJoin(b1t1 b1t2)
26832647 MergeJoin(b2t1 b2t2)
26842648 MergeJoin(b3t1 b3t2)
2685-MergeJoin(bmt2 c1)
2686-HashJoin(bmt1 bmt2 c1)
2687-Leading(c1 bmt2 bmt1)
26882649 Leading(b1t2 b1t1)
26892650 Leading(b2t2 b2t1)
26902651 Leading(b3t2 b3t1)
26912652 Rows(b1t1 b1t2 #1)
26922653 Rows(b2t1 b2t2 #1)
26932654 Rows(b3t1 b3t2 #1)
2655+not used hint:
2656+MergeJoin(bmt2 c1)
2657+HashJoin(bmt1 bmt2 c1)
2658+Leading(c1 bmt2 bmt1)
26942659 Rows(bmt2 c1 #1)
26952660 Rows(bmt1 bmt2 c1 #1)
2696-not used hint:
26972661 duplication hint:
26982662 error hint:
26992663
27002664 \o
27012665 \! sql/maskout.sh results/ut-R.tmpout
2702- QUERY PLAN
2703---------------------------------------------------------------------------------------------
2704- Hash Join (cost=xxx..xxx rows=1 width=xxx)
2705- Hash Cond: (bmt1.c1 = bmt2.c1)
2706- CTE c1
2707- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2708- Merge Cond: (b1t1.c1 = b1t2.c1)
2709- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
2710- -> Sort (cost=xxx..xxx rows=100 width=xxx)
2711- Sort Key: b1t2.c1
2712- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2713- InitPlan 2 (returns $1)
2666+ QUERY PLAN
2667+------------------------------------------------------------------------------------------------
2668+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
2669+ Join Filter: (bmt1.c1 = bmt2.c1)
2670+ InitPlan 1 (returns $0)
27142671 -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
27152672 Merge Cond: (b2t1.c1 = b2t2.c1)
27162673 -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
27172674 -> Sort (cost=xxx..xxx rows=100 width=xxx)
27182675 Sort Key: b2t2.c1
27192676 -> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=100 width=xxx)
2720- InitPlan 3 (returns $2)
2677+ InitPlan 2 (returns $1)
27212678 -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
27222679 Merge Cond: (b3t1.c1 = b3t2.c1)
27232680 -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
27242681 -> Sort (cost=xxx..xxx rows=100 width=xxx)
27252682 Sort Key: b3t2.c1
27262683 -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
2727- -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
2728- Filter: (c1 <> $2)
2729- -> Hash (cost=xxx..xxx rows=1 width=xxx)
2684+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
27302685 -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2731- Merge Cond: (bmt2.c1 = c1.c1)
2686+ Merge Cond: (b1t1.c1 = b1t2.c1)
2687+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
27322688 -> Sort (cost=xxx..xxx rows=100 width=xxx)
2733- Sort Key: bmt2.c1
2734- -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
2735- -> Sort (cost=xxx..xxx rows=1 width=xxx)
2736- Sort Key: c1.c1
2737- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
2689+ Sort Key: b1t2.c1
2690+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
2691+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
2692+ Index Cond: (c1 = b1t1.c1)
2693+ Filter: (c1 <> $1)
2694+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
2695+ Index Cond: (c1 = b1t1.c1)
27382696
27392697 -- No. R-2-2-3
27402698 \o results/ut-R.tmpout
@@ -2773,45 +2731,31 @@ used hint:
27732731 HashJoin(b1t3 b1t4)
27742732 HashJoin(b2t3 b2t4)
27752733 HashJoin(b3t3 b3t4)
2776-MergeJoin(bmt4 c1)
27772734 NestLoop(b1t2 b1t3 b1t4)
27782735 NestLoop(b2t2 b2t3 b2t4)
27792736 NestLoop(b3t2 b3t3 b3t4)
2780-HashJoin(bmt3 bmt4 c1)
27812737 MergeJoin(b1t1 b1t2 b1t3 b1t4)
27822738 MergeJoin(b2t1 b2t2 b2t3 b2t4)
27832739 MergeJoin(b3t1 b3t2 b3t3 b3t4)
2784-NestLoop(bmt2 bmt3 bmt4 c1)
2785-MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
2786-Leading(c1 bmt4 bmt3 bmt2 bmt1)
27872740 Leading(b1t4 b1t3 b1t2 b1t1)
27882741 Leading(b2t4 b2t3 b2t2 b2t1)
27892742 Leading(b3t4 b3t3 b3t2 b3t1)
27902743 not used hint:
2744+MergeJoin(bmt4 c1)
2745+HashJoin(bmt3 bmt4 c1)
2746+NestLoop(bmt2 bmt3 bmt4 c1)
2747+MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
2748+Leading(c1 bmt4 bmt3 bmt2 bmt1)
27912749 duplication hint:
27922750 error hint:
27932751
27942752 \o
27952753 \! sql/maskout.sh results/ut-R.tmpout
2796- QUERY PLAN
2797-------------------------------------------------------------------------------------------------------------------
2798- Merge Join (cost=xxx..xxx rows=10 width=xxx)
2799- Merge Cond: (bmt1.c1 = bmt2.c1)
2800- CTE c1
2801- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
2802- Merge Cond: (b1t1.c1 = b1t2.c1)
2803- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
2804- -> Sort (cost=xxx..xxx rows=100 width=xxx)
2805- Sort Key: b1t2.c1
2806- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2807- -> Hash Join (cost=xxx..xxx rows=1130 width=xxx)
2808- Hash Cond: (b1t3.c1 = b1t4.c1)
2809- -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2810- -> Hash (cost=xxx..xxx rows=1130 width=xxx)
2811- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2812- -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
2813- Index Cond: (c1 = b1t3.c1)
2814- InitPlan 2 (returns $3)
2754+ QUERY PLAN
2755+--------------------------------------------------------------------------------------------------------------------
2756+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
2757+ Join Filter: (bmt1.c1 = bmt4.c1)
2758+ InitPlan 1 (returns $1)
28152759 -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
28162760 Merge Cond: (b2t1.c1 = b2t2.c1)
28172761 -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
@@ -2825,7 +2769,7 @@ error hint:
28252769 -> Seq Scan on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
28262770 -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
28272771 Index Cond: (c1 = b2t3.c1)
2828- InitPlan 3 (returns $5)
2772+ InitPlan 2 (returns $3)
28292773 -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
28302774 Merge Cond: (b3t1.c1 = b3t2.c1)
28312775 -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
@@ -2839,23 +2783,34 @@ error hint:
28392783 -> Seq Scan on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
28402784 -> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx..xxx rows=1 width=xxx)
28412785 Index Cond: (c1 = b3t3.c1)
2842- -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
2843- Filter: (c1 <> $5)
2844- -> Sort (cost=xxx..xxx rows=100 width=xxx)
2845- Sort Key: bmt2.c1
2846- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2847- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
2848- Hash Cond: (bmt3.c1 = bmt4.c1)
2849- -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
2850- -> Hash (cost=xxx..xxx rows=100 width=xxx)
2851- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
2852- Merge Cond: (bmt4.c1 = c1.c1)
2853- -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
2854- -> Sort (cost=xxx..xxx rows=100 width=xxx)
2855- Sort Key: c1.c1
2856- -> CTE Scan on c1 (cost=xxx..xxx rows=100 width=xxx)
2786+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
2787+ Join Filter: (bmt1.c1 = bmt3.c1)
2788+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
2789+ Join Filter: (bmt1.c1 = bmt2.c1)
2790+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
2791+ Join Filter: (b1t1.c1 = bmt1.c1)
2792+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
2793+ Merge Cond: (b1t1.c1 = b1t2.c1)
2794+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
2795+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
2796+ Sort Key: b1t2.c1
2797+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
2798+ -> Hash Join (cost=xxx..xxx rows=1130 width=xxx)
2799+ Hash Cond: (b1t3.c1 = b1t4.c1)
2800+ -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2801+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
2802+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2803+ -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
2804+ Index Cond: (c1 = b1t3.c1)
2805+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
2806+ Index Cond: (c1 = b1t3.c1)
2807+ Filter: (c1 <> $3)
28572808 -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
2858- Index Cond: (c1 = bmt3.c1)
2809+ Index Cond: (c1 = b1t3.c1)
2810+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
2811+ Index Cond: (c1 = b1t3.c1)
2812+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
2813+ Index Cond: (c1 = bmt3.c1)
28592814
28602815 \o results/ut-R.tmpout
28612816 /*+
@@ -2906,58 +2861,44 @@ used hint:
29062861 HashJoin(b1t3 b1t4)
29072862 HashJoin(b2t3 b2t4)
29082863 HashJoin(b3t3 b3t4)
2909-MergeJoin(bmt4 c1)
29102864 NestLoop(b1t2 b1t3 b1t4)
29112865 NestLoop(b2t2 b2t3 b2t4)
29122866 NestLoop(b3t2 b3t3 b3t4)
2913-HashJoin(bmt3 bmt4 c1)
29142867 MergeJoin(b1t1 b1t2 b1t3 b1t4)
29152868 MergeJoin(b2t1 b2t2 b2t3 b2t4)
29162869 MergeJoin(b3t1 b3t2 b3t3 b3t4)
2917-NestLoop(bmt2 bmt3 bmt4 c1)
2918-MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
2919-Leading(c1 bmt4 bmt3 bmt2 bmt1)
29202870 Leading(b1t4 b1t3 b1t2 b1t1)
29212871 Leading(b2t4 b2t3 b2t2 b2t1)
29222872 Leading(b3t4 b3t3 b3t2 b3t1)
29232873 Rows(b1t3 b1t4 #1)
29242874 Rows(b2t3 b2t4 #1)
29252875 Rows(b3t3 b3t4 #1)
2926-Rows(bmt4 c1 #1)
29272876 Rows(b1t2 b1t3 b1t4 #1)
29282877 Rows(b2t2 b2t3 b2t4 #1)
29292878 Rows(b3t2 b3t3 b3t4 #1)
2930-Rows(bmt3 bmt4 c1 #1)
29312879 Rows(b1t1 b1t2 b1t3 b1t4 #1)
29322880 Rows(b2t1 b2t2 b2t3 b2t4 #1)
29332881 Rows(b3t1 b3t2 b3t3 b3t4 #1)
2882+not used hint:
2883+MergeJoin(bmt4 c1)
2884+HashJoin(bmt3 bmt4 c1)
2885+NestLoop(bmt2 bmt3 bmt4 c1)
2886+MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
2887+Leading(c1 bmt4 bmt3 bmt2 bmt1)
2888+Rows(bmt4 c1 #1)
2889+Rows(bmt3 bmt4 c1 #1)
29342890 Rows(bmt2 bmt3 bmt4 c1 #1)
29352891 Rows(bmt1 bmt2 bmt3 bmt4 c1 #1)
2936-not used hint:
29372892 duplication hint:
29382893 error hint:
29392894
29402895 \o
29412896 \! sql/maskout.sh results/ut-R.tmpout
2942- QUERY PLAN
2943-------------------------------------------------------------------------------------------------------------------
2944- Merge Join (cost=xxx..xxx rows=1 width=xxx)
2945- Merge Cond: (bmt1.c1 = bmt2.c1)
2946- CTE c1
2947- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2948- Merge Cond: (b1t1.c1 = b1t2.c1)
2949- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
2950- -> Sort (cost=xxx..xxx rows=1 width=xxx)
2951- Sort Key: b1t2.c1
2952- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2953- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2954- Hash Cond: (b1t3.c1 = b1t4.c1)
2955- -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2956- -> Hash (cost=xxx..xxx rows=1130 width=xxx)
2957- -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2958- -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
2959- Index Cond: (c1 = b1t3.c1)
2960- InitPlan 2 (returns $3)
2897+ QUERY PLAN
2898+--------------------------------------------------------------------------------------------------------------------
2899+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
2900+ Join Filter: (bmt1.c1 = bmt4.c1)
2901+ InitPlan 1 (returns $1)
29612902 -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
29622903 Merge Cond: (b2t1.c1 = b2t2.c1)
29632904 -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
@@ -2971,7 +2912,7 @@ error hint:
29712912 -> Seq Scan on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
29722913 -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
29732914 Index Cond: (c1 = b2t3.c1)
2974- InitPlan 3 (returns $5)
2915+ InitPlan 2 (returns $3)
29752916 -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
29762917 Merge Cond: (b3t1.c1 = b3t2.c1)
29772918 -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
@@ -2985,23 +2926,34 @@ error hint:
29852926 -> Seq Scan on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
29862927 -> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx..xxx rows=1 width=xxx)
29872928 Index Cond: (c1 = b3t3.c1)
2988- -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
2989- Filter: (c1 <> $5)
2990- -> Sort (cost=xxx..xxx rows=1 width=xxx)
2991- Sort Key: bmt2.c1
2992- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2993- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2994- Hash Cond: (bmt3.c1 = bmt4.c1)
2995- -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
2996- -> Hash (cost=xxx..xxx rows=1 width=xxx)
2997- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2998- Merge Cond: (bmt4.c1 = c1.c1)
2999- -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
3000- -> Sort (cost=xxx..xxx rows=1 width=xxx)
3001- Sort Key: c1.c1
3002- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
2929+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
2930+ Join Filter: (bmt1.c1 = bmt3.c1)
2931+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
2932+ Join Filter: (bmt1.c1 = bmt2.c1)
2933+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
2934+ Join Filter: (b1t1.c1 = bmt1.c1)
2935+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
2936+ Merge Cond: (b1t1.c1 = b1t2.c1)
2937+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
2938+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
2939+ Sort Key: b1t2.c1
2940+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
2941+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
2942+ Hash Cond: (b1t3.c1 = b1t4.c1)
2943+ -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
2944+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
2945+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
2946+ -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
2947+ Index Cond: (c1 = b1t3.c1)
2948+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
2949+ Index Cond: (c1 = b1t3.c1)
2950+ Filter: (c1 <> $3)
30032951 -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
3004- Index Cond: (c1 = bmt3.c1)
2952+ Index Cond: (c1 = b1t3.c1)
2953+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
2954+ Index Cond: (c1 = b1t3.c1)
2955+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
2956+ Index Cond: (c1 = bmt3.c1)
30052957
30062958 -- No. R-2-2-4
30072959 \o results/ut-R.tmpout
@@ -3030,59 +2982,56 @@ SELECT b3t1.c1 FROM s1.t1 b3t1
30302982 LOG: pg_hint_plan:
30312983 used hint:
30322984 MergeJoin(b1t3 b1t4)
3033-MergeJoin(bmt4 c1)
30342985 HashJoin(b1t2 b1t3 b1t4)
3035-HashJoin(bmt3 bmt4 c1)
30362986 NestLoop(b1t1 b1t2 b1t3 b1t4)
2987+Leading(b1t4 b1t3 b1t2 b1t1)
2988+not used hint:
2989+MergeJoin(bmt4 c1)
2990+HashJoin(bmt3 bmt4 c1)
30372991 NestLoop(bmt2 bmt3 bmt4 c1)
30382992 MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
30392993 Leading(c1 bmt4 bmt3 bmt2 bmt1)
3040-Leading(b1t4 b1t3 b1t2 b1t1)
3041-not used hint:
30422994 duplication hint:
30432995 error hint:
30442996
30452997 \o
30462998 \! sql/maskout.sh results/ut-R.tmpout
3047- QUERY PLAN
3048-------------------------------------------------------------------------------------------------------------------
3049- Merge Join (cost=xxx..xxx rows=10 width=xxx)
3050- Merge Cond: (bmt1.c1 = bmt2.c1)
3051- CTE c1
3052- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
3053- Join Filter: (b1t2.c1 = b1t1.c1)
3054- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
3055- Hash Cond: (b1t3.c1 = b1t2.c1)
3056- -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
3057- Merge Cond: (b1t3.c1 = b1t4.c1)
3058- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
3059- -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
3060- -> Hash (cost=xxx..xxx rows=100 width=xxx)
3061- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
3062- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
3063- Index Cond: (c1 = b1t3.c1)
3064- InitPlan 2 (returns $2)
2999+ QUERY PLAN
3000+------------------------------------------------------------------------------------------------------------------------------
3001+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
3002+ Join Filter: (bmt1.c1 = bmt4.c1)
3003+ InitPlan 1 (returns $0)
30653004 -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
30663005 Index Cond: (c1 = 1)
3067- InitPlan 3 (returns $3)
3006+ InitPlan 2 (returns $1)
30683007 -> Seq Scan on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
3069- -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
3070- Filter: (c1 <> $3)
3071- -> Sort (cost=xxx..xxx rows=100 width=xxx)
3072- Sort Key: bmt2.c1
3073- -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
3074- -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
3075- Hash Cond: (bmt3.c1 = bmt4.c1)
3076- -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
3077- -> Hash (cost=xxx..xxx rows=100 width=xxx)
3078- -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
3079- Merge Cond: (bmt4.c1 = c1.c1)
3080- -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
3081- -> Sort (cost=xxx..xxx rows=100 width=xxx)
3082- Sort Key: c1.c1
3083- -> CTE Scan on c1 (cost=xxx..xxx rows=100 width=xxx)
3084- -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
3085- Index Cond: (c1 = bmt3.c1)
3008+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
3009+ Join Filter: (bmt1.c1 = bmt3.c1)
3010+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
3011+ Join Filter: (bmt2.c1 = bmt1.c1)
3012+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
3013+ Join Filter: (b1t1.c1 = bmt2.c1)
3014+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
3015+ -> Materialize (cost=xxx..xxx rows=100 width=xxx)
3016+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
3017+ Join Filter: (b1t2.c1 = b1t1.c1)
3018+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
3019+ Hash Cond: (b1t3.c1 = b1t2.c1)
3020+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
3021+ Merge Cond: (b1t3.c1 = b1t4.c1)
3022+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
3023+ -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
3024+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
3025+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
3026+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
3027+ Index Cond: (c1 = b1t3.c1)
3028+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
3029+ Index Cond: (c1 = b1t3.c1)
3030+ Filter: (c1 <> $1)
3031+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
3032+ Index Cond: (c1 = b1t3.c1)
3033+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
3034+ Index Cond: (c1 = bmt3.c1)
30863035
30873036 \o results/ut-R.tmpout
30883037 /*+
@@ -3117,66 +3066,63 @@ SELECT b3t1.c1 FROM s1.t1 b3t1
31173066 LOG: pg_hint_plan:
31183067 used hint:
31193068 MergeJoin(b1t3 b1t4)
3120-MergeJoin(bmt4 c1)
31213069 HashJoin(b1t2 b1t3 b1t4)
3122-HashJoin(bmt3 bmt4 c1)
31233070 NestLoop(b1t1 b1t2 b1t3 b1t4)
3071+Leading(b1t4 b1t3 b1t2 b1t1)
3072+Rows(b1t3 b1t4 #1)
3073+Rows(b1t2 b1t3 b1t4 #1)
3074+Rows(b1t1 b1t2 b1t3 b1t4 #1)
3075+not used hint:
3076+MergeJoin(bmt4 c1)
3077+HashJoin(bmt3 bmt4 c1)
31243078 NestLoop(bmt2 bmt3 bmt4 c1)
31253079 MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
31263080 Leading(c1 bmt4 bmt3 bmt2 bmt1)
3127-Leading(b1t4 b1t3 b1t2 b1t1)
3128-Rows(b1t3 b1t4 #1)
31293081 Rows(bmt4 c1 #1)
3130-Rows(b1t2 b1t3 b1t4 #1)
31313082 Rows(bmt3 bmt4 c1 #1)
3132-Rows(b1t1 b1t2 b1t3 b1t4 #1)
31333083 Rows(bmt2 bmt3 bmt4 c1 #1)
31343084 Rows(bmt1 bmt2 bmt3 bmt4 c1 #1)
3135-not used hint:
31363085 duplication hint:
31373086 error hint:
31383087
31393088 \o
31403089 \! sql/maskout.sh results/ut-R.tmpout
3141- QUERY PLAN
3142-------------------------------------------------------------------------------------------------------------------
3143- Merge Join (cost=xxx..xxx rows=1 width=xxx)
3144- Merge Cond: (bmt1.c1 = bmt2.c1)
3145- CTE c1
3146- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
3147- Join Filter: (b1t2.c1 = b1t1.c1)
3148- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
3149- Hash Cond: (b1t3.c1 = b1t2.c1)
3150- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
3151- Merge Cond: (b1t3.c1 = b1t4.c1)
3152- -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
3153- -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
3154- -> Hash (cost=xxx..xxx rows=100 width=xxx)
3155- -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
3156- -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
3157- Index Cond: (c1 = b1t3.c1)
3158- InitPlan 2 (returns $2)
3090+ QUERY PLAN
3091+------------------------------------------------------------------------------------------------------------------------------
3092+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
3093+ Join Filter: (bmt1.c1 = bmt4.c1)
3094+ InitPlan 1 (returns $0)
31593095 -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
31603096 Index Cond: (c1 = 1)
3161- InitPlan 3 (returns $3)
3097+ InitPlan 2 (returns $1)
31623098 -> Seq Scan on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
3163- -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=999 width=xxx)
3164- Filter: (c1 <> $3)
3165- -> Sort (cost=xxx..xxx rows=1 width=xxx)
3166- Sort Key: bmt2.c1
3167- -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
3168- -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
3169- Hash Cond: (bmt3.c1 = bmt4.c1)
3170- -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
3171- -> Hash (cost=xxx..xxx rows=1 width=xxx)
3172- -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
3173- Merge Cond: (bmt4.c1 = c1.c1)
3174- -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
3175- -> Sort (cost=xxx..xxx rows=1 width=xxx)
3176- Sort Key: c1.c1
3177- -> CTE Scan on c1 (cost=xxx..xxx rows=1 width=xxx)
3178- -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
3179- Index Cond: (c1 = bmt3.c1)
3099+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
3100+ Join Filter: (bmt1.c1 = bmt3.c1)
3101+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
3102+ Join Filter: (bmt2.c1 = bmt1.c1)
3103+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
3104+ Join Filter: (b1t1.c1 = bmt2.c1)
3105+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
3106+ -> Materialize (cost=xxx..xxx rows=1 width=xxx)
3107+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
3108+ Join Filter: (b1t2.c1 = b1t1.c1)
3109+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
3110+ Hash Cond: (b1t3.c1 = b1t2.c1)
3111+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
3112+ Merge Cond: (b1t3.c1 = b1t4.c1)
3113+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
3114+ -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
3115+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
3116+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
3117+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
3118+ Index Cond: (c1 = b1t3.c1)
3119+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
3120+ Index Cond: (c1 = b1t3.c1)
3121+ Filter: (c1 <> $1)
3122+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
3123+ Index Cond: (c1 = b1t3.c1)
3124+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
3125+ Index Cond: (c1 = bmt3.c1)
31803126
31813127 ----
31823128 ---- No. R-2-3 RULE or VIEW
--- a/expected/ut-S.out
+++ b/expected/ut-S.out
@@ -540,20 +540,18 @@ error hint:
540540 -- No. S-1-5-9
541541 EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1)
542542 SELECT * FROM s1.t1, c1 WHERE t1.c1 = 1 AND t1.c1 = c1.c1;
543- QUERY PLAN
544----------------------------------------------------------------------
543+ QUERY PLAN
544+-------------------------------------------------------------------
545545 Nested Loop
546- CTE c1
547- -> Result
548- InitPlan 1 (returns $0)
549- -> Limit
550- -> Index Only Scan using t1_i1 on t1 t1_1
551- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
552546 -> Index Scan using t1_i1 on t1
553547 Index Cond: (c1 = 1)
554- -> CTE Scan on c1
555- Filter: (c1 = 1)
556-(11 rows)
548+ -> Result
549+ One-Time Filter: ($0 = 1)
550+ InitPlan 1 (returns $0)
551+ -> Limit
552+ -> Index Only Scan using t1_i1 on t1 t1_1
553+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
554+(9 rows)
557555
558556 /*+SeqScan(c1)*/
559557 EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1)
@@ -565,20 +563,18 @@ SeqScan(c1)
565563 duplication hint:
566564 error hint:
567565
568- QUERY PLAN
569----------------------------------------------------------------------
566+ QUERY PLAN
567+-------------------------------------------------------------------
570568 Nested Loop
571- CTE c1
572- -> Result
573- InitPlan 1 (returns $0)
574- -> Limit
575- -> Index Only Scan using t1_i1 on t1 t1_1
576- Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
577569 -> Index Scan using t1_i1 on t1
578570 Index Cond: (c1 = 1)
579- -> CTE Scan on c1
580- Filter: (c1 = 1)
581-(11 rows)
571+ -> Result
572+ One-Time Filter: ($0 = 1)
573+ InitPlan 1 (returns $0)
574+ -> Limit
575+ -> Index Only Scan using t1_i1 on t1 t1_1
576+ Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
577+(9 rows)
582578
583579 -- No. S-1-5-10
584580 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
12331229 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
12341230 AND bmt1.c1 = c1.c1
12351231 ;
1236- QUERY PLAN
1237-------------------------------------------------------------------------
1232+ QUERY PLAN
1233+----------------------------------------------------------------------------------------------------
12381234 Aggregate
1239- CTE c1
1240- -> Aggregate
1241- -> Nested Loop
1242- -> Merge Join
1243- Merge Cond: (b1t1.c1 = b1t2.c1)
1244- -> Merge Join
1245- Merge Cond: (b1t1.c1 = b1t3.c1)
1246- -> Index Only Scan using t1_i1 on t1 b1t1
1247- -> Index Only Scan using t3_i1 on t3 b1t3
1248- -> Sort
1249- Sort Key: b1t2.c1
1250- -> Seq Scan on t2 b1t2
1251- -> Index Only Scan using t4_i1 on t4 b1t4
1252- Index Cond: (c1 = b1t1.c1)
12531235 -> Nested Loop
12541236 -> Nested Loop
12551237 -> Nested Loop
12561238 -> Hash Join
1257- Hash Cond: (bmt2.c1 = c1.c1)
1239+ Hash Cond: (bmt2.c1 = (max(b1t1.c1)))
12581240 -> Seq Scan on t2 bmt2
12591241 -> Hash
1260- -> CTE Scan on c1
1242+ -> Aggregate
1243+ -> Nested Loop
1244+ -> Merge Join
1245+ Merge Cond: (b1t1.c1 = b1t2.c1)
1246+ -> Merge Join
1247+ Merge Cond: (b1t1.c1 = b1t3.c1)
1248+ -> Index Only Scan using t1_i1 on t1 b1t1
1249+ -> Index Only Scan using t3_i1 on t3 b1t3
1250+ -> Sort
1251+ Sort Key: b1t2.c1
1252+ -> Seq Scan on t2 b1t2
1253+ -> Index Only Scan using t4_i1 on t4 b1t4
1254+ Index Cond: (c1 = b1t1.c1)
12611255 -> Index Only Scan using t1_i1 on t1 bmt1
12621256 Index Cond: (c1 = bmt2.c1)
12631257 -> Index Only Scan using t3_i1 on t3 bmt3
12641258 Index Cond: (c1 = bmt1.c1)
12651259 -> Index Only Scan using t4_i1 on t4 bmt4
12661260 Index Cond: (c1 = bmt1.c1)
1267-(29 rows)
1261+(27 rows)
12681262
12691263 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
12701264 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
@@ -1296,43 +1290,45 @@ not used hint:
12961290 duplication hint:
12971291 error hint:
12981292
1299- QUERY PLAN
1300---------------------------------------------------------------------------
1293+ QUERY PLAN
1294+------------------------------------------------------------------------------------------------------
13011295 Aggregate
1302- CTE c1
1303- -> Aggregate
1304- -> Hash Join
1305- Hash Cond: (b1t1.c1 = b1t2.c1)
1306- -> Seq Scan on t1 b1t1
1307- -> Hash
1308- -> Nested Loop
1309- Join Filter: (b1t2.c1 = b1t3.c1)
1310- -> Nested Loop
1311- -> Seq Scan on t2 b1t2
1312- -> Bitmap Heap Scan on t4 b1t4
1313- Recheck Cond: (c1 = b1t2.c1)
1314- -> Bitmap Index Scan on t4_pkey
1315- Index Cond: (c1 = b1t2.c1)
1316- -> Index Scan using t3_pkey on t3 b1t3
1317- Index Cond: (c1 = b1t4.c1)
13181296 -> Hash Join
13191297 Hash Cond: (bmt4.c1 = bmt1.c1)
13201298 -> Seq Scan on t4 bmt4
13211299 -> Hash
13221300 -> Nested Loop
1323- -> Nested Loop
1324- -> Hash Join
1325- Hash Cond: (bmt1.c1 = c1.c1)
1326- -> Seq Scan on t1 bmt1
1327- -> Hash
1328- -> CTE Scan on c1
1329- -> Index Scan using t2_pkey on t2 bmt2
1330- Index Cond: (c1 = bmt1.c1)
1301+ -> Merge Join
1302+ Merge Cond: (bmt1.c1 = (max(b1t1.c1)))
1303+ -> Sort
1304+ Sort Key: bmt1.c1
1305+ -> Hash Join
1306+ Hash Cond: (bmt1.c1 = bmt2.c1)
1307+ -> Seq Scan on t1 bmt1
1308+ -> Hash
1309+ -> Index Scan using t2_pkey on t2 bmt2
1310+ -> Sort
1311+ Sort Key: (max(b1t1.c1))
1312+ -> Aggregate
1313+ -> Hash Join
1314+ Hash Cond: (b1t1.c1 = b1t2.c1)
1315+ -> Seq Scan on t1 b1t1
1316+ -> Hash
1317+ -> Nested Loop
1318+ Join Filter: (b1t2.c1 = b1t3.c1)
1319+ -> Nested Loop
1320+ -> Seq Scan on t2 b1t2
1321+ -> Bitmap Heap Scan on t4 b1t4
1322+ Recheck Cond: (c1 = b1t2.c1)
1323+ -> Bitmap Index Scan on t4_pkey
1324+ Index Cond: (c1 = b1t2.c1)
1325+ -> Index Scan using t3_pkey on t3 b1t3
1326+ Index Cond: (c1 = b1t4.c1)
13311327 -> Bitmap Heap Scan on t3 bmt3
13321328 Recheck Cond: (c1 = bmt1.c1)
13331329 -> Bitmap Index Scan on t3_pkey
13341330 Index Cond: (c1 = bmt1.c1)
1335-(34 rows)
1331+(36 rows)
13361332
13371333 -- No. S-2-1-8
13381334 EXPLAIN (COSTS false)
@@ -1348,55 +1344,51 @@ SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
13481344 AND bmt1.c1 = c1.c1
13491345 AND bmt1.c1 = c2.c1
13501346 ;
1351- QUERY PLAN
1352-------------------------------------------------------------------------
1347+ QUERY PLAN
1348+----------------------------------------------------------------------------------------------------------
13531349 Aggregate
1354- CTE c1
1355- -> Aggregate
1356- -> Nested Loop
1357- -> Merge Join
1358- Merge Cond: (b1t1.c1 = b1t2.c1)
1359- -> Merge Join
1360- Merge Cond: (b1t1.c1 = b1t3.c1)
1361- -> Index Only Scan using t1_i1 on t1 b1t1
1362- -> Index Only Scan using t3_i1 on t3 b1t3
1363- -> Sort
1364- Sort Key: b1t2.c1
1365- -> Seq Scan on t2 b1t2
1366- -> Index Only Scan using t4_i1 on t4 b1t4
1367- Index Cond: (c1 = b1t1.c1)
1368- CTE c2
1369- -> Aggregate
1370- -> Nested Loop
1371- -> Merge Join
1372- Merge Cond: (b2t1.c1 = b2t2.c1)
1373- -> Merge Join
1374- Merge Cond: (b2t1.c1 = b2t3.c1)
1375- -> Index Only Scan using t1_i1 on t1 b2t1
1376- -> Index Only Scan using t3_i1 on t3 b2t3
1377- -> Sort
1378- Sort Key: b2t2.c1
1379- -> Seq Scan on t2 b2t2
1380- -> Index Only Scan using t4_i1 on t4 b2t4
1381- Index Cond: (c1 = b2t1.c1)
13821350 -> Nested Loop
1383- Join Filter: (bmt1.c1 = c2.c1)
1351+ Join Filter: (bmt1.c1 = (max(b2t1.c1)))
13841352 -> Nested Loop
13851353 -> Nested Loop
13861354 -> Nested Loop
13871355 -> Hash Join
1388- Hash Cond: (bmt2.c1 = c1.c1)
1356+ Hash Cond: (bmt2.c1 = (max(b1t1.c1)))
13891357 -> Seq Scan on t2 bmt2
13901358 -> Hash
1391- -> CTE Scan on c1
1359+ -> Aggregate
1360+ -> Nested Loop
1361+ -> Merge Join
1362+ Merge Cond: (b1t1.c1 = b1t2.c1)
1363+ -> Merge Join
1364+ Merge Cond: (b1t1.c1 = b1t3.c1)
1365+ -> Index Only Scan using t1_i1 on t1 b1t1
1366+ -> Index Only Scan using t3_i1 on t3 b1t3
1367+ -> Sort
1368+ Sort Key: b1t2.c1
1369+ -> Seq Scan on t2 b1t2
1370+ -> Index Only Scan using t4_i1 on t4 b1t4
1371+ Index Cond: (c1 = b1t1.c1)
13921372 -> Index Only Scan using t1_i1 on t1 bmt1
13931373 Index Cond: (c1 = bmt2.c1)
13941374 -> Index Only Scan using t3_i1 on t3 bmt3
13951375 Index Cond: (c1 = bmt1.c1)
13961376 -> Index Only Scan using t4_i1 on t4 bmt4
13971377 Index Cond: (c1 = bmt1.c1)
1398- -> CTE Scan on c2
1399-(46 rows)
1378+ -> Aggregate
1379+ -> Nested Loop
1380+ -> Merge Join
1381+ Merge Cond: (b2t1.c1 = b2t2.c1)
1382+ -> Merge Join
1383+ Merge Cond: (b2t1.c1 = b2t3.c1)
1384+ -> Index Only Scan using t1_i1 on t1 b2t1
1385+ -> Index Only Scan using t3_i1 on t3 b2t3
1386+ -> Sort
1387+ Sort Key: b2t2.c1
1388+ -> Seq Scan on t2 b2t2
1389+ -> Index Only Scan using t4_i1 on t4 b2t4
1390+ Index Cond: (c1 = b2t1.c1)
1391+(42 rows)
14001392
14011393 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
14021394 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
@@ -1439,63 +1431,62 @@ not used hint:
14391431 duplication hint:
14401432 error hint:
14411433
1442- QUERY PLAN
1443----------------------------------------------------------------------------
1434+ QUERY PLAN
1435+-------------------------------------------------------------------------------------------------------------------
14441436 Aggregate
1445- CTE c1
1446- -> Aggregate
1447- -> Hash Join
1448- Hash Cond: (b1t1.c1 = b1t2.c1)
1449- -> Seq Scan on t1 b1t1
1450- -> Hash
1451- -> Nested Loop
1452- Join Filter: (b1t2.c1 = b1t3.c1)
1453- -> Nested Loop
1454- -> Seq Scan on t2 b1t2
1455- -> Bitmap Heap Scan on t4 b1t4
1456- Recheck Cond: (c1 = b1t2.c1)
1457- -> Bitmap Index Scan on t4_pkey
1458- Index Cond: (c1 = b1t2.c1)
1459- -> Index Scan using t3_pkey on t3 b1t3
1460- Index Cond: (c1 = b1t4.c1)
1461- CTE c2
1462- -> Aggregate
1463- -> Hash Join
1464- Hash Cond: (b2t3.c1 = b2t1.c1)
1465- -> Seq Scan on t3 b2t3
1466- -> Hash
1467- -> Merge Join
1468- Merge Cond: (b2t1.c1 = b2t2.c1)
1469- -> Nested Loop
1470- -> Index Scan using t4_pkey on t4 b2t4
1471- -> Bitmap Heap Scan on t1 b2t1
1472- Recheck Cond: (c1 = b2t4.c1)
1473- -> Bitmap Index Scan on t1_pkey
1474- Index Cond: (c1 = b2t4.c1)
1475- -> Sort
1476- Sort Key: b2t2.c1
1477- -> Seq Scan on t2 b2t2
14781437 -> Hash Join
14791438 Hash Cond: (bmt4.c1 = bmt1.c1)
14801439 -> Seq Scan on t4 bmt4
14811440 -> Hash
14821441 -> Nested Loop
1483- Join Filter: (bmt1.c1 = c2.c1)
14841442 -> Nested Loop
14851443 -> Nested Loop
1486- -> Hash Join
1487- Hash Cond: (bmt1.c1 = c1.c1)
1488- -> Seq Scan on t1 bmt1
1489- -> Hash
1490- -> CTE Scan on c1
1491- -> Index Scan using t2_pkey on t2 bmt2
1492- Index Cond: (c1 = bmt1.c1)
1493- -> Bitmap Heap Scan on t3 bmt3
1494- Recheck Cond: (c1 = bmt1.c1)
1495- -> Bitmap Index Scan on t3_pkey
1496- Index Cond: (c1 = bmt1.c1)
1497- -> CTE Scan on c2
1498-(54 rows)
1444+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
1445+ -> Merge Join
1446+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
1447+ -> Sort
1448+ Sort Key: (max(b1t1.c1))
1449+ -> Aggregate
1450+ -> Hash Join
1451+ Hash Cond: (b1t1.c1 = b1t2.c1)
1452+ -> Seq Scan on t1 b1t1
1453+ -> Hash
1454+ -> Nested Loop
1455+ Join Filter: (b1t2.c1 = b1t3.c1)
1456+ -> Nested Loop
1457+ -> Seq Scan on t2 b1t2
1458+ -> Bitmap Heap Scan on t4 b1t4
1459+ Recheck Cond: (c1 = b1t2.c1)
1460+ -> Bitmap Index Scan on t4_pkey
1461+ Index Cond: (c1 = b1t2.c1)
1462+ -> Index Scan using t3_pkey on t3 b1t3
1463+ Index Cond: (c1 = b1t4.c1)
1464+ -> Sort
1465+ Sort Key: (max(b2t1.c1))
1466+ -> Aggregate
1467+ -> Hash Join
1468+ Hash Cond: (b2t3.c1 = b2t1.c1)
1469+ -> Seq Scan on t3 b2t3
1470+ -> Hash
1471+ -> Merge Join
1472+ Merge Cond: (b2t1.c1 = b2t2.c1)
1473+ -> Nested Loop
1474+ -> Index Scan using t4_pkey on t4 b2t4
1475+ -> Bitmap Heap Scan on t1 b2t1
1476+ Recheck Cond: (c1 = b2t4.c1)
1477+ -> Bitmap Index Scan on t1_pkey
1478+ Index Cond: (c1 = b2t4.c1)
1479+ -> Sort
1480+ Sort Key: b2t2.c1
1481+ -> Seq Scan on t2 b2t2
1482+ -> Seq Scan on t1 bmt1
1483+ -> Index Scan using t2_pkey on t2 bmt2
1484+ Index Cond: (c1 = bmt1.c1)
1485+ -> Bitmap Heap Scan on t3 bmt3
1486+ Recheck Cond: (c1 = bmt1.c1)
1487+ -> Bitmap Index Scan on t3_pkey
1488+ Index Cond: (c1 = bmt1.c1)
1489+(53 rows)
14991490
15001491 ----
15011492 ---- 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
15161507 QUERY PLAN
15171508 -----------------------------------------------------------------
15181509 Aggregate
1519- CTE c1
1510+ InitPlan 2 (returns $1)
15201511 -> Result
15211512 InitPlan 1 (returns $0)
15221513 -> Limit
1523- -> Tid Scan on t1 b1t1
1514+ -> Tid Scan on t1 b2t1
15241515 TID Cond: (ctid = '(1,1)'::tid)
15251516 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
15261517 InitPlan 4 (returns $3)
15271518 -> Result
15281519 InitPlan 3 (returns $2)
15291520 -> Limit
1530- -> Tid Scan on t1 b2t1
1531- TID Cond: (ctid = '(1,1)'::tid)
1532- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
1533- InitPlan 6 (returns $5)
1534- -> Result
1535- InitPlan 5 (returns $4)
1536- -> Limit
15371521 -> Tid Scan on t1 b3t1
15381522 TID Cond: (ctid = '(1,1)'::tid)
15391523 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
15401524 -> Nested Loop
15411525 -> Tid Scan on t1 bmt1
15421526 TID Cond: (ctid = '(1,1)'::tid)
1543- Filter: ((c1 <> $5) AND (c1 = 1))
1544- -> CTE Scan on c1
1545-(27 rows)
1527+ Filter: ((c1 <> $3) AND (c1 = 1))
1528+ -> Result
1529+(20 rows)
15461530
15471531 /*+SeqScan(bmt1)
15481532 TidScan(b1t1)
@@ -1578,34 +1562,27 @@ error hint:
15781562 QUERY PLAN
15791563 ---------------------------------------------------------------------------
15801564 Aggregate
1581- CTE c1
1565+ InitPlan 2 (returns $1)
15821566 -> Result
15831567 InitPlan 1 (returns $0)
15841568 -> Limit
1585- -> Tid Scan on t1 b1t1
1586- TID Cond: (ctid = '(1,1)'::tid)
1587- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
1588- InitPlan 4 (returns $3)
1589- -> Result
1590- InitPlan 3 (returns $2)
1591- -> Limit
15921569 -> Bitmap Heap Scan on t1 b2t1
15931570 Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
15941571 Filter: (ctid = '(1,1)'::tid)
15951572 -> Bitmap Index Scan on t1_pkey
15961573 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
1597- InitPlan 6 (returns $5)
1574+ InitPlan 4 (returns $3)
15981575 -> Result
1599- InitPlan 5 (returns $4)
1576+ InitPlan 3 (returns $2)
16001577 -> Limit
16011578 -> Index Scan using t1_pkey on t1 b3t1
16021579 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
16031580 Filter: (ctid = '(1,1)'::tid)
16041581 -> Nested Loop
16051582 -> Seq Scan on t1 bmt1
1606- Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid) AND (c1 = 1))
1607- -> CTE Scan on c1
1608-(28 rows)
1583+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid) AND (c1 = 1))
1584+ -> Result
1585+(21 rows)
16091586
16101587 -- No. S-2-2-2
16111588 EXPLAIN (COSTS false)
@@ -1620,18 +1597,10 @@ AND bmt1.c1 <> (
16201597 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)'
16211598 )
16221599 ;
1623- QUERY PLAN
1624--------------------------------------------------------
1600+ QUERY PLAN
1601+-----------------------------------------------------------
16251602 Aggregate
1626- CTE c1
1627- -> Aggregate
1628- -> Nested Loop
1629- Join Filter: (b1t1.c1 = b1t2.c1)
1630- -> Tid Scan on t1 b1t1
1631- TID Cond: (ctid = '(1,1)'::tid)
1632- -> Seq Scan on t2 b1t2
1633- Filter: (ctid = '(1,1)'::tid)
1634- InitPlan 2 (returns $1)
1603+ InitPlan 1 (returns $0)
16351604 -> Aggregate
16361605 -> Nested Loop
16371606 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
16391608 TID Cond: (ctid = '(1,1)'::tid)
16401609 -> Seq Scan on t2 b2t2
16411610 Filter: (ctid = '(1,1)'::tid)
1642- InitPlan 3 (returns $2)
1611+ InitPlan 2 (returns $1)
16431612 -> Aggregate
16441613 -> Nested Loop
16451614 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
16521621 Join Filter: (bmt1.c1 = bmt2.c1)
16531622 -> Tid Scan on t1 bmt1
16541623 TID Cond: (ctid = '(1,1)'::tid)
1655- Filter: (c1 <> $2)
1624+ Filter: (c1 <> $1)
16561625 -> Seq Scan on t2 bmt2
16571626 Filter: (ctid = '(1,1)'::tid)
1658- -> CTE Scan on c1
1659-(34 rows)
1627+ -> Aggregate
1628+ -> Nested Loop
1629+ Join Filter: (b1t1.c1 = b1t2.c1)
1630+ -> Tid Scan on t1 b1t1
1631+ TID Cond: (ctid = '(1,1)'::tid)
1632+ -> Seq Scan on t2 b1t2
1633+ Filter: (ctid = '(1,1)'::tid)
1634+(32 rows)
16601635
16611636 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)
16621637 TidScan(b1t1)SeqScan(b1t2)
@@ -1696,15 +1671,7 @@ error hint:
16961671 QUERY PLAN
16971672 --------------------------------------------------------------------
16981673 Aggregate
1699- CTE c1
1700- -> Aggregate
1701- -> Nested Loop
1702- Join Filter: (b1t1.c1 = b1t2.c1)
1703- -> Tid Scan on t1 b1t1
1704- TID Cond: (ctid = '(1,1)'::tid)
1705- -> Seq Scan on t2 b1t2
1706- Filter: (ctid = '(1,1)'::tid)
1707- InitPlan 2 (returns $2)
1674+ InitPlan 1 (returns $1)
17081675 -> Aggregate
17091676 -> Nested Loop
17101677 -> Tid Scan on t2 b2t2
@@ -1714,7 +1681,7 @@ error hint:
17141681 Filter: (ctid = '(1,1)'::tid)
17151682 -> Bitmap Index Scan on t1_pkey
17161683 Index Cond: (c1 = b2t2.c1)
1717- InitPlan 3 (returns $4)
1684+ InitPlan 2 (returns $3)
17181685 -> Aggregate
17191686 -> Nested Loop
17201687 -> Index Scan using t1_pkey on t1 b3t1
@@ -1728,11 +1695,17 @@ error hint:
17281695 -> Nested Loop
17291696 Join Filter: (bmt1.c1 = bmt2.c1)
17301697 -> Seq Scan on t1 bmt1
1731- Filter: ((c1 <> $4) AND (ctid = '(1,1)'::tid))
1698+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid))
17321699 -> Index Scan using t2_pkey on t2 bmt2
17331700 Filter: (ctid = '(1,1)'::tid)
1734- -> CTE Scan on c1
1735-(37 rows)
1701+ -> Aggregate
1702+ -> Nested Loop
1703+ Join Filter: (b1t1.c1 = b1t2.c1)
1704+ -> Tid Scan on t1 b1t1
1705+ TID Cond: (ctid = '(1,1)'::tid)
1706+ -> Seq Scan on t2 b1t2
1707+ Filter: (ctid = '(1,1)'::tid)
1708+(35 rows)
17361709
17371710 -- No. S-2-2-3
17381711 EXPLAIN (COSTS false)
@@ -1747,26 +1720,10 @@ AND bmt1.c1 <> (
17471720 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)'
17481721 )
17491722 ;
1750- QUERY PLAN
1751--------------------------------------------------------------------
1723+ QUERY PLAN
1724+-----------------------------------------------------------------------
17521725 Aggregate
1753- CTE c1
1754- -> Aggregate
1755- -> Nested Loop
1756- Join Filter: (b1t1.c1 = b1t4.c1)
1757- -> Nested Loop
1758- Join Filter: (b1t1.c1 = b1t3.c1)
1759- -> Nested Loop
1760- Join Filter: (b1t1.c1 = b1t2.c1)
1761- -> Tid Scan on t1 b1t1
1762- TID Cond: (ctid = '(1,1)'::tid)
1763- -> Seq Scan on t2 b1t2
1764- Filter: (ctid = '(1,1)'::tid)
1765- -> Tid Scan on t3 b1t3
1766- TID Cond: (ctid = '(1,1)'::tid)
1767- -> Tid Scan on t4 b1t4
1768- TID Cond: (ctid = '(1,1)'::tid)
1769- InitPlan 2 (returns $1)
1726+ InitPlan 1 (returns $0)
17701727 -> Aggregate
17711728 -> Nested Loop
17721729 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
17821739 TID Cond: (ctid = '(1,1)'::tid)
17831740 -> Tid Scan on t4 b2t4
17841741 TID Cond: (ctid = '(1,1)'::tid)
1785- InitPlan 3 (returns $2)
1742+ InitPlan 2 (returns $1)
17861743 -> Aggregate
17871744 -> Nested Loop
17881745 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
17991756 -> Tid Scan on t4 b3t4
18001757 TID Cond: (ctid = '(1,1)'::tid)
18011758 -> Nested Loop
1802- Join Filter: (bmt1.c1 = c1.c1)
1759+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
18031760 -> Nested Loop
18041761 Join Filter: (bmt1.c1 = bmt4.c1)
18051762 -> 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
18081765 Join Filter: (bmt1.c1 = bmt2.c1)
18091766 -> Tid Scan on t1 bmt1
18101767 TID Cond: (ctid = '(1,1)'::tid)
1811- Filter: (c1 <> $2)
1768+ Filter: (c1 <> $1)
18121769 -> Seq Scan on t2 bmt2
18131770 Filter: (ctid = '(1,1)'::tid)
18141771 -> Tid Scan on t3 bmt3
18151772 TID Cond: (ctid = '(1,1)'::tid)
18161773 -> Tid Scan on t4 bmt4
18171774 TID Cond: (ctid = '(1,1)'::tid)
1818- -> CTE Scan on c1
1819-(67 rows)
1775+ -> Aggregate
1776+ -> Nested Loop
1777+ Join Filter: (b1t1.c1 = b1t4.c1)
1778+ -> Nested Loop
1779+ Join Filter: (b1t1.c1 = b1t3.c1)
1780+ -> Nested Loop
1781+ Join Filter: (b1t1.c1 = b1t2.c1)
1782+ -> Tid Scan on t1 b1t1
1783+ TID Cond: (ctid = '(1,1)'::tid)
1784+ -> Seq Scan on t2 b1t2
1785+ Filter: (ctid = '(1,1)'::tid)
1786+ -> Tid Scan on t3 b1t3
1787+ TID Cond: (ctid = '(1,1)'::tid)
1788+ -> Tid Scan on t4 b1t4
1789+ TID Cond: (ctid = '(1,1)'::tid)
1790+(65 rows)
18201791
18211792 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
18221793 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
@@ -1835,12 +1806,12 @@ AND bmt1.c1 <> (
18351806 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)'
18361807 )
18371808 ;
1838-LOG: available indexes for IndexScan(b1t3): t3_pkey
1839-LOG: available indexes for BitmapScan(b1t4): t4_pkey
18401809 LOG: available indexes for BitmapScan(b2t1): t1_pkey
18411810 LOG: available indexes for IndexScan(b2t4): t4_pkey
18421811 LOG: available indexes for IndexScan(b3t1): t1_pkey
18431812 LOG: available indexes for BitmapScan(b3t2): t2_pkey
1813+LOG: available indexes for IndexScan(b1t3): t3_pkey
1814+LOG: available indexes for BitmapScan(b1t4): t4_pkey
18441815 LOG: available indexes for IndexScan(bmt2): t2_pkey
18451816 LOG: available indexes for BitmapScan(bmt3): t3_pkey
18461817 LOG: pg_hint_plan:
@@ -1868,27 +1839,7 @@ error hint:
18681839 QUERY PLAN
18691840 --------------------------------------------------------------------------------
18701841 Aggregate
1871- CTE c1
1872- -> Aggregate
1873- -> Nested Loop
1874- Join Filter: (b1t1.c1 = b1t4.c1)
1875- -> Nested Loop
1876- Join Filter: (b1t1.c1 = b1t3.c1)
1877- -> Nested Loop
1878- Join Filter: (b1t1.c1 = b1t2.c1)
1879- -> Tid Scan on t1 b1t1
1880- TID Cond: (ctid = '(1,1)'::tid)
1881- -> Seq Scan on t2 b1t2
1882- Filter: (ctid = '(1,1)'::tid)
1883- -> Index Scan using t3_pkey on t3 b1t3
1884- Index Cond: (c1 = b1t2.c1)
1885- Filter: (ctid = '(1,1)'::tid)
1886- -> Bitmap Heap Scan on t4 b1t4
1887- Recheck Cond: (c1 = b1t2.c1)
1888- Filter: (ctid = '(1,1)'::tid)
1889- -> Bitmap Index Scan on t4_pkey
1890- Index Cond: (c1 = b1t2.c1)
1891- InitPlan 2 (returns $5)
1842+ InitPlan 1 (returns $2)
18921843 -> Aggregate
18931844 -> Nested Loop
18941845 Join Filter: (b2t1.c1 = b2t4.c1)
@@ -1908,7 +1859,7 @@ error hint:
19081859 -> Index Scan using t4_pkey on t4 b2t4
19091860 Index Cond: (c1 = b2t2.c1)
19101861 Filter: (ctid = '(1,1)'::tid)
1911- InitPlan 3 (returns $8)
1862+ InitPlan 2 (returns $5)
19121863 -> Aggregate
19131864 -> Nested Loop
19141865 Join Filter: (b3t1.c1 = b3t2.c1)
@@ -1929,7 +1880,7 @@ error hint:
19291880 -> Bitmap Index Scan on t2_pkey
19301881 Index Cond: (c1 = b3t3.c1)
19311882 -> Nested Loop
1932- Join Filter: (bmt1.c1 = c1.c1)
1883+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
19331884 -> Nested Loop
19341885 Join Filter: (bmt1.c1 = bmt3.c1)
19351886 -> Nested Loop
@@ -1937,7 +1888,7 @@ error hint:
19371888 -> Nested Loop
19381889 Join Filter: (bmt1.c1 = bmt4.c1)
19391890 -> Seq Scan on t1 bmt1
1940- Filter: ((c1 <> $8) AND (ctid = '(1,1)'::tid))
1891+ Filter: ((c1 <> $5) AND (ctid = '(1,1)'::tid))
19411892 -> Tid Scan on t4 bmt4
19421893 TID Cond: (ctid = '(1,1)'::tid)
19431894 -> Index Scan using t2_pkey on t2 bmt2
@@ -1948,8 +1899,26 @@ error hint:
19481899 Filter: (ctid = '(1,1)'::tid)
19491900 -> Bitmap Index Scan on t3_pkey
19501901 Index Cond: (c1 = bmt2.c1)
1951- -> CTE Scan on c1
1952-(82 rows)
1902+ -> Aggregate
1903+ -> Nested Loop
1904+ Join Filter: (b1t1.c1 = b1t4.c1)
1905+ -> Nested Loop
1906+ Join Filter: (b1t1.c1 = b1t3.c1)
1907+ -> Nested Loop
1908+ Join Filter: (b1t1.c1 = b1t2.c1)
1909+ -> Tid Scan on t1 b1t1
1910+ TID Cond: (ctid = '(1,1)'::tid)
1911+ -> Seq Scan on t2 b1t2
1912+ Filter: (ctid = '(1,1)'::tid)
1913+ -> Index Scan using t3_pkey on t3 b1t3
1914+ Index Cond: (c1 = b1t2.c1)
1915+ Filter: (ctid = '(1,1)'::tid)
1916+ -> Bitmap Heap Scan on t4 b1t4
1917+ Recheck Cond: (c1 = b1t2.c1)
1918+ Filter: (ctid = '(1,1)'::tid)
1919+ -> Bitmap Index Scan on t4_pkey
1920+ Index Cond: (c1 = b1t2.c1)
1921+(80 rows)
19531922
19541923 -- No. S-2-2-4
19551924 EXPLAIN (COSTS false)
@@ -1964,38 +1933,22 @@ AND bmt1.c1 <> (
19641933 SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
19651934 )
19661935 ;
1967- QUERY PLAN
1968--------------------------------------------------------------------
1936+ QUERY PLAN
1937+-----------------------------------------------------------------------
19691938 Aggregate
1970- CTE c1
1971- -> Aggregate
1972- -> Nested Loop
1973- Join Filter: (b1t1.c1 = b1t4.c1)
1974- -> Nested Loop
1975- Join Filter: (b1t1.c1 = b1t3.c1)
1976- -> Nested Loop
1977- Join Filter: (b1t1.c1 = b1t2.c1)
1978- -> Tid Scan on t1 b1t1
1979- TID Cond: (ctid = '(1,1)'::tid)
1980- -> Seq Scan on t2 b1t2
1981- Filter: (ctid = '(1,1)'::tid)
1982- -> Tid Scan on t3 b1t3
1983- TID Cond: (ctid = '(1,1)'::tid)
1984- -> Tid Scan on t4 b1t4
1985- TID Cond: (ctid = '(1,1)'::tid)
1986- InitPlan 3 (returns $2)
1939+ InitPlan 2 (returns $1)
19871940 -> Result
1988- InitPlan 2 (returns $1)
1941+ InitPlan 1 (returns $0)
19891942 -> Limit
19901943 -> Tid Scan on t1 b2t1
19911944 TID Cond: (ctid = '(1,1)'::tid)
19921945 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
1993- InitPlan 4 (returns $4)
1946+ InitPlan 3 (returns $3)
19941947 -> Aggregate
19951948 -> Tid Scan on t1 b3t1
19961949 TID Cond: (ctid = '(1,1)'::tid)
19971950 -> Nested Loop
1998- Join Filter: (bmt1.c1 = c1.c1)
1951+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
19991952 -> Nested Loop
20001953 Join Filter: (bmt1.c1 = bmt4.c1)
20011954 -> Nested Loop
@@ -2004,15 +1957,29 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
20041957 Join Filter: (bmt1.c1 = bmt2.c1)
20051958 -> Tid Scan on t1 bmt1
20061959 TID Cond: (ctid = '(1,1)'::tid)
2007- Filter: (c1 <> $4)
1960+ Filter: (c1 <> $3)
20081961 -> Seq Scan on t2 bmt2
20091962 Filter: (ctid = '(1,1)'::tid)
20101963 -> Tid Scan on t3 bmt3
20111964 TID Cond: (ctid = '(1,1)'::tid)
20121965 -> Tid Scan on t4 bmt4
20131966 TID Cond: (ctid = '(1,1)'::tid)
2014- -> CTE Scan on c1
2015-(46 rows)
1967+ -> Aggregate
1968+ -> Nested Loop
1969+ Join Filter: (b1t1.c1 = b1t4.c1)
1970+ -> Nested Loop
1971+ Join Filter: (b1t1.c1 = b1t3.c1)
1972+ -> Nested Loop
1973+ Join Filter: (b1t1.c1 = b1t2.c1)
1974+ -> Tid Scan on t1 b1t1
1975+ TID Cond: (ctid = '(1,1)'::tid)
1976+ -> Seq Scan on t2 b1t2
1977+ Filter: (ctid = '(1,1)'::tid)
1978+ -> Tid Scan on t3 b1t3
1979+ TID Cond: (ctid = '(1,1)'::tid)
1980+ -> Tid Scan on t4 b1t4
1981+ TID Cond: (ctid = '(1,1)'::tid)
1982+(44 rows)
20161983
20171984 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
20181985 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
@@ -2031,12 +1998,12 @@ AND bmt1.c1 <> (
20311998 SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
20321999 )
20332000 ;
2034-LOG: available indexes for IndexScan(b1t3): t3_pkey
2035-LOG: available indexes for BitmapScan(b1t4): t4_pkey
20362001 LOG: available indexes for BitmapScan(b2t1): t1_pkey
20372002 LOG: available indexes for BitmapScan(b2t1): t1_pkey
20382003 LOG: available indexes for IndexScan(b3t1): t1_pkey
20392004 LOG: available indexes for IndexScan(b3t1): t1_pkey
2005+LOG: available indexes for IndexScan(b1t3): t3_pkey
2006+LOG: available indexes for BitmapScan(b1t4): t4_pkey
20402007 LOG: available indexes for IndexScan(bmt2): t2_pkey
20412008 LOG: available indexes for BitmapScan(bmt3): t3_pkey
20422009 LOG: pg_hint_plan:
@@ -2058,44 +2025,24 @@ error hint:
20582025 QUERY PLAN
20592026 --------------------------------------------------------------------------------
20602027 Aggregate
2061- CTE c1
2062- -> Aggregate
2063- -> Nested Loop
2064- Join Filter: (b1t1.c1 = b1t4.c1)
2065- -> Nested Loop
2066- Join Filter: (b1t1.c1 = b1t3.c1)
2067- -> Nested Loop
2068- Join Filter: (b1t1.c1 = b1t2.c1)
2069- -> Tid Scan on t1 b1t1
2070- TID Cond: (ctid = '(1,1)'::tid)
2071- -> Seq Scan on t2 b1t2
2072- Filter: (ctid = '(1,1)'::tid)
2073- -> Index Scan using t3_pkey on t3 b1t3
2074- Index Cond: (c1 = b1t2.c1)
2075- Filter: (ctid = '(1,1)'::tid)
2076- -> Bitmap Heap Scan on t4 b1t4
2077- Recheck Cond: (c1 = b1t2.c1)
2078- Filter: (ctid = '(1,1)'::tid)
2079- -> Bitmap Index Scan on t4_pkey
2080- Index Cond: (c1 = b1t2.c1)
2081- InitPlan 3 (returns $4)
2028+ InitPlan 2 (returns $1)
20822029 -> Result
2083- InitPlan 2 (returns $3)
2030+ InitPlan 1 (returns $0)
20842031 -> Limit
20852032 -> Bitmap Heap Scan on t1 b2t1
20862033 Recheck Cond: ((c1 IS NOT NULL) AND (c1 = 1))
20872034 Filter: (ctid = '(1,1)'::tid)
20882035 -> Bitmap Index Scan on t1_pkey
20892036 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
2090- InitPlan 5 (returns $6)
2037+ InitPlan 4 (returns $3)
20912038 -> Result
2092- InitPlan 4 (returns $5)
2039+ InitPlan 3 (returns $2)
20932040 -> Limit
20942041 -> Index Scan Backward using t1_pkey on t1 b3t1
20952042 Index Cond: (c1 IS NOT NULL)
20962043 Filter: (ctid = '(1,1)'::tid)
20972044 -> Nested Loop
2098- Join Filter: (bmt1.c1 = c1.c1)
2045+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
20992046 -> Nested Loop
21002047 Join Filter: (bmt1.c1 = bmt3.c1)
21012048 -> Nested Loop
@@ -2103,7 +2050,7 @@ error hint:
21032050 -> Nested Loop
21042051 Join Filter: (bmt1.c1 = bmt4.c1)
21052052 -> Seq Scan on t1 bmt1
2106- Filter: ((c1 <> $6) AND (ctid = '(1,1)'::tid))
2053+ Filter: ((c1 <> $3) AND (ctid = '(1,1)'::tid))
21072054 -> Tid Scan on t4 bmt4
21082055 TID Cond: (ctid = '(1,1)'::tid)
21092056 -> Index Scan using t2_pkey on t2 bmt2
@@ -2114,8 +2061,26 @@ error hint:
21142061 Filter: (ctid = '(1,1)'::tid)
21152062 -> Bitmap Index Scan on t3_pkey
21162063 Index Cond: (c1 = bmt2.c1)
2117- -> CTE Scan on c1
2118-(58 rows)
2064+ -> Aggregate
2065+ -> Nested Loop
2066+ Join Filter: (b1t1.c1 = b1t4.c1)
2067+ -> Nested Loop
2068+ Join Filter: (b1t1.c1 = b1t3.c1)
2069+ -> Nested Loop
2070+ Join Filter: (b1t1.c1 = b1t2.c1)
2071+ -> Tid Scan on t1 b1t1
2072+ TID Cond: (ctid = '(1,1)'::tid)
2073+ -> Seq Scan on t2 b1t2
2074+ Filter: (ctid = '(1,1)'::tid)
2075+ -> Index Scan using t3_pkey on t3 b1t3
2076+ Index Cond: (c1 = b1t2.c1)
2077+ Filter: (ctid = '(1,1)'::tid)
2078+ -> Bitmap Heap Scan on t4 b1t4
2079+ Recheck Cond: (c1 = b1t2.c1)
2080+ Filter: (ctid = '(1,1)'::tid)
2081+ -> Bitmap Index Scan on t4_pkey
2082+ Index Cond: (c1 = b1t2.c1)
2083+(56 rows)
21192084
21202085 ----
21212086 ---- No. S-2-3 RULE or VIEW
@@ -3902,11 +3867,11 @@ not used hint:
39023867 duplication hint:
39033868 error hint:
39043869
3905- QUERY PLAN
3906--------------------------------------------------------------------------------------------------------------------------------------------------------------------
3870+ QUERY PLAN
3871+----------------------------------------------------------------------------------------------------------------------------------------------------
39073872 Index Scan using ti1_expr on ti1
39083873 Index Cond: ((c1 < 100) = true)
3909- 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))
3874+ Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery))
39103875 (3 rows)
39113876
39123877 -- No. S-3-4-6
@@ -4356,8 +4321,7 @@ error hint:
43564321 -----------------------------------
43574322 Index Scan using ti1_expr on ti1
43584323 Index Cond: ((c1 < 100) = true)
4359- Filter: (c1 < 100)
4360-(3 rows)
4324+(2 rows)
43614325
43624326 -- No. S-3-4-28
43634327 EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1';
@@ -4523,8 +4487,8 @@ error hint:
45234487
45244488 \o
45254489 \! sql/maskout.sh results/ut-S.tmpout
4526- QUERY PLAN
4527--------------------------------------------------------------------------------
4490+ QUERY PLAN
4491+------------------------------------------------------------------------
45284492 Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
45294493 Filter: (c1 = 100)
45304494
@@ -4541,8 +4505,8 @@ error hint:
45414505
45424506 \o
45434507 \! sql/maskout.sh results/ut-S.tmpout
4544- QUERY PLAN
4545--------------------------------------------------------------------------------
4508+ QUERY PLAN
4509+------------------------------------------------------------------------
45464510 Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
45474511 Filter: (c1 = 100)
45484512
@@ -4559,8 +4523,8 @@ error hint:
45594523
45604524 \o
45614525 \! sql/maskout.sh results/ut-S.tmpout
4562- QUERY PLAN
4563--------------------------------------------------------------------------------
4526+ QUERY PLAN
4527+-----------------------------------------------------------------------
45644528 Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
45654529 Filter: (c1 = 100)
45664530
@@ -4577,8 +4541,8 @@ error hint:
45774541
45784542 \o
45794543 \! sql/maskout.sh results/ut-S.tmpout
4580- QUERY PLAN
4581--------------------------------------------------------------------------------
4544+ QUERY PLAN
4545+------------------------------------------------------------------------
45824546 Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
45834547 Filter: (c1 = 100)
45844548
@@ -4595,8 +4559,8 @@ error hint:
45954559
45964560 \o
45974561 \! sql/maskout.sh results/ut-S.tmpout
4598- QUERY PLAN
4599--------------------------------------------------------------------------------
4562+ QUERY PLAN
4563+------------------------------------------------------------------------
46004564 Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
46014565 Filter: (c1 = 100)
46024566
@@ -4613,8 +4577,8 @@ error hint:
46134577
46144578 \o
46154579 \! sql/maskout.sh results/ut-S.tmpout
4616- QUERY PLAN
4617--------------------------------------------------------------------------------
4580+ QUERY PLAN
4581+-----------------------------------------------------------------------
46184582 Seq Scan on ti1 (cost={inf}..{inf} rows=1 width=xxx)
46194583 Filter: (c1 = 100)
46204584
@@ -4637,8 +4601,8 @@ error hint:
46374601
46384602 \o
46394603 \! sql/maskout.sh results/ut-S.tmpout
4640- QUERY PLAN
4641--------------------------------------------------------------------------------
4604+ QUERY PLAN
4605+-----------------------------------------------------------------------
46424606 Seq Scan on t1 (cost={inf}..{inf} rows=1 width=xxx)
46434607 Filter: (c1 = 1)
46444608
@@ -4795,24 +4759,17 @@ SELECT max(b3t1.c1), (
47954759 SELECT max(b2t1.c1) FROM s1.t1 b2t1 JOIN s1.t2 b2t2 ON(b2t1.c1 = b2t2.c1) WHERE b2t1.c1 = 1
47964760 ) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
47974761 SELECT max(b4t1.c1) FROM s1.t1 b4t1 JOIN s1.t2 b4t2 ON(b4t1.c1 = b4t2.c1) WHERE b4t1.c1 = 1);
4798- QUERY PLAN
4799-------------------------------------------------------------
4762+ QUERY PLAN
4763+----------------------------------------------------------------
48004764 Aggregate
4801- CTE cte1
4802- -> Aggregate
4803- -> Nested Loop
4804- -> Index Only Scan using t1_i1 on t1 b1t1
4805- Index Cond: (c1 = 1)
4806- -> Seq Scan on t2 b1t2
4807- Filter: (c1 = 1)
4808- InitPlan 2 (returns $1)
4765+ InitPlan 1 (returns $0)
48094766 -> Aggregate
48104767 -> Nested Loop
48114768 -> Index Only Scan using t1_i1 on t1 b2t1
48124769 Index Cond: (c1 = 1)
48134770 -> Seq Scan on t2 b2t2
48144771 Filter: (c1 = 1)
4815- InitPlan 3 (returns $2)
4772+ InitPlan 2 (returns $1)
48164773 -> Aggregate
48174774 -> Nested Loop
48184775 -> 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
48224779 -> Nested Loop
48234780 -> Nested Loop
48244781 -> Index Only Scan using t1_i1 on t1 b3t1
4825- Index Cond: (c1 = $2)
4782+ Index Cond: (c1 = $1)
48264783 -> Seq Scan on t2 b3t2
4827- Filter: (c1 = $2)
4828- -> CTE Scan on cte1
4829- Filter: (c1 = $2)
4830-(30 rows)
4784+ Filter: (c1 = $1)
4785+ -> Aggregate
4786+ Filter: (max(b1t1.c1) = $1)
4787+ -> Nested Loop
4788+ -> Index Only Scan using t1_i1 on t1 b1t1
4789+ Index Cond: (c1 = 1)
4790+ -> Seq Scan on t2 b1t2
4791+ Filter: (c1 = 1)
4792+(28 rows)
48314793
48324794 /*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
48334795 TidScan(b1t2)SeqScan(b2t2)IndexScan(b3t2 t2_pkey)BitmapScan(b4t2 t2_pkey)
@@ -4860,21 +4822,14 @@ error hint:
48604822 QUERY PLAN
48614823 ---------------------------------------------------------
48624824 Aggregate
4863- CTE cte1
4864- -> Aggregate
4865- -> Nested Loop
4866- -> Seq Scan on t1 b1t1
4867- Filter: (c1 = 1)
4868- -> Seq Scan on t2 b1t2
4869- Filter: (c1 = 1)
4870- InitPlan 2 (returns $1)
4825+ InitPlan 1 (returns $0)
48714826 -> Aggregate
48724827 -> Nested Loop
48734828 -> Index Scan using t1_pkey on t1 b2t1
48744829 Index Cond: (c1 = 1)
48754830 -> Seq Scan on t2 b2t2
48764831 Filter: (c1 = 1)
4877- InitPlan 3 (returns $2)
4832+ InitPlan 2 (returns $1)
48784833 -> Aggregate
48794834 -> Nested Loop
48804835 -> Seq Scan on t1 b4t1
@@ -4886,14 +4841,19 @@ error hint:
48864841 -> Nested Loop
48874842 -> Nested Loop
48884843 -> Bitmap Heap Scan on t1 b3t1
4889- Recheck Cond: (c1 = $2)
4844+ Recheck Cond: (c1 = $1)
48904845 -> Bitmap Index Scan on t1_pkey
4891- Index Cond: (c1 = $2)
4846+ Index Cond: (c1 = $1)
48924847 -> Index Scan using t2_pkey on t2 b3t2
4893- Index Cond: (c1 = $2)
4894- -> CTE Scan on cte1
4895- Filter: (c1 = $2)
4896-(34 rows)
4848+ Index Cond: (c1 = $1)
4849+ -> Aggregate
4850+ Filter: (max(b1t1.c1) = $1)
4851+ -> Nested Loop
4852+ -> Seq Scan on t1 b1t1
4853+ Filter: (c1 = 1)
4854+ -> Seq Scan on t2 b1t2
4855+ Filter: (c1 = 1)
4856+(32 rows)
48974857
48984858 -- No. S-3-7-3
48994859 EXPLAIN (COSTS false)
@@ -4906,34 +4866,32 @@ SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
49064866 QUERY PLAN
49074867 ---------------------------------------------------------------------
49084868 Aggregate
4909- CTE cte1
4910- -> Aggregate
4911- -> Nested Loop
4912- -> Index Only Scan using t1_i1 on t1 b1t1
4913- Index Cond: (c1 = 1)
4914- -> Seq Scan on t2 b1t2
4915- Filter: (c1 = 1)
4916- InitPlan 3 (returns $2)
4869+ InitPlan 2 (returns $1)
49174870 -> Result
4918- InitPlan 2 (returns $1)
4871+ InitPlan 1 (returns $0)
49194872 -> Limit
49204873 -> Index Only Scan using t1_i1 on t1 b2t1
49214874 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
4922- InitPlan 5 (returns $4)
4875+ InitPlan 4 (returns $3)
49234876 -> Result
4924- InitPlan 4 (returns $3)
4877+ InitPlan 3 (returns $2)
49254878 -> Limit
49264879 -> Index Only Scan using t1_i1 on t1 b4t1
49274880 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
49284881 -> Nested Loop
49294882 -> Nested Loop
49304883 -> Index Only Scan using t1_i1 on t1 b3t1
4931- Index Cond: (c1 = $4)
4884+ Index Cond: (c1 = $3)
49324885 -> Seq Scan on t2 b3t2
4933- Filter: (c1 = $4)
4934- -> CTE Scan on cte1
4935- Filter: (c1 = $4)
4936-(28 rows)
4886+ Filter: (c1 = $3)
4887+ -> Aggregate
4888+ Filter: (max(b1t1.c1) = $3)
4889+ -> Nested Loop
4890+ -> Index Only Scan using t1_i1 on t1 b1t1
4891+ Index Cond: (c1 = 1)
4892+ -> Seq Scan on t2 b1t2
4893+ Filter: (c1 = 1)
4894+(26 rows)
49374895
49384896 /*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
49394897 TidScan(b1t2)IndexScan(b3t2 t2_pkey)
@@ -4964,36 +4922,34 @@ error hint:
49644922 QUERY PLAN
49654923 ---------------------------------------------------------------------
49664924 Aggregate
4967- CTE cte1
4968- -> Aggregate
4969- -> Nested Loop
4970- -> Seq Scan on t1 b1t1
4971- Filter: (c1 = 1)
4972- -> Seq Scan on t2 b1t2
4973- Filter: (c1 = 1)
4974- InitPlan 3 (returns $2)
4925+ InitPlan 2 (returns $1)
49754926 -> Result
4976- InitPlan 2 (returns $1)
4927+ InitPlan 1 (returns $0)
49774928 -> Limit
49784929 -> Index Scan using t1_pkey on t1 b2t1
49794930 Index Cond: ((c1 IS NOT NULL) AND (c1 = 1))
4980- InitPlan 5 (returns $4)
4931+ InitPlan 4 (returns $3)
49814932 -> Result
4982- InitPlan 4 (returns $3)
4933+ InitPlan 3 (returns $2)
49834934 -> Limit
49844935 -> Seq Scan on t1 b4t1
49854936 Filter: ((c1 IS NOT NULL) AND (c1 = 1))
49864937 -> Nested Loop
49874938 -> Nested Loop
49884939 -> Bitmap Heap Scan on t1 b3t1
4989- Recheck Cond: (c1 = $4)
4940+ Recheck Cond: (c1 = $3)
49904941 -> Bitmap Index Scan on t1_pkey
4991- Index Cond: (c1 = $4)
4942+ Index Cond: (c1 = $3)
49924943 -> Index Scan using t2_pkey on t2 b3t2
4993- Index Cond: (c1 = $4)
4994- -> CTE Scan on cte1
4995- Filter: (c1 = $4)
4996-(30 rows)
4944+ Index Cond: (c1 = $3)
4945+ -> Aggregate
4946+ Filter: (max(b1t1.c1) = $3)
4947+ -> Nested Loop
4948+ -> Seq Scan on t1 b1t1
4949+ Filter: (c1 = 1)
4950+ -> Seq Scan on t2 b1t2
4951+ Filter: (c1 = 1)
4952+(28 rows)
49974953
49984954 ----
49994955 ---- No. S-3-8 inheritance table select/update type
@@ -5319,8 +5275,8 @@ error hint:
53195275
53205276 \o
53215277 \! sql/maskout.sh results/ut-S.tmpout
5322- QUERY PLAN
5323--------------------------------------------------------------------------------
5278+ QUERY PLAN
5279+------------------------------------------------------------------------------
53245280 Append (cost=xxx..xxx rows=4 width=xxx)
53255281 -> Index Scan using p1_i2 on p1 (cost=xxx..xxx rows=1 width=xxx)
53265282 Index Cond: (c2 = 1)
--- a/output/ut-W.source
+++ b/expected/ut-W.out
@@ -677,33 +677,33 @@ not used hint:
677677 duplication hint:
678678 error hint:
679679
680- QUERY PLAN
681------------------------------------------------------------------------------------
682- Gather
683- Workers Planned: 8
684- -> Parallel Hash Join
685- Hash Cond: (p1.id = p2.id)
686- -> Append
687- -> Parallel Seq Scan on p1
688- -> Parallel Seq Scan on p1_c1
689- -> Parallel Seq Scan on p1_c2
690- -> Parallel Seq Scan on p1_c3
691- -> Parallel Seq Scan on p1_c4
692- -> Parallel Seq Scan on p1_c1_c1
693- -> Parallel Seq Scan on p1_c1_c2
694- -> Parallel Seq Scan on p1_c3_c1
695- -> Parallel Seq Scan on p1_c3_c2
696- -> Parallel Hash
680+ QUERY PLAN
681+--------------------------------------------------------------
682+ Hash Join
683+ Hash Cond: (p2.id = p1.id)
684+ -> Append
685+ -> Index Scan using p2_id2_val on p2
686+ -> Index Scan using p2_c1_id2_val on p2_c1
687+ -> Index Scan using p2_c2_id2_val on p2_c2
688+ -> Index Scan using p2_c3_id_val_idx on p2_c3
689+ -> Index Scan using p2_c4_id_val_idx on p2_c4
690+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
691+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
692+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
693+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
694+ -> Hash
695+ -> Gather
696+ Workers Planned: 8
697697 -> Append
698- -> Parallel Index Scan using p2_id2_val on p2
699- -> Parallel Index Scan using p2_c1_id2_val on p2_c1
700- -> Parallel Index Scan using p2_c2_id2_val on p2_c2
701- -> Parallel Index Scan using p2_c3_id_val_idx on p2_c3
702- -> Parallel Index Scan using p2_c4_id_val_idx on p2_c4
703- -> Parallel Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
704- -> Parallel Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
705- -> Parallel Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
706- -> Parallel Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
698+ -> Parallel Seq Scan on p1
699+ -> Parallel Seq Scan on p1_c1
700+ -> Parallel Seq Scan on p1_c2
701+ -> Parallel Seq Scan on p1_c3
702+ -> Parallel Seq Scan on p1_c4
703+ -> Parallel Seq Scan on p1_c1_c1
704+ -> Parallel Seq Scan on p1_c1_c2
705+ -> Parallel Seq Scan on p1_c3_c1
706+ -> Parallel Seq Scan on p1_c3_c2
707707 (25 rows)
708708
709709 SET enable_parallel_append to true;
@@ -717,33 +717,33 @@ not used hint:
717717 duplication hint:
718718 error hint:
719719
720- QUERY PLAN
721------------------------------------------------------------------------------------
722- Gather
723- Workers Planned: 8
724- -> Parallel Hash Join
725- Hash Cond: (p1.id = p2_c2.id)
726- -> Parallel Append
727- -> Seq Scan on p1
728- -> Seq Scan on p1_c1
729- -> Seq Scan on p1_c3
730- -> Parallel Seq Scan on p1_c2
731- -> Parallel Seq Scan on p1_c4
732- -> Parallel Seq Scan on p1_c1_c1
733- -> Parallel Seq Scan on p1_c1_c2
734- -> Parallel Seq Scan on p1_c3_c1
735- -> Parallel Seq Scan on p1_c3_c2
736- -> Parallel Hash
720+ QUERY PLAN
721+--------------------------------------------------------------
722+ Hash Join
723+ Hash Cond: (p2.id = p1.id)
724+ -> Append
725+ -> Index Scan using p2_id2_val on p2
726+ -> Index Scan using p2_c1_id2_val on p2_c1
727+ -> Index Scan using p2_c2_id2_val on p2_c2
728+ -> Index Scan using p2_c3_id_val_idx on p2_c3
729+ -> Index Scan using p2_c4_id_val_idx on p2_c4
730+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
731+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
732+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
733+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
734+ -> Hash
735+ -> Gather
736+ Workers Planned: 8
737737 -> Parallel Append
738- -> Parallel Index Scan using p2_c2_id2_val on p2_c2
739- -> Parallel Index Scan using p2_c4_id_val_idx on p2_c4
740- -> Parallel Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
741- -> Parallel Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
742- -> Parallel Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
743- -> Parallel Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
744- -> Parallel Index Scan using p2_id2_val on p2
745- -> Parallel Index Scan using p2_c1_id2_val on p2_c1
746- -> Parallel Index Scan using p2_c3_id_val_idx on p2_c3
738+ -> Seq Scan on p1
739+ -> Seq Scan on p1_c1
740+ -> Seq Scan on p1_c3
741+ -> Parallel Seq Scan on p1_c2
742+ -> Parallel Seq Scan on p1_c4
743+ -> Parallel Seq Scan on p1_c1_c1
744+ -> Parallel Seq Scan on p1_c1_c2
745+ -> Parallel Seq Scan on p1_c3_c1
746+ -> Parallel Seq Scan on p1_c3_c2
747747 (25 rows)
748748
749749 -- Parallel sequential scan
@@ -1246,34 +1246,32 @@ error hint:
12461246 -> Sample Scan on p1_c1_c1 s1
12471247 Sampling: system ('10'::real)
12481248 -> Foreign Scan on ft1
1249- Foreign File: @abs_srcdir@/data/data.csv
1250- -> CTE Scan on cte1
1251- CTE cte1
1252- -> Gather
1253- Workers Planned: 5
1254- -> Parallel Append
1255- -> Seq Scan on p1
1256- Filter: ((id % 2) = 0)
1257- -> Seq Scan on p1_c1
1258- Filter: ((id % 2) = 0)
1259- -> Seq Scan on p1_c3
1260- Filter: ((id % 2) = 0)
1261- -> Parallel Seq Scan on p1_c2
1262- Filter: ((id % 2) = 0)
1263- -> Parallel Seq Scan on p1_c4
1264- Filter: ((id % 2) = 0)
1265- -> Parallel Seq Scan on p1_c1_c1
1266- Filter: ((id % 2) = 0)
1267- -> Parallel Seq Scan on p1_c1_c2
1268- Filter: ((id % 2) = 0)
1269- -> Parallel Seq Scan on p1_c3_c1
1270- Filter: ((id % 2) = 0)
1271- -> Parallel Seq Scan on p1_c3_c2
1272- Filter: ((id % 2) = 0)
1249+ Foreign File: /home/horiguti/work/pg_hint_plan/pg_hint_plan/data/data.csv
1250+ -> Gather
1251+ Workers Planned: 5
1252+ -> Parallel Append
1253+ -> Seq Scan on p1
1254+ Filter: ((id % 2) = 0)
1255+ -> Seq Scan on p1_c1
1256+ Filter: ((id % 2) = 0)
1257+ -> Seq Scan on p1_c3
1258+ Filter: ((id % 2) = 0)
1259+ -> Parallel Seq Scan on p1_c2
1260+ Filter: ((id % 2) = 0)
1261+ -> Parallel Seq Scan on p1_c4
1262+ Filter: ((id % 2) = 0)
1263+ -> Parallel Seq Scan on p1_c1_c1
1264+ Filter: ((id % 2) = 0)
1265+ -> Parallel Seq Scan on p1_c1_c2
1266+ Filter: ((id % 2) = 0)
1267+ -> Parallel Seq Scan on p1_c3_c1
1268+ Filter: ((id % 2) = 0)
1269+ -> Parallel Seq Scan on p1_c3_c2
1270+ Filter: ((id % 2) = 0)
12731271 -> Function Scan on pg_stat_statements
12741272 -> Subquery Scan on "*SELECT* 5"
12751273 -> Values Scan on "*VALUES*"
1276-(33 rows)
1274+(31 rows)
12771275
12781276 ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
12791277 SELECT pg_reload_conf();
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -12,6 +12,7 @@
1212 #include "postgres.h"
1313 #include "access/genam.h"
1414 #include "access/heapam.h"
15+#include "access/relation.h"
1516 #include "catalog/pg_collation.h"
1617 #include "catalog/pg_index.h"
1718 #include "commands/prepare.h"
@@ -19,12 +20,12 @@
1920 #include "miscadmin.h"
2021 #include "nodes/nodeFuncs.h"
2122 #include "nodes/params.h"
22-#include "nodes/relation.h"
2323 #include "optimizer/appendinfo.h"
2424 #include "optimizer/clauses.h"
2525 #include "optimizer/cost.h"
2626 #include "optimizer/geqo.h"
2727 #include "optimizer/joininfo.h"
28+#include "optimizer/optimizer.h"
2829 #include "optimizer/pathnode.h"
2930 #include "optimizer/paths.h"
3031 #include "optimizer/plancat.h"
--- a/pg_stat_statements.c
+++ b/pg_stat_statements.c
@@ -48,8 +48,10 @@ AppendJumble(pgssJumbleState *jstate, const unsigned char *item, Size size)
4848
4949 if (jumble_len >= JUMBLE_SIZE)
5050 {
51- uint32 start_hash = hash_any(jumble, JUMBLE_SIZE);
51+ uint64 start_hash;
5252
53+ start_hash = DatumGetUInt64(hash_any_extended(jumble,
54+ JUMBLE_SIZE, 0));
5355 memcpy(jumble, &start_hash, sizeof(start_hash));
5456 jumble_len = sizeof(start_hash);
5557 }
@@ -152,6 +154,8 @@ JumbleRangeTable(pgssJumbleState *jstate, List *rtable)
152154 case RTE_NAMEDTUPLESTORE:
153155 APP_JUMB_STRING(rte->enrname);
154156 break;
157+ case RTE_RESULT:
158+ break;
155159 default:
156160 elog(ERROR, "unrecognized RTE kind: %d", (int) rte->rtekind);
157161 break;
@@ -253,14 +257,14 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
253257 JumbleExpr(jstate, (Node *) expr->aggfilter);
254258 }
255259 break;
256- case T_ArrayRef:
260+ case T_SubscriptingRef:
257261 {
258- ArrayRef *aref = (ArrayRef *) node;
262+ SubscriptingRef *sbsref = (SubscriptingRef *) node;
259263
260- JumbleExpr(jstate, (Node *) aref->refupperindexpr);
261- JumbleExpr(jstate, (Node *) aref->reflowerindexpr);
262- JumbleExpr(jstate, (Node *) aref->refexpr);
263- JumbleExpr(jstate, (Node *) aref->refassgnexpr);
264+ JumbleExpr(jstate, (Node *) sbsref->refupperindexpr);
265+ JumbleExpr(jstate, (Node *) sbsref->reflowerindexpr);
266+ JumbleExpr(jstate, (Node *) sbsref->refexpr);
267+ JumbleExpr(jstate, (Node *) sbsref->refassgnexpr);
264268 }
265269 break;
266270 case T_FuncExpr:
@@ -354,6 +358,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
354358
355359 APP_JUMB(acexpr->resulttype);
356360 JumbleExpr(jstate, (Node *) acexpr->arg);
361+ JumbleExpr(jstate, (Node *) acexpr->elemexpr);
357362 }
358363 break;
359364 case T_ConvertRowtypeExpr:
@@ -600,6 +605,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
600605
601606 /* we store the string name because RTE_CTE RTEs need it */
602607 APP_JUMB_STRING(cte->ctename);
608+ APP_JUMB(cte->ctematerialized);
603609 JumbleQuery(jstate, castNode(Query, cte->ctequery));
604610 }
605611 break;
Show on old repository browser