SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO LOG;
SET search_path TO public;
-\o results/R_sample.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_sample.out.log > results/R_sample.out
-\! diff expected/R_sample.out results/R_sample.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
----
---- No. R-1-1 specified pattern of the object name
----
-- No. R-1-1-1
-\o results/R_1-1-1.out.log
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-1-1.out.log > results/R_1-1-1.out
-\! diff expected/R_1-1-1.out results/R_1-1-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-1-2
-\o results/R_1-1-2.out.log
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.t1 t_1, s1.t2 t_2 WHERE t_1.c1 = t_2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-1-2.out.log > results/R_1-1-2.out
-\! diff expected/R_1-1-2.out results/R_1-1-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t_1.c1 = t_2.c1)
+ -> Index Scan using t1_i1 on t1 t_1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t_2.c1
+ -> Seq Scan on t2 t_2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-1-3
-\o results/R_1-1-3.out.log
+\o results/ut-R.tmpout
/*+Rows(t_1 t_2 #1)*/
EXPLAIN SELECT * FROM s1.t1 t_1, s1.t2 t_2 WHERE t_1.c1 = t_2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-1-3.out.log > results/R_1-1-3.out
-\! diff expected/R_1-1-3.out results/R_1-1-3.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t_1.c1 = t_2.c1)
+ -> Index Scan using t1_i1 on t1 t_1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t_2.c1
+ -> Seq Scan on t2 t_2 (cost=xxx..xxx rows=100 width=xxx)
+
----
---- No. R-1-2 specified schema name in the hint option
----
-- No. R-1-2-1
-\o results/R_1-2-1.out.log
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-2-1.out.log > results/R_1-2-1.out
-\! diff expected/R_1-2-1.out results/R_1-2-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-2-2
-\o results/R_1-2-2.out.log
+\o results/ut-R.tmpout
/*+Rows(s1.t1 s1.t2 #1)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-2-2.out.log > results/R_1-2-2.out
-\! diff expected/R_1-2-2.out results/R_1-2-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
----
---- No. R-1-3 table doesn't exist in the hint option
----
-- No. R-1-3-1
-\o results/R_1-3-1.out.log
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-3-1.out.log > results/R_1-3-1.out
-\! diff expected/R_1-3-1.out results/R_1-3-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-3-2
-\o results/R_1-3-2.out.log
+\o results/ut-R.tmpout
/*+Rows(t3 t4 #1)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-3-2.out.log > results/R_1-3-2.out
-\! diff expected/R_1-3-2.out results/R_1-3-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
----
---- No. R-1-4 conflict table name
----
-- No. R-1-4-1
-\o results/R_1-4-1.out.log
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-4-1.out.log > results/R_1-4-1.out
-\! diff expected/R_1-4-1.out results/R_1-4-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-4-2
-\o results/R_1-4-2.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t1_1.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t1_1.c1
+ -> Seq Scan on t1 t1_1 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 t1 #1)*/
EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1;
-INFO: hint syntax error at or near "Rows(t1 t1 #1)"
+INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t1 #1)"
DETAIL: Relation name "t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
error hint:
Rows(t1 t1 #1)
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t1_1.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t1_1.c1
+ -> Seq Scan on t1 t1_1 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(s1.t1 s2.t1 #1)*/
EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1;
LOG: pg_hint_plan:
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t1_1.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t1_1.c1
+ -> Seq Scan on t1 t1_1 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = s2t1.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = s2t1.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: s2t1.c1
+ -> Seq Scan on t1 s2t1 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 s2t1 #1)*/
EXPLAIN SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = s2t1.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-4-2.out.log > results/R_1-4-2.out
-\! diff expected/R_1-4-2.out results/R_1-4-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = s2t1.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: s2t1.c1
+ -> Seq Scan on t1 s2t1 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-4-3
-\o results/R_1-4-3.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT *, (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ InitPlan 1 (returns $0)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1_1.c1 = t2_1.c1)
+ -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2_1.c1
+ -> Seq Scan on t2 t2_1 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT *, (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ InitPlan 1 (returns $0)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1_1.c1 = t2_1.c1)
+ -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2_1.c1
+ -> Seq Scan on t2 t2_1 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(st1 st2 #1)Rows(t1 t2 #1)*/
EXPLAIN SELECT *, (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-4-3.out.log > results/R_1-4-3.out
-\! diff expected/R_1-4-3.out results/R_1-4-3.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ InitPlan 1 (returns $0)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (st1.c1 = st2.c1)
+ -> Index Only Scan using t1_i1 on t1 st1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: st2.c1
+ -> Seq Scan on t2 st2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
----
---- No. R-1-5 conflict table name
----
-- No. R-1-5-1
-\o results/R_1-5-1.out.log
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-5-1.out.log > results/R_1-5-1.out
-\! diff expected/R_1-5-1.out results/R_1-5-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-5-2
-\o results/R_1-5-2.out.log
+\o results/ut-R.tmpout
/*+Rows(t1 t1 #1)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "Rows(t1 t1 #1)"
+INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t1 #1)"
DETAIL: Relation name "t1" is duplicated.
LOG: pg_hint_plan:
used hint:
Rows(t1 t1 #1)
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-5-2.out.log > results/R_1-5-2.out
-\! diff expected/R_1-5-2.out results/R_1-5-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-5-3
-\o results/R_1-5-3.out.log
+\o results/ut-R.tmpout
/*+(t1 t1)(t2 t2)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "(t1 t1)(t2 t2)"
+INFO: pg_hint_plan: hint syntax error at or near "(t1 t1)(t2 t2)"
DETAIL: Unrecognized hint keyword "".
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM s1.t1, s1.t2, s1.t3 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
+ Merge Cond: (t1.c1 = t3.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Scan using t3_i1 on t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
/*+(t1 t2 t1 t2)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
-INFO: hint syntax error at or near "(t1 t2 t1 t2)"
+INFO: pg_hint_plan: hint syntax error at or near "(t1 t2 t1 t2)"
DETAIL: Unrecognized hint keyword "".
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-5-3.out.log > results/R_1-5-3.out
-\! diff expected/R_1-5-3.out results/R_1-5-3.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
+ Merge Cond: (t1.c1 = t3.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Scan using t3_i1 on t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Scan using t4_i1 on t4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = t1.c1)
+
----
---- No. R-1-6 object type for the hint
----
-- No. R-1-6-1
-\o results/R_1-6-1.out.log
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-1.out.log > results/R_1-6-1.out
-\! diff expected/R_1-6-1.out results/R_1-6-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-6-2
-\o results/R_1-6-2.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM s1.p1 t1, s1.p1 t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=301 width=xxx)
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Append (cost=xxx..xxx rows=301 width=xxx)
+ -> Seq Scan on p1 t1_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p1c1 t1_2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c2 t1_3 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c3 t1_4 (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash (cost=xxx..xxx rows=301 width=xxx)
+ -> Append (cost=xxx..xxx rows=301 width=xxx)
+ -> Seq Scan on p1 t2_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p1c1 t2_2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c2 t2_3 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c3 t2_4 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.p1 t1, s1.p1 t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-2.out.log > results/R_1-6-2.out
-\! diff expected/R_1-6-2.out results/R_1-6-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Append (cost=xxx..xxx rows=301 width=xxx)
+ -> Seq Scan on p1 t1_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p1c1 t1_2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c2 t1_3 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c3 t1_4 (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash (cost=xxx..xxx rows=301 width=xxx)
+ -> Append (cost=xxx..xxx rows=301 width=xxx)
+ -> Seq Scan on p1 t2_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p1c1 t2_2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c2 t2_3 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c3 t2_4 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-6-3
-\o results/R_1-6-3.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM s1.ul1 t1, s1.ul1 t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1130 width=xxx)
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on ul1 t1 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on ul1 t2 (cost=xxx..xxx rows=1130 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.ul1 t1, s1.ul1 t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-3.out.log > results/R_1-6-3.out
-\! diff expected/R_1-6-3.out results/R_1-6-3.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on ul1 t1 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on ul1 t2 (cost=xxx..xxx rows=1130 width=xxx)
+
-- No. R-1-6-4
CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL);
-\o results/R_1-6-4.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM tm1 t1, tm1 t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1130 width=xxx)
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on tm1 t1 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on tm1 t2 (cost=xxx..xxx rows=1130 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM tm1 t1, tm1 t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-4.out.log > results/R_1-6-4.out
-\! diff expected/R_1-6-4.out results/R_1-6-4.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on tm1 t1 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on tm1 t2 (cost=xxx..xxx rows=1130 width=xxx)
+
-- No. R-1-6-5
-\o results/R_1-6-5.out.log
-EXPLAIN SELECT * FROM pg_catalog.pg_class t1, pg_catalog.pg_class t2 WHERE t1.oid = t2.oid;
+CREATE TEMP TABLE t_pg_class AS SELECT * from pg_class LIMIT 100;
+\o results/ut-R.tmpout
+EXPLAIN SELECT * FROM t_pg_class t1, t_pg_class t2 WHERE t1.oid = t2.oid;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=450 width=xxx)
+ Hash Cond: (t1.oid = t2.oid)
+ -> Seq Scan on t_pg_class t1 (cost=xxx..xxx rows=300 width=xxx)
+ -> Hash (cost=xxx..xxx rows=300 width=xxx)
+ -> Seq Scan on t_pg_class t2 (cost=xxx..xxx rows=300 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
-EXPLAIN SELECT * FROM pg_catalog.pg_class t1, pg_catalog.pg_class t2 WHERE t1.oid = t2.oid;
+EXPLAIN SELECT * FROM t_pg_class t1, t_pg_class t2 WHERE t1.oid = t2.oid;
LOG: pg_hint_plan:
used hint:
Rows(t1 t2 #1)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-5.out.log > results/R_1-6-5.out
-\! diff expected/R_1-6-5.out results/R_1-6-5.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (t1.oid = t2.oid)
+ -> Seq Scan on t_pg_class t1 (cost=xxx..xxx rows=300 width=xxx)
+ -> Hash (cost=xxx..xxx rows=300 width=xxx)
+ -> Seq Scan on t_pg_class t2 (cost=xxx..xxx rows=300 width=xxx)
+
-- No. R-1-6-6
-- refer ut-fdw.sql
-- No. R-1-6-7
-\o results/R_1-6-7.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM s1.f1() t1, s1.f1() t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t2.c1)
+ -> Function Scan on f1 t1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Function Scan on f1 t2 (cost=xxx..xxx rows=1 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.f1() t1, s1.f1() t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-7.out.log > results/R_1-6-7.out
-\! diff expected/R_1-6-7.out results/R_1-6-7.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t2.c1)
+ -> Function Scan on f1 t1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Function Scan on f1 t2 (cost=xxx..xxx rows=1 width=xxx)
+
-- No. R-1-6-8
-\o results/R_1-6-8.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=3 width=xxx)
+ Hash Cond: (t2.c1 = "*VALUES*".column1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash (cost=xxx..xxx rows=3 width=xxx)
+ -> Values Scan on "*VALUES*" (cost=xxx..xxx rows=3 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=3 width=xxx)
+ Hash Cond: (t2.c1 = "*VALUES*".column1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash (cost=xxx..xxx rows=3 width=xxx)
+ -> Values Scan on "*VALUES*" (cost=xxx..xxx rows=3 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(*VALUES* t2 #1)*/
EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-8.out.log > results/R_1-6-8.out
-\! diff expected/R_1-6-8.out results/R_1-6-8.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (t2.c1 = "*VALUES*".column1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash (cost=xxx..xxx rows=3 width=xxx)
+ -> Values Scan on "*VALUES*" (cost=xxx..xxx rows=3 width=xxx)
+
-- No. R-1-6-9
-\o results/R_1-6-9.out.log
+\o results/ut-R.tmpout
EXPLAIN WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1_1.c1 = t2.c1)
+ -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = (max(t1_1.c1)))
+
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)Rows(t1 c1 +1)*/
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;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-9.out.log > results/R_1-6-9.out
-\! diff expected/R_1-6-9.out results/R_1-6-9.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1_1.c1 = t2.c1)
+ -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = (max(t1_1.c1)))
+
-- No. R-1-6-10
-\o results/R_1-6-10.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM s1.v1 t1, s1.v1 t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
+ -> Seq Scan on t1 v1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 v1t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.v1 t1, s1.v1 t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
+ -> Seq Scan on t1 v1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 v1t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(v1t1 v1t1_ #1)*/
EXPLAIN SELECT * FROM s1.v1 t1, s1.v1_ t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-10.out.log > results/R_1-6-10.out
-\! diff expected/R_1-6-10.out results/R_1-6-10.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (v1t1.c1 = v1t1_.c1)
+ -> Seq Scan on t1 v1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 v1t1_ (cost=xxx..xxx rows=1000 width=xxx)
+
-- No. R-1-6-11
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.c1 = (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1);
- QUERY PLAN
--------------------------------------------------------------------------------------------------
- Nested Loop (cost=12.28..22.56 rows=1 width=29)
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
InitPlan 1 (returns $0)
- -> Aggregate (cost=12.00..12.01 rows=1 width=4)
- -> Merge Join (cost=5.60..11.75 rows=100 width=4)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
Merge Cond: (st1.c1 = st2.c1)
- -> Index Only Scan using t1_i1 on t1 st1 (cost=0.28..44.27 rows=1000 width=4)
- -> Sort (cost=5.32..5.57 rows=100 width=4)
+ -> Index Only Scan using t1_i1 on t1 st1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: st2.c1
- -> Seq Scan on t2 st2 (cost=0.00..2.00 rows=100 width=4)
- -> Index Scan using t1_i1 on t1 (cost=0.28..8.29 rows=1 width=15)
+ -> Seq Scan on t2 st2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = $0)
- -> Seq Scan on t2 (cost=0.00..2.25 rows=1 width=14)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
Filter: (c1 = $0)
-(13 rows)
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)Rows(st1 st2 #1)*/
EXPLAIN (COSTS true) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.c1 = (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1);
LOG: pg_hint_plan:
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------------------------------------
- Nested Loop (cost=12.03..22.31 rows=1 width=29)
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
InitPlan 1 (returns $0)
- -> Aggregate (cost=11.75..11.76 rows=1 width=4)
- -> Merge Join (cost=5.60..11.75 rows=1 width=4)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
Merge Cond: (st1.c1 = st2.c1)
- -> Index Only Scan using t1_i1 on t1 st1 (cost=0.28..44.27 rows=1000 width=4)
- -> Sort (cost=5.32..5.57 rows=100 width=4)
+ -> Index Only Scan using t1_i1 on t1 st1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
Sort Key: st2.c1
- -> Seq Scan on t2 st2 (cost=0.00..2.00 rows=100 width=4)
- -> Index Scan using t1_i1 on t1 (cost=0.28..8.29 rows=1 width=15)
+ -> Seq Scan on t2 st2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = $0)
- -> Seq Scan on t2 (cost=0.00..2.25 rows=1 width=14)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
Filter: (c1 = $0)
-(13 rows)
--
-- There are cases where difference in the measured value and predicted value
-- depending upon the version of PostgreSQL
--
-\o results/R_1-6-11.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 st2 #1)*/
EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1;
LOG: pg_hint_plan:
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #1)*/
EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-6-11.out.log > results/R_1-6-11.out
-\! diff expected/R_1-6-11.out results/R_1-6-11.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
----
---- No. R-1-7 specified number of conditions
----
-- No. R-1-7-1
-\o results/R_1-7-1.out.log
+\o results/ut-R.tmpout
/*+Rows(t1 #1)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: Rows hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
Rows(t1 #1)
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-7-1.out.log > results/R_1-7-1.out
-\! diff expected/R_1-7-1.out results/R_1-7-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-7-2
-\o results/R_1-7-2.out.log
+\o results/ut-R.tmpout
/*+Rows(t1 t2 1)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "1"
-DETAIL: unrecognized rows value type notation.
+INFO: pg_hint_plan: hint syntax error at or near "1"
+DETAIL: Unrecognized rows value type notation.
LOG: pg_hint_plan:
used hint:
not used hint:
Rows(t1 t2 1)
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-7-2.out.log > results/R_1-7-2.out
-\! diff expected/R_1-7-2.out results/R_1-7-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-1-7-3
-\o results/R_1-7-3.out.log
+\o results/ut-R.tmpout
/*+Rows(t1 t2 #notrows)*/
EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "notrows"
+INFO: pg_hint_plan: hint syntax error at or near "notrows"
DETAIL: Rows hint requires valid number as rows estimation.
LOG: pg_hint_plan:
used hint:
Rows(t1 t2 #notrows)
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_1-7-3.out.log > results/R_1-7-3.out
-\! diff expected/R_1-7-3.out results/R_1-7-3.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
----
---- No. R-2-1 some complexity query blocks
----
-- No. R-2-1-1
-\o results/R_2-1-1.out.log
+\o results/ut-R.tmpout
/*+
Leading(bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ InitPlan 1 (returns $1)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ InitPlan 2 (returns $3)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-1.out.log > results/R_2-1-1.out
-\! diff expected/R_2-1-1.out results/R_2-1-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ InitPlan 1 (returns $1)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ InitPlan 2 (returns $3)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
-- No. R-2-1-2
-\o results/R_2-1-2.out.log
+\o results/ut-R.tmpout
/*+
Leading(bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ InitPlan 1 (returns $1)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ InitPlan 2 (returns $3)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ InitPlan 3 (returns $5)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b3t1.c1 = b3t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t4.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b3t1.c1)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-2.out.log > results/R_2-1-2.out
-\! diff expected/R_2-1-2.out results/R_2-1-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ InitPlan 1 (returns $1)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ InitPlan 2 (returns $3)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ InitPlan 3 (returns $5)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b3t1.c1 = b3t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t4.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b3t1.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
-- No. R-2-1-3
-\o results/R_2-1-3.out.log
+\o results/ut-R.tmpout
/*+
Leading(bmt4 bmt3 bmt2 bmt1)
*/
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt2.c1)
+ -> Hash Join (cost=xxx..xxx rows=1130 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt4.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
/*+
Leading(bmt4 bmt3 bmt2 bmt1)
Rows(bmt4 bmt3 #1)Rows(bmt4 bmt3 bmt2 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-3.out.log > results/R_2-1-3.out
-\! diff expected/R_2-1-3.out results/R_2-1-3.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (bmt2.c1 = bmt1.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt4.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
+
-- No. R-2-1-4
-\o results/R_2-1-4.out.log
+\o results/ut-R.tmpout
/*+
Leading(bmt4 bmt3 bmt2 bmt1)
*/
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt2.c1)
+ -> Hash Join (cost=xxx..xxx rows=1130 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt4.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
/*+
Leading(bmt4 bmt3 bmt2 bmt1)
Rows(bmt4 bmt3 #1)Rows(bmt4 bmt3 bmt2 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-4.out.log > results/R_2-1-4.out
-\! diff expected/R_2-1-4.out results/R_2-1-4.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (bmt2.c1 = bmt1.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt4.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
+
-- No. R-2-1-5
-\o results/R_2-1-5.out.log
+\o results/ut-R.tmpout
/*+
Leading(bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
) AND bmt1.c1 <> (
SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
-)
-;
+);
LOG: pg_hint_plan:
used hint:
MergeJoin(b1t2 b1t3)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ InitPlan 1 (returns $1)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ InitPlan 2 (returns $3)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=998 width=xxx)
+ Filter: ((c1 <> $1) AND (c1 <> $3))
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-5.out.log > results/R_2-1-5.out
-\! diff expected/R_2-1-5.out results/R_2-1-5.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ InitPlan 1 (returns $1)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ InitPlan 2 (returns $3)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=998 width=xxx)
+ Filter: ((c1 <> $1) AND (c1 <> $3))
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
-- No. R-2-1-6
-\o results/R_2-1-6.out.log
+\o results/ut-R.tmpout
/*+
Leading(bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ InitPlan 1 (returns $1)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ InitPlan 2 (returns $3)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ InitPlan 3 (returns $5)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b3t1.c1 = b3t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t4.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b3t1.c1)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=997 width=xxx)
+ Filter: ((c1 <> $1) AND (c1 <> $3) AND (c1 <> $5))
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-6.out.log > results/R_2-1-6.out
-\! diff expected/R_2-1-6.out results/R_2-1-6.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ InitPlan 1 (returns $1)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ InitPlan 2 (returns $3)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ InitPlan 3 (returns $5)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b3t1.c1 = b3t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t4.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b3t1.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=997 width=xxx)
+ Filter: ((c1 <> $1) AND (c1 <> $3) AND (c1 <> $5))
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
-- No. R-2-1-7
-\o results/R_2-1-7.out.log
+\o results/ut-R.tmpout
/*+
Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (bmt3.c1 = bmt1.c1)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: bmt1.c1
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt1.c1 = (max(b1t1.c1)))
+ -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
, c1, c2
WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
AND bmt1.c1 = c1.c1
-AND bmt1.c1 = c2.c1
-;
+AND bmt1.c1 = c2.c1;
LOG: pg_hint_plan:
used hint:
MergeJoin(b1t2 b1t3)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-7.out.log > results/R_2-1-7.out
-\! diff expected/R_2-1-7.out results/R_2-1-7.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (bmt3.c1 = bmt1.c1)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: bmt1.c1
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt1.c1 = (max(b1t1.c1)))
+ -> Seq Scan on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
-- No. R-2-1-8
-\o results/R_2-1-8.out.log
+\o results/ut-R.tmpout
/*+
Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
AND bmt1.c1 = c1.c1
AND bmt1.c1 = c2.c1
-AND bmt1.c1 = c3.c1
-;
+AND bmt1.c1 = c3.c1;
LOG: pg_hint_plan:
used hint:
MergeJoin(b1t2 b1t3)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: bmt1.c1
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1)))
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b3t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b3t1.c1 = b3t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t4.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b3t1.c1)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = (max(b2t1.c1)))
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
AND bmt1.c1 = c1.c1
AND bmt1.c1 = c2.c1
-AND bmt1.c1 = c3.c1
-;
+AND bmt1.c1 = c3.c1;
LOG: pg_hint_plan:
used hint:
MergeJoin(b1t2 b1t3)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-1-8.out.log > results/R_2-1-8.out
-\! diff expected/R_2-1-8.out results/R_2-1-8.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: bmt1.c1
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1)))
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Seq Scan on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t1.c1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: (max(b3t1.c1))
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b3t1.c1 = b3t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t4.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b3t1.c1)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t2.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = (max(b2t1.c1)))
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
----
---- No. R-2-2 the number of the tables per quiry block
----
-- No. R-2-2-1
-\o results/R_2-2-1.out.log
+\o results/ut-R.tmpout
/*+
Leading(c1 bmt1)
*/
AND bmt1.c1 = c1.c1
AND bmt1.c1 <> (
SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1
-)
-;
+);
LOG: pg_hint_plan:
used hint:
-Leading(c1 bmt1)
not used hint:
+Leading(c1 bmt1)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ InitPlan 1 (returns $0)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
+ InitPlan 2 (returns $1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
+
+\o results/ut-R.tmpout
/*+
Leading(c1 bmt1)
Rows(bmt1 c1 #1)
AND bmt1.c1 = c1.c1
AND bmt1.c1 <> (
SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1
-)
-;
+);
LOG: pg_hint_plan:
used hint:
-Leading(c1 bmt1)
-Rows(bmt1 c1 #1)
not used hint:
+Leading(c1 bmt1)
Rows(b1t1 c1 #1)
Rows(b2t1 c1 #1)
Rows(b3t1 c1 #1)
+Rows(bmt1 c1 #1)
duplication hint:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-1.out.log > results/R_2-2-1.out
-\! diff expected/R_2-2-1.out results/R_2-2-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ InitPlan 1 (returns $0)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
+ InitPlan 2 (returns $1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
+
-- No. R-2-2-2
-\o results/R_2-2-2.out.log
+\o results/ut-R.tmpout
/*+
Leading(c1 bmt2 bmt1)
Leading(b1t2 b1t1)
AND bmt1.c1 = c1.c1
AND bmt1.c1 <> (
SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.c1 = b3t2.c1
-)
-;
+);
LOG: pg_hint_plan:
used hint:
MergeJoin(b1t1 b1t2)
MergeJoin(b2t1 b2t2)
MergeJoin(b3t1 b3t2)
-MergeJoin(bmt2 c1)
-HashJoin(bmt1 bmt2 c1)
-Leading(c1 bmt2 bmt1)
Leading(b1t2 b1t1)
Leading(b2t2 b2t1)
Leading(b3t2 b3t1)
not used hint:
+MergeJoin(bmt2 c1)
+HashJoin(bmt1 bmt2 c1)
+Leading(c1 bmt2 bmt1)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ InitPlan 1 (returns $0)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=100 width=xxx)
+ InitPlan 2 (returns $1)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b3t2.c1
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t1.c1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t1.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(c1 bmt2 bmt1)
Leading(b1t2 b1t1)
MergeJoin(b1t1 b1t2)
MergeJoin(b2t1 b2t2)
MergeJoin(b3t1 b3t2)
-MergeJoin(bmt2 c1)
-HashJoin(bmt1 bmt2 c1)
-Leading(c1 bmt2 bmt1)
Leading(b1t2 b1t1)
Leading(b2t2 b2t1)
Leading(b3t2 b3t1)
Rows(b1t1 b1t2 #1)
Rows(b2t1 b2t2 #1)
Rows(b3t1 b3t2 #1)
+not used hint:
+MergeJoin(bmt2 c1)
+HashJoin(bmt1 bmt2 c1)
+Leading(c1 bmt2 bmt1)
Rows(bmt2 c1 #1)
Rows(bmt1 bmt2 c1 #1)
-not used hint:
duplication hint:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-2.out.log > results/R_2-2-2.out
-\! diff expected/R_2-2-2.out results/R_2-2-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ InitPlan 1 (returns $0)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=100 width=xxx)
+ InitPlan 2 (returns $1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b3t2.c1
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t1.c1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t1.c1)
+
-- No. R-2-2-3
-\o results/R_2-2-3.out.log
+\o results/ut-R.tmpout
/*+
Leading(c1 bmt4 bmt3 bmt2 bmt1)
Leading(b1t4 b1t3 b1t2 b1t1)
FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
AND bmt1.c1 <> (
SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
-)
-;
+);
LOG: pg_hint_plan:
used hint:
HashJoin(b1t3 b1t4)
HashJoin(b2t3 b2t4)
HashJoin(b3t3 b3t4)
-MergeJoin(bmt4 c1)
NestLoop(b1t2 b1t3 b1t4)
NestLoop(b2t2 b2t3 b2t4)
NestLoop(b3t2 b3t3 b3t4)
-HashJoin(bmt3 bmt4 c1)
MergeJoin(b1t1 b1t2 b1t3 b1t4)
MergeJoin(b2t1 b2t2 b2t3 b2t4)
MergeJoin(b3t1 b3t2 b3t3 b3t4)
-NestLoop(bmt2 bmt3 bmt4 c1)
-MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
-Leading(c1 bmt4 bmt3 bmt2 bmt1)
Leading(b1t4 b1t3 b1t2 b1t1)
Leading(b2t4 b2t3 b2t2 b2t1)
Leading(b3t4 b3t3 b3t2 b3t1)
not used hint:
+MergeJoin(bmt4 c1)
+HashJoin(bmt3 bmt4 c1)
+NestLoop(bmt2 bmt3 bmt4 c1)
+MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ InitPlan 1 (returns $1)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b2t2.c1
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1130 width=xxx)
+ Hash Cond: (b2t3.c1 = b2t4.c1)
+ -> Seq Scan on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t3.c1)
+ InitPlan 2 (returns $3)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b3t2.c1
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1130 width=xxx)
+ Hash Cond: (b3t3.c1 = b3t4.c1)
+ -> Seq Scan on t3 b3t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b3t3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
+ Join Filter: (b1t1.c1 = bmt1.c1)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: b1t2.c1
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1130 width=xxx)
+ Hash Cond: (b1t3.c1 = b1t4.c1)
+ -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ Filter: (c1 <> $3)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(c1 bmt4 bmt3 bmt2 bmt1)
Leading(b1t4 b1t3 b1t2 b1t1)
FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
AND bmt1.c1 <> (
SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1
-)
-;
+);
LOG: pg_hint_plan:
used hint:
HashJoin(b1t3 b1t4)
HashJoin(b2t3 b2t4)
HashJoin(b3t3 b3t4)
-MergeJoin(bmt4 c1)
NestLoop(b1t2 b1t3 b1t4)
NestLoop(b2t2 b2t3 b2t4)
NestLoop(b3t2 b3t3 b3t4)
-HashJoin(bmt3 bmt4 c1)
MergeJoin(b1t1 b1t2 b1t3 b1t4)
MergeJoin(b2t1 b2t2 b2t3 b2t4)
MergeJoin(b3t1 b3t2 b3t3 b3t4)
-NestLoop(bmt2 bmt3 bmt4 c1)
-MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
-Leading(c1 bmt4 bmt3 bmt2 bmt1)
Leading(b1t4 b1t3 b1t2 b1t1)
Leading(b2t4 b2t3 b2t2 b2t1)
Leading(b3t4 b3t3 b3t2 b3t1)
Rows(b1t3 b1t4 #1)
Rows(b2t3 b2t4 #1)
Rows(b3t3 b3t4 #1)
-Rows(bmt4 c1 #1)
Rows(b1t2 b1t3 b1t4 #1)
Rows(b2t2 b2t3 b2t4 #1)
Rows(b3t2 b3t3 b3t4 #1)
-Rows(bmt3 bmt4 c1 #1)
Rows(b1t1 b1t2 b1t3 b1t4 #1)
Rows(b2t1 b2t2 b2t3 b2t4 #1)
Rows(b3t1 b3t2 b3t3 b3t4 #1)
+not used hint:
+MergeJoin(bmt4 c1)
+HashJoin(bmt3 bmt4 c1)
+NestLoop(bmt2 bmt3 bmt4 c1)
+MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Rows(bmt4 c1 #1)
+Rows(bmt3 bmt4 c1 #1)
Rows(bmt2 bmt3 bmt4 c1 #1)
Rows(bmt1 bmt2 bmt3 bmt4 c1 #1)
-not used hint:
duplication hint:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-3.out.log > results/R_2-2-3.out
-\! diff expected/R_2-2-3.out results/R_2-2-3.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ InitPlan 1 (returns $1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: b2t2.c1
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b2t3.c1 = b2t4.c1)
+ -> Seq Scan on t3 b2t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 b2t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b2t3.c1)
+ InitPlan 2 (returns $3)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: b3t2.c1
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b3t3.c1 = b3t4.c1)
+ -> Seq Scan on t3 b3t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 b3t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b3t3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
+ Join Filter: (b1t1.c1 = bmt1.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: b1t2.c1
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t3.c1 = b1t4.c1)
+ -> Seq Scan on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ Filter: (c1 <> $3)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
+
-- No. R-2-2-4
-\o results/R_2-2-4.out.log
+\o results/ut-R.tmpout
/*+
Leading(c1 bmt4 bmt3 bmt2 bmt1)
Leading(b1t4 b1t3 b1t2 b1t1)
FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
AND bmt1.c1 <> (
SELECT b3t1.c1 FROM s1.t1 b3t1
-)
-;
+);
LOG: pg_hint_plan:
used hint:
MergeJoin(b1t3 b1t4)
-MergeJoin(bmt4 c1)
HashJoin(b1t2 b1t3 b1t4)
-HashJoin(bmt3 bmt4 c1)
NestLoop(b1t1 b1t2 b1t3 b1t4)
+Leading(b1t4 b1t3 b1t2 b1t1)
+not used hint:
+MergeJoin(bmt4 c1)
+HashJoin(bmt3 bmt4 c1)
NestLoop(bmt2 bmt3 bmt4 c1)
MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
Leading(c1 bmt4 bmt3 bmt2 bmt1)
-Leading(b1t4 b1t3 b1t2 b1t1)
-not used hint:
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ InitPlan 1 (returns $0)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
+ InitPlan 2 (returns $1)
+ -> Seq Scan on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt2.c1 = bmt1.c1)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
+ Join Filter: (b1t1.c1 = bmt2.c1)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Materialize (cost=xxx..xxx rows=100 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (b1t3.c1 = b1t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1130 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(c1 bmt4 bmt3 bmt2 bmt1)
Leading(b1t4 b1t3 b1t2 b1t1)
FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1
AND bmt1.c1 <> (
SELECT b3t1.c1 FROM s1.t1 b3t1
-)
-;
+);
LOG: pg_hint_plan:
used hint:
MergeJoin(b1t3 b1t4)
-MergeJoin(bmt4 c1)
HashJoin(b1t2 b1t3 b1t4)
-HashJoin(bmt3 bmt4 c1)
NestLoop(b1t1 b1t2 b1t3 b1t4)
+Leading(b1t4 b1t3 b1t2 b1t1)
+Rows(b1t3 b1t4 #1)
+Rows(b1t2 b1t3 b1t4 #1)
+Rows(b1t1 b1t2 b1t3 b1t4 #1)
+not used hint:
+MergeJoin(bmt4 c1)
+HashJoin(bmt3 bmt4 c1)
NestLoop(bmt2 bmt3 bmt4 c1)
MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
Leading(c1 bmt4 bmt3 bmt2 bmt1)
-Leading(b1t4 b1t3 b1t2 b1t1)
-Rows(b1t3 b1t4 #1)
Rows(bmt4 c1 #1)
-Rows(b1t2 b1t3 b1t4 #1)
Rows(bmt3 bmt4 c1 #1)
-Rows(b1t1 b1t2 b1t3 b1t4 #1)
Rows(bmt2 bmt3 bmt4 c1 #1)
Rows(bmt1 bmt2 bmt3 bmt4 c1 #1)
-not used hint:
duplication hint:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-4.out.log > results/R_2-2-4.out
-\! diff expected/R_2-2-4.out results/R_2-2-4.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ InitPlan 1 (returns $0)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = 1)
+ InitPlan 2 (returns $1)
+ -> Seq Scan on t1 b3t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop (cost=xxx..xxx rows=10 width=xxx)
+ Join Filter: (bmt2.c1 = bmt1.c1)
+ -> Nested Loop (cost={inf}..{inf} rows=100 width=xxx)
+ Join Filter: (b1t1.c1 = bmt2.c1)
+ -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Materialize (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (b1t3.c1 = b1t2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t3.c1 = b1t4.c1)
+ -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ Filter: (c1 <> $1)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = b1t3.c1)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt3.c1)
+
----
---- No. R-2-3 RULE or VIEW
----
-- No. R-2-3-1
-\o results/R_2-3-1.out.log
+\o results/ut-R.tmpout
/*+
Leading(r1 t1 t2 t3 t4)
*/
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r1 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+\o results/ut-R.tmpout
/*+
Leading(r1 t1 t2 t3 t4)
Rows(r1 t1 t2 t3 t4 #2)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t2.c1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r1 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
+\o results/ut-R.tmpout
/*+
Leading(r1_ b1t1 b1t2 b1t3 b1t4)
*/
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r1_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+\o results/ut-R.tmpout
/*+
Leading(r1_ b1t1 b1t2 b1t3 b1t4)
Rows(r1_ b1t1 b1t2 b1t3 b1t4 #2)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-1.out.log > results/R_2-3-1.out
-\! diff expected/R_2-3-1.out results/R_2-3-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Tid Scan on t4 b1t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Tid Scan on t3 b1t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r1_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
-- No. R-2-3-2
-\o results/R_2-3-2.out.log
+\o results/ut-R.tmpout
/*+
Leading(r2 t1 t2 t3 t4)
*/
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r2 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r2 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+\o results/ut-R.tmpout
/*+
Leading(r2 t1 t2 t3 t4)
Rows(r2 t1 t2 t3 t4 #2)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t2.c1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r2 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t2.c1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r2 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
+\o results/ut-R.tmpout
/*+
Leading(r2_ b1t1 b1t2 b1t3 b1t4)
Leading(r2_ b2t1 b2t2 b2t3 b2t4)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r2_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r2_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b2t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+\o results/ut-R.tmpout
/*+
Leading(r2_ b1t1 b1t2 b1t3 b1t4)
Leading(r2_ b2t1 b2t2 b2t3 b2t4)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-2.out.log > results/R_2-3-2.out
-\! diff expected/R_2-3-2.out results/R_2-3-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Tid Scan on t4 b1t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Tid Scan on t3 b1t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r2_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Tid Scan on t4 b2t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Tid Scan on t3 b2t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r2_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
-- No. R-2-3-3
-\o results/R_2-3-3.out.log
+\o results/ut-R.tmpout
/*+
Leading(r3 t1 t2 t3 t4)
*/
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+\o results/ut-R.tmpout
/*+
Leading(r3 t1 t2 t3 t4)
Rows(r3 t1 t2 t3 t4 #2)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t2.c1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t2.c1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t4.c1)
+ -> Tid Scan on t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t3.c1)
+ -> Tid Scan on t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (t1.c1 = t2.c1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3 (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
+\o results/ut-R.tmpout
/*+
Leading(r3_ b1t1 b1t2 b1t3 b1t4)
Leading(r3_ b2t1 b2t2 b2t3 b2t4)
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b2t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b3t1.c1 = b3t4.c1)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (b3t1.c1 = b3t3.c1)
+ -> Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (b3t1.c1 = b3t2.c1)
+ -> Nested Loop (cost=xxx..xxx rows=6 width=xxx)
+ -> Index Scan using t1_i1 on t1 b3t1 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+ -> Sort (cost=xxx..xxx rows=1 width=xxx)
+ Sort Key: b3t2.c1
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b3t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+
+\o results/ut-R.tmpout
/*+
Leading(r3_ b1t1 b1t2 b1t3 b1t4)
Leading(r3_ b2t1 b2t2 b2t3 b2t4)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-3.out.log > results/R_2-3-3.out
-\! diff expected/R_2-3-3.out results/R_2-3-3.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Tid Scan on t4 b1t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Tid Scan on t3 b1t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Seq Scan on t2 b1t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 b1t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Tid Scan on t4 b2t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Tid Scan on t3 b2t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Seq Scan on t2 b2t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 b2t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b3t1.c1 = b3t4.c1)
+ -> Tid Scan on t4 b3t4 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b3t1.c1 = b3t3.c1)
+ -> Tid Scan on t3 b3t3 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ Join Filter: (b3t1.c1 = b3t2.c1)
+ -> Seq Scan on t2 b3t2 (cost=xxx..xxx rows=1 width=xxx)
+ Filter: (ctid = '(1,1)'::tid)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Tid Scan on t1 b3t1 (cost=xxx..xxx rows=1 width=xxx)
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on r3_ (cost=xxx..xxx rows=6 width=xxx)
+ Filter: (c1 = 1)
+
-- No. R-2-3-4
-\o results/R_2-3-4.out.log
+\o results/ut-R.tmpout
/*+HashJoin(v1t1 v1t1)*/
EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
-INFO: hint syntax error at or near "HashJoin(v1t1 v1t1)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(v1t1 v1t1)"
DETAIL: Relation name "v1t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
error hint:
HashJoin(v1t1 v1t1)
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
+ -> Seq Scan on t1 v1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 v1t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+
+\o results/ut-R.tmpout
/*+HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)*/
EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
-INFO: hint syntax error at or near "HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)"
DETAIL: Relation name "v1t1" is ambiguous.
-INFO: hint syntax error at or near "Rows(v1t1 v1t1 #1)"
+INFO: pg_hint_plan: hint syntax error at or near "Rows(v1t1 v1t1 #1)"
DETAIL: Relation name "v1t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
Rows(v1t1 v1t1 #1)
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-4.out.log > results/R_2-3-4.out
-\! diff expected/R_2-3-4.out results/R_2-3-4.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
+ -> Seq Scan on t1 v1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 v1t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+
-- No. R-2-3-5
-\o results/R_2-3-5.out.log
+\o results/ut-R.tmpout
/*+NestLoop(v1t1 v1t1_)*/
EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
LOG: pg_hint_plan:
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 v1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Scan using t1_i1 on t1 v1t1_ (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = v1t1.c1)
+
+\o results/ut-R.tmpout
/*+NestLoop(v1t1 v1t1_)Rows(v1t1 v1t1_ #1)*/
EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-5.out.log > results/R_2-3-5.out
-\! diff expected/R_2-3-5.out results/R_2-3-5.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on t1 v1t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Scan using t1_i1 on t1 v1t1_ (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = v1t1.c1)
+
-- No. R-2-3-6
-\o results/R_2-3-6.out.log
+\o results/ut-R.tmpout
/*+RowsHashJoin(r4t1 r4t1)*/
EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "RowsHashJoin(r4t1 r4t1)"
+INFO: pg_hint_plan: hint syntax error at or near "RowsHashJoin(r4t1 r4t1)"
DETAIL: Unrecognized hint keyword "RowsHashJoin".
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (r4t1.c1 = r4t1_1.c1)
+ -> Seq Scan on t1 r4t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 r4t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+
+\o results/ut-R.tmpout
/*+RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)*/
EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)"
+INFO: pg_hint_plan: hint syntax error at or near "RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)"
DETAIL: Unrecognized hint keyword "RowsHashJoin".
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-6.out.log > results/R_2-3-6.out
-\! diff expected/R_2-3-6.out results/R_2-3-6.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1000 width=xxx)
+ Hash Cond: (r4t1.c1 = r4t1_1.c1)
+ -> Seq Scan on t1 r4t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 r4t1_1 (cost=xxx..xxx rows=1000 width=xxx)
+
-- No. R-2-3-7
-\o results/R_2-3-7.out.log
+\o results/ut-R.tmpout
/*+NestLoop(r4t1 r5t1)*/
EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1000 width=xxx)
+ -> Seq Scan on t1 r4t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Scan using t1_i1 on t1 r5t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = r4t1.c1)
+
+\o results/ut-R.tmpout
/*+NestLoop(r4t1 r5t1)Rows(r4t1 r5t1 #1)*/
EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-7.out.log > results/R_2-3-7.out
-\! diff expected/R_2-3-7.out results/R_2-3-7.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on t1 r4t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Scan using t1_i1 on t1 r5t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = r4t1.c1)
+
----
---- No. R-2-4 VALUES clause
----
-- No. R-2-4-1
-\o results/R_2-4-1.out.log
-EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
+\o results/ut-R.tmpout
+EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=2 width=xxx)
+ Hash Cond: (t2.c1 = "*VALUES*".column1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash (cost=xxx..xxx rows=2 width=xxx)
+ -> Values Scan on "*VALUES*" (cost=xxx..xxx rows=2 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = t2.c1)
+
+\o results/ut-R.tmpout
/*+ Leading(t3 t1 t2) Rows(t3 t1 #2)Rows(t3 t1 t2 #2)*/
-EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
+EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
-/*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #2)*/
-EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Hash Join (cost=xxx..xxx rows=2 width=xxx)
+ Hash Cond: (t2.c1 = "*VALUES*".column1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash (cost=xxx..xxx rows=2 width=xxx)
+ -> Values Scan on "*VALUES*" (cost=xxx..xxx rows=2 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = t2.c1)
+
+\o results/ut-R.tmpout
+/*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #20)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
LOG: pg_hint_plan:
used hint:
Leading(*VALUES* t1 t2)
Rows(*VALUES* t1 #2)
-Rows(*VALUES* t1 t2 #2)
+Rows(*VALUES* t1 t2 #20)
not used hint:
duplication hint:
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-4-1.out.log > results/R_2-4-1.out
-\! diff expected/R_2-4-1.out results/R_2-4-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=20 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=2 width=xxx)
+ -> Values Scan on "*VALUES*" (cost=xxx..xxx rows=2 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = "*VALUES*".column1)
+ -> Index Scan using t2_i1 on t2 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = t1.c1)
+
-- No. R-2-4-2
-\o results/R_2-4-2.out.log
-EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
+\o results/ut-R.tmpout
+EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t2.c1 = "*VALUES*_1".column1)
+ -> Hash Join (cost=xxx..xxx rows=2 width=xxx)
+ Hash Cond: (t2.c1 = "*VALUES*".column1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash (cost=xxx..xxx rows=2 width=xxx)
+ -> Values Scan on "*VALUES*" (cost=xxx..xxx rows=2 width=xxx)
+ -> Materialize (cost=xxx..xxx rows=2 width=xxx)
+ -> Values Scan on "*VALUES*_1" (cost=xxx..xxx rows=2 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = t2.c1)
+
+\o results/ut-R.tmpout
/*+ Leading(t4 t3 t2 t1) Rows(t4 t3 #2) Rows(t4 t3 t2 #2)Rows(t4 t3 t2 t1 #2)*/
-EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
+EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t2.c1 = "*VALUES*_1".column1)
+ -> Hash Join (cost=xxx..xxx rows=2 width=xxx)
+ Hash Cond: (t2.c1 = "*VALUES*".column1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash (cost=xxx..xxx rows=2 width=xxx)
+ -> Values Scan on "*VALUES*" (cost=xxx..xxx rows=2 width=xxx)
+ -> Materialize (cost=xxx..xxx rows=2 width=xxx)
+ -> Values Scan on "*VALUES*_1" (cost=xxx..xxx rows=2 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = t2.c1)
+
+\o results/ut-R.tmpout
/*+ Leading(*VALUES* t3 t2 t1) Rows(t4 t3 #2)Rows(*VALUES* t3 t2 #2)Rows(*VALUES* t3 t2 t1 #2)*/
-EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
-INFO: hint syntax error at or near "Rows(*VALUES* t3 t2 #2)Rows(*VALUES* t3 t2 t1 #2)"
+EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
+INFO: pg_hint_plan: hint syntax error at or near "Rows(*VALUES* t3 t2 #2)Rows(*VALUES* t3 t2 t1 #2)"
DETAIL: Relation name "*VALUES*" is ambiguous.
-INFO: hint syntax error at or near "Rows(*VALUES* t3 t2 t1 #2)"
+INFO: pg_hint_plan: hint syntax error at or near "Rows(*VALUES* t3 t2 t1 #2)"
DETAIL: Relation name "*VALUES*" is ambiguous.
-INFO: hint syntax error at or near "Leading(*VALUES* t3 t2 t1) Rows(t4 t3 #2)Rows(*VALUES* t3 t2 #2)Rows(*VALUES* t3 t2 t1 #2)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(*VALUES* t3 t2 t1) Rows(t4 t3 #2)Rows(*VALUES* t3 t2 #2)Rows(*VALUES* t3 t2 t1 #2)"
DETAIL: Relation name "*VALUES*" is ambiguous.
LOG: pg_hint_plan:
used hint:
Rows(*VALUES* t1 t2 t3 #2)
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-4-2.out.log > results/R_2-4-2.out
-\! diff expected/R_2-4-2.out results/R_2-4-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
+ Join Filter: (t2.c1 = "*VALUES*_1".column1)
+ -> Hash Join (cost=xxx..xxx rows=2 width=xxx)
+ Hash Cond: (t2.c1 = "*VALUES*".column1)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Hash (cost=xxx..xxx rows=2 width=xxx)
+ -> Values Scan on "*VALUES*" (cost=xxx..xxx rows=2 width=xxx)
+ -> Materialize (cost=xxx..xxx rows=2 width=xxx)
+ -> Values Scan on "*VALUES*_1" (cost=xxx..xxx rows=2 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = t2.c1)
+
----
---- No. R-2-5
----
-- No. R-2-5-1
-\o results/R_2-5-1.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(bmt4 bmt3 bmt2 bmt1)
Rows(bmt1 bmt2 bmt3 bmt4 *0.7)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-5-1.out.log > results/R_2-5-1.out
-\! diff expected/R_2-5-1.out results/R_2-5-1.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=70 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt2.c1)
+ -> Hash Join (cost=xxx..xxx rows=1130 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt4.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-2-5-2
-\o results/R_2-5-2.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT bmt1.c1 FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(bmt4 bmt3 bmt2 bmt1)
Rows(bmt4 bmt3 *0.6)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-5-2.out.log > results/R_2-5-2.out
-\! diff expected/R_2-5-2.out results/R_2-5-2.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=60 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=60 width=xxx)
+ Sort Key: bmt2.c1
+ -> Hash Join (cost=xxx..xxx rows=60 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt2.c1)
+ -> Hash Join (cost=xxx..xxx rows=678 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt4.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+
-- No. R-2-5-3
-\o results/R_2-5-3.out.log
+\o results/ut-R.tmpout
EXPLAIN SELECT bmt1.c1 FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Merge Join (cost=xxx..xxx rows=1000 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt3.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx..xxx rows=1 width=xxx)
+ Index Cond: (c1 = bmt1.c1)
+
+\o results/ut-R.tmpout
/*+
Leading(bmt4 bmt3 bmt2 bmt1)
Rows(bmt4 bmt1 *0.5)
error hint:
\o
-\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-5-3.out.log > results/R_2-5-3.out
-\! diff expected/R_2-5-3.out results/R_2-5-3.out
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=50 width=xxx)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: bmt2.c1
+ -> Hash Join (cost=xxx..xxx rows=100 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt2.c1)
+ -> Hash Join (cost=xxx..xxx rows=1130 width=xxx)
+ Hash Cond: (bmt3.c1 = bmt4.c1)
+ -> Seq Scan on t3 bmt3 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=1130 width=xxx)
+ -> Seq Scan on t4 bmt4 (cost=xxx..xxx rows=1130 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 bmt2 (cost=xxx..xxx rows=100 width=xxx)
+
+----
+---- No. R-3-1 abusolute value
+----
+-- No. R-3-1-1
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 #0)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+WARNING: Force estimate to be at least one row, to avoid possible divide-by-zero when interpolating costs : Rows(t1 t2 #0)
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 #0)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+-- No. R-3-1-2
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 #5)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 #5)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=5 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+----
+---- No. R-3-2 increase or decrease value
+----
+-- No. R-3-2-1
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 +1)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 +1)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=101 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+-- No. R-3-2-2
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 -1)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 -1)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=99 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+-- No. R-3-2-3
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 -1000)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+WARNING: Force estimate to be at least one row, to avoid possible divide-by-zero when interpolating costs : Rows(t1 t2 -1000)
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 -1000)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+----
+---- No. R-3-3 multiple
+----
+-- No. R-3-3-1
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 *0)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+WARNING: Force estimate to be at least one row, to avoid possible divide-by-zero when interpolating costs : Rows(t1 t2 *0)
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 *0)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+-- No. R-3-3-2
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 *2)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 *2)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=200 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+-- No. R-3-3-3
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 *0.1)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 *0.1)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=10 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+----
+---- No. R-3-4 join inherit tables
+----
+-- No. R-3-4-1
+\o results/ut-R.tmpout
+EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=301 width=xxx)
+ Hash Cond: (p2.c1 = p1.c1)
+ -> Append (cost=xxx..xxx rows=304 width=xxx)
+ -> Seq Scan on p2 p2_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c1 p2_2 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c2 p2_3 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c3 p2_4 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c1c1 p2_5 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c1c2 p2_6 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c2c1 p2_7 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c2c2 p2_8 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c3c1 p2_9 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c3c2 p2_10 (cost=xxx..xxx rows=50 width=xxx)
+ -> Hash (cost=xxx..xxx rows=301 width=xxx)
+ -> Append (cost=xxx..xxx rows=301 width=xxx)
+ -> Seq Scan on p1 p1_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p1c1 p1_2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c2 p1_3 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c3 p1_4 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
+/*+Rows(p1 p2 #1)*/
+EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
+LOG: pg_hint_plan:
+used hint:
+Rows(p1 p2 #1)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=1 width=xxx)
+ Hash Cond: (p2.c1 = p1.c1)
+ -> Append (cost=xxx..xxx rows=304 width=xxx)
+ -> Seq Scan on p2 p2_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c1 p2_2 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c2 p2_3 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c3 p2_4 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c1c1 p2_5 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c1c2 p2_6 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c2c1 p2_7 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c2c2 p2_8 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c3c1 p2_9 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c3c2 p2_10 (cost=xxx..xxx rows=50 width=xxx)
+ -> Hash (cost=xxx..xxx rows=301 width=xxx)
+ -> Append (cost=xxx..xxx rows=301 width=xxx)
+ -> Seq Scan on p1 p1_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p1c1 p1_2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c2 p1_3 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c3 p1_4 (cost=xxx..xxx rows=100 width=xxx)
+
+-- No. R-3-4-2
+\o results/ut-R.tmpout
+EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=301 width=xxx)
+ Hash Cond: (p2.c1 = p1.c1)
+ -> Append (cost=xxx..xxx rows=304 width=xxx)
+ -> Seq Scan on p2 p2_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c1 p2_2 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c2 p2_3 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c3 p2_4 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c1c1 p2_5 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c1c2 p2_6 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c2c1 p2_7 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c2c2 p2_8 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c3c1 p2_9 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c3c2 p2_10 (cost=xxx..xxx rows=50 width=xxx)
+ -> Hash (cost=xxx..xxx rows=301 width=xxx)
+ -> Append (cost=xxx..xxx rows=301 width=xxx)
+ -> Seq Scan on p1 p1_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p1c1 p1_2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c2 p1_3 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c3 p1_4 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
+/*+Rows(p1c1 p2c1 #1)*/
+EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+Rows(p1c1 p2c1 #1)
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Hash Join (cost=xxx..xxx rows=301 width=xxx)
+ Hash Cond: (p2.c1 = p1.c1)
+ -> Append (cost=xxx..xxx rows=304 width=xxx)
+ -> Seq Scan on p2 p2_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c1 p2_2 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c2 p2_3 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c3 p2_4 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p2c1c1 p2_5 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c1c2 p2_6 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c2c1 p2_7 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c2c2 p2_8 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c3c1 p2_9 (cost=xxx..xxx rows=50 width=xxx)
+ -> Seq Scan on p2c3c2 p2_10 (cost=xxx..xxx rows=50 width=xxx)
+ -> Hash (cost=xxx..xxx rows=301 width=xxx)
+ -> Append (cost=xxx..xxx rows=301 width=xxx)
+ -> Seq Scan on p1 p1_1 (cost=xxx..xxx rows=1 width=xxx)
+ -> Seq Scan on p1c1 p1_2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c2 p1_3 (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on p1c3 p1_4 (cost=xxx..xxx rows=100 width=xxx)
+
+----
+---- No. R-3-5 conflict join method hint
+----
+-- No. R-3-5-1
+\o results/ut-R.tmpout
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 #1)Rows(t1 t2 #1)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t2 #1)Rows(t1 t2 #1)"
+DETAIL: Conflict rows hint.
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 #1)
+not used hint:
+duplication hint:
+Rows(t1 t2 #1)
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+-- No. R-3-5-2
+\o results/ut-R.tmpout
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 #1)Rows(t1 t2 #1)Rows(t1 t2 #1)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t2 #1)Rows(t1 t2 #1)Rows(t1 t2 #1)"
+DETAIL: Conflict rows hint.
+INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t2 #1)Rows(t1 t2 #1)"
+DETAIL: Conflict rows hint.
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 #1)
+not used hint:
+duplication hint:
+Rows(t1 t2 #1)
+Rows(t1 t2 #1)
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+-- No. R-3-5-3
+\o results/ut-R.tmpout
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 #1)Rows(t2 t1 #1)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t2 #1)Rows(t2 t1 #1)"
+DETAIL: Conflict rows hint.
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 #1)
+not used hint:
+duplication hint:
+Rows(t1 t2 #1)
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+-- No. R-3-5-4
+\o results/ut-R.tmpout
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
+/*+Rows(t2 t1 #1)Rows(t1 t2 #1)Rows(t2 t1 #1)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+INFO: pg_hint_plan: hint syntax error at or near "Rows(t2 t1 #1)Rows(t1 t2 #1)Rows(t2 t1 #1)"
+DETAIL: Conflict rows hint.
+INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t2 #1)Rows(t2 t1 #1)"
+DETAIL: Conflict rows hint.
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 #1)
+not used hint:
+duplication hint:
+Rows(t1 t2 #1)
+Rows(t1 t2 #1)
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=1 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+----
+---- No. R-3-6 hint state output
+----
+-- No. R-3-6-1
+SET client_min_messages TO DEBUG1;
+\o results/ut-R.tmpout
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=100 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\o results/ut-R.tmpout
+/*+Rows(t1 t2 +1)*/
+EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+DEBUG: adjusted rows 100 to 101
+LOG: pg_hint_plan:
+used hint:
+Rows(t1 t2 +1)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-R.tmpout
+ QUERY PLAN
+----------------
+ Merge Join (cost=xxx..xxx rows=101 width=xxx)
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Sort (cost=xxx..xxx rows=100 width=xxx)
+ Sort Key: t2.c1
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\! rm results/ut-R.tmpout
OSDN Git Service
