From ccb6be894242af58db39c5a196c9793fdbc803b5 Mon Sep 17 00:00:00 2001 From: Takashi Suzuki Date: Wed, 15 Jan 2014 17:42:11 +0900 Subject: [PATCH] =?utf8?q?1=E3=83=96=E3=83=AD=E3=83=83=E3=82=AF=E3=81=82?= =?utf8?q?=E3=81=9F=E3=82=8A=E3=81=AE=E3=83=86=E3=83=BC=E3=83=96=E3=83=AB?= =?utf8?q?=E6=95=B0(R-2-2-*)=E3=81=AE=E8=A9=A6=E9=A8=93=E3=82=92=E8=BF=BD?= =?utf8?q?=E5=8A=A0=E3=81=97=E3=81=9F=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit ただし、不合格 --- expected/R_2-2-1.out | 38 +++++++ expected/R_2-2-2.out | 70 +++++++++++++ expected/R_2-2-3.out | 133 ++++++++++++++++++++++++ expected/R_2-2-4.out | 83 +++++++++++++++ expected/ut-R.out | 285 +++++++++++++++++++++++++++++++++++++++++++++++++++ sql/ut-R.sql | 189 ++++++++++++++++++++++++++++++++++ 6 files changed, 798 insertions(+) create mode 100644 expected/R_2-2-1.out create mode 100644 expected/R_2-2-2.out create mode 100644 expected/R_2-2-3.out create mode 100644 expected/R_2-2-4.out diff --git a/expected/R_2-2-1.out b/expected/R_2-2-1.out new file mode 100644 index 0000000..b3b4374 --- /dev/null +++ b/expected/R_2-2-1.out @@ -0,0 +1,38 @@ + 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) + diff --git a/expected/R_2-2-2.out b/expected/R_2-2-2.out new file mode 100644 index 0000000..a302c70 --- /dev/null +++ b/expected/R_2-2-2.out @@ -0,0 +1,70 @@ + 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) + diff --git a/expected/R_2-2-3.out b/expected/R_2-2-3.out new file mode 100644 index 0000000..3a4d4d8 --- /dev/null +++ b/expected/R_2-2-3.out @@ -0,0 +1,133 @@ + 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) + diff --git a/expected/R_2-2-4.out b/expected/R_2-2-4.out new file mode 100644 index 0000000..d4a97d1 --- /dev/null +++ b/expected/R_2-2-4.out @@ -0,0 +1,83 @@ + 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) + diff --git a/expected/ut-R.out b/expected/ut-R.out index 4440157..60457ef 100644 --- a/expected/ut-R.out +++ b/expected/ut-R.out @@ -1200,3 +1200,288 @@ error hint: ---- 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 diff --git a/sql/ut-R.sql b/sql/ut-R.sql index 2647252..66a45c3 100644 --- a/sql/ut-R.sql +++ b/sql/ut-R.sql @@ -622,3 +622,192 @@ AND bmt1.c1 = c3.c1 ---- -- 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 -- 2.11.0