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_pkey 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
--------------------------------
(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
------------------------------------
-> Seq Scan on t2
(5 rows)
-\q
+----
+---- No. J-2-1 some complexity query blocks
+----
+-- No. J-2-1-1
+EXPLAIN (COSTS false)
+SELECT max(bmt1.c1), (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+;
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> 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)
+ -> 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 = bmt4.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
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(47 rows)
+
+/*+
+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)
+*/
+EXPLAIN (COSTS false)
+SELECT max(bmt1.c1), (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b1t2 b1t3)
+MergeJoin(b2t3 b2t4)
+MergeJoin(bmt1 bmt2)
+HashJoin(b1t2 b1t3 b1t4)
+HashJoin(b2t1 b2t3 b2t4)
+HashJoin(bmt1 bmt2 bmt3)
+NestLoop(b1t1 b1t2 b1t3 b1t4)
+NestLoop(b2t1 b2t2 b2t3 b2t4)
+NestLoop(bmt1 bmt2 bmt3 bmt4)
+Leading(bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> 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)
+ InitPlan 2 (returns $1)
+ -> 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)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ -> Hash Join
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Sort
+ Sort Key: bmt1.c1
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(62 rows)
+
+-- No. J-2-1-2
+EXPLAIN (COSTS false)
+SELECT max(bmt1.c1), (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+), (
+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)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+;
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> 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)
+ -> 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)
+ InitPlan 3 (returns $2)
+ -> 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 = bmt4.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
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(63 rows)
+
+/*+
+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)
+MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
+*/
+EXPLAIN (COSTS false)
+SELECT max(bmt1.c1), (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+), (
+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)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b1t2 b1t3)
+MergeJoin(b2t3 b2t4)
+MergeJoin(b3t1 b3t4)
+MergeJoin(bmt1 bmt2)
+HashJoin(b1t2 b1t3 b1t4)
+HashJoin(b2t1 b2t3 b2t4)
+HashJoin(b3t1 b3t2 b3t4)
+HashJoin(bmt1 bmt2 bmt3)
+NestLoop(b1t1 b1t2 b1t3 b1t4)
+NestLoop(b2t1 b2t2 b2t3 b2t4)
+NestLoop(b3t1 b3t2 b3t3 b3t4)
+NestLoop(bmt1 bmt2 bmt3 bmt4)
+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:
+
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> 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)
+ InitPlan 2 (returns $1)
+ -> 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)
+ InitPlan 3 (returns $2)
+ -> 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
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Sort
+ Sort Key: bmt1.c1
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(83 rows)
+
+-- No. J-2-1-3
+EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = sbmt3.c1 AND sbmt3.ctid = '(1,1)' AND bmt1.c1 = sbmt4.c1 AND sbmt4.ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.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
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(15 rows)
+
+/*+
+Leading(bmt4 bmt3 bmt2 bmt1)
+MergeJoin(bmt4 bmt3)HashJoin(bmt4 bmt3 bmt2)NestLoop(bmt1 bmt2 bmt3 bmt4)
+*/
+EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = sbmt3.c1 AND sbmt3.ctid = '(1,1)' AND bmt1.c1 = sbmt4.c1 AND sbmt4.ctid = '(1,1)';
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(bmt3 bmt4)
+HashJoin(bmt2 bmt3 bmt4)
+NestLoop(bmt1 bmt2 bmt3 bmt4)
+Leading(bmt4 bmt3 bmt2 bmt1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (bmt2.c1 = bmt1.c1)
+ -> Hash Join
+ Hash Cond: (bmt2.c1 = bmt3.c1)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (bmt3.c1 = bmt4.c1)
+ -> Sort
+ Sort Key: bmt3.c1
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: bmt4.c1
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+(20 rows)
+
+-- No. J-2-1-4
+EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = sbmt2.c1 AND sbmt2.ctid = '(1,1)' AND bmt1.c1 = sbmt3.c1 AND sbmt3.ctid = '(1,1)' AND bmt1.c1 = sbmt4.c1 AND sbmt4.ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.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
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(15 rows)
+
+/*+
+Leading(bmt4 bmt3 bmt2 bmt1)
+MergeJoin(bmt4 bmt3)HashJoin(bmt4 bmt3 bmt2)NestLoop(bmt1 bmt2 bmt3 bmt4)
+*/
+EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, (SELECT ctid, * FROM s1.t2 bmt2) sbmt2, (SELECT ctid, * FROM s1.t3 bmt3) sbmt3, (SELECT ctid, * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = sbmt2.c1 AND sbmt2.ctid = '(1,1)' AND bmt1.c1 = sbmt3.c1 AND sbmt3.ctid = '(1,1)' AND bmt1.c1 = sbmt4.c1 AND sbmt4.ctid = '(1,1)';
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(bmt3 bmt4)
+HashJoin(bmt2 bmt3 bmt4)
+NestLoop(bmt1 bmt2 bmt3 bmt4)
+Leading(bmt4 bmt3 bmt2 bmt1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (bmt2.c1 = bmt1.c1)
+ -> Hash Join
+ Hash Cond: (bmt2.c1 = bmt3.c1)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (bmt3.c1 = bmt4.c1)
+ -> Sort
+ Sort Key: bmt3.c1
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: bmt4.c1
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+(20 rows)
+
+-- No. J-2-1-5
+EXPLAIN (COSTS false)
+SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+ AND bmt1.c1 <> (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+) AND bmt1.c1 <> (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+;
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> 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)
+ -> 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 = bmt4.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)
+ Filter: ((c1 <> $0) AND (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)
+(48 rows)
+
+/*+
+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)
+*/
+EXPLAIN (COSTS false)
+SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+ AND bmt1.c1 <> (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+) AND bmt1.c1 <> (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b1t2 b1t3)
+MergeJoin(b2t3 b2t4)
+MergeJoin(bmt1 bmt2)
+HashJoin(b1t2 b1t3 b1t4)
+HashJoin(b2t1 b2t3 b2t4)
+HashJoin(bmt1 bmt2 bmt3)
+NestLoop(b1t1 b1t2 b1t3 b1t4)
+NestLoop(b2t1 b2t2 b2t3 b2t4)
+NestLoop(bmt1 bmt2 bmt3 bmt4)
+Leading(bmt1 bmt2 bmt3 bmt4)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> 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)
+ InitPlan 2 (returns $1)
+ -> 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)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ -> Hash Join
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Sort
+ Sort Key: bmt1.c1
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 <> $0) AND (c1 <> $1))
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(63 rows)
+
+-- No. J-2-1-6
+EXPLAIN (COSTS false)
+SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+ AND bmt1.c1 <> (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+) AND bmt1.c1 <> (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+) AND bmt1.c1 <> (
+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
+------------------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> 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)
+ -> 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)
+ InitPlan 3 (returns $2)
+ -> 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 = bmt4.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)
+ Filter: ((c1 <> $0) AND (c1 <> $1) AND (c1 <> $2))
+ -> 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)
+(64 rows)
+
+/*+
+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)
+MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
+*/
+EXPLAIN (COSTS false)
+SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+ AND bmt1.c1 <> (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+) AND bmt1.c1 <> (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+) AND bmt1.c1 <> (
+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)'
+)
+;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b1t2 b1t3)
+MergeJoin(b2t3 b2t4)
+MergeJoin(b3t1 b3t4)
+MergeJoin(bmt1 bmt2)
+HashJoin(b1t2 b1t3 b1t4)
+HashJoin(b2t1 b2t3 b2t4)
+HashJoin(b3t1 b3t2 b3t4)
+HashJoin(bmt1 bmt2 bmt3)
+NestLoop(b1t1 b1t2 b1t3 b1t4)
+NestLoop(b2t1 b2t2 b2t3 b2t4)
+NestLoop(b3t1 b3t2 b3t3 b3t4)
+NestLoop(bmt1 bmt2 bmt3 bmt4)
+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:
+
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> 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)
+ InitPlan 2 (returns $1)
+ -> 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)
+ InitPlan 3 (returns $2)
+ -> 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
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Sort
+ Sort Key: bmt1.c1
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 <> $0) AND (c1 <> $1) AND (c1 <> $2))
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(84 rows)
+
+-- No. J-2-1-7
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+, c2 (c1) AS (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
+, c1, c2
+ WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 = c2.c1
+;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = (max(b2t1.c1)))
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.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
+ 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 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)
+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)
+*/
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+, c2 (c1) AS (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
+, c1, c2
+ WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 = c2.c1
+;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b1t2 b1t3)
+MergeJoin(b2t3 b2t4)
+MergeJoin(c1 c2)
+HashJoin(b1t2 b1t3 b1t4)
+HashJoin(b2t1 b2t3 b2t4)
+HashJoin(bmt1 c1 c2)
+NestLoop(b1t1 b1t2 b1t3 b1t4)
+NestLoop(b2t1 b2t2 b2t3 b2t4)
+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)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Hash Join
+ Hash Cond: (bmt4.c1 = bmt1.c1)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (bmt1.c1 = bmt3.c1)
+ -> Sort
+ Sort Key: bmt1.c1
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = (max(b1t1.c1)))
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
+ -> Sort
+ 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: (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)
+(69 rows)
+
+-- No. J-2-1-8
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+, c2 (c1) AS (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+, c3 (c1) AS (
+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)'
+)
+SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
+, c1, c2, c3
+ WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 = c2.c1
+AND bmt1.c1 = c3.c1
+;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = (max(b3t1.c1)))
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt3.c1)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt2.c1)
+ -> Nested Loop
+ 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)
+ -> 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)
+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(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4)
+*/
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+, c2 (c1) AS (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+, c3 (c1) AS (
+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)'
+)
+SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
+, c1, c2, c3
+ WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)'
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 = c2.c1
+AND bmt1.c1 = c3.c1
+;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b1t2 b1t3)
+MergeJoin(b2t3 b2t4)
+MergeJoin(b3t1 b3t4)
+MergeJoin(c2 c3)
+HashJoin(b1t2 b1t3 b1t4)
+HashJoin(b2t1 b2t3 b2t4)
+HashJoin(b3t1 b3t2 b3t4)
+HashJoin(c1 c2 c3)
+NestLoop(b1t1 b1t2 b1t3 b1t4)
+NestLoop(b2t1 b2t2 b2t3 b2t4)
+NestLoop(b3t1 b3t2 b3t3 b3t4)
+NestLoop(bmt1 c1 c2 c3)
+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)
+Leading(b1t2 b1t3 b1t4 b1t1)
+Leading(b2t3 b2t4 b2t1 b2t2)
+Leading(b3t4 b3t1 b3t2 b3t3)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.c1)
+ -> Hash Join
+ Hash Cond: (bmt3.c1 = bmt1.c1)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (bmt1.c1 = bmt2.c1)
+ -> Sort
+ Sort Key: bmt1.c1
+ -> Nested Loop
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
+ -> Hash Join
+ Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1)))
+ -> Merge Join
+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
+ -> Sort
+ 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: (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
+ -> 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
+ Sort Key: bmt2.c1
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(91 rows)
+
+----
+---- No. J-2-2 the number of the tables per quiry block
+----
+-- No. J-2-2-1
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = 1
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = 1
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1
+)
+;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ 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)
+ -> Result
+ InitPlan 3 (returns $2)
+ -> 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 <> $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)
+HashJoin(bmt1 c1)
+HashJoin(b1t1 c1)
+HashJoin(b2t1 c1)
+HashJoin(b3t1 c1)
+*/
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = 1
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = 1
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1
+)
+;
+LOG: pg_hint_plan:
+used hint:
+HashJoin(bmt1 c1)
+Leading(c1 bmt1)
+not used hint:
+HashJoin(b1t1 c1)
+HashJoin(b2t1 c1)
+HashJoin(b3t1 c1)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ 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)
+ -> Result
+ InitPlan 3 (returns $2)
+ -> 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 <> $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)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)'
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+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
+-----------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Aggregate
+ -> 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)
+ InitPlan 2 (returns $1)
+ -> Aggregate
+ -> 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)
+ -> Nested Loop
+ 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 <> $1)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> 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 bmt1)
+Leading(b1t2 b1t1)
+Leading(b2t2 b2t1)
+Leading(b3t2 b3t1)
+MergeJoin(c1 bmt2)
+HashJoin(c1 bmt1 bmt2)
+MergeJoin(b1t1 b1t2)
+MergeJoin(b2t1 b2t2)
+MergeJoin(b3t1 b3t2)
+*/
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)'
+AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+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)'
+)
+;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b1t1 b1t2)
+MergeJoin(b2t1 b2t2)
+MergeJoin(b3t1 b3t2)
+MergeJoin(bmt2 c1)
+HashJoin(bmt1 bmt2 c1)
+Leading(c1 bmt2 bmt1)
+Leading(b1t2 b1t1)
+Leading(b2t2 b2t1)
+Leading(b3t2 b3t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Aggregate
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Sort
+ Sort Key: b2t1.c1
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ InitPlan 2 (returns $1)
+ -> Aggregate
+ -> Merge Join
+ Merge Cond: (b3t1.c1 = b3t2.c1)
+ -> Sort
+ Sort Key: b3t1.c1
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b3t2.c1
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Hash Join
+ Hash Cond: (bmt1.c1 = bmt2.c1)
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 <> $1)
+ -> Hash
+ -> Merge Join
+ 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: (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)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+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
+-----------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> 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)
+ InitPlan 2 (returns $1)
+ -> 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 = (max(b1t1.c1)))
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.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)
+ 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 t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(65 rows)
+
+/*+
+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(c1 bmt4 bmt3 bmt2 bmt1)
+HashJoin(b1t4 b1t3)
+NestLoop(b1t4 b1t3 b1t2)
+MergeJoin(b1t4 b1t3 b1t2 b1t1)
+HashJoin(b2t4 b2t3)
+NestLoop(b2t4 b2t3 b2t2)
+MergeJoin(b2t4 b2t3 b2t2 b2t1)
+HashJoin(b3t4 b3t3)
+NestLoop(b3t4 b3t3 b3t2)
+MergeJoin(b3t4 b3t3 b3t2 b3t1)
+*/
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+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)'
+)
+;
+LOG: pg_hint_plan:
+used hint:
+HashJoin(b1t3 b1t4)
+HashJoin(b2t3 b2t4)
+HashJoin(b3t3 b3t4)
+MergeJoin(bmt4 c1)
+NestLoop(b1t2 b1t3 b1t4)
+NestLoop(b2t2 b2t3 b2t4)
+NestLoop(b3t2 b3t3 b3t4)
+HashJoin(bmt3 bmt4 c1)
+MergeJoin(b1t1 b1t2 b1t3 b1t4)
+MergeJoin(b2t1 b2t2 b2t3 b2t4)
+MergeJoin(b3t1 b3t2 b3t3 b3t4)
+NestLoop(bmt2 bmt3 bmt4 c1)
+MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
+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
+-----------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Aggregate
+ -> Merge Join
+ Merge Cond: (b2t2.c1 = b2t1.c1)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Nested Loop
+ Join Filter: (b2t3.c1 = b2t2.c1)
+ -> Hash Join
+ Hash Cond: (b2t3.c1 = b2t4.c1)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b2t1.c1
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ InitPlan 2 (returns $1)
+ -> Aggregate
+ -> Merge Join
+ Merge Cond: (b3t2.c1 = b3t1.c1)
+ -> Sort
+ Sort Key: b3t2.c1
+ -> Nested Loop
+ Join Filter: (b3t3.c1 = b3t2.c1)
+ -> Hash Join
+ Hash Cond: (b3t3.c1 = b3t4.c1)
+ -> Tid Scan on t3 b3t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Tid Scan on t4 b3t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b3t1.c1
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Merge Join
+ Merge Cond: (bmt2.c1 = bmt1.c1)
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Nested Loop
+ Join Filter: (bmt3.c1 = bmt2.c1)
+ -> Hash Join
+ Hash Cond: (bmt3.c1 = bmt4.c1)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ 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: (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 <> $1)
+(89 rows)
+
+-- No. J-2-2-4
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
+)
+;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ 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 3 (returns $3)
+ -> Aggregate
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
+ -> Nested Loop
+ Join Filter: (bmt1.c1 = bmt4.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)
+ 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 t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+(44 rows)
+
+/*+
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+MergeJoin(c1 bmt4)
+HashJoin(c1 bmt4 bmt3)
+NestLoop(c1 bmt4 bmt3 bmt2)
+MergeJoin(c1 bmt4 bmt3 bmt2 bmt1)
+MergeJoin(b1t4 b1t3)
+HashJoin(b1t4 b1t3 b1t2)
+NestLoop(b1t4 b1t3 b1t2 b1t1)
+*/
+EXPLAIN (COSTS false)
+WITH c1 (c1) AS (
+SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
+)
+SELECT max(bmt1.c1), (
+SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
+)
+ FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
+AND bmt1.c1 <> (
+SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
+)
+;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b1t3 b1t4)
+MergeJoin(bmt4 c1)
+HashJoin(b1t2 b1t3 b1t4)
+HashJoin(bmt3 bmt4 c1)
+NestLoop(b1t1 b1t2 b1t3 b1t4)
+NestLoop(bmt2 bmt3 bmt4 c1)
+MergeJoin(bmt1 bmt2 bmt3 bmt4 c1)
+Leading(c1 bmt4 bmt3 bmt2 bmt1)
+Leading(b1t4 b1t3 b1t2 b1t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ InitPlan 2 (returns $1)
+ -> Result
+ 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 3 (returns $3)
+ -> Aggregate
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Merge Join
+ Merge Cond: (bmt2.c1 = bmt1.c1)
+ -> Sort
+ Sort Key: bmt2.c1
+ -> Nested Loop
+ Join Filter: (bmt3.c1 = bmt2.c1)
+ -> Hash Join
+ Hash Cond: (bmt3.c1 = bmt4.c1)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ 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: (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
+ Sort Key: bmt1.c1
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 <> $3)
+(58 rows)
+
+----
+---- 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)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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
+ TID Cond: (ctid = '(1,1)'::tid)
+(19 rows)
+
+/*+
+Leading(t4 t3 t2 t1 r1)
+MergeJoin(t4 t3 t2 t1 r1)
+HashJoin(t4 t3 t2 t1)
+NestLoop(t4 t3 t2)
+MergeJoin(t4 t3)
+*/
+EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t3 t4)
+NestLoop(t2 t3 t4)
+HashJoin(t1 t2 t3 t4)
+MergeJoin(r1 t1 t2 t3 t4)
+Leading(t4 t3 t2 t1 r1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Nested Loop
+ Join Filter: (t3.c1 = t2.c1)
+ -> Merge Join
+ Merge Cond: (t3.c1 = t4.c1)
+ -> Sort
+ Sort Key: t3.c1
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: t4.c1
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(24 rows)
+
+EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ 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)
+ -> Nested Loop
+ -> Tid Scan on r1_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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)
+(19 rows)
+
+/*+
+Leading(b1t4 b1t3 b1t2 b1t1 r1_)
+MergeJoin(b1t4 b1t3 b1t2 b1t1 r1_)
+HashJoin(b1t4 b1t3 b1t2 b1t1)
+NestLoop(b1t4 b1t3 b1t2)
+MergeJoin(b1t4 b1t3)
+*/
+EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b1t3 b1t4)
+NestLoop(b1t2 b1t3 b1t4)
+HashJoin(b1t1 b1t2 b1t3 b1t4)
+MergeJoin(b1t1 b1t2 b1t3 b1t4 r1_)
+Leading(b1t4 b1t3 b1t2 b1t1 r1_)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (b1t1.c1 = b1t2.c1)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Nested Loop
+ Join Filter: (b1t3.c1 = b1t2.c1)
+ -> 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)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r1_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(24 rows)
+
+-- No. J-2-3-2
+EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r2
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r2
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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
+ TID Cond: (ctid = '(1,1)'::tid)
+(39 rows)
+
+/*+
+Leading(t4 t3 t2 t1 r2)
+MergeJoin(t4 t3 t2 t1 r2)
+HashJoin(t4 t3 t2 t1)
+NestLoop(t4 t3 t2)
+MergeJoin(t4 t3)
+*/
+EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t3 t4)
+NestLoop(t2 t3 t4)
+HashJoin(t1 t2 t3 t4)
+MergeJoin(r2 t1 t2 t3 t4)
+Leading(t4 t3 t2 t1 r2)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t3 t4)
+NestLoop(t2 t3 t4)
+HashJoin(t1 t2 t3 t4)
+MergeJoin(r2 t1 t2 t3 t4)
+Leading(t4 t3 t2 t1 r2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Nested Loop
+ Join Filter: (t3.c1 = t2.c1)
+ -> Merge Join
+ Merge Cond: (t3.c1 = t4.c1)
+ -> Sort
+ Sort Key: t3.c1
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: t4.c1
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r2
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+
+ Aggregate
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Nested Loop
+ Join Filter: (t3.c1 = t2.c1)
+ -> Merge Join
+ Merge Cond: (t3.c1 = t4.c1)
+ -> Sort
+ Sort Key: t3.c1
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: t4.c1
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r2
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(49 rows)
+
+EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ 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)
+ -> Nested Loop
+ -> Tid Scan on r2_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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)
+ -> Nested Loop
+ -> Tid Scan on r2_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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 r2_)
+Leading(b2t1 b2t2 b2t3 b2t4 r2_)
+MergeJoin(b1t1 b1t2)
+HashJoin(b1t1 b1t2 b1t3)
+NestLoop(b1t1 b1t2 b1t3 b1t4)
+MergeJoin(b1t1 b1t2 b1t3 b1t4 r2_)
+MergeJoin(b2t1 b2t2)
+HashJoin(b2t1 b2t2 b2t3)
+NestLoop(b2t1 b2t2 b2t3 b2t4)
+MergeJoin(b2t1 b2t2 b2t3 b2t4 r2_)
+*/
+EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: pg_hint_plan:
+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_)
+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:
+
+LOG: pg_hint_plan:
+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_)
+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:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Hash Join
+ Hash Cond: (b1t3.c1 = b1t1.c1)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> 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)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r2_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+
+ Aggregate
+ -> Nested Loop
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Hash Join
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Sort
+ Sort Key: b2t1.c1
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r2_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(49 rows)
+
+-- No. J-2-3-3
+EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r3
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r3
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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
+ TID Cond: (ctid = '(1,1)'::tid)
+
+ Aggregate
+ -> Nested Loop
+ Join Filter: (t1.c1 = t4.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t3.c1)
+ -> Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Nested Loop
+ -> Tid Scan on r3
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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
+ TID Cond: (ctid = '(1,1)'::tid)
+(59 rows)
+
+/*+
+Leading(t4 t3 t2 t1 r3)
+MergeJoin(t4 t3 t2 t1 r3)
+HashJoin(t4 t3 t2 t1)
+NestLoop(t4 t3 t2)
+MergeJoin(t4 t3)
+*/
+EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t3 t4)
+NestLoop(t2 t3 t4)
+HashJoin(t1 t2 t3 t4)
+MergeJoin(r3 t1 t2 t3 t4)
+Leading(t4 t3 t2 t1 r3)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t3 t4)
+NestLoop(t2 t3 t4)
+HashJoin(t1 t2 t3 t4)
+MergeJoin(r3 t1 t2 t3 t4)
+Leading(t4 t3 t2 t1 r3)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t3 t4)
+NestLoop(t2 t3 t4)
+HashJoin(t1 t2 t3 t4)
+MergeJoin(r3 t1 t2 t3 t4)
+Leading(t4 t3 t2 t1 r3)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Nested Loop
+ Join Filter: (t3.c1 = t2.c1)
+ -> Merge Join
+ Merge Cond: (t3.c1 = t4.c1)
+ -> Sort
+ Sort Key: t3.c1
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: t4.c1
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r3
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+
+ Aggregate
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Nested Loop
+ Join Filter: (t3.c1 = t2.c1)
+ -> Merge Join
+ Merge Cond: (t3.c1 = t4.c1)
+ -> Sort
+ Sort Key: t3.c1
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: t4.c1
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r3
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+
+ Aggregate
+ -> Nested Loop
+ -> Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Nested Loop
+ Join Filter: (t3.c1 = t2.c1)
+ -> Merge Join
+ Merge Cond: (t3.c1 = t4.c1)
+ -> Sort
+ Sort Key: t3.c1
+ -> Tid Scan on t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: t4.c1
+ -> Tid Scan on t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r3
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+(74 rows)
+
+EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+ QUERY PLAN
+-----------------------------------------------------------------
+ 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)
+ -> Nested Loop
+ -> Tid Scan on r3_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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)
+ -> Nested Loop
+ -> Tid Scan on r3_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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)
+
+ 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)
+ -> Nested Loop
+ -> Tid Scan on r3_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+ -> 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 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)
+MergeJoin(b1t1 b1t2 b1t3 b1t4 r3_)
+MergeJoin(b2t1 b2t2)
+HashJoin(b2t1 b2t2 b2t3)
+NestLoop(b2t1 b2t2 b2t3 b2t4)
+MergeJoin(b2t1 b2t2 b2t3 b2t4 r3_)
+MergeJoin(b3t1 b3t2)
+HashJoin(b3t1 b3t2 b3t3)
+NestLoop(b3t1 b3t2 b3t3 b3t4)
+MergeJoin(b3t1 b3t2 b3t3 b3t4 r3_)
+*/
+EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b1t1 b1t2)
+HashJoin(b1t1 b1t2 b1t3)
+NestLoop(b1t1 b1t2 b1t3 b1t4)
+MergeJoin(b1t1 b1t2 b1t3 b1t4 r3_)
+Leading(b1t1 b1t2 b1t3 b1t4 r3_)
+not used hint:
+MergeJoin(b2t1 b2t2)
+MergeJoin(b3t1 b3t2)
+HashJoin(b2t1 b2t2 b2t3)
+HashJoin(b3t1 b3t2 b3t3)
+NestLoop(b2t1 b2t2 b2t3 b2t4)
+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:
+
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b2t1 b2t2)
+HashJoin(b2t1 b2t2 b2t3)
+NestLoop(b2t1 b2t2 b2t3 b2t4)
+MergeJoin(b2t1 b2t2 b2t3 b2t4 r3_)
+Leading(b2t1 b2t2 b2t3 b2t4 r3_)
+not used hint:
+MergeJoin(b1t1 b1t2)
+MergeJoin(b3t1 b3t2)
+HashJoin(b1t1 b1t2 b1t3)
+HashJoin(b3t1 b3t2 b3t3)
+NestLoop(b1t1 b1t2 b1t3 b1t4)
+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:
+
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(b3t1 b3t2)
+HashJoin(b3t1 b3t2 b3t3)
+NestLoop(b3t1 b3t2 b3t3 b3t4)
+MergeJoin(b3t1 b3t2 b3t3 b3t4 r3_)
+Leading(b3t1 b3t2 b3t3 b3t4 r3_)
+not used hint:
+MergeJoin(b1t1 b1t2)
+MergeJoin(b2t1 b2t2)
+HashJoin(b1t1 b1t2 b1t3)
+HashJoin(b2t1 b2t2 b2t3)
+NestLoop(b1t1 b1t2 b1t3 b1t4)
+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:
+
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Nested Loop
+ Join Filter: (b1t1.c1 = b1t4.c1)
+ -> Hash Join
+ Hash Cond: (b1t3.c1 = b1t1.c1)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> 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)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r3_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+
+ Aggregate
+ -> Nested Loop
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t4.c1)
+ -> Hash Join
+ Hash Cond: (b2t3.c1 = b2t1.c1)
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b2t1.c1 = b2t2.c1)
+ -> Sort
+ Sort Key: b2t1.c1
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b2t2.c1
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r3_
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: (c1 = 1)
+
+ Aggregate
+ -> Nested Loop
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t4.c1)
+ -> Hash Join
+ Hash Cond: (b3t3.c1 = b3t1.c1)
+ -> Tid Scan on t3 b3t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b3t1.c1 = b3t2.c1)
+ -> Sort
+ Sort Key: b3t1.c1
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b3t2.c1
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 b3t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on r3_
+ TID Cond: (ctid = '(1,1)'::tid)
+ 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
+------------------------------------
+ Nested Loop
+ -> Index Scan using t1_i1 on t1
+ 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:
+not used hint:
+HashJoin(t1 t3)
+NestLoop(t1 t2 t3)
+Leading(t3 t1 t2)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> 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'), (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)
+NestLoop(*VALUES* t1 t2)
+Leading(*VALUES* t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------
+ Nested Loop
+ -> Hash Join
+ Hash Cond: (t1.c1 = "*VALUES*".column1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Values Scan on "*VALUES*"
+ -> 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'), (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
+ -> 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'), (2,2,2,'2')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+NestLoop(t3 t4)
+HashJoin(t2 t3 t4)
+MergeJoin(t1 t2 t3 t4)
+Leading(t4 t3 t2 t1)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-------------------------------------------------------
+ Nested Loop
+ -> 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(*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'), (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: pg_hint_plan: hint syntax error at or near "MergeJoin(*VALUES* t3 t2 t1)"
+DETAIL: Relation name "*VALUES*" is ambiguous.
+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)
+duplication hint:
+error hint:
+HashJoin(*VALUES* t2 t3)
+MergeJoin(*VALUES* t1 t2 t3)
+Leading(*VALUES* t3 t2 t1)
+
+ QUERY PLAN
+-------------------------------------------------------
+ Nested Loop
+ -> 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-1-1~6
+SET enable_nestloop TO on;
+SET enable_mergejoin TO off;
+SET enable_hashjoin TO off;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on t2
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(4 rows)
+
+/*+NestLoop(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NestLoop(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on t2
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(4 rows)
+
+/*+HashJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+HashJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+/*+MergeJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ 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)
+
+SET enable_mergejoin TO on;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';;
+ QUERY PLAN
+-----------------------------------------
+ Nested Loop
+ Join Filter: (t1.c1 = t2.c1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2
+(5 rows)
+
+/*+NoNestLoop(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';;
+LOG: pg_hint_plan:
+used hint:
+NoNestLoop(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Sort
+ Sort Key: t1.c1
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+(9 rows)
+
+SET enable_mergejoin TO off;
+/*+NoHashJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NoHashJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on t2
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(4 rows)
+
+/*+NoMergeJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NoMergeJoin(t1 t2)
+not used hint:
+duplication hint:
+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-1-7~12
+SET enable_nestloop TO off;
+SET enable_mergejoin TO off;
+SET enable_hashjoin TO on;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+/*+NestLoop(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NestLoop(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on t2
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(4 rows)
+
+/*+HashJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+HashJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+/*+MergeJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ 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)
+
+/*+NoNestLoop(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NoNestLoop(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+/*+NoHashJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NoHashJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on t2
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(4 rows)
+
+/*+NoMergeJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NoMergeJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+-- No. J-3-1-13~18
+SET enable_nestloop TO off;
+SET enable_mergejoin TO on;
+SET enable_hashjoin TO off;
+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)
+
+/*+NestLoop(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NestLoop(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on t2
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(4 rows)
+
+/*+HashJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+HashJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+/*+MergeJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ 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)
+
+/*+NoNestLoop(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NoNestLoop(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ 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)
+
+/*+NoHashJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NoHashJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ 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)
+
+/*+NoMergeJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+NoMergeJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Nested Loop
+ -> Seq Scan on t2
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(4 rows)
+
+SET enable_nestloop TO on;
+SET enable_mergejoin TO on;
+SET enable_hashjoin TO on;
+----
+---- No. J-3-2 join inherit tables
+----
+EXPLAIN (COSTS false) SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
+ QUERY PLAN
+-----------------------------------------
+ Hash Join
+ Hash Cond: (p2.c1 = p1.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 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
+/*+MergeJoin(p1 p2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(p1 p2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------------
+ Merge Join
+ Merge Cond: (p1.c1 = p2.c1)
+ -> Sort
+ Sort Key: p1.c1
+ -> Append
+ -> 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: 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
+(22 rows)
+
+-- No. J-3-2-2
+/*+MergeJoin(p1c1 p2c1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+MergeJoin(p1c1 p2c1)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+-----------------------------------------
+ Hash Join
+ Hash Cond: (p2.c1 = p1.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 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
+/*+NestLoop(t1 t2)*/
+SELECT * FROM s1.t1, s1.t2 WHERE false;
+LOG: pg_hint_plan:
+used hint:
+NestLoop(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4
+----+----+----+----+----+----+----+----
+(0 rows)
+
+-- No. J-3-4-2
+/*+HashJoin(t1 t2)*/
+SELECT * FROM s1.t1, s1.t2 WHERE false;
+LOG: pg_hint_plan:
+used hint:
+HashJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4
+----+----+----+----+----+----+----+----
+(0 rows)
+
+-- No. J-3-4-3
+/*+MergeJoin(t1 t2)*/
+SELECT * FROM s1.t1, s1.t2 WHERE false;
+LOG: pg_hint_plan:
+used hint:
+MergeJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4
+----+----+----+----+----+----+----+----
+(0 rows)
+
+-- No. J-3-4-4
+/*+NoNestLoop(t1 t2)*/
+SELECT * FROM s1.t1, s1.t2 WHERE false;
+LOG: pg_hint_plan:
+used hint:
+NoNestLoop(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4
+----+----+----+----+----+----+----+----
+(0 rows)
+
+-- No. J-3-4-5
+/*+NoHashJoin(t1 t2)*/
+SELECT * FROM s1.t1, s1.t2 WHERE false;
+LOG: pg_hint_plan:
+used hint:
+NoHashJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4
+----+----+----+----+----+----+----+----
+(0 rows)
+
+-- No. J-3-4-6
+/*+NoMergeJoin(t1 t2)*/
+SELECT * FROM s1.t1, s1.t2 WHERE false;
+LOG: pg_hint_plan:
+used hint:
+NoMergeJoin(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4
+----+----+----+----+----+----+----+----
+(0 rows)
+
+-- No. J-3-4-7
+/*+NestLoop()*/
+SELECT * FROM s1.t1 WHERE false;
+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: 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(t1)
+
+ c1 | c2 | c3 | c4
+----+----+----+----
+(0 rows)
+
+-- No. J-3-4-9
+/*+NestLoop(t1 t2)*/
+SELECT * FROM s1.t1, s1.t2 WHERE false;
+LOG: pg_hint_plan:
+used hint:
+NestLoop(t1 t2)
+not used hint:
+duplication hint:
+error hint:
+
+ c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4
+----+----+----+----+----+----+----+----
+(0 rows)
+
+-- No. J-3-4-10
+/*+NestLoop(t1 t2 t3)*/
+SELECT * FROM s1.t1, s1.t2, s1.t3 WHERE false;
+LOG: pg_hint_plan:
+used hint:
+NestLoop(t1 t2 t3)
+not used hint:
+duplication hint:
+error hint:
+
+ c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4
+----+----+----+----+----+----+----+----+----+----+----+----
+(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
