-- No. J-1-4-2
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Merge Join
- Merge Cond: (t1.c1 = t1.c1)
+ Merge Cond: (t1.c1 = t1_1.c1)
-> Index Scan using t1_i1 on t1
-> Sort
- Sort Key: t1.c1
- -> Seq Scan on t1
+ Sort Key: t1_1.c1
+ -> Seq Scan on t1 t1_1
(6 rows)
/*+HashJoin(t1 t1)*/
error hint:
HashJoin(t1 t1)
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Merge Join
- Merge Cond: (t1.c1 = t1.c1)
+ Merge Cond: (t1.c1 = t1_1.c1)
-> Index Scan using t1_i1 on t1
-> Sort
- Sort Key: t1.c1
- -> Seq Scan on t1
+ Sort Key: t1_1.c1
+ -> Seq Scan on t1 t1_1
(6 rows)
/*+HashJoin(s1.t1 s2.t1)*/
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Merge Join
- Merge Cond: (t1.c1 = t1.c1)
+ Merge Cond: (t1.c1 = t1_1.c1)
-> Index Scan using t1_i1 on t1
-> Sort
- Sort Key: t1.c1
- -> Seq Scan on t1
+ Sort Key: t1_1.c1
+ -> Seq Scan on t1 t1_1
(6 rows)
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = s2t1.c1;
-- No. J-1-4-3
EXPLAIN (COSTS false) SELECT *, (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Merge Join
Merge Cond: (t1.c1 = t2.c1)
InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Only Scan using t1_i1 on t1
+ Merge Cond: (t1_1.c1 = t2_1.c1)
+ -> Index Only Scan using t1_i1 on t1 t1_1
-> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
+ Sort Key: t2_1.c1
+ -> Seq Scan on t2 t2_1
-> Index Scan using t1_i1 on t1
-> Sort
Sort Key: t2.c1
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Hash Join
Hash Cond: (t1.c1 = t2.c1)
InitPlan 1 (returns $0)
-> Aggregate
-> Hash Join
- Hash Cond: (t1.c1 = t2.c1)
- -> Seq Scan on t1
+ Hash Cond: (t1_1.c1 = t2_1.c1)
+ -> Seq Scan on t1 t1_1
-> Hash
- -> Seq Scan on t2
+ -> Seq Scan on t2 t2_1
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2
QUERY PLAN
------------------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
-> Merge Join
- Merge Cond: (t1.c1 = t4.c1)
+ Merge Cond: (t1.c1 = t2.c1)
-> Merge Join
Merge Cond: (t1.c1 = t3.c1)
-> Index Scan using t1_i1 on t1
-> Index Scan using t3_i1 on t3
- -> Index Scan using t4_i1 on t4
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
-(12 rows)
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t4_i1 on t4
+ Index Cond: (c1 = t3.c1)
+(13 rows)
----
---- No. J-1-6 object type for the hint
-- No. J-1-6-2
EXPLAIN (COSTS false) SELECT * FROM s1.p1 t1, s1.p1 t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+-----------------------------------------
Hash Join
Hash Cond: (t1.c1 = t2.c1)
-> Append
-> Seq Scan on p1 t1
- -> Seq Scan on p1c1 t1
- -> Seq Scan on p1c2 t1
- -> Seq Scan on p1c3 t1
+ -> Seq Scan on p1c1 t1_1
+ -> Seq Scan on p1c2 t1_2
+ -> Seq Scan on p1c3 t1_3
-> Hash
-> Append
-> Seq Scan on p1 t2
- -> Seq Scan on p1c1 t2
- -> Seq Scan on p1c2 t2
- -> Seq Scan on p1c3 t2
+ -> Seq Scan on p1c1 t2_1
+ -> Seq Scan on p1c2 t2_2
+ -> Seq Scan on p1c3 t2_3
(13 rows)
/*+NestLoop(t1 t2)*/
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Nested Loop
-> Append
-> Seq Scan on p1 t1
- -> Seq Scan on p1c1 t1
- -> Seq Scan on p1c2 t1
- -> Seq Scan on p1c3 t1
+ -> Seq Scan on p1c1 t1_1
+ -> Seq Scan on p1c2 t1_2
+ -> Seq Scan on p1c3 t1_3
-> Append
-> Seq Scan on p1 t2
Filter: (t1.c1 = c1)
- -> Index Scan using p1c1_i on p1c1 t2
+ -> Index Scan using p1c1_i on p1c1 t2_1
Index Cond: (c1 = t1.c1)
- -> Index Scan using p1c2_i on p1c2 t2
+ -> Index Scan using p1c2_i on p1c2 t2_2
Index Cond: (c1 = t1.c1)
- -> Index Scan using p1c3_i on p1c3 t2
+ -> Index Scan using p1c3_i on p1c3 t2_3
Index Cond: (c1 = t1.c1)
(15 rows)
-- No. J-1-6-9
EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) SELECT * FROM s1.t1, c1 WHERE t1.c1 = c1.c1;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Nested Loop
CTE c1
-> Aggregate
-> Merge Join
- Merge Cond: (s1.t1.c1 = t2.c1)
- -> Index Only Scan using t1_i1 on t1
+ Merge Cond: (t1_1.c1 = t2.c1)
+ -> Index Only Scan using t1_i1 on t1 t1_1
-> Sort
Sort Key: t2.c1
-> Seq Scan on t2
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------
Hash Join
- Hash Cond: (s1.t1.c1 = c1.c1)
+ Hash Cond: (t1.c1 = c1.c1)
CTE c1
-> Aggregate
-> Nested Loop
-> Seq Scan on t2
- -> Index Only Scan using t1_i1 on t1
+ -> Index Only Scan using t1_i1 on t1 t1_1
Index Cond: (c1 = t2.c1)
-> Seq Scan on t1
-> Hash
-- No. J-1-6-10
EXPLAIN (COSTS false) SELECT * FROM s1.v1 t1, s1.v1 t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
-> Seq Scan on t1 v1t1
-> Hash
- -> Seq Scan on t1 v1t1
+ -> Seq Scan on t1 v1t1_1
(5 rows)
/*+NestLoop(t1 t2)*/
duplication hint:
error hint:
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
-> Seq Scan on t1 v1t1
-> Hash
- -> Seq Scan on t1 v1t1
+ -> Seq Scan on t1 v1t1_1
(5 rows)
/*+NestLoop(v1t1 v1t1_)*/
QUERY PLAN
---------------------------------------------------------------------------------------------
- Nested Loop (cost=10000000074.46..10000000084.99 rows=1 width=29)
+ Nested Loop (cost=10000000077.03..10000000087.31 rows=1 width=29)
InitPlan 1 (returns $1)
- -> Aggregate (cost=74.45..74.46 rows=1 width=4)
- -> Nested Loop (cost=0.00..74.20 rows=100 width=4)
+ -> Aggregate (cost=76.75..76.76 rows=1 width=4)
+ -> Nested Loop (cost=0.28..76.50 rows=100 width=4)
-> Seq Scan on t2 st2 (cost=0.00..2.00 rows=100 width=4)
- -> Index Only Scan using t1_i1 on t1 st1 (cost=0.00..0.71 rows=1 width=4)
+ -> Index Only Scan using t1_i1 on t1 st1 (cost=0.28..0.74 rows=1 width=4)
Index Cond: (c1 = st2.c1)
- -> Index Scan using t1_i1 on t1 (cost=0.00..8.27 rows=1 width=15)
+ -> Index Scan using t1_i1 on t1 (cost=0.28..8.29 rows=1 width=15)
Index Cond: (c1 = $1)
-> Seq Scan on t2 (cost=0.00..2.25 rows=1 width=14)
Filter: (c1 = $1)
(11 rows)
+--
+-- There are cases where difference in the measured value and predicted value
+-- depending upon the version of PostgreSQL
+--
EXPLAIN (COSTS false) SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1;
QUERY PLAN
------------------------------------
-- No. J-2-3-4
EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
-> Seq Scan on t1 v1t1
-> Hash
- -> Seq Scan on t1 v1t1
+ -> Seq Scan on t1 v1t1_1
(5 rows)
/*+HashJoin(v1t1 v1t1)*/
error hint:
HashJoin(v1t1 v1t1)
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
-> Seq Scan on t1 v1t1
-> Hash
- -> Seq Scan on t1 v1t1
+ -> Seq Scan on t1 v1t1_1
(5 rows)
-- No. J-2-3-5
-- No. J-2-3-6
EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (r4t1.c1 = r4t1.c1)
+ Hash Cond: (r4t1.c1 = r4t1_1.c1)
-> Seq Scan on t1 r4t1
-> Hash
- -> Seq Scan on t1 r4t1
+ -> Seq Scan on t1 r4t1_1
(5 rows)
/*+HashJoin(r4t1 r4t1)*/
error hint:
HashJoin(r4t1 r4t1)
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (r4t1.c1 = r4t1.c1)
+ Hash Cond: (r4t1.c1 = r4t1_1.c1)
-> Seq Scan on t1 r4t1
-> Hash
- -> Seq Scan on t1 r4t1
+ -> Seq Scan on t1 r4t1_1
(5 rows)
-- No. J-2-3-7
QUERY PLAN
-------------------------------------------------------
Nested Loop
- Join Filter: (t1.c1 = "*VALUES*".column1)
+ Join Filter: (t1.c1 = "*VALUES*_1".column1)
-> Nested Loop
-> Hash Join
Hash Cond: (t2.c1 = "*VALUES*".column1)
-> Values Scan on "*VALUES*"
-> Index Scan using t1_i1 on t1
Index Cond: (c1 = t2.c1)
- -> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
(11 rows)
/*+ Leading(t4 t3 t2 t1) NestLoop(t4 t3)HashJoin(t4 t3 t2)MergeJoin(t4 t3 t2 t1)*/
QUERY PLAN
-------------------------------------------------------
Nested Loop
- Join Filter: (t1.c1 = "*VALUES*".column1)
+ Join Filter: (t1.c1 = "*VALUES*_1".column1)
-> Nested Loop
-> Hash Join
Hash Cond: (t2.c1 = "*VALUES*".column1)
-> Values Scan on "*VALUES*"
-> Index Scan using t1_i1 on t1
Index Cond: (c1 = t2.c1)
- -> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
(11 rows)
/*+ Leading(*VALUES* t3 t2 t1) NestLoop(t4 t3)HashJoin(*VALUES* t3 t2)MergeJoin(*VALUES* t3 t2 t1)*/
QUERY PLAN
-------------------------------------------------------
Nested Loop
- Join Filter: (t1.c1 = "*VALUES*".column1)
+ Join Filter: (t1.c1 = "*VALUES*_1".column1)
-> Nested Loop
-> Hash Join
Hash Cond: (t2.c1 = "*VALUES*".column1)
-> Values Scan on "*VALUES*"
-> Index Scan using t1_i1 on t1
Index Cond: (c1 = t2.c1)
- -> Values Scan on "*VALUES*"
+ -> Values Scan on "*VALUES*_1"
(11 rows)
----
---- No. J-3-2 join inherit tables
----
EXPLAIN (COSTS false) SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
Hash Cond: (p2.c1 = p1.c1)
-> Append
-> Seq Scan on p2
- -> Seq Scan on p2c1 p2
- -> Seq Scan on p2c2 p2
- -> Seq Scan on p2c3 p2
- -> Seq Scan on p2c1c1 p2
- -> Seq Scan on p2c1c2 p2
- -> Seq Scan on p2c2c1 p2
- -> Seq Scan on p2c2c2 p2
- -> Seq Scan on p2c3c1 p2
- -> Seq Scan on p2c3c2 p2
+ -> Seq Scan on p2c1
+ -> Seq Scan on p2c2
+ -> Seq Scan on p2c3
+ -> Seq Scan on p2c1c1
+ -> Seq Scan on p2c1c2
+ -> Seq Scan on p2c2c1
+ -> Seq Scan on p2c2c2
+ -> Seq Scan on p2c3c1
+ -> Seq Scan on p2c3c2
-> Hash
-> Append
-> Seq Scan on p1
- -> Seq Scan on p1c1 p1
- -> Seq Scan on p1c2 p1
- -> Seq Scan on p1c3 p1
+ -> Seq Scan on p1c1
+ -> Seq Scan on p1c2
+ -> Seq Scan on p1c3
(19 rows)
-- No. J-3-2-1
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+--------------------------------------
Merge Join
Merge Cond: (p1.c1 = p2.c1)
-> Sort
Sort Key: p1.c1
-> Append
-> Seq Scan on p1
- -> Seq Scan on p1c1 p1
- -> Seq Scan on p1c2 p1
- -> Seq Scan on p1c3 p1
+ -> Seq Scan on p1c1
+ -> Seq Scan on p1c2
+ -> Seq Scan on p1c3
-> Sort
Sort Key: p2.c1
-> Append
-> Seq Scan on p2
- -> Seq Scan on p2c1 p2
- -> Seq Scan on p2c2 p2
- -> Seq Scan on p2c3 p2
- -> Seq Scan on p2c1c1 p2
- -> Seq Scan on p2c1c2 p2
- -> Seq Scan on p2c2c1 p2
- -> Seq Scan on p2c2c2 p2
- -> Seq Scan on p2c3c1 p2
- -> Seq Scan on p2c3c2 p2
+ -> Seq Scan on p2c1
+ -> Seq Scan on p2c2
+ -> Seq Scan on p2c3
+ -> Seq Scan on p2c1c1
+ -> Seq Scan on p2c1c2
+ -> Seq Scan on p2c2c1
+ -> Seq Scan on p2c2c2
+ -> Seq Scan on p2c3c1
+ -> Seq Scan on p2c3c2
(22 rows)
-- No. J-3-2-2
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
Hash Cond: (p2.c1 = p1.c1)
-> Append
-> Seq Scan on p2
- -> Seq Scan on p2c1 p2
- -> Seq Scan on p2c2 p2
- -> Seq Scan on p2c3 p2
- -> Seq Scan on p2c1c1 p2
- -> Seq Scan on p2c1c2 p2
- -> Seq Scan on p2c2c1 p2
- -> Seq Scan on p2c2c2 p2
- -> Seq Scan on p2c3c1 p2
- -> Seq Scan on p2c3c2 p2
+ -> Seq Scan on p2c1
+ -> Seq Scan on p2c2
+ -> Seq Scan on p2c3
+ -> Seq Scan on p2c1c1
+ -> Seq Scan on p2c1c2
+ -> Seq Scan on p2c2c1
+ -> Seq Scan on p2c2c2
+ -> Seq Scan on p2c3c1
+ -> Seq Scan on p2c3c2
-> Hash
-> Append
-> Seq Scan on p1
- -> Seq Scan on p1c1 p1
- -> Seq Scan on p1c2 p1
- -> Seq Scan on p1c3 p1
+ -> Seq Scan on p1c1
+ -> Seq Scan on p1c2
+ -> Seq Scan on p1c3
(19 rows)
----