From 958c60d6b6abcac2387971c3462d663ced8ce783 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Mon, 17 Aug 2015 18:33:38 +0900 Subject: [PATCH] Made compatible with 9.5a1 Made make_join_rel.c to catch up REL9_5_ALPHA2. Changed regression tests following some differences of PostgreSQL's behavior in such like collapsing of simple VALUES clause, rows estimation and explain representation of join filter. --- expected/ut-A.out | 7 +- expected/ut-J.out | 71 +++++----- expected/ut-L.out | 49 +++---- expected/ut-R.out | 383 +++++++++++++++++++++++++++--------------------------- expected/ut-S.out | 138 ++++++++++---------- make_join_rel.c | 5 +- sql/ut-J.sql | 8 +- sql/ut-L.sql | 16 +-- sql/ut-R.sql | 12 +- sql/ut-S.sql | 6 +- 10 files changed, 356 insertions(+), 339 deletions(-) diff --git a/expected/ut-A.out b/expected/ut-A.out index 7ba9017..00432ac 100644 --- a/expected/ut-A.out +++ b/expected/ut-A.out @@ -4276,7 +4276,12 @@ SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) ORDER BY t_1.c1 LIMIT 1" PL/pgSQL function nested_planner(integer) line 12 at SQL statement LOG: pg_hint_plan: -no hint +used hint: +IndexScan(t_1) +not used hint: +duplication hint: +error hint: + CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) ORDER BY t_1.c1 LIMIT 1" diff --git a/expected/ut-J.out b/expected/ut-J.out index c7c48a1..f728011 100644 --- a/expected/ut-J.out +++ b/expected/ut-J.out @@ -3763,17 +3763,14 @@ error hint: ---- -- No. J-2-4-1 EXPLAIN (COSTS false) 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; - QUERY PLAN -------------------------------------------------- + QUERY PLAN +------------------------------------ Nested Loop - -> Hash Join - Hash Cond: (t2.c1 = "*VALUES*".column1) - -> Seq Scan on t2 - -> Hash - -> Values Scan on "*VALUES*" -> Index Scan using t1_i1 on t1 - Index Cond: (c1 = t2.c1) -(8 rows) + Index Cond: (c1 = 1) + -> Seq Scan on t2 + Filter: (c1 = 1) +(5 rows) /*+ Leading(t3 t1 t2) HashJoin(t3 t1)NestLoop(t3 t1 t2)*/ EXPLAIN (COSTS false) 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; @@ -3786,20 +3783,17 @@ Leading(t3 t1 t2) duplication hint: error hint: - QUERY PLAN -------------------------------------------------- + QUERY PLAN +------------------------------------ Nested Loop - -> Hash Join - Hash Cond: (t2.c1 = "*VALUES*".column1) - -> Seq Scan on t2 - -> Hash - -> Values Scan on "*VALUES*" -> Index Scan using t1_i1 on t1 - Index Cond: (c1 = t2.c1) -(8 rows) + Index Cond: (c1 = 1) + -> Seq Scan on t2 + Filter: (c1 = 1) +(5 rows) /*+ Leading(*VALUES* t1 t2) HashJoin(*VALUES* t1)NestLoop(*VALUES* t1 t2)*/ -EXPLAIN (COSTS false) 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 (COSTS false) 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: HashJoin(*VALUES* t1) @@ -3822,24 +3816,25 @@ error hint: (8 rows) -- No. J-2-4-2 -EXPLAIN (COSTS false) 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 (COSTS false) 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; QUERY PLAN ------------------------------------------------------- Nested Loop - Join Filter: (t1.c1 = "*VALUES*_1".column1) -> Nested Loop + Join Filter: (t2.c1 = "*VALUES*_1".column1) -> Hash Join Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 -> Hash -> Values Scan on "*VALUES*" - -> Index Scan using t1_i1 on t1 - Index Cond: (c1 = t2.c1) - -> Values Scan on "*VALUES*_1" -(11 rows) + -> Materialize + -> Values Scan on "*VALUES*_1" + -> Index Scan using t1_i1 on t1 + Index Cond: (c1 = t2.c1) +(12 rows) /*+ Leading(t4 t3 t2 t1) NestLoop(t4 t3)HashJoin(t4 t3 t2)MergeJoin(t4 t3 t2 t1)*/ -EXPLAIN (COSTS false) 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 (COSTS false) 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: @@ -3853,20 +3848,21 @@ error hint: QUERY PLAN ------------------------------------------------------- Nested Loop - Join Filter: (t1.c1 = "*VALUES*_1".column1) -> Nested Loop + Join Filter: (t2.c1 = "*VALUES*_1".column1) -> Hash Join Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 -> Hash -> Values Scan on "*VALUES*" - -> Index Scan using t1_i1 on t1 - Index Cond: (c1 = t2.c1) - -> Values Scan on "*VALUES*_1" -(11 rows) + -> Materialize + -> Values Scan on "*VALUES*_1" + -> Index Scan using t1_i1 on t1 + Index Cond: (c1 = t2.c1) +(12 rows) /*+ Leading(*VALUES* t3 t2 t1) NestLoop(t4 t3)HashJoin(*VALUES* t3 t2)MergeJoin(*VALUES* t3 t2 t1)*/ -EXPLAIN (COSTS false) 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 (COSTS false) 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 "HashJoin(*VALUES* t3 t2)MergeJoin(*VALUES* t3 t2 t1)" DETAIL: Relation name "*VALUES*" is ambiguous. INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(*VALUES* t3 t2 t1)" @@ -3886,17 +3882,18 @@ Leading(*VALUES* t3 t2 t1) QUERY PLAN ------------------------------------------------------- Nested Loop - Join Filter: (t1.c1 = "*VALUES*_1".column1) -> Nested Loop + Join Filter: (t2.c1 = "*VALUES*_1".column1) -> Hash Join Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 -> Hash -> Values Scan on "*VALUES*" - -> Index Scan using t1_i1 on t1 - Index Cond: (c1 = t2.c1) - -> Values Scan on "*VALUES*_1" -(11 rows) + -> Materialize + -> Values Scan on "*VALUES*_1" + -> Index Scan using t1_i1 on t1 + Index Cond: (c1 = t2.c1) +(12 rows) ---- ---- No. J-3-1 join method hint diff --git a/expected/ut-L.out b/expected/ut-L.out index 1d0fed2..fdc43f1 100644 --- a/expected/ut-L.out +++ b/expected/ut-L.out @@ -926,7 +926,7 @@ error hint: (10 rows) -- No. L-1-6-8 -EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1; +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1; QUERY PLAN ------------------------------------------------------- Nested Loop @@ -945,7 +945,7 @@ EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (13 rows) /*+Leading(t4 t3 t2 t1)*/ -EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1; +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1; LOG: pg_hint_plan: used hint: not used hint: @@ -3597,7 +3597,7 @@ error hint: ---- No. L-2-4 VALUES clause ---- -- No. L-2-4-1 -EXPLAIN (COSTS false) 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 (COSTS false) 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; QUERY PLAN ------------------------------------------------- Nested Loop @@ -3611,7 +3611,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, (8 rows) /*+ Leading(t3 t1 t2) */ -EXPLAIN (COSTS false) 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 (COSTS false) 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: @@ -3632,7 +3632,7 @@ error hint: (8 rows) /*+ Leading(*VALUES* t1 t2) */ -EXPLAIN (COSTS false) 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 (COSTS false) 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) @@ -3652,24 +3652,25 @@ error hint: (7 rows) -- No. L-2-4-2 -EXPLAIN (COSTS false) 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 (COSTS false) 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; QUERY PLAN ------------------------------------------------------- Nested Loop - Join Filter: (t1.c1 = "*VALUES*_1".column1) -> Nested Loop + Join Filter: (t2.c1 = "*VALUES*_1".column1) -> Hash Join Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 -> Hash -> Values Scan on "*VALUES*" - -> Index Scan using t1_i1 on t1 - Index Cond: (c1 = t2.c1) - -> Values Scan on "*VALUES*_1" -(11 rows) + -> Materialize + -> Values Scan on "*VALUES*_1" + -> Index Scan using t1_i1 on t1 + Index Cond: (c1 = t2.c1) +(12 rows) /*+ Leading(t4 t3 t2 t1) */ -EXPLAIN (COSTS false) 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 (COSTS false) 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: @@ -3680,20 +3681,21 @@ error hint: QUERY PLAN ------------------------------------------------------- Nested Loop - Join Filter: (t1.c1 = "*VALUES*_1".column1) -> Nested Loop + Join Filter: (t2.c1 = "*VALUES*_1".column1) -> Hash Join Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 -> Hash -> Values Scan on "*VALUES*" - -> Index Scan using t1_i1 on t1 - Index Cond: (c1 = t2.c1) - -> Values Scan on "*VALUES*_1" -(11 rows) + -> Materialize + -> Values Scan on "*VALUES*_1" + -> Index Scan using t1_i1 on t1 + Index Cond: (c1 = t2.c1) +(12 rows) /*+ Leading(*VALUES* t3 t2 t1) */ -EXPLAIN (COSTS false) 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 (COSTS false) 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 "Leading(*VALUES* t3 t2 t1) " DETAIL: Relation name "*VALUES*" is ambiguous. LOG: pg_hint_plan: @@ -3706,17 +3708,18 @@ Leading(*VALUES* t3 t2 t1) QUERY PLAN ------------------------------------------------------- Nested Loop - Join Filter: (t1.c1 = "*VALUES*_1".column1) -> Nested Loop + Join Filter: (t2.c1 = "*VALUES*_1".column1) -> Hash Join Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 -> Hash -> Values Scan on "*VALUES*" - -> Index Scan using t1_i1 on t1 - Index Cond: (c1 = t2.c1) - -> Values Scan on "*VALUES*_1" -(11 rows) + -> Materialize + -> Values Scan on "*VALUES*_1" + -> Index Scan using t1_i1 on t1 + Index Cond: (c1 = t2.c1) +(12 rows) ---- ---- No. L-3-1 leading the order of table joins diff --git a/expected/ut-R.out b/expected/ut-R.out index fb827df..804f298 100644 --- a/expected/ut-R.out +++ b/expected/ut-R.out @@ -474,7 +474,7 @@ EXPLAIN SELECT * FROM s1.t1, s1.t2, s1.t3 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1; -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (t1.c1 = t3.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) - -> Index Scan using t3_i1 on t3 (cost=xxx rows=1100 width=xxx) + -> Index Scan using t3_i1 on t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) @@ -496,7 +496,7 @@ DETAIL: Unrecognized hint keyword "". -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (t1.c1 = t3.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) - -> Index Scan using t3_i1 on t3 (cost=xxx rows=1100 width=xxx) + -> Index Scan using t3_i1 on t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) @@ -588,11 +588,11 @@ EXPLAIN SELECT * FROM s1.ul1 t1, s1.ul1 t2 WHERE t1.c1 = t2.c1; \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------- - Hash Join (cost=xxx rows=1100 width=xxx) + Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (t1.c1 = t2.c1) - -> Seq Scan on ul1 t1 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on ul1 t2 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on ul1 t1 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on ul1 t2 (cost=xxx rows=1130 width=xxx) (5 rows) \o results/ut-R.tmpout @@ -611,9 +611,9 @@ error hint: ----------------------------------------------------------------------- Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (t1.c1 = t2.c1) - -> Seq Scan on ul1 t1 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on ul1 t2 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on ul1 t1 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on ul1 t2 (cost=xxx rows=1130 width=xxx) (5 rows) -- No. R-1-6-4 @@ -624,11 +624,11 @@ EXPLAIN SELECT * FROM tm1 t1, tm1 t2 WHERE t1.c1 = t2.c1; \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------- - Hash Join (cost=xxx rows=1100 width=xxx) + Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (t1.c1 = t2.c1) - -> Seq Scan on tm1 t1 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on tm1 t2 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on tm1 t1 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on tm1 t2 (cost=xxx rows=1130 width=xxx) (5 rows) \o results/ut-R.tmpout @@ -647,9 +647,9 @@ error hint: ----------------------------------------------------------------------- Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (t1.c1 = t2.c1) - -> Seq Scan on tm1 t1 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on tm1 t2 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on tm1 t1 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on tm1 t2 (cost=xxx rows=1130 width=xxx) (5 rows) -- No. R-1-6-5 @@ -1127,11 +1127,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -1142,10 +1142,10 @@ error hint: -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) - -> Merge Join (cost=xxx rows=1100 width=xxx) + -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) @@ -1154,7 +1154,7 @@ error hint: Join Filter: (bmt1.c1 = bmt4.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) @@ -1223,11 +1223,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -1242,15 +1242,15 @@ error hint: -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (bmt1.c1 = bmt4.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) @@ -1317,11 +1317,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -1332,10 +1332,10 @@ error hint: -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) - -> Merge Join (cost=xxx rows=1100 width=xxx) + -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) @@ -1349,7 +1349,7 @@ error hint: -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) - -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) @@ -1358,7 +1358,7 @@ error hint: Join Filter: (bmt1.c1 = bmt4.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) @@ -1440,11 +1440,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -1459,8 +1459,8 @@ error hint: -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) InitPlan 3 (returns $5) @@ -1472,7 +1472,7 @@ error hint: -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) - -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) @@ -1481,7 +1481,7 @@ error hint: Join Filter: (bmt1.c1 = bmt4.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) @@ -1518,11 +1518,11 @@ error hint: Sort Key: bmt2.c1 -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt2.c1) - -> Hash Join (cost=xxx rows=1100 width=xxx) + -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) (15 rows) @@ -1553,9 +1553,9 @@ error hint: -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=xxx) @@ -1587,11 +1587,11 @@ error hint: Sort Key: bmt2.c1 -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt2.c1) - -> Hash Join (cost=xxx rows=1100 width=xxx) + -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) (15 rows) @@ -1622,9 +1622,9 @@ error hint: -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=xxx) @@ -1677,11 +1677,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -1692,10 +1692,10 @@ error hint: -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) - -> Merge Join (cost=xxx rows=1100 width=xxx) + -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) @@ -1704,7 +1704,7 @@ error hint: Join Filter: (bmt1.c1 = bmt4.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) @@ -1775,11 +1775,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -1794,15 +1794,15 @@ error hint: -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (bmt1.c1 = bmt4.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) @@ -1870,11 +1870,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -1885,10 +1885,10 @@ error hint: -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) - -> Merge Join (cost=xxx rows=1100 width=xxx) + -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) @@ -1902,7 +1902,7 @@ error hint: -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) - -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) @@ -1911,7 +1911,7 @@ error hint: Join Filter: (bmt1.c1 = bmt4.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) @@ -1994,11 +1994,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -2013,8 +2013,8 @@ error hint: -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) InitPlan 3 (returns $5) @@ -2026,7 +2026,7 @@ error hint: -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) - -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) @@ -2035,7 +2035,7 @@ error hint: Join Filter: (bmt1.c1 = bmt4.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) @@ -2102,11 +2102,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -2117,21 +2117,21 @@ error hint: -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) - -> Merge Join (cost=xxx rows=1100 width=xxx) + -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt4.c1 = bmt1.c1) - -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt3.c1 = bmt1.c1) - -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: bmt1.c1 -> Nested Loop (cost=xxx rows=1 width=xxx) @@ -2217,11 +2217,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -2236,17 +2236,17 @@ error hint: -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt4.c1 = bmt1.c1) - -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt3.c1 = bmt1.c1) - -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: bmt1.c1 -> Nested Loop (cost=xxx rows=1 width=xxx) @@ -2330,11 +2330,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -2345,10 +2345,10 @@ error hint: -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) - -> Merge Join (cost=xxx rows=1100 width=xxx) + -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) @@ -2362,7 +2362,7 @@ error hint: -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) - -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) @@ -2371,7 +2371,7 @@ error hint: Join Filter: (bmt1.c1 = bmt4.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) @@ -2481,11 +2481,11 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) @@ -2500,8 +2500,8 @@ error hint: -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) - -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) CTE c3 @@ -2513,7 +2513,7 @@ error hint: -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) - -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) @@ -2522,7 +2522,7 @@ error hint: Join Filter: (bmt1.c1 = bmt4.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) @@ -2888,11 +2888,11 @@ error hint: -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Nested Loop (cost=xxx rows=100 width=xxx) - -> Hash Join (cost=xxx rows=1100 width=xxx) + -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (b1t3.c1 = b1t4.c1) - -> Seq Scan on t3 b1t3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 b1t3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) @@ -2902,11 +2902,11 @@ error hint: -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b2t2.c1 -> Nested Loop (cost=xxx rows=100 width=xxx) - -> Hash Join (cost=xxx rows=1100 width=xxx) + -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (b2t3.c1 = b2t4.c1) - -> Seq Scan on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t3.c1) InitPlan 3 (returns $5) @@ -2916,11 +2916,11 @@ error hint: -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b3t2.c1 -> Nested Loop (cost=xxx rows=100 width=xxx) - -> Hash Join (cost=xxx rows=1100 width=xxx) + -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (b3t3.c1 = b3t4.c1) - -> Seq Scan on t3 b3t3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 b3t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 b3t3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b3t3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=999 width=xxx) @@ -2930,11 +2930,11 @@ error hint: -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt4.c1 = c1.c1) - -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=100 width=xxx) @@ -3037,9 +3037,9 @@ error hint: -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t3.c1 = b1t4.c1) - -> Seq Scan on t3 b1t3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 b1t3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) @@ -3051,9 +3051,9 @@ error hint: -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b2t3.c1 = b2t4.c1) - -> Seq Scan on t3 b2t3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 b2t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 b2t3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t3.c1) InitPlan 3 (returns $5) @@ -3065,9 +3065,9 @@ error hint: -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b3t3.c1 = b3t4.c1) - -> Seq Scan on t3 b3t3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 b3t4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 b3t3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b3t3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=999 width=xxx) @@ -3077,11 +3077,11 @@ error hint: -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt4.c1 = c1.c1) - -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) @@ -3139,10 +3139,10 @@ error hint: Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t3.c1 = b1t2.c1) - -> Merge Join (cost=xxx rows=1100 width=xxx) + -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b1t3.c1 = b1t4.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) @@ -3159,11 +3159,11 @@ error hint: -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt4.c1 = c1.c1) - -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=100 width=xxx) @@ -3236,8 +3236,8 @@ error hint: Hash Cond: (b1t3.c1 = b1t2.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t4.c1) - -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1100 width=xxx) - -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) + -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) @@ -3254,11 +3254,11 @@ error hint: -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt4.c1 = c1.c1) - -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) @@ -4350,24 +4350,24 @@ error hint: ---- -- No. R-2-4-1 \o results/ut-R.tmpout -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; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) - -> Hash Join (cost=xxx rows=1 width=xxx) + -> Hash Join (cost=xxx rows=2 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) - -> Hash (cost=xxx rows=1 width=xxx) - -> Values Scan on "*VALUES*" (cost=xxx rows=1 width=xxx) + -> Hash (cost=xxx rows=2 width=xxx) + -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = t2.c1) (8 rows) \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: @@ -4382,18 +4382,18 @@ error hint: QUERY PLAN -------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) - -> Hash Join (cost=xxx rows=1 width=xxx) + -> Hash Join (cost=xxx rows=2 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) - -> Hash (cost=xxx rows=1 width=xxx) - -> Values Scan on "*VALUES*" (cost=xxx rows=1 width=xxx) + -> Hash (cost=xxx rows=2 width=xxx) + -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = t2.c1) (8 rows) \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')) 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: Leading(*VALUES* t1 t2) @@ -4409,7 +4409,7 @@ error hint: ----------------------------------------------------------------------------- Nested Loop (cost=xxx rows=20 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) - -> Values Scan on "*VALUES*" (cost=xxx rows=1 width=xxx) + -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = "*VALUES*".column1) -> Index Scan using t2_i1 on t2 (cost=xxx rows=1 width=xxx) @@ -4418,27 +4418,28 @@ error hint: -- No. R-2-4-2 \o results/ut-R.tmpout -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; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) - Join Filter: (t1.c1 = "*VALUES*_1".column1) -> Nested Loop (cost=xxx rows=1 width=xxx) - -> Hash Join (cost=xxx rows=1 width=xxx) + Join Filter: (t2.c1 = "*VALUES*_1".column1) + -> Hash Join (cost=xxx rows=2 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) - -> Hash (cost=xxx rows=1 width=xxx) - -> Values Scan on "*VALUES*" (cost=xxx rows=1 width=xxx) - -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) - Index Cond: (c1 = t2.c1) - -> Values Scan on "*VALUES*_1" (cost=xxx rows=2 width=xxx) -(11 rows) + -> Hash (cost=xxx rows=2 width=xxx) + -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) + -> Materialize (cost=xxx rows=2 width=xxx) + -> Values Scan on "*VALUES*_1" (cost=xxx rows=2 width=xxx) + -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) + Index Cond: (c1 = t2.c1) +(12 rows) \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: @@ -4454,21 +4455,22 @@ error hint: QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) - Join Filter: (t1.c1 = "*VALUES*_1".column1) -> Nested Loop (cost=xxx rows=1 width=xxx) - -> Hash Join (cost=xxx rows=1 width=xxx) + Join Filter: (t2.c1 = "*VALUES*_1".column1) + -> Hash Join (cost=xxx rows=2 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) - -> Hash (cost=xxx rows=1 width=xxx) - -> Values Scan on "*VALUES*" (cost=xxx rows=1 width=xxx) - -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) - Index Cond: (c1 = t2.c1) - -> Values Scan on "*VALUES*_1" (cost=xxx rows=2 width=xxx) -(11 rows) + -> Hash (cost=xxx rows=2 width=xxx) + -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) + -> Materialize (cost=xxx rows=2 width=xxx) + -> Values Scan on "*VALUES*_1" (cost=xxx rows=2 width=xxx) + -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) + Index Cond: (c1 = t2.c1) +(12 rows) \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; +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: pg_hint_plan: hint syntax error at or near "Rows(*VALUES* t3 t2 t1 #2)" @@ -4490,17 +4492,18 @@ Rows(*VALUES* t1 t2 t3 #2) QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) - Join Filter: (t1.c1 = "*VALUES*_1".column1) -> Nested Loop (cost=xxx rows=1 width=xxx) - -> Hash Join (cost=xxx rows=1 width=xxx) + Join Filter: (t2.c1 = "*VALUES*_1".column1) + -> Hash Join (cost=xxx rows=2 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) - -> Hash (cost=xxx rows=1 width=xxx) - -> Values Scan on "*VALUES*" (cost=xxx rows=1 width=xxx) - -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) - Index Cond: (c1 = t2.c1) - -> Values Scan on "*VALUES*_1" (cost=xxx rows=2 width=xxx) -(11 rows) + -> Hash (cost=xxx rows=2 width=xxx) + -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) + -> Materialize (cost=xxx rows=2 width=xxx) + -> Values Scan on "*VALUES*_1" (cost=xxx rows=2 width=xxx) + -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) + Index Cond: (c1 = t2.c1) +(12 rows) ---- ---- No. R-2-5 @@ -4520,7 +4523,7 @@ EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sb -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) - -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) @@ -4553,11 +4556,11 @@ error hint: Sort Key: bmt2.c1 -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt2.c1) - -> Hash Join (cost=xxx rows=1100 width=xxx) + -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) (14 rows) @@ -4576,7 +4579,7 @@ EXPLAIN SELECT bmt1.c1 FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) - -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) @@ -4609,11 +4612,11 @@ error hint: Sort Key: bmt2.c1 -> Hash Join (cost=xxx rows=60 width=xxx) Hash Cond: (bmt3.c1 = bmt2.c1) - -> Hash Join (cost=xxx rows=660 width=xxx) + -> Hash Join (cost=xxx rows=678 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) (14 rows) @@ -4632,7 +4635,7 @@ EXPLAIN SELECT bmt1.c1 FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) - -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1100 width=xxx) + -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) @@ -4665,11 +4668,11 @@ error hint: Sort Key: bmt2.c1 -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt2.c1) - -> Hash Join (cost=xxx rows=1100 width=xxx) + -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) - -> Seq Scan on t3 bmt3 (cost=xxx rows=1100 width=xxx) - -> Hash (cost=xxx rows=1100 width=xxx) - -> Seq Scan on t4 bmt4 (cost=xxx rows=1100 width=xxx) + -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) + -> Hash (cost=xxx rows=1130 width=xxx) + -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) (14 rows) diff --git a/expected/ut-S.out b/expected/ut-S.out index 519f8ea..3f6d14f 100644 --- a/expected/ut-S.out +++ b/expected/ut-S.out @@ -451,7 +451,7 @@ EXPLAIN (COSTS false) SELECT * FROM pg_catalog.pg_class WHERE oid = 1; QUERY PLAN ------------------------------------------------- Index Scan using pg_class_oid_index on pg_class - Index Cond: (oid = 1::oid) + Index Cond: (oid = '1'::oid) (2 rows) /*+SeqScan(pg_class)*/ @@ -463,10 +463,10 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------- Seq Scan on pg_class - Filter: (oid = 1::oid) + Filter: (oid = '1'::oid) (2 rows) -- No. S-1-5-6 @@ -2995,11 +2995,10 @@ error hint: ---- -- No. S-2-4-1 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1; - QUERY PLAN ---------------------------- - Values Scan on "*VALUES*" - Filter: (column1 = 1) -(2 rows) + QUERY PLAN +------------ + Result +(1 row) /*+SeqScan(t1)*/ EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1; @@ -3010,11 +3009,10 @@ SeqScan(t1) duplication hint: error hint: - QUERY PLAN ---------------------------- - Values Scan on "*VALUES*" - Filter: (column1 = 1) -(2 rows) + QUERY PLAN +------------ + Result +(1 row) /*+SeqScan(*VALUES*)*/ EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1; @@ -3025,24 +3023,24 @@ SeqScan(*VALUES*) duplication hint: error hint: - QUERY PLAN ---------------------------- - Values Scan on "*VALUES*" - Filter: (column1 = 1) -(2 rows) + QUERY PLAN +------------ + Result +(1 row) -- No. S-2-4-2 -EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; - QUERY PLAN ------------------------------------------------------------- - Nested Loop - Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1) +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; + QUERY PLAN +---------------------------------------------------------- + Hash Join + Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1) -> Values Scan on "*VALUES*" - -> Values Scan on "*VALUES*_1" -(4 rows) + -> Hash + -> Values Scan on "*VALUES*_1" +(5 rows) /*+SeqScan(t1 t2)*/ -EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: SeqScan hint accepts only one relation. LOG: pg_hint_plan: @@ -3052,16 +3050,17 @@ duplication hint: error hint: SeqScan(t1 t2) - QUERY PLAN ------------------------------------------------------------- - Nested Loop - Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1) + QUERY PLAN +---------------------------------------------------------- + Hash Join + Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1) -> Values Scan on "*VALUES*" - -> Values Scan on "*VALUES*_1" -(4 rows) + -> Hash + -> Values Scan on "*VALUES*_1" +(5 rows) /*+SeqScan(*VALUES*)*/ -EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: not used hint: @@ -3069,13 +3068,14 @@ SeqScan(*VALUES*) duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------- - Nested Loop - Join Filter: ("*VALUES*".column1 = "*VALUES*_1".column1) + QUERY PLAN +---------------------------------------------------------- + Hash Join + Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1) -> Values Scan on "*VALUES*" - -> Values Scan on "*VALUES*_1" -(4 rows) + -> Hash + -> Values Scan on "*VALUES*_1" +(5 rows) ---- ---- No. S-3-1 scan method hint @@ -3794,7 +3794,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c ----------------------------------------------------------------------------------------------------------------------------------------- Tid Scan on ti1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 < 100) AND (c2 = 1) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-1 @@ -3812,7 +3812,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_btree on ti1 Index Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-2 @@ -3830,7 +3830,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_hash on ti1 Index Cond: (c1 = 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-3 @@ -3848,7 +3848,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_gist on ti1 Index Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-4 @@ -3866,7 +3866,7 @@ error hint: ----------------------------------------------------------------------------------------------------------------------------------------- Tid Scan on ti1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 < 100) AND (c2 = 1) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-5 @@ -3884,7 +3884,7 @@ error hint: ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_expr on ti1 Index Cond: ((c1 < 100) = true) - Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-6 @@ -3920,7 +3920,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_uniq on ti1 Index Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-8 @@ -3938,7 +3938,7 @@ error hint: --------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_multi on ti1 Index Cond: ((c1 < 100) AND (c2 = 1)) - Filter: ((ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-9 @@ -3956,7 +3956,7 @@ error hint: ----------------------------------------------------------------------------------------------------------------------------------------- Tid Scan on ti1 TID Cond: (ctid = '(1,1)'::tid) - Filter: ((c1 < 100) AND (c2 = 1) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (c2 = 1) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-10 @@ -3974,7 +3974,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using ti1_pkey on ti1 Index Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-11 @@ -3992,7 +3992,7 @@ error hint: ------------------------------------------------------------------------------------------------------------------------------------------------------ Index Scan using ti1_c2_key on ti1 Index Cond: (c2 = 1) - Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) (3 rows) -- No. S-3-4-12 @@ -4010,7 +4010,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_btree Index Cond: (c1 < 100) (5 rows) @@ -4030,7 +4030,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 = 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_hash Index Cond: (c1 = 100) (5 rows) @@ -4050,7 +4050,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_gist Index Cond: (c1 < 100) (5 rows) @@ -4070,7 +4070,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_gin Index Cond: (c1 < 100) (5 rows) @@ -4089,7 +4089,7 @@ error hint: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 - Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_expr Index Cond: ((c1 < 100) = true) (4 rows) @@ -4129,7 +4129,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_uniq Index Cond: (c1 < 100) (5 rows) @@ -4149,7 +4149,7 @@ error hint: --------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: ((c1 < 100) AND (c2 = 1)) - Filter: ((ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_multi Index Cond: ((c1 < 100) AND (c2 = 1)) (5 rows) @@ -4189,7 +4189,7 @@ error hint: ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on ti1 Recheck Cond: (c1 < 100) - Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c2 = 1) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_pkey Index Cond: (c1 < 100) (5 rows) @@ -4209,7 +4209,7 @@ error hint: ------------------------------------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on ti1 Recheck Cond: (c2 = 1) - Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery) AND (lower(c4) = '1'::text)) + Filter: ((c1 < 100) AND (ctid = '(1,1)'::tid) AND (lower(c4) = '1'::text) AND (to_tsvector('english'::regconfig, c4) @@ '''a'' & ''b'''::tsquery)) -> Bitmap Index Scan on ti1_c2_key Index Cond: (c2 = 1) (5 rows) @@ -4280,9 +4280,9 @@ not used hint: duplication hint: error hint: - QUERY PLAN ----------------------------------- - Index Scan using ti1_gist on ti1 + QUERY PLAN +--------------------------------------- + Index Only Scan using ti1_gist on ti1 Index Cond: (c1 < 1) (2 rows) @@ -5066,11 +5066,13 @@ EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; QUERY PLAN -------------------------- Update on p1 + Update on p1 + Update on p1c1 -> Seq Scan on p1 Filter: (c1 = 1) -> Seq Scan on p1c1 Filter: (c1 = 1) -(5 rows) +(7 rows) /*+IndexScan(p1)*/ EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; @@ -5084,11 +5086,13 @@ error hint: QUERY PLAN --------------------------------------- Update on p1 + Update on p1 + Update on p1c1 -> Index Scan using p1_i on p1 Index Cond: (c1 = 1) -> Index Scan using p1c1_i on p1c1 Index Cond: (c1 = 1) -(5 rows) +(7 rows) /*+IndexScan(p1 p1_pkey)*/ EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; @@ -5106,11 +5110,13 @@ error hint: QUERY PLAN ------------------------------------------ Update on p1 + Update on p1 + Update on p1c1 -> Index Scan using p1_pkey on p1 Index Cond: (c1 = 1) -> Index Scan using p1c1_pkey on p1c1 Index Cond: (c1 = 1) -(5 rows) +(7 rows) ---- ---- No. S-3-9 inheritance table number diff --git a/make_join_rel.c b/make_join_rel.c index e98b64e..5cda3c4 100644 --- a/make_join_rel.c +++ b/make_join_rel.c @@ -106,7 +106,10 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) /* we don't bother trying to make the remaining fields valid */ sjinfo->lhs_strict = false; sjinfo->delay_upper_joins = false; - sjinfo->join_quals = NIL; + sjinfo->semi_can_btree = false; + sjinfo->semi_can_hash = false; + sjinfo->semi_operators = NIL; + sjinfo->semi_rhs_exprs = NIL; } /* diff --git a/sql/ut-J.sql b/sql/ut-J.sql index 7a9e0b8..dbcdf3d 100644 --- a/sql/ut-J.sql +++ b/sql/ut-J.sql @@ -654,14 +654,14 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, /*+ Leading(t3 t1 t2) HashJoin(t3 t1)NestLoop(t3 t1 t2)*/ EXPLAIN (COSTS false) 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; /*+ Leading(*VALUES* t1 t2) HashJoin(*VALUES* t1)NestLoop(*VALUES* t1 t2)*/ -EXPLAIN (COSTS false) 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 (COSTS false) 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; -- No. J-2-4-2 -EXPLAIN (COSTS false) 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 (COSTS false) 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; /*+ Leading(t4 t3 t2 t1) NestLoop(t4 t3)HashJoin(t4 t3 t2)MergeJoin(t4 t3 t2 t1)*/ -EXPLAIN (COSTS false) 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 (COSTS false) 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; /*+ Leading(*VALUES* t3 t2 t1) NestLoop(t4 t3)HashJoin(*VALUES* t3 t2)MergeJoin(*VALUES* t3 t2 t1)*/ -EXPLAIN (COSTS false) 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 (COSTS false) 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; ---- ---- No. J-3-1 join method hint diff --git a/sql/ut-L.sql b/sql/ut-L.sql index aa02a73..bb452e2 100644 --- a/sql/ut-L.sql +++ b/sql/ut-L.sql @@ -130,9 +130,9 @@ EXPLAIN (COSTS false) SELECT * FROM s1.f1() t1, s1.f1() t2, s1.f1() t3, s1.f1() EXPLAIN (COSTS false) SELECT * FROM s1.f1() t1, s1.f1() t2, s1.f1() t3, s1.f1() t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1; -- No. L-1-6-8 -EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1; +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1; /*+Leading(t4 t3 t2 t1)*/ -EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1; +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1; -- No. L-1-6-9 EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT st1.c1 FROM s1.t1 st1, s1.t1 st2, s1.t1 st3, s1.t1 st4 WHERE st1.c1 = st2.c1 AND st1.c1 = st3.c1 AND st1.c1 = st4.c1) SELECT * FROM c1 ct1, c1 ct2, c1 ct3, c1 ct4 WHERE ct1.c1 = ct2.c1 AND ct1.c1 = ct3.c1 AND ct1.c1 = ct4.c1; @@ -538,18 +538,18 @@ EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1; ---- -- No. L-2-4-1 -EXPLAIN (COSTS false) 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 (COSTS false) 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; /*+ Leading(t3 t1 t2) */ -EXPLAIN (COSTS false) 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 (COSTS false) 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; /*+ Leading(*VALUES* t1 t2) */ -EXPLAIN (COSTS false) 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 (COSTS false) 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; -- No. L-2-4-2 -EXPLAIN (COSTS false) 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 (COSTS false) 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; /*+ Leading(t4 t3 t2 t1) */ -EXPLAIN (COSTS false) 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 (COSTS false) 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; /*+ Leading(*VALUES* t3 t2 t1) */ -EXPLAIN (COSTS false) 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 (COSTS false) 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; ---- ---- No. L-3-1 leading the order of table joins diff --git a/sql/ut-R.sql b/sql/ut-R.sql index 718d93d..196809c 100644 --- a/sql/ut-R.sql +++ b/sql/ut-R.sql @@ -1152,37 +1152,37 @@ EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1; -- No. R-2-4-1 \o results/ut-R.tmpout -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; \o \! sql/maskout.sh results/ut-R.tmpout \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; \o \! sql/maskout.sh results/ut-R.tmpout \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')) 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; \o \! sql/maskout.sh results/ut-R.tmpout -- No. R-2-4-2 \o results/ut-R.tmpout -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; \o \! sql/maskout.sh results/ut-R.tmpout \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; \o \! sql/maskout.sh results/ut-R.tmpout \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; +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 diff --git a/sql/ut-S.sql b/sql/ut-S.sql index 0b6120b..2eed774 100644 --- a/sql/ut-S.sql +++ b/sql/ut-S.sql @@ -490,11 +490,11 @@ EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1; -- No. S-2-4-2 -EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; /*+SeqScan(t1 t2)*/ -EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; /*+SeqScan(*VALUES*)*/ -EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; +EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1; ---- ---- No. S-3-1 scan method hint -- 2.11.0