SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO LOG;
SET search_path TO public;
+SET max_parallel_workers_per_gather TO 0;
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
QUERY PLAN
------------------------------------
-- 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: (s1.t1.c1 = s2.t1.c1)
+ Merge Cond: (t1.c1 = t1_1.c1)
-> Index Scan using t1_i1 on t1
-> Sort
- Sort Key: s2.t1.c1
- -> Seq Scan on t1
+ Sort Key: t1_1.c1
+ -> Seq Scan on t1 t1_1
(6 rows)
/*+HashJoin(t1 t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1;
-INFO: hint syntax error at or near "HashJoin(t1 t1)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t1 t1)"
DETAIL: Relation name "t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
error hint:
HashJoin(t1 t1)
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Merge Join
- Merge Cond: (s1.t1.c1 = s2.t1.c1)
+ Merge Cond: (t1.c1 = t1_1.c1)
-> Index Scan using t1_i1 on t1
-> Sort
- Sort Key: s2.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: (s1.t1.c1 = s2.t1.c1)
+ Merge Cond: (t1.c1 = t1_1.c1)
-> Index Scan using t1_i1 on t1
-> Sort
- Sort Key: s2.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: (s1.t1.c1 = s1.t2.c1)
+ Merge Cond: (t1.c1 = t2.c1)
InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
- Merge Cond: (s1.t1.c1 = s1.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: s1.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: s1.t2.c1
+ Sort Key: t2.c1
-> Seq Scan on t2
(14 rows)
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Hash Join
- Hash Cond: (s1.t1.c1 = s1.t2.c1)
+ Hash Cond: (t1.c1 = t2.c1)
InitPlan 1 (returns $0)
-> Aggregate
-> Hash Join
- Hash Cond: (s1.t1.c1 = s1.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
-- No. J-1-5-2
/*+HashJoin(t1 t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "HashJoin(t1 t1)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t1 t1)"
DETAIL: Relation name "t1" is duplicated.
LOG: pg_hint_plan:
used hint:
-- No. J-1-5-3
/*+HashJoin(t1 t1)HashJoin(t2 t2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "HashJoin(t1 t1)HashJoin(t2 t2)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t1 t1)HashJoin(t2 t2)"
DETAIL: Relation name "t1" is duplicated.
-INFO: hint syntax error at or near "HashJoin(t2 t2)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t2 t2)"
DETAIL: Relation name "t2" is duplicated.
LOG: pg_hint_plan:
used hint:
/*+HashJoin(t1 t2 t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
-INFO: hint syntax error at or near "HashJoin(t1 t2 t1 t2)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t1 t2 t1 t2)"
DETAIL: Relation name "t1" is duplicated.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
------------------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ Nested Loop
-> 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
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t4_i1 on t4
+ Index Cond: (c1 = t1.c1)
(12 rows)
----
-- 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_)*/
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)
+--
+-- 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
------------------------------------
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $3)
+ InitPlan 4 (returns $4)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $3)
+ Filter: (c1 <> $4)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 bmt3
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $3)
+ InitPlan 4 (returns $4)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Sort Key: bmt1.c1
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $3)
+ Filter: (c1 <> $4)
(60 rows)
----
-- 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)*/
EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
-INFO: hint syntax error at or near "HashJoin(v1t1 v1t1)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(v1t1 v1t1)"
DETAIL: Relation name "v1t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
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)*/
EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "HashJoin(r4t1 r4t1)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(r4t1 r4t1)"
DETAIL: Relation name "r4t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
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
----
-- 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;
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)
(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*".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*"
-(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:
QUERY PLAN
-------------------------------------------------------
Nested Loop
- Join Filter: (t1.c1 = "*VALUES*".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*"
-(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;
-INFO: hint syntax error at or near "HashJoin(*VALUES* t3 t2)MergeJoin(*VALUES* t3 t2 t1)"
+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: hint syntax error at or near "MergeJoin(*VALUES* t3 t2 t1)"
+INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(*VALUES* t3 t2 t1)"
DETAIL: Relation name "*VALUES*" is ambiguous.
-INFO: hint syntax error at or near "Leading(*VALUES* t3 t2 t1) NestLoop(t4 t3)HashJoin(*VALUES* t3 t2)MergeJoin(*VALUES* t3 t2 t1)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(*VALUES* t3 t2 t1) NestLoop(t4 t3)HashJoin(*VALUES* t3 t2)MergeJoin(*VALUES* t3 t2 t1)"
DETAIL: Relation name "*VALUES*" is ambiguous.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
-------------------------------------------------------
Nested Loop
- Join Filter: (t1.c1 = "*VALUES*".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*"
-(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
---- 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: (s1.p2.c1 = s1.p1.c1)
+ 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: (s1.p1.c1 = s1.p2.c1)
+ Merge Cond: (p1.c1 = p2.c1)
-> Sort
- Sort Key: s1.p1.c1
+ 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: s1.p2.c1
+ 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: (s1.p2.c1 = s1.p1.c1)
+ 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-2 join partitioned tables
+----
+EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1;
+ QUERY PLAN
+--------------------------------------
+ Hash Join
+ Hash Cond: (p2.c1 = pt1_c1.c1)
+ -> Append
+ -> Seq Scan on 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 pt1_c1
+ -> Seq Scan on pt1_c2
+ -> Seq Scan on pt1_c3
+(18 rows)
+
+/*+MergeJoin(pt1 p2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(p2 pt1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------
+ Merge Join
+ Merge Cond: (pt1_c1.c1 = p2.c1)
+ -> Sort
+ Sort Key: pt1_c1.c1
+ -> Append
+ -> Seq Scan on pt1_c1
+ -> Seq Scan on pt1_c2
+ -> Seq Scan on pt1_c3
+ -> Sort
+ Sort Key: p2.c1
+ -> Append
+ -> Seq Scan on 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
+(21 rows)
+
+/*+MergeJoin(pt1_c1 p2c1)*/ /* will ignored */
+EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1;
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+MergeJoin(p2c1 pt1_c1)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------
+ Hash Join
+ Hash Cond: (p2.c1 = pt1_c1.c1)
+ -> Append
+ -> Seq Scan on 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 pt1_c1
+ -> Seq Scan on pt1_c2
+ -> Seq Scan on pt1_c3
+(18 rows)
+
+----
---- No. J-3-3 conflict join method hint
----
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-- No. J-3-3-1
/*+HashJoin(t1 t2)NestLoop(t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "HashJoin(t1 t2)NestLoop(t1 t2)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t1 t2)NestLoop(t1 t2)"
DETAIL: Conflict join method hint.
LOG: pg_hint_plan:
used hint:
-- No. J-3-3-2
/*+MergeJoin(t1 t2)HashJoin(t1 t2)NestLoop(t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "MergeJoin(t1 t2)HashJoin(t1 t2)NestLoop(t1 t2)"
+INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t1 t2)HashJoin(t1 t2)NestLoop(t1 t2)"
DETAIL: Conflict join method hint.
-INFO: hint syntax error at or near "HashJoin(t1 t2)NestLoop(t1 t2)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t1 t2)NestLoop(t1 t2)"
DETAIL: Conflict join method hint.
LOG: pg_hint_plan:
used hint:
-- No. J-3-3-3
/*+HashJoin(t1 t2)NestLoop(t2 t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "HashJoin(t1 t2)NestLoop(t2 t1)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t1 t2)NestLoop(t2 t1)"
DETAIL: Conflict join method hint.
LOG: pg_hint_plan:
used hint:
-- No. J-3-3-4
/*+MergeJoin(t2 t1)HashJoin(t1 t2)NestLoop(t2 t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "MergeJoin(t2 t1)HashJoin(t1 t2)NestLoop(t2 t1)"
+INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t2 t1)HashJoin(t1 t2)NestLoop(t2 t1)"
DETAIL: Conflict join method hint.
-INFO: hint syntax error at or near "HashJoin(t1 t2)NestLoop(t2 t1)"
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t1 t2)NestLoop(t2 t1)"
DETAIL: Conflict join method hint.
LOG: pg_hint_plan:
used hint:
-- No. J-3-4-7
/*+NestLoop()*/
SELECT * FROM s1.t1 WHERE false;
-INFO: hint syntax error at or near ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NestLoop hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
-- No. J-3-4-8
/*+NestLoop(t1)*/
SELECT * FROM s1.t1 WHERE false;
-INFO: hint syntax error at or near ""
+INFO: pg_hint_plan: hint syntax error at or near ""
DETAIL: NestLoop hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
-> 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)
+ -> 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)
(5 rows)
+\! rm results/ut-J.tmpout