LOAD 'pg_hint_plan';
-SET pg_hint_plan.enable TO on;
+SET pg_hint_plan.enable_hint TO on;
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
------------------------------------
-> Seq Scan on t2
(5 rows)
--- No. J-1-3-1
+-- No. J-1-3-2
/*+HashJoin(t3 t4)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_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 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
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Hash Join
Hash Cond: (t1.c1 = t2.c1)
InitPlan 1 (returns $1)
-> Aggregate
-> Nested Loop
-> Seq Scan on t2 st2
- -> Index Scan using t1_i1 on t1 st1
+ -> Index Only Scan using t1_i1 on t1 st1
Index Cond: (c1 = st2.c1)
-> Seq Scan on t1
-> Hash
-- 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)"
-DETAIL: Relation name "t1" is duplicate.
+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:
not 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)"
-DETAIL: Relation name "t1" is duplicate.
-INFO: hint syntax error at or near "HashJoin(t2 t2)"
-DETAIL: Relation name "t2" is duplicate.
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t1 t1)HashJoin(t2 t2)"
+DETAIL: Relation name "t1" is duplicated.
+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:
not 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)"
-DETAIL: Relation name "t1" is duplicate.
+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:
not 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 p1 t1_1
+ -> Seq Scan on p1c1 t1_2
+ -> Seq Scan on p1c2 t1_3
+ -> Seq Scan on p1c3 t1_4
-> 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 p1 t2_1
+ -> Seq Scan on p1c1 t2_2
+ -> Seq Scan on p1c2 t2_3
+ -> Seq Scan on p1c3 t2_4
(13 rows)
/*+NestLoop(t1 t2)*/
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Nested Loop
- Join Filter: (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 p1 t1_1
+ -> Seq Scan on p1c1 t1_2
+ -> Seq Scan on p1c2 t1_3
+ -> Seq Scan on p1c3 t1_4
-> Append
- -> Index Scan using p1_i on p1 t2
- Index Cond: ((c1 = t1.c1) AND (c1 = t1.c1))
- -> Index Scan using p1c1_i on p1c1 t2
- Index Cond: ((c1 = t1.c1) AND (c1 = t1.c1))
- -> Index Scan using p1c2_i on p1c2 t2
- Index Cond: ((c1 = t1.c1) AND (c1 = t1.c1))
- -> Index Scan using p1c3_i on p1c3 t2
- Index Cond: ((c1 = t1.c1) AND (c1 = t1.c1))
-(16 rows)
+ -> Seq Scan on p1 t2_1
+ Filter: (t1.c1 = c1)
+ -> Index Scan using p1c1_i on p1c1 t2_2
+ Index Cond: (c1 = t1.c1)
+ -> Index Scan using p1c2_i on p1c2 t2_3
+ Index Cond: (c1 = t1.c1)
+ -> Index Scan using p1c3_i on p1c3 t2_4
+ Index Cond: (c1 = t1.c1)
+(15 rows)
-- No. J-1-6-3
EXPLAIN (COSTS false) SELECT * FROM s1.ul1 t1, s1.ul1 t2 WHERE t1.c1 = t2.c1;
-- No. J-1-6-4
CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL);
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tm1_pkey" for table "tm1"
EXPLAIN (COSTS false) SELECT * FROM tm1 t1, tm1 t2 WHERE t1.c1 = t2.c1;
QUERY PLAN
--------------------------------
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+---------------------------------------------
Nested Loop
-> Seq Scan on tm1 t1
- -> Index Scan using tm1_pkey on tm1 t2
+ -> Index Scan using tm1_c1_idx on tm1 t2
Index Cond: (c1 = t1.c1)
(4 rows)
(4 rows)
-- No. J-1-6-6
--- refer fdw.sql
+-- refer ut-fdw.sql
-- No. J-1-6-7
EXPLAIN (COSTS false) SELECT * FROM s1.f1() t1, s1.f1() t2 WHERE t1.c1 = t2.c1;
QUERY PLAN
(5 rows)
-- No. J-1-6-8
-EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t2 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1;
- QUERY PLAN
---------------------------------------------------------
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+-------------------------------------------
Hash Join
- Hash Cond: ("*VALUES*".column1 = "*VALUES*".column1)
- -> Values Scan on "*VALUES*"
+ Hash Cond: (t2.c1 = "*VALUES*".column1)
+ -> Seq Scan on t2
-> Hash
-> Values Scan on "*VALUES*"
(5 rows)
/*+NestLoop(t1 t2)*/
-EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t2 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1;
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
used hint:
not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------
Hash Join
- Hash Cond: ("*VALUES*".column1 = "*VALUES*".column1)
- -> Values Scan on "*VALUES*"
+ Hash Cond: (t2.c1 = "*VALUES*".column1)
+ -> Seq Scan on t2
-> Hash
-> Values Scan on "*VALUES*"
(5 rows)
-/*+NestLoop(*VALUES* *VALUES*)*/
-EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t2 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "NestLoop(*VALUES* *VALUES*)"
-DETAIL: Relation name "*VALUES*" is ambiguous.
+/*+NestLoop(*VALUES* t2)*/
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1;
LOG: pg_hint_plan:
used hint:
+NestLoop(*VALUES* t2)
not used hint:
duplication hint:
error hint:
-NestLoop(*VALUES* *VALUES*)
- QUERY PLAN
---------------------------------------------------------
- Hash Join
- Hash Cond: ("*VALUES*".column1 = "*VALUES*".column1)
- -> Values Scan on "*VALUES*"
- -> Hash
+ QUERY PLAN
+---------------------------------------------
+ Nested Loop
+ Join Filter: ("*VALUES*".column1 = t2.c1)
+ -> Seq Scan on t2
+ -> Materialize
-> Values Scan on "*VALUES*"
(5 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 Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
- -> CTE Scan on c1
+ -> Aggregate
+ -> Merge Join
+ 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
-> Index Scan using t1_i1 on t1
- Index Cond: (c1 = c1.c1)
-(12 rows)
+ Index Cond: (c1 = (max(t1_1.c1)))
+(10 rows)
/*+NestLoop(t1 t2)HashJoin(t1 c1)*/
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;
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Hash Join
- Hash Cond: (s1.t1.c1 = c1.c1)
- CTE c1
- -> Aggregate
- -> Nested Loop
- -> Seq Scan on t2
- -> Index Scan using t1_i1 on t1
- Index Cond: (c1 = t2.c1)
+ Hash Cond: (t1.c1 = (max(t1_1.c1)))
-> Seq Scan on t1
-> Hash
- -> CTE Scan on c1
-(11 rows)
+ -> Aggregate
+ -> Nested Loop
+ -> Seq Scan on t2
+ -> Index Only Scan using t1_i1 on t1 t1_1
+ Index Cond: (c1 = t2.c1)
+(9 rows)
-- 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_)*/
-- No. J-1-6-11
EXPLAIN (COSTS false) 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);
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Nested Loop
InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (st1.c1 = st2.c1)
- -> Index Scan using t1_i1 on t1 st1
+ -> Index Only Scan using t1_i1 on t1 st1
-> Sort
Sort Key: st2.c1
-> Seq Scan on t2 st2
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:
- QUERY PLAN
-----------------------------------------------------------------------------------------
- Nested Loop (cost=10000000074.28..10000000084.81 rows=1 width=29)
+\o
+\! sql/maskout.sh results/ut-J.tmpout
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Nested Loop (cost={inf}..{inf} rows=1 width=xxx)
InitPlan 1 (returns $1)
- -> Aggregate (cost=74.27..74.28 rows=1 width=4)
- -> Nested Loop (cost=0.00..74.02 rows=100 width=4)
- -> Seq Scan on t2 st2 (cost=0.00..2.00 rows=100 width=4)
- -> Index Scan using t1_i1 on t1 st1 (cost=0.00..0.71 rows=1 width=4)
+ -> Aggregate (cost=xxx..xxx rows=1 width=xxx)
+ -> Nested Loop (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 st2 (cost=xxx..xxx rows=100 width=xxx)
+ -> Index Only Scan using t1_i1 on t1 st1 (cost=xxx..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..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..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
------------------------------------
(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:
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt2.c1)
+ Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt3.c1)
+ Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 <> $0) AND (c1 <> $1))
- -> Tid Scan on t3 bmt3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
(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:
AND bmt1.c1 = c1.c1
AND bmt1.c1 = c2.c1
;
- QUERY PLAN
------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t4.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t3.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
+ Join Filter: (bmt1.c1 = (max(b2t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
- -> CTE Scan on c2
-(53 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(49 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:
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join
- Hash Cond: (b1t4.c1 = b1t2.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b1t2.c1 = b1t3.c1)
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b1t3.c1
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Hash Join
- Hash Cond: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b2t3.c1 = b2t4.c1)
- -> Sort
- Sort Key: b2t3.c1
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b2t4.c1
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (bmt4.c1 = bmt1.c1)
-> Tid Scan on t4 bmt4
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Hash Join
- Hash Cond: (bmt1.c1 = c1.c1)
+ Hash Cond: (bmt1.c1 = (max(b1t1.c1)))
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
-> Hash
-> Merge Join
- Merge Cond: (c1.c1 = c2.c1)
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b1t2.c1 = b1t3.c1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t3.c1
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c2.c1
- -> CTE Scan on c2
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Hash Join
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t3.c1
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b2t4.c1
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Sort
Sort Key: bmt3.c1
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-(73 rows)
+(69 rows)
-- No. J-2-1-8
EXPLAIN (COSTS false)
AND bmt1.c1 = c2.c1
AND bmt1.c1 = c3.c1
;
- QUERY PLAN
------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t4.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t3.c1)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c3
- -> Aggregate
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t4.c1)
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t3.c1)
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t2.c1)
- -> Tid Scan on t1 b3t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b3t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c3.c1)
+ Join Filter: (bmt1.c1 = (max(b3t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
+ Join Filter: (bmt1.c1 = bmt2.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt3.c1)
- -> Nested Loop
- Join Filter: (bmt1.c1 = bmt2.c1)
- -> Tid Scan on t1 bmt1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 bmt3
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
+ -> Merge Join
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
+ -> Sort
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t3.c1)
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
- -> CTE Scan on c2
- -> CTE Scan on c3
-(72 rows)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t4.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t3.c1)
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t2.c1)
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b3t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b3t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(70 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:
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join
- Hash Cond: (b1t4.c1 = b1t2.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b1t2.c1 = b1t3.c1)
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b1t3.c1
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Hash Join
- Hash Cond: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b2t3.c1 = b2t4.c1)
- -> Sort
- Sort Key: b2t3.c1
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b2t4.c1
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- CTE c3
- -> Aggregate
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t3.c1)
- -> Hash Join
- Hash Cond: (b3t2.c1 = b3t1.c1)
- -> Seq Scan on t2 b3t2
- Filter: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b3t1.c1 = b3t4.c1)
- -> Sort
- Sort Key: b3t1.c1
- -> Tid Scan on t1 b3t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b3t4.c1
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
-> Hash Join
-> Sort
Sort Key: bmt1.c1
-> Nested Loop
- Join Filter: (c1.c1 = bmt1.c1)
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
-> Hash Join
- Hash Cond: (c2.c1 = c1.c1)
+ Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1)))
-> Merge Join
- Merge Cond: (c2.c1 = c3.c1)
+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
-> Sort
- Sort Key: c2.c1
- -> CTE Scan on c2
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Hash Join
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t3.c1
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b2t4.c1
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c3.c1
- -> CTE Scan on c3
+ Sort Key: (max(b3t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t3.c1)
+ -> Hash Join
+ Hash Cond: (b3t2.c1 = b3t1.c1)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b3t1.c1 = b3t4.c1)
+ -> Sort
+ Sort Key: b3t1.c1
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b3t4.c1
+ -> Tid Scan on t4 b3t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b3t3
+ TID Cond: (ctid = '(1,1)'::tid)
-> Hash
- -> CTE Scan on c1
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b1t2.c1 = b1t3.c1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t3.c1
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
-> Sort
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
-(97 rows)
+(91 rows)
----
---- No. J-2-2 the number of the tables per quiry block
SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1
)
;
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
+ -> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
- -> Result
- InitPlan 5 (returns $4)
- -> Limit
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 <> $5) AND (c1 = 1))
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(28 rows)
+ Filter: ((c1 <> $3) AND (c1 = 1))
+ -> Result
+ One-Time Filter: ($4 = 1)
+ InitPlan 5 (returns $4)
+ -> Limit
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
+(26 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)
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
+ -> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
- -> Result
- InitPlan 5 (returns $4)
- -> Limit
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 <> $5) AND (c1 = 1))
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(28 rows)
+ Filter: ((c1 <> $3) AND (c1 = 1))
+ -> Result
+ One-Time Filter: ($4 = 1)
+ InitPlan 5 (returns $4)
+ -> Limit
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
+(26 rows)
-- No. J-2-2-2
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 b2t2
Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t2.c1)
-> Seq Scan on t2 b3t2
Filter: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(35 rows)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(33 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:
- QUERY PLAN
----------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
- -> Sort
- Sort Key: b1t1.c1
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (b2t1.c1 = b2t2.c1)
Sort Key: b2t2.c1
-> Seq Scan on t2 b2t2
Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (b3t1.c1 = b3t2.c1)
Hash Cond: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Hash
-> Merge Join
- Merge Cond: (bmt2.c1 = c1.c1)
+ Merge Cond: (bmt2.c1 = (max(b1t1.c1)))
-> Sort
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
-(52 rows)
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Sort
+ Sort Key: b1t1.c1
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(50 rows)
-- No. J-2-2-3
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t4.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 b2t4
TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Nested Loop
Join Filter: (b3t1.c1 = b3t4.c1)
-> Tid Scan on t4 b3t4
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt2.c1)
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt3.c1 = bmt1.c1)
+ Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
- Join Filter: (bmt3.c1 = bmt4.c1)
- -> Tid Scan on t3 bmt3
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 bmt4
+ Filter: (c1 <> $1)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 bmt1
+ -> Tid Scan on t4 b1t4
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(67 rows)
+(65 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:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Merge Join
- Merge Cond: (b1t2.c1 = b1t1.c1)
- -> Sort
- Sort Key: b1t2.c1
- -> Nested Loop
- Join Filter: (b1t3.c1 = b1t2.c1)
- -> Hash Join
- Hash Cond: (b1t3.c1 = b1t4.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Hash
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b1t1.c1
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (b2t2.c1 = b2t1.c1)
Sort Key: b2t1.c1
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (b3t2.c1 = b3t1.c1)
TID Cond: (ctid = '(1,1)'::tid)
-> Hash
-> Merge Join
- Merge Cond: (bmt4.c1 = c1.c1)
+ Merge Cond: (bmt4.c1 = (max(b1t1.c1)))
-> Sort
Sort Key: bmt4.c1
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Merge Join
+ Merge Cond: (b1t2.c1 = b1t1.c1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Nested Loop
+ Join Filter: (b1t3.c1 = b1t2.c1)
+ -> Hash Join
+ Hash Cond: (b1t3.c1 = b1t4.c1)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t1.c1
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Sort
Sort Key: bmt1.c1
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
-(91 rows)
+ Filter: (c1 <> $1)
+(89 rows)
-- No. J-2-2-4
EXPLAIN (COSTS false)
SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
)
;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $3)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt2.c1)
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt3.c1 = bmt1.c1)
+ Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
- Join Filter: (bmt3.c1 = bmt4.c1)
- -> Tid Scan on t3 bmt3
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4 bmt4
+ Filter: (c1 <> $3)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t3.c1)
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b1t3
TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 bmt1
+ -> Tid Scan on t4 b1t4
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $3)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
-(46 rows)
+(44 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:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Hash Join
- Hash Cond: (b1t2.c1 = b1t3.c1)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Hash
- -> Merge Join
- Merge Cond: (b1t3.c1 = b1t4.c1)
- -> Sort
- Sort Key: b1t3.c1
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b1t4.c1
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $3)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
TID Cond: (ctid = '(1,1)'::tid)
-> Hash
-> Merge Join
- Merge Cond: (bmt4.c1 = c1.c1)
+ Merge Cond: (bmt4.c1 = (max(b1t1.c1)))
-> Sort
Sort Key: bmt4.c1
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join
+ Hash Cond: (b1t2.c1 = b1t3.c1)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b1t3.c1 = b1t4.c1)
+ -> Sort
+ Sort Key: b1t3.c1
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t4.c1
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Sort
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 <> $3)
-(60 rows)
+(58 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)';
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
- Join Filter: (t2.c1 = t1.c1)
+ Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Tid Scan on r1
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4
-> Nested Loop
Join Filter: (b1t1.c1 = b1t3.c1)
-> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
+ Join Filter: (b1t1.c1 = b1t2.c1)
-> Nested Loop
-> Tid Scan on r1_
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 b1t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 b1t4
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
- Join Filter: (t2.c1 = t1.c1)
+ Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Tid Scan on r2
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
- Join Filter: (t2.c1 = t1.c1)
+ Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Tid Scan on r2
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4
-> Nested Loop
Join Filter: (b1t1.c1 = b1t3.c1)
-> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
+ Join Filter: (b1t1.c1 = b1t2.c1)
-> Nested Loop
-> Tid Scan on r2_
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 b1t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 b1t4
-> Nested Loop
Join Filter: (b2t1.c1 = b2t3.c1)
-> Nested Loop
- Join Filter: (b2t2.c1 = b2t1.c1)
+ Join Filter: (b2t1.c1 = b2t2.c1)
-> Nested Loop
-> Tid Scan on r2_
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 b2t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 b2t4
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:
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
- Join Filter: (t2.c1 = t1.c1)
+ Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Tid Scan on r3
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
- Join Filter: (t2.c1 = t1.c1)
+ Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Tid Scan on r3
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
-> Nested Loop
- Join Filter: (t2.c1 = t1.c1)
+ Join Filter: (t1.c1 = t2.c1)
-> Nested Loop
-> Tid Scan on r3
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4
-> Nested Loop
Join Filter: (b1t1.c1 = b1t3.c1)
-> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
+ Join Filter: (b1t1.c1 = b1t2.c1)
-> Nested Loop
-> Tid Scan on r3_
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 b1t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 b1t4
-> Nested Loop
Join Filter: (b2t1.c1 = b2t3.c1)
-> Nested Loop
- Join Filter: (b2t2.c1 = b2t1.c1)
+ Join Filter: (b2t1.c1 = b2t2.c1)
-> Nested Loop
-> Tid Scan on r3_
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 b2t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 b2t4
-> Nested Loop
Join Filter: (b3t1.c1 = b3t3.c1)
-> Nested Loop
- Join Filter: (b3t2.c1 = b3t1.c1)
+ Join Filter: (b3t1.c1 = b3t2.c1)
-> Nested Loop
-> Tid Scan on r3_
TID Cond: (ctid = '(1,1)'::tid)
Filter: (c1 = 1)
- -> Seq Scan on t2 b3t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t1 b3t1
- TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 b3t3
TID Cond: (ctid = '(1,1)'::tid)
-> Tid Scan on t4 b3t4
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_1.c1)
+ -> Seq Scan on t1 v1t1
+ -> Hash
+ -> 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: pg_hint_plan: 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_1.c1)
+ -> Seq Scan on t1 v1t1
+ -> Hash
+ -> Seq Scan on t1 v1t1_1
+(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_1.c1)
+ -> Seq Scan on t1 r4t1
+ -> Hash
+ -> 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: pg_hint_plan: 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_1.c1)
+ -> Seq Scan on t1 r4t1
+ -> Hash
+ -> Seq Scan on t1 r4t1_1
+(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
----
-- 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
--------------------------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ QUERY PLAN
+------------------------------------
+ Nested Loop
-> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Hash Join
- Hash Cond: (t2.c1 = "*VALUES*".column1)
- -> Seq Scan on t2
- -> Hash
- -> Values Scan on "*VALUES*"
-(10 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;
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)
+ -> Index Scan using t1_i1 on t1
+ 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)
QUERY PLAN
-------------------------------------------------
Nested Loop
- Join Filter: (t1.c1 = t2.c1)
-> Hash Join
Hash Cond: (t1.c1 = "*VALUES*".column1)
-> Seq Scan on t1
-> Hash
-> Values Scan on "*VALUES*"
- -> Seq Scan on t2
+ -> Index Scan using t2_i1 on t2
+ Index Cond: (c1 = t1.c1)
(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;
- QUERY PLAN
--------------------------------------------------------------
+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)
- -> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Hash Join
- Hash Cond: (t2.c1 = "*VALUES*".column1)
- -> Seq Scan on t2
- -> Hash
- -> Values Scan on "*VALUES*"
- -> Values Scan on "*VALUES*"
-(13 rows)
+ -> 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*"
+ -> 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:
-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: ("*VALUES*".column1 = "*VALUES*".column1)
- -> Values Scan on "*VALUES*"
- -> Values Scan on "*VALUES*"
- -> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
+ Join Filter: (t2.c1 = "*VALUES*_1".column1)
+ -> Hash Join
+ Hash Cond: (t2.c1 = "*VALUES*".column1)
-> Seq Scan on t2
+ -> Hash
+ -> Values Scan on "*VALUES*"
+ -> 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:
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
--------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
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
- -> Hash Join
- Hash Cond: (t2.c1 = "*VALUES*".column1)
- -> Seq Scan on t2
- -> Hash
- -> Values Scan on "*VALUES*"
- -> Values Scan on "*VALUES*"
-(13 rows)
+ -> 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*"
+ -> 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 p2 p2_1
+ -> Seq Scan on p2c1 p2_2
+ -> Seq Scan on p2c2 p2_3
+ -> Seq Scan on p2c3 p2_4
+ -> Seq Scan on p2c1c1 p2_5
+ -> Seq Scan on p2c1c2 p2_6
+ -> Seq Scan on p2c2c1 p2_7
+ -> Seq Scan on p2c2c2 p2_8
+ -> Seq Scan on p2c3c1 p2_9
+ -> Seq Scan on p2c3c2 p2_10
-> Hash
-> Append
- -> Seq Scan on p1
- -> Seq Scan on p1c1 p1
- -> Seq Scan on p1c2 p1
- -> Seq Scan on p1c3 p1
+ -> Seq Scan on p1 p1_1
+ -> Seq Scan on p1c1 p1_2
+ -> Seq Scan on p1c2 p1_3
+ -> Seq Scan on p1c3 p1_4
(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 p1 p1_1
+ -> Seq Scan on p1c1 p1_2
+ -> Seq Scan on p1c2 p1_3
+ -> Seq Scan on p1c3 p1_4
-> 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 p2 p2_1
+ -> Seq Scan on p2c1 p2_2
+ -> Seq Scan on p2c2 p2_3
+ -> Seq Scan on p2c3 p2_4
+ -> Seq Scan on p2c1c1 p2_5
+ -> Seq Scan on p2c1c2 p2_6
+ -> Seq Scan on p2c2c1 p2_7
+ -> Seq Scan on p2c2c2 p2_8
+ -> Seq Scan on p2c3c1 p2_9
+ -> Seq Scan on p2c3c2 p2_10
(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 p2 p2_1
+ -> Seq Scan on p2c1 p2_2
+ -> Seq Scan on p2c2 p2_3
+ -> Seq Scan on p2c3 p2_4
+ -> Seq Scan on p2c1c1 p2_5
+ -> Seq Scan on p2c1c2 p2_6
+ -> Seq Scan on p2c2c1 p2_7
+ -> Seq Scan on p2c2c2 p2_8
+ -> Seq Scan on p2c3c1 p2_9
+ -> Seq Scan on p2c3c2 p2_10
-> Hash
-> Append
- -> Seq Scan on p1
- -> Seq Scan on p1c1 p1
- -> Seq Scan on p1c2 p1
- -> Seq Scan on p1c3 p1
+ -> Seq Scan on p1 p1_1
+ -> Seq Scan on p1c1 p1_2
+ -> Seq Scan on p1c2 p1_3
+ -> Seq Scan on p1c3 p1_4
(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)
+ -> Append
+ -> Seq Scan on p2 p2_1
+ -> Seq Scan on p2c1 p2_2
+ -> Seq Scan on p2c2 p2_3
+ -> Seq Scan on p2c3 p2_4
+ -> Seq Scan on p2c1c1 p2_5
+ -> Seq Scan on p2c1c2 p2_6
+ -> Seq Scan on p2c2c1 p2_7
+ -> Seq Scan on p2c2c2 p2_8
+ -> Seq Scan on p2c3c1 p2_9
+ -> Seq Scan on p2c3c2 p2_10
+ -> Hash
+ -> Append
+ -> Seq Scan on pt1_c1 pt1_1
+ -> Seq Scan on pt1_c2 pt1_2
+ -> Seq Scan on pt1_c3 pt1_3
+(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 = p2.c1)
+ -> Sort
+ Sort Key: pt1.c1
+ -> Append
+ -> Seq Scan on pt1_c1 pt1_1
+ -> Seq Scan on pt1_c2 pt1_2
+ -> Seq Scan on pt1_c3 pt1_3
+ -> Sort
+ Sort Key: p2.c1
+ -> Append
+ -> Seq Scan on p2 p2_1
+ -> Seq Scan on p2c1 p2_2
+ -> Seq Scan on p2c2 p2_3
+ -> Seq Scan on p2c3 p2_4
+ -> Seq Scan on p2c1c1 p2_5
+ -> Seq Scan on p2c1c2 p2_6
+ -> Seq Scan on p2c2c1 p2_7
+ -> Seq Scan on p2c2c2 p2_8
+ -> Seq Scan on p2c3c1 p2_9
+ -> Seq Scan on p2c3c2 p2_10
+(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)
+ -> Append
+ -> Seq Scan on p2 p2_1
+ -> Seq Scan on p2c1 p2_2
+ -> Seq Scan on p2c2 p2_3
+ -> Seq Scan on p2c3 p2_4
+ -> Seq Scan on p2c1c1 p2_5
+ -> Seq Scan on p2c1c2 p2_6
+ -> Seq Scan on p2c2c1 p2_7
+ -> Seq Scan on p2c2c2 p2_8
+ -> Seq Scan on p2c3c1 p2_9
+ -> Seq Scan on p2c3c2 p2_10
+ -> Hash
+ -> Append
+ -> Seq Scan on pt1_c1 pt1_1
+ -> Seq Scan on pt1_c2 pt1_2
+ -> Seq Scan on pt1_c3 pt1_3
+(18 rows)
+
+----
+---- No. J-3-3 conflict join method hint
+----
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+(6 rows)
+
+-- 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: 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:
+NestLoop(t1 t2)
+not used hint:
+duplication hint:
+HashJoin(t1 t2)
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on t2
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(4 rows)
+
+-- 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: pg_hint_plan: hint syntax error at or near "MergeJoin(t1 t2)HashJoin(t1 t2)NestLoop(t1 t2)"
+DETAIL: Conflict join method hint.
+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:
+NestLoop(t1 t2)
+not used hint:
+duplication hint:
+MergeJoin(t1 t2)
+HashJoin(t1 t2)
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on t2
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(4 rows)
+
+-- 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: 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:
+NestLoop(t1 t2)
+not used hint:
+duplication hint:
+HashJoin(t1 t2)
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on t2
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(4 rows)
+
+-- 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: pg_hint_plan: hint syntax error at or near "MergeJoin(t2 t1)HashJoin(t1 t2)NestLoop(t2 t1)"
+DETAIL: Conflict join method hint.
+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:
+NestLoop(t1 t2)
+not used hint:
+duplication hint:
+MergeJoin(t1 t2)
+HashJoin(t1 t2)
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on t2
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(4 rows)
+
+----
---- No. J-3-4 hint state output
----
-- No. J-3-4-1
-- 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: pg_hint_plan: 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 ")"
-DETAIL: Specified relation more than two.
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: NestLoop hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
not 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)
+
+\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:
+used hint:
+NestLoop(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+\o
+\! sql/maskout.sh results/ut-J.tmpout
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Hash Full Join (cost={inf}..{inf} rows=1000 width=xxx)
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1 (cost=xxx..xxx rows=1000 width=xxx)
+ -> Hash (cost=xxx..xxx rows=100 width=xxx)
+ -> Seq Scan on t2 (cost=xxx..xxx rows=100 width=xxx)
+
+\! rm results/ut-J.tmpout
OSDN Git Service
