(5 rows)
----
----- No. J-2-1 complexity query block
+---- No. J-2-1 some complexity query blocks
----
-- No. J-2-1-1
EXPLAIN (COSTS false)
(47 rows)
/*+
-Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2)
+Leading(bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
NestLoop(b1t1 b1t2 b1t3 b1t4)
NestLoop(b2t1 b2t2 b2t3 b2t4)
NestLoop(bmt1 bmt2 bmt3 bmt4)
-Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2)
+Leading(bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
not used hint:
duplication hint:
error hint:
(63 rows)
/*+
-Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3)
+Leading(bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
+Leading(b3t4 b3t1 b3t2 b3t3)
MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
NestLoop(b2t1 b2t2 b2t3 b2t4)
NestLoop(b3t1 b3t2 b3t3 b3t4)
NestLoop(bmt1 bmt2 bmt3 bmt4)
-Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3)
+Leading(bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
+Leading(b3t4 b3t1 b3t2 b3t3)
not used hint:
duplication hint:
error hint:
(48 rows)
/*+
-Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2)
+Leading(bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
NestLoop(b1t1 b1t2 b1t3 b1t4)
NestLoop(b2t1 b2t2 b2t3 b2t4)
NestLoop(bmt1 bmt2 bmt3 bmt4)
-Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2)
+Leading(bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
not used hint:
duplication hint:
error hint:
(64 rows)
/*+
-Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3)
+Leading(bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
+Leading(b3t4 b3t1 b3t2 b3t3)
MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4)
MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
NestLoop(b2t1 b2t2 b2t3 b2t4)
NestLoop(b3t1 b3t2 b3t3 b3t4)
NestLoop(bmt1 bmt2 bmt3 bmt4)
-Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3)
+Leading(bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
+Leading(b3t4 b3t1 b3t2 b3t3)
not used hint:
duplication hint:
error hint:
(53 rows)
/*+
-Leading(c2 c1 bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2)
+Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
MergeJoin(c2 c1)HashJoin(c2 c1 bmt1)NestLoop(c2 c1 bmt1 bmt2)MergeJoin(c2 c1 bmt1 bmt2 bmt3)HashJoin(c2 c1 bmt1 bmt2 bmt3 bmt4)
MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
NestLoop(bmt1 bmt2 c1 c2)
MergeJoin(bmt1 bmt2 bmt3 c1 c2)
HashJoin(bmt1 bmt2 bmt3 bmt4 c1 c2)
-Leading(c2 c1 bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2)
+Leading(c2 c1 bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
not used hint:
duplication hint:
error hint:
(72 rows)
/*+
-Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3)
+Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
+Leading(b3t4 b3t1 b3t2 b3t3)
MergeJoin(c3 c2)HashJoin(c3 c2 c1)NestLoop(c3 c2 c1 bmt1)MergeJoin(c3 c2 c1 bmt1 bmt2)HashJoin(c3 c2 c1 bmt1 bmt2 bmt3)NestLoop(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1)
MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2)
MergeJoin(bmt1 bmt2 c1 c2 c3)
HashJoin(bmt1 bmt2 bmt3 c1 c2 c3)
NestLoop(bmt1 bmt2 bmt3 bmt4 c1 c2 c3)
-Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3)
+Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
+Leading(b3t4 b3t1 b3t2 b3t3)
not used hint:
duplication hint:
error hint:
(28 rows)
/*+
-Leading(c1 bmt1 b1t1 b2t1 b3t1)
+Leading(c1 bmt1)
HashJoin(bmt1 c1)
HashJoin(b1t1 c1)
HashJoin(b2t1 c1)
LOG: pg_hint_plan:
used hint:
HashJoin(bmt1 c1)
-Leading(c1 bmt1 b1t1 b2t1 b3t1)
+Leading(c1 bmt1)
not used hint:
HashJoin(b1t1 c1)
HashJoin(b2t1 c1)
(35 rows)
/*+
-Leading(c1 bmt2 b1t2 b2t2 b3t2 bmt1 b1t1 b2t1 b3t1)
+Leading(c1 bmt2 bmt1)
+Leading(b1t2 b1t1)
+Leading(b2t2 b2t1)
+Leading(b3t2 b3t1)
MergeJoin(c1 bmt2)
HashJoin(c1 bmt1 bmt2)
MergeJoin(b1t1 b1t2)
MergeJoin(b3t1 b3t2)
MergeJoin(bmt2 c1)
HashJoin(bmt1 bmt2 c1)
-Leading(c1 bmt2 b1t2 b2t2 b3t2 bmt1 b1t1 b2t1 b3t1)
+Leading(c1 bmt2 bmt1)
+Leading(b1t2 b1t1)
+Leading(b2t2 b2t1)
+Leading(b3t2 b3t1)
not used hint:
duplication hint:
error hint:
(67 rows)
/*+
-Leading(c1 bmt4 b1t4 b2t4 b3t4 bmt3 b1t3 b2t3 b3t3 bmt2 b1t2 b2t2 b3t2 bmt1 b1t1 b2t1 b3t1)
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+Leading(b2t4 b2t3 b2t2 b2t1)
+Leading(b3t4 b3t3 b3t2 b3t1)
MergeJoin(c1 bmt4)
HashJoin(c1 bmt4 bmt3)
NestLoop(c1 bmt4 bmt3 bmt2)
MergeJoin(b3t1 b3t2 b3t3 b3t4)
NestLoop(bmt2 bmt3 bmt4 c1)
MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
-Leading(c1 bmt4 b1t4 b2t4 b3t4 bmt3 b1t3 b2t3 b3t3 bmt2 b1t2 b2t2 b3t2 bmt1 b1t1 b2t1 b3t1)
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+Leading(b2t4 b2t3 b2t2 b2t1)
+Leading(b3t4 b3t3 b3t2 b3t1)
not used hint:
duplication hint:
error hint:
(46 rows)
/*+
-Leading(c1 bmt4 b1t4 bmt3 b1t3 bmt2 b1t2 bmt1 b1t1 b2t1 b3t1)
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
MergeJoin(c1 bmt4)
HashJoin(c1 bmt4 bmt3)
NestLoop(c1 bmt4 bmt3 bmt2)
NestLoop(b1t1 b1t2 b1t3 b1t4)
NestLoop(bmt2 bmt3 bmt4 c1)
MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
-Leading(c1 bmt4 b1t4 bmt3 b1t3 bmt2 b1t2 bmt1 b1t1 b2t1 b3t1)
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
not used hint:
duplication hint:
error hint:
(60 rows)
----
----- No. J-2-3 RULE definition table
+---- No. J-2-3 RULE or VIEW
----
-- No. J-2-3-1
EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
TID Cond: (ctid = '(1,1)'::tid)
(39 rows)
-/*+Leading(
-b1t1 b1t2 b1t3 b1t4
-b2t1 b2t2 b2t3 b2t4 r2_
-)
+/*+
+Leading(b1t1 b1t2 b1t3 b1t4 r2_)
+Leading(b2t1 b2t2 b2t3 b2t4 r2_)
MergeJoin(b1t1 b1t2)
HashJoin(b1t1 b1t2 b1t3)
NestLoop(b1t1 b1t2 b1t3 b1t4)
HashJoin(b1t1 b1t2 b1t3)
NestLoop(b1t1 b1t2 b1t3 b1t4)
MergeJoin(b1t1 b1t2 b1t3 b1t4 r2_)
-Leading(b1t1 b1t2 b1t3 b1t4 b2t1 b2t2 b2t3 b2t4 r2_)
+Leading(b1t1 b1t2 b1t3 b1t4 r2_)
not used hint:
MergeJoin(b2t1 b2t2)
HashJoin(b2t1 b2t2 b2t3)
NestLoop(b2t1 b2t2 b2t3 b2t4)
MergeJoin(b2t1 b2t2 b2t3 b2t4 r2_)
+Leading(b2t1 b2t2 b2t3 b2t4 r2_)
duplication hint:
error hint:
HashJoin(b2t1 b2t2 b2t3)
NestLoop(b2t1 b2t2 b2t3 b2t4)
MergeJoin(b2t1 b2t2 b2t3 b2t4 r2_)
-Leading(b1t1 b1t2 b1t3 b1t4 b2t1 b2t2 b2t3 b2t4 r2_)
+Leading(b2t1 b2t2 b2t3 b2t4 r2_)
not used hint:
MergeJoin(b1t1 b1t2)
HashJoin(b1t1 b1t2 b1t3)
NestLoop(b1t1 b1t2 b1t3 b1t4)
MergeJoin(b1t1 b1t2 b1t3 b1t4 r2_)
+Leading(b1t1 b1t2 b1t3 b1t4 r2_)
duplication hint:
error hint:
TID Cond: (ctid = '(1,1)'::tid)
(59 rows)
-/*+Leading(
-b1t1 b1t2 b1t3 b1t4
-b2t1 b2t2 b2t3 b2t4
-b3t1 b3t2 b3t3 b3t4 r3_
-)
+/*+
+Leading(b1t1 b1t2 b1t3 b1t4 r3_)
+Leading(b2t1 b2t2 b2t3 b2t4 r3_)
+Leading(b3t1 b3t2 b3t3 b3t4 r3_)
MergeJoin(b1t1 b1t2)
HashJoin(b1t1 b1t2 b1t3)
NestLoop(b1t1 b1t2 b1t3 b1t4)
HashJoin(b1t1 b1t2 b1t3)
NestLoop(b1t1 b1t2 b1t3 b1t4)
MergeJoin(b1t1 b1t2 b1t3 b1t4 r3_)
-Leading(b1t1 b1t2 b1t3 b1t4 b2t1 b2t2 b2t3 b2t4 b3t1 b3t2 b3t3 b3t4 r3_)
+Leading(b1t1 b1t2 b1t3 b1t4 r3_)
not used hint:
MergeJoin(b2t1 b2t2)
MergeJoin(b3t1 b3t2)
NestLoop(b3t1 b3t2 b3t3 b3t4)
MergeJoin(b2t1 b2t2 b2t3 b2t4 r3_)
MergeJoin(b3t1 b3t2 b3t3 b3t4 r3_)
+Leading(b2t1 b2t2 b2t3 b2t4 r3_)
+Leading(b3t1 b3t2 b3t3 b3t4 r3_)
duplication hint:
error hint:
HashJoin(b2t1 b2t2 b2t3)
NestLoop(b2t1 b2t2 b2t3 b2t4)
MergeJoin(b2t1 b2t2 b2t3 b2t4 r3_)
-Leading(b1t1 b1t2 b1t3 b1t4 b2t1 b2t2 b2t3 b2t4 b3t1 b3t2 b3t3 b3t4 r3_)
+Leading(b2t1 b2t2 b2t3 b2t4 r3_)
not used hint:
MergeJoin(b1t1 b1t2)
MergeJoin(b3t1 b3t2)
NestLoop(b3t1 b3t2 b3t3 b3t4)
MergeJoin(b1t1 b1t2 b1t3 b1t4 r3_)
MergeJoin(b3t1 b3t2 b3t3 b3t4 r3_)
+Leading(b1t1 b1t2 b1t3 b1t4 r3_)
+Leading(b3t1 b3t2 b3t3 b3t4 r3_)
duplication hint:
error hint:
HashJoin(b3t1 b3t2 b3t3)
NestLoop(b3t1 b3t2 b3t3 b3t4)
MergeJoin(b3t1 b3t2 b3t3 b3t4 r3_)
-Leading(b1t1 b1t2 b1t3 b1t4 b2t1 b2t2 b2t3 b2t4 b3t1 b3t2 b3t3 b3t4 r3_)
+Leading(b3t1 b3t2 b3t3 b3t4 r3_)
not used hint:
MergeJoin(b1t1 b1t2)
MergeJoin(b2t1 b2t2)
NestLoop(b2t1 b2t2 b2t3 b2t4)
MergeJoin(b1t1 b1t2 b1t3 b1t4 r3_)
MergeJoin(b2t1 b2t2 b2t3 b2t4 r3_)
+Leading(b1t1 b1t2 b1t3 b1t4 r3_)
+Leading(b2t1 b2t2 b2t3 b2t4 r3_)
duplication hint:
error hint:
Filter: (c1 = 1)
(74 rows)
+-- No. J-2-3-4
+EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
+ QUERY PLAN
+----------------------------------
+ Hash Join
+ Hash Cond: (v1t1.c1 = v1t1.c1)
+ -> Seq Scan on t1 v1t1
+ -> Hash
+ -> Seq Scan on t1 v1t1
+(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)"
+DETAIL: Relation name "v1t1" is ambiguous.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+HashJoin(v1t1 v1t1)
+
+ QUERY PLAN
+----------------------------------
+ Hash Join
+ Hash Cond: (v1t1.c1 = v1t1.c1)
+ -> Seq Scan on t1 v1t1
+ -> Hash
+ -> Seq Scan on t1 v1t1
+(5 rows)
+
+-- No. J-2-3-5
+EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
+ QUERY PLAN
+-----------------------------------
+ Hash Join
+ Hash Cond: (v1t1.c1 = v1t1_.c1)
+ -> Seq Scan on t1 v1t1
+ -> Hash
+ -> Seq Scan on t1 v1t1_
+(5 rows)
+
+/*+NestLoop(v1t1 v1t1_)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
+LOG: pg_hint_plan:
+used hint:
+NestLoop(v1t1 v1t1_)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Seq Scan on t1 v1t1
+ -> Index Scan using t1_i1 on t1 v1t1_
+ Index Cond: (c1 = v1t1.c1)
+(4 rows)
+
+-- No. J-2-3-6
+EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+----------------------------------
+ Hash Join
+ Hash Cond: (r4t1.c1 = r4t1.c1)
+ -> Seq Scan on t1 r4t1
+ -> Hash
+ -> Seq Scan on t1 r4t1
+(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)"
+DETAIL: Relation name "r4t1" is ambiguous.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+HashJoin(r4t1 r4t1)
+
+ QUERY PLAN
+----------------------------------
+ Hash Join
+ Hash Cond: (r4t1.c1 = r4t1.c1)
+ -> Seq Scan on t1 r4t1
+ -> Hash
+ -> Seq Scan on t1 r4t1
+(5 rows)
+
+-- No. J-2-3-7
+EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+----------------------------------
+ Hash Join
+ Hash Cond: (r4t1.c1 = r5t1.c1)
+ -> Seq Scan on t1 r4t1
+ -> Hash
+ -> Seq Scan on t1 r5t1
+(5 rows)
+
+/*+NestLoop(r4t1 r5t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NestLoop(r4t1 r5t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------
+ Nested Loop
+ -> Seq Scan on t1 r4t1
+ -> Index Scan using t1_i1 on t1 r5t1
+ Index Cond: (c1 = r4t1.c1)
+(4 rows)
+
----
---- No. J-2-4 VALUES clause
----
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;
LOG: pg_hint_plan:
used hint:
-Leading(t3 t1 t2)
not used hint:
HashJoin(t1 t3)
NestLoop(t1 t2 t3)
+Leading(t3 t1 t2)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Nested Loop
- Join Filter: (t1.c1 = "*VALUES*".column1)
- -> Values Scan on "*VALUES*"
- -> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
-(9 rows)
+ -> 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)
/*+ 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')) 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:
-Leading(t4 t3 t2 t1)
not used hint:
NestLoop(t3 t4)
HashJoin(t2 t3 t4)
MergeJoin(t1 t2 t3 t4)
+Leading(t4 t3 t2 t1)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Nested Loop
Join Filter: (t1.c1 = "*VALUES*".column1)
-> Nested Loop
- Join Filter: (t1.c1 = "*VALUES*".column1)
- -> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
- -> Values Scan on "*VALUES*"
+ -> 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*"
-(12 rows)
+(11 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;
used hint:
not used hint:
NestLoop(t3 t4)
-Leading(*VALUES* t3 t2 t1)
duplication hint:
error hint:
HashJoin(*VALUES* t2 t3)
MergeJoin(*VALUES* t1 t2 t3)
+Leading(*VALUES* t3 t2 t1)
QUERY PLAN
-------------------------------------------------------
-- No. J-3-4-7
/*+NestLoop()*/
SELECT * FROM s1.t1 WHERE false;
-INFO: hint syntax error at or near ")"
-DETAIL: Relation name is necessary.
+INFO: hint syntax error at or near ""
+DETAIL: NestLoop hint requires at least two relations.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+NestLoop()
+
c1 | c2 | c3 | c4
----+----+----+----
(0 rows)
-- No. J-3-4-8
/*+NestLoop(t1)*/
SELECT * FROM s1.t1 WHERE false;
-INFO: hint syntax error at or near ")"
+INFO: hint syntax error at or near ""
DETAIL: NestLoop hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
----+----+----+----+----+----+----+----+----+----+----+----
(0 rows)
+----
+---- No. J-3-5 not used hint
+----
+-- No. J-3-5-1
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
+ QUERY PLAN
+------------------------------
+ Hash Full Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+/*+NestLoop(t1 t2)*/
+EXPLAIN (COSTS true) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
+LOG: pg_hint_plan:
+used hint:
+NestLoop(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Hash Full Join (cost=10000000003.25..10000000024.00 rows=1000 width=29)
+ 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)
+