--- /dev/null
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Nested Loop (cost=xxx rows=1 width=4)
+ CTE c1
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = 1)
+ InitPlan 2 (returns $1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = 1)
+ InitPlan 3 (returns $2)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = 1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = 1)
+ Filter: (c1 <> $2)
+ -> CTE Scan on c1 (cost=xxx rows=1 width=4)
+ Filter: (c1 = 1)
+(15 rows)
+
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Nested Loop (cost=xxx rows=1 width=4)
+ CTE c1
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = 1)
+ InitPlan 2 (returns $1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = 1)
+ InitPlan 3 (returns $2)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = 1)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = 1)
+ Filter: (c1 <> $2)
+ -> CTE Scan on c1 (cost=xxx rows=1 width=4)
+ Filter: (c1 = 1)
+(15 rows)
+
--- /dev/null
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=10 width=4)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ CTE c1
+ -> Merge Join (cost=xxx rows=100 width=4)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=4)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=4)
+ InitPlan 2 (returns $1)
+ -> Merge Join (cost=xxx rows=100 width=4)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=4)
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2 (cost=xxx rows=100 width=4)
+ InitPlan 3 (returns $2)
+ -> Merge Join (cost=xxx rows=100 width=4)
+ Merge Cond: (b3t1.c1 = b3t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=4)
+ Sort Key: b3t2.c1
+ -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=4)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=999 width=4)
+ Filter: (c1 <> $2)
+ -> Sort (cost=xxx rows=100 width=8)
+ Sort Key: bmt2.c1
+ -> Hash Join (cost=xxx rows=100 width=8)
+ Hash Cond: (bmt2.c1 = c1.c1)
+ -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> CTE Scan on c1 (cost=xxx rows=100 width=4)
+(32 rows)
+
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
+ Nested Loop (cost=xxx rows=1 width=4)
+ CTE c1
+ -> Merge Join (cost=xxx rows=1 width=4)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=4)
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=4)
+ InitPlan 2 (returns $1)
+ -> Merge Join (cost=xxx rows=1 width=4)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=4)
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2 (cost=xxx rows=100 width=4)
+ InitPlan 3 (returns $2)
+ -> Merge Join (cost=xxx rows=1 width=4)
+ Merge Cond: (b3t1.c1 = b3t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=4)
+ Sort Key: b3t2.c1
+ -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=4)
+ -> Hash Join (cost=xxx rows=1 width=8)
+ Hash Cond: (bmt2.c1 = c1.c1)
+ -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> CTE Scan on c1 (cost=xxx rows=100 width=4)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = bmt2.c1)
+ Filter: (c1 <> $2)
+(30 rows)
+
--- /dev/null
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=10 width=4)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ CTE c1
+ -> Merge Join (cost=xxx rows=100 width=4)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=12)
+ Sort Key: b1t2.c1
+ -> Hash Join (cost=xxx rows=100 width=12)
+ Hash Cond: (b1t3.c1 = b1t2.c1)
+ -> Hash Join (cost=xxx rows=1100 width=8)
+ Hash Cond: (b1t3.c1 = b1t4.c1)
+ -> Seq Scan on t3 b1t3 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=1100 width=4)
+ -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=4)
+ InitPlan 2 (returns $1)
+ -> Merge Join (cost=xxx rows=100 width=4)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=12)
+ Sort Key: b2t2.c1
+ -> Hash Join (cost=xxx rows=100 width=12)
+ Hash Cond: (b2t3.c1 = b2t2.c1)
+ -> Hash Join (cost=xxx rows=1100 width=8)
+ Hash Cond: (b2t3.c1 = b2t4.c1)
+ -> Seq Scan on t3 b2t3 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=1100 width=4)
+ -> Seq Scan on t4 b2t4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 b2t2 (cost=xxx rows=100 width=4)
+ InitPlan 3 (returns $2)
+ -> Merge Join (cost=xxx rows=100 width=4)
+ Merge Cond: (b3t1.c1 = b3t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=12)
+ Sort Key: b3t2.c1
+ -> Hash Join (cost=xxx rows=100 width=12)
+ Hash Cond: (b3t3.c1 = b3t2.c1)
+ -> Hash Join (cost=xxx rows=1100 width=8)
+ Hash Cond: (b3t3.c1 = b3t4.c1)
+ -> Seq Scan on t3 b3t3 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=1100 width=4)
+ -> Seq Scan on t4 b3t4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=4)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=999 width=4)
+ Filter: (c1 <> $2)
+ -> Sort (cost=xxx rows=100 width=16)
+ Sort Key: bmt2.c1
+ -> Hash Join (cost=xxx rows=100 width=16)
+ Hash Cond: (bmt3.c1 = bmt2.c1)
+ -> Nested Loop (cost=xxx rows=100 width=12)
+ -> Hash Join (cost=xxx rows=100 width=8)
+ Hash Cond: (bmt4.c1 = c1.c1)
+ -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> CTE Scan on c1 (cost=xxx rows=100 width=4)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = bmt4.c1)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=4)
+(63 rows)
+
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Nested Loop (cost=xxx rows=1 width=4)
+ Join Filter: (bmt2.c1 = bmt1.c1)
+ CTE c1
+ -> Merge Join (cost=xxx rows=1 width=4)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=12)
+ Sort Key: b1t2.c1
+ -> Hash Join (cost=xxx rows=1 width=12)
+ Hash Cond: (b1t3.c1 = b1t2.c1)
+ -> Hash Join (cost=xxx rows=1 width=8)
+ Hash Cond: (b1t3.c1 = b1t4.c1)
+ -> Seq Scan on t3 b1t3 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=1100 width=4)
+ -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=4)
+ InitPlan 2 (returns $1)
+ -> Merge Join (cost=xxx rows=1 width=4)
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=12)
+ Sort Key: b2t2.c1
+ -> Hash Join (cost=xxx rows=1 width=12)
+ Hash Cond: (b2t3.c1 = b2t2.c1)
+ -> Hash Join (cost=xxx rows=1 width=8)
+ Hash Cond: (b2t3.c1 = b2t4.c1)
+ -> Seq Scan on t3 b2t3 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=1100 width=4)
+ -> Seq Scan on t4 b2t4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 b2t2 (cost=xxx rows=100 width=4)
+ InitPlan 3 (returns $2)
+ -> Merge Join (cost=xxx rows=1 width=4)
+ Merge Cond: (b3t1.c1 = b3t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=12)
+ Sort Key: b3t2.c1
+ -> Hash Join (cost=xxx rows=1 width=12)
+ Hash Cond: (b3t3.c1 = b3t2.c1)
+ -> Hash Join (cost=xxx rows=1 width=8)
+ Hash Cond: (b3t3.c1 = b3t4.c1)
+ -> Seq Scan on t3 b3t3 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=1100 width=4)
+ -> Seq Scan on t4 b3t4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=4)
+ -> Hash Join (cost=xxx rows=1 width=16)
+ Hash Cond: (bmt3.c1 = bmt2.c1)
+ -> Nested Loop (cost=xxx rows=1 width=12)
+ -> Hash Join (cost=xxx rows=1 width=8)
+ Hash Cond: (bmt4.c1 = c1.c1)
+ -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> CTE Scan on c1 (cost=xxx rows=100 width=4)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = bmt4.c1)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=4)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = bmt3.c1)
+ Filter: (c1 <> $2)
+(62 rows)
+
--- /dev/null
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
+ Merge Join (cost=xxx rows=10 width=4)
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ CTE c1
+ -> Merge Join (cost=xxx rows=100 width=4)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=12)
+ Sort Key: b1t2.c1
+ -> Hash Join (cost=xxx rows=100 width=12)
+ Hash Cond: (b1t3.c1 = b1t2.c1)
+ -> Hash Join (cost=xxx rows=1100 width=8)
+ Hash Cond: (b1t3.c1 = b1t4.c1)
+ -> Seq Scan on t3 b1t3 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=1100 width=4)
+ -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=4)
+ InitPlan 2 (returns $1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = 1)
+ InitPlan 3 (returns $2)
+ -> Seq Scan on t1 b3t1 (cost=xxx rows=1000 width=4)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=999 width=4)
+ Filter: (c1 <> $2)
+ -> Sort (cost=xxx rows=100 width=16)
+ Sort Key: bmt2.c1
+ -> Hash Join (cost=xxx rows=100 width=16)
+ Hash Cond: (bmt3.c1 = bmt2.c1)
+ -> Nested Loop (cost=xxx rows=100 width=12)
+ -> Hash Join (cost=xxx rows=100 width=8)
+ Hash Cond: (bmt4.c1 = c1.c1)
+ -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> CTE Scan on c1 (cost=xxx rows=100 width=4)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = bmt4.c1)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=4)
+(38 rows)
+
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Nested Loop (cost=xxx rows=1 width=4)
+ Join Filter: (bmt2.c1 = bmt1.c1)
+ CTE c1
+ -> Merge Join (cost=xxx rows=1 width=4)
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1000 width=4)
+ -> Sort (cost=xxx rows=100 width=12)
+ Sort Key: b1t2.c1
+ -> Hash Join (cost=xxx rows=1 width=12)
+ Hash Cond: (b1t3.c1 = b1t2.c1)
+ -> Hash Join (cost=xxx rows=1 width=8)
+ Hash Cond: (b1t3.c1 = b1t4.c1)
+ -> Seq Scan on t3 b1t3 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=1100 width=4)
+ -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=4)
+ InitPlan 2 (returns $1)
+ -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = 1)
+ InitPlan 3 (returns $2)
+ -> Seq Scan on t1 b3t1 (cost=xxx rows=1000 width=4)
+ -> Hash Join (cost=xxx rows=1 width=16)
+ Hash Cond: (bmt3.c1 = bmt2.c1)
+ -> Nested Loop (cost=xxx rows=1 width=12)
+ -> Hash Join (cost=xxx rows=1 width=8)
+ Hash Cond: (bmt4.c1 = c1.c1)
+ -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=4)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> CTE Scan on c1 (cost=xxx rows=100 width=4)
+ -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = bmt4.c1)
+ -> Hash (cost=xxx rows=100 width=4)
+ -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=4)
+ -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=4)
+ Index Cond: (c1 = bmt3.c1)
+ Filter: (c1 <> $2)
+(37 rows)
+
---- 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
+/*+
+Leading(c1 bmt1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT b1t1.c1 FROM s1.t1 b1t1 WHERE b1t1.c1 = 1
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, c1 WHERE 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)
+not used hint:
+duplication hint:
+error hint:
+
+/*+
+Leading(c1 bmt1)
+Rows(bmt1 c1 #1)
+Rows(b1t1 c1 #1)
+Rows(b2t1 c1 #1)
+Rows(b3t1 c1 #1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT b1t1.c1 FROM s1.t1 b1t1 WHERE b1t1.c1 = 1
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, c1 WHERE 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:
+Rows(b1t1 c1 #1)
+Rows(b2t1 c1 #1)
+Rows(b3t1 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
+-- No. R-2-2-2
+\o results/R_2-2-2.out.log
+/*+
+Leading(c1 bmt2 bmt1)
+Leading(b1t2 b1t1)
+Leading(b2t2 b2t1)
+Leading(b3t2 b3t1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.c1 = b1t2.c1
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.c1 = b2t2.c1
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.c1 = bmt2.c1
+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:
+Leading(c1 bmt2 bmt1)
+Leading(b1t2 b1t1)
+Leading(b2t2 b2t1)
+Leading(b3t2 b3t1)
+not used hint:
+duplication hint:
+error hint:
+
+/*+
+Leading(c1 bmt2 bmt1)
+Leading(b1t2 b1t1)
+Leading(b2t2 b2t1)
+Leading(b3t2 b3t1)
+Rows(c1 bmt2 #1)
+Rows(c1 bmt1 bmt2 #1)
+Rows(b1t1 b1t2 #1)
+Rows(b2t1 b2t2 #1)
+Rows(b3t1 b3t2 #1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.c1 = b1t2.c1
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.c1 = b2t2.c1
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.c1 = bmt2.c1
+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:
+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)
+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
+-- No. R-2-2-3
+\o results/R_2-2-3.out.log
+/*+
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+Leading(b2t4 b2t3 b2t2 b2t1)
+Leading(b3t4 b3t3 b3t2 b3t1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT 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
+)
+SELECT bmt1.c1, (
+SELECT 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
+)
+ 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:
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+Leading(b2t4 b2t3 b2t2 b2t1)
+Leading(b3t4 b3t3 b3t2 b3t1)
+not used hint:
+duplication hint:
+error hint:
+
+/*+
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+Leading(b2t4 b2t3 b2t2 b2t1)
+Leading(b3t4 b3t3 b3t2 b3t1)
+Rows(c1 bmt4 #1)
+Rows(c1 bmt4 bmt3 #1)
+Rows(c1 bmt4 bmt3 bmt2 #1)
+Rows(c1 bmt4 bmt3 bmt2 bmt1 #1)
+Rows(b1t4 b1t3 #1)
+Rows(b1t4 b1t3 b1t2 #1)
+Rows(b1t4 b1t3 b1t2 b1t1 #1)
+Rows(b2t4 b2t3 #1)
+Rows(b2t4 b2t3 b2t2 #1)
+Rows(b2t4 b2t3 b2t2 b2t1 #1)
+Rows(b3t4 b3t3 #1)
+Rows(b3t4 b3t3 b3t2 #1)
+Rows(b3t4 b3t3 b3t2 b3t1 #1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT 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
+)
+SELECT bmt1.c1, (
+SELECT 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
+)
+ 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:
+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)
+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
+-- No. R-2-2-4
+\o results/R_2-2-4.out.log
+/*+
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT 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
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+)
+ 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:
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+not used hint:
+duplication hint:
+error hint:
+
+/*+
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+Rows(c1 bmt4 #1)
+Rows(c1 bmt4 bmt3 #1)
+Rows(c1 bmt4 bmt3 bmt2 #1)
+Rows(c1 bmt4 bmt3 bmt2 bmt1 #1)
+Rows(b1t4 b1t3 #1)
+Rows(b1t4 b1t3 b1t2 #1)
+Rows(b1t4 b1t3 b1t2 b1t1 #1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT 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
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+)
+ 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:
+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
----
-- No. R-2-2-1
+\o results/R_2-2-1.out.log
+/*+
+Leading(c1 bmt1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT b1t1.c1 FROM s1.t1 b1t1 WHERE b1t1.c1 = 1
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, c1 WHERE bmt1.c1 = 1
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1
+)
+;
+/*+
+Leading(c1 bmt1)
+Rows(bmt1 c1 #1)
+Rows(b1t1 c1 #1)
+Rows(b2t1 c1 #1)
+Rows(b3t1 c1 #1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT b1t1.c1 FROM s1.t1 b1t1 WHERE b1t1.c1 = 1
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, c1 WHERE bmt1.c1 = 1
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1
+)
+;
+\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
+
+-- No. R-2-2-2
+\o results/R_2-2-2.out.log
+/*+
+Leading(c1 bmt2 bmt1)
+Leading(b1t2 b1t1)
+Leading(b2t2 b2t1)
+Leading(b3t2 b3t1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.c1 = b1t2.c1
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.c1 = b2t2.c1
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.c1 = bmt2.c1
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.c1 = b3t2.c1
+)
+;
+/*+
+Leading(c1 bmt2 bmt1)
+Leading(b1t2 b1t1)
+Leading(b2t2 b2t1)
+Leading(b3t2 b3t1)
+Rows(c1 bmt2 #1)
+Rows(c1 bmt1 bmt2 #1)
+Rows(b1t1 b1t2 #1)
+Rows(b2t1 b2t2 #1)
+Rows(b3t1 b3t2 #1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.c1 = b1t2.c1
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.c1 = b2t2.c1
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.c1 = bmt2.c1
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.c1 = b3t2.c1
+)
+;
+\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
+
+-- No. R-2-2-3
+\o results/R_2-2-3.out.log
+/*+
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+Leading(b2t4 b2t3 b2t2 b2t1)
+Leading(b3t4 b3t3 b3t2 b3t1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT 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
+)
+SELECT bmt1.c1, (
+SELECT 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
+)
+ 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
+)
+;
+/*+
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+Leading(b2t4 b2t3 b2t2 b2t1)
+Leading(b3t4 b3t3 b3t2 b3t1)
+Rows(c1 bmt4 #1)
+Rows(c1 bmt4 bmt3 #1)
+Rows(c1 bmt4 bmt3 bmt2 #1)
+Rows(c1 bmt4 bmt3 bmt2 bmt1 #1)
+Rows(b1t4 b1t3 #1)
+Rows(b1t4 b1t3 b1t2 #1)
+Rows(b1t4 b1t3 b1t2 b1t1 #1)
+Rows(b2t4 b2t3 #1)
+Rows(b2t4 b2t3 b2t2 #1)
+Rows(b2t4 b2t3 b2t2 b2t1 #1)
+Rows(b3t4 b3t3 #1)
+Rows(b3t4 b3t3 b3t2 #1)
+Rows(b3t4 b3t3 b3t2 b3t1 #1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT 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
+)
+SELECT bmt1.c1, (
+SELECT 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
+)
+ 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
+)
+;
+\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
+
+-- No. R-2-2-4
+\o results/R_2-2-4.out.log
+/*+
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT 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
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+)
+ 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
+)
+;
+/*+
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+Rows(c1 bmt4 #1)
+Rows(c1 bmt4 bmt3 #1)
+Rows(c1 bmt4 bmt3 bmt2 #1)
+Rows(c1 bmt4 bmt3 bmt2 bmt1 #1)
+Rows(b1t4 b1t3 #1)
+Rows(b1t4 b1t3 b1t2 #1)
+Rows(b1t4 b1t3 b1t2 b1t1 #1)
+*/
+EXPLAIN
+WITH c1 (c1) AS (
+SELECT 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
+)
+SELECT bmt1.c1, (
+SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
+)
+ 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
+)
+;
+\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