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
Filter: (c1 = $0)
(13 rows)
+\o results/ut-J.tmpout
/*+MergeJoin(t1 t2)NestLoop(st1 st2)*/
EXPLAIN (COSTS true) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.c1 = (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1);
LOG: pg_hint_plan:
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-J.tmpout
QUERY PLAN
---------------------------------------------------------------------------------------------
- Nested Loop (cost=10000000074.46..10000000084.99 rows=1 width=29)
+ Nested Loop (cost=xxx rows=1 width=xxx)
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)
- -> 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)
+ -> Aggregate (cost=xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx rows=100 width=xxx)
+ -> Seq Scan on t2 st2 (cost=xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 st1 (cost=xxx rows=1 width=xxx)
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=xxx rows=1 width=xxx)
Index Cond: (c1 = $1)
- -> Seq Scan on t2 (cost=0.00..2.25 rows=1 width=14)
+ -> Seq Scan on t2 (cost=xxx rows=1 width=xxx)
Filter: (c1 = $1)
-(11 rows)
+(12 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-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)
----
-> Seq Scan on t2
(5 rows)
+\o results/ut-J.tmpout
/*+NestLoop(t1 t2)*/
EXPLAIN (COSTS true) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
LOG: pg_hint_plan:
duplication hint:
error hint:
+\o
+\! sql/maskout.sh results/ut-J.tmpout
QUERY PLAN
--------------------------------------------------------------------------
- Hash Full Join (cost=10000000003.25..10000000024.00 rows=1000 width=29)
+ Hash Full Join (cost=xxx rows=1000 width=xxx)
Hash Cond: (t1.c1 = t2.c1)
- -> Seq Scan on t1 (cost=0.00..16.00 rows=1000 width=15)
- -> Hash (cost=2.00..2.00 rows=100 width=14)
- -> Seq Scan on t2 (cost=0.00..2.00 rows=100 width=14)
-(5 rows)
+ -> Seq Scan on t1 (cost=xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx rows=100 width=xxx)
+ -> Seq Scan on t2 (cost=xxx rows=100 width=xxx)
+(6 rows)
+\! rm results/ut-J.tmpout