Hash Join
Hash Cond: (t1.c1 = t2.c1)
-> Append
- -> Seq Scan on p1 t1
- -> Seq Scan on p1c1 t1_1
- -> Seq Scan on p1c2 t1_2
- -> Seq Scan on p1c3 t1_3
+ -> 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_1
- -> Seq Scan on p1c2 t2_2
- -> Seq Scan on p1c3 t2_3
+ -> 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)*/
--------------------------------------------------
Nested Loop
-> Append
- -> Seq Scan on p1 t1
- -> Seq Scan on p1c1 t1_1
- -> Seq Scan on p1c2 t1_2
- -> Seq Scan on p1c3 t1_3
+ -> 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
- -> Seq Scan on p1 t2
+ -> Seq Scan on p1 t2_1
Filter: (t1.c1 = c1)
- -> Index Scan using p1c1_i on p1c1 t2_1
+ -> Index Scan using p1c1_i on p1c1 t2_2
Index Cond: (c1 = t1.c1)
- -> Index Scan using p1c2_i on p1c2 t2_2
+ -> Index Scan using p1c2_i on p1c2 t2_3
Index Cond: (c1 = t1.c1)
- -> Index Scan using p1c3_i on p1c3 t2_3
+ -> Index Scan using p1c3_i on p1c3 t2_4
Index Cond: (c1 = t1.c1)
(15 rows)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+---------------------------------------------
Nested Loop
-> Seq Scan on tm1 t1
- -> Index Scan using tm1_pkey on tm1 t2
+ -> Index Scan using tm1_c1_idx on tm1 t2
Index Cond: (c1 = t1.c1)
(4 rows)
-- 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: (t1_1.c1 = t2.c1)
- -> Index Only Scan using t1_i1 on t1 t1_1
- -> 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: (t1.c1 = c1.c1)
- CTE c1
- -> Aggregate
- -> Nested Loop
- -> Seq Scan on t2
- -> Index Only Scan using t1_i1 on t1 t1_1
- 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;
\! sql/maskout.sh results/ut-J.tmpout
QUERY PLAN
---------------------------------------------------------------------------------------------
- Nested Loop (cost=xxx rows=1 width=xxx)
+ Nested Loop (cost={inf}..{inf} rows=1 width=xxx)
InitPlan 1 (returns $1)
- -> Aggregate (cost=xxx rows=1 width=xxx)
- -> Nested Loop (cost=xxx rows=100 width=xxx)
- -> Seq Scan on t2 st2 (cost=xxx rows=100 width=xxx)
- -> Index Only Scan using t1_i1 on t1 st1 (cost=xxx rows=1 width=xxx)
+ -> 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=xxx rows=1 width=xxx)
+ -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
Index Cond: (c1 = $1)
- -> Seq Scan on t2 (cost=xxx rows=1 width=xxx)
+ -> 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
)
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
------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Aggregate
InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b1t1.c1 = b1t4.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t3.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t2.c1)
-> Tid Scan on t1 b1t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b1t2
- Filter: (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)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b2t2
- Filter: (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)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt3.c1)
- -> Tid Scan on t3 bmt3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
-(50 rows)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(47 rows)
/*+
Leading(bmt1 bmt2 bmt3 bmt4)
-> Aggregate
-> Nested Loop
Join Filter: (b1t2.c1 = b1t1.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b1t4.c1 = b1t2.c1)
-> Tid Scan on t4 b1t4
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)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b2t1.c1 = b2t3.c1)
-> Tid Scan on t1 b2t1
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)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (bmt3.c1 = bmt1.c1)
-> Tid Scan on t3 bmt3
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
)
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
------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Aggregate
InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b1t1.c1 = b1t4.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t3.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t2.c1)
-> Tid Scan on t1 b1t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b1t2
- Filter: (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)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b2t2
- Filter: (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)
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b3t1.c1 = b3t3.c1)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b3t1.c1 = b3t2.c1)
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b3t2
- Filter: (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)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt3.c1)
- -> Tid Scan on t3 bmt3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
-(67 rows)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(63 rows)
/*+
Leading(bmt1 bmt2 bmt3 bmt4)
-> Aggregate
-> Nested Loop
Join Filter: (b1t2.c1 = b1t1.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b1t4.c1 = b1t2.c1)
-> Tid Scan on t4 b1t4
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)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b2t1.c1 = b2t3.c1)
-> Tid Scan on t1 b2t1
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)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b3t2.c1 = b3t1.c1)
-> Seq Scan on t2 b3t2
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)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (bmt3.c1 = bmt1.c1)
-> Tid Scan on t3 bmt3
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
----------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Aggregate
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt3.c1)
- -> Tid Scan on t3 bmt3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
-(16 rows)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(15 rows)
/*+
Leading(bmt4 bmt3 bmt2 bmt1)
Aggregate
-> Nested Loop
Join Filter: (bmt2.c1 = bmt1.c1)
- -> Tid Scan on t1 bmt1
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (bmt2.c1 = bmt3.c1)
-> Seq Scan on t2 bmt2
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
----------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Aggregate
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt3.c1)
- -> Tid Scan on t3 bmt3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
-(16 rows)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(15 rows)
/*+
Leading(bmt4 bmt3 bmt2 bmt1)
Aggregate
-> Nested Loop
Join Filter: (bmt2.c1 = bmt1.c1)
- -> Tid Scan on t1 bmt1
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (bmt2.c1 = bmt3.c1)
-> Seq Scan on t2 bmt2
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
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
------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Aggregate
InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b1t1.c1 = b1t4.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t3.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t2.c1)
-> Tid Scan on t1 b1t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b1t2
- Filter: (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)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b2t2
- Filter: (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)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt3.c1)
- -> Tid Scan on t3 bmt3
- TID Cond: (ctid = '(1,1)'::tid)
-> 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))
- -> Materialize
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
-(51 rows)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(48 rows)
/*+
Leading(bmt1 bmt2 bmt3 bmt4)
-> Aggregate
-> Nested Loop
Join Filter: (b1t2.c1 = b1t1.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b1t4.c1 = b1t2.c1)
-> Tid Scan on t4 b1t4
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)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b2t1.c1 = b2t3.c1)
-> Tid Scan on t1 b2t1
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)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (bmt3.c1 = bmt1.c1)
-> Tid Scan on t3 bmt3
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
-> Aggregate
-> Nested Loop
Join Filter: (b1t1.c1 = b1t4.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t3.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t2.c1)
-> Tid Scan on t1 b1t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b1t2
- Filter: (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)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b2t2
- Filter: (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)
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b3t1.c1 = b3t3.c1)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b3t1.c1 = b3t2.c1)
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b3t2
- Filter: (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)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt3.c1)
- -> Tid Scan on t3 bmt3
- TID Cond: (ctid = '(1,1)'::tid)
-> 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))
- -> Materialize
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
-(68 rows)
-
-/*+
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+(64 rows)
+
+/*+
Leading(bmt1 bmt2 bmt3 bmt4)
Leading(b1t2 b1t3 b1t4 b1t1)
Leading(b2t3 b2t4 b2t1 b2t2)
-> Aggregate
-> Nested Loop
Join Filter: (b1t2.c1 = b1t1.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b1t4.c1 = b1t2.c1)
-> Tid Scan on t4 b1t4
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)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b2t1.c1 = b2t3.c1)
-> Tid Scan on t1 b2t1
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)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b3t2.c1 = b3t1.c1)
-> Seq Scan on t2 b3t2
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)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (bmt3.c1 = bmt1.c1)
-> Tid Scan on t3 bmt3
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
AND bmt1.c1 = c1.c1
AND bmt1.c1 = c2.c1
;
- QUERY PLAN
------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t4.c1)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
+ Join Filter: (bmt1.c1 = (max(b2t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt3.c1)
- -> Tid Scan on t3 bmt3
- TID Cond: (ctid = '(1,1)'::tid)
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
+ Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
- -> CTE Scan on c2
-(55 rows)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 bmt3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t4 bmt4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (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)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> 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)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- -> 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)
-> Hash Join
Hash Cond: (bmt4.c1 = bmt1.c1)
-> Tid Scan on t4 bmt4
Sort Key: bmt1.c1
-> Nested Loop
Join Filter: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
-> Hash Join
- Hash Cond: (bmt1.c1 = c1.c1)
+ Hash Cond: (bmt1.c1 = (max(b1t1.c1)))
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
-> Hash
-> Merge Join
- Merge Cond: (c1.c1 = c2.c1)
+ Merge Cond: ((max(b1t1.c1)) = (max(b2t1.c1)))
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b1t2.c1 = b1t3.c1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t3.c1
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c2.c1
- -> CTE Scan on c2
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Hash Join
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t3.c1
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b2t4.c1
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
-> Sort
Sort Key: bmt3.c1
-> Tid Scan on t3 bmt3
TID Cond: (ctid = '(1,1)'::tid)
-(73 rows)
+(69 rows)
-- No. J-2-1-8
EXPLAIN (COSTS false)
AND bmt1.c1 = c2.c1
AND bmt1.c1 = c3.c1
;
- QUERY PLAN
------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t4.c1)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- CTE c3
- -> Aggregate
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t4.c1)
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t3.c1)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t2.c1)
- -> Tid Scan on t1 b3t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b3t2
- Filter: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = c3.c1)
+ Join Filter: (bmt1.c1 = (max(b3t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = c2.c1)
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
- -> Tid Scan on t4 bmt4
+ 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: (bmt1.c1 = bmt3.c1)
- -> Tid Scan on t3 bmt3
- TID Cond: (ctid = '(1,1)'::tid)
+ Join Filter: (b3t1.c1 = b3t3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t2 bmt2
+ 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 = c1.c1)
- -> Tid Scan on t1 bmt1
- TID Cond: (ctid = '(1,1)'::tid)
- -> CTE Scan on c1
- -> CTE Scan on c2
- -> CTE Scan on c3
-(75 rows)
+ -> 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)
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> 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)
- CTE c2
- -> Aggregate
- -> Nested Loop
- Join Filter: (b2t1.c1 = b2t2.c1)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- -> 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)
- CTE c3
- -> Aggregate
- -> Nested Loop
- Join Filter: (b3t1.c1 = b3t3.c1)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> 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)
-> Nested Loop
Join Filter: (bmt1.c1 = bmt4.c1)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (bmt3.c1 = bmt1.c1)
-> Tid Scan on t3 bmt3
-> Sort
Sort Key: bmt1.c1
-> Nested Loop
- Join Filter: (c1.c1 = bmt1.c1)
- -> Tid Scan on t1 bmt1
- TID Cond: (ctid = '(1,1)'::tid)
+ Join Filter: ((max(b1t1.c1)) = bmt1.c1)
-> Hash Join
- Hash Cond: (c2.c1 = c1.c1)
+ Hash Cond: ((max(b2t1.c1)) = (max(b1t1.c1)))
-> Merge Join
- Merge Cond: (c2.c1 = c3.c1)
+ Merge Cond: ((max(b2t1.c1)) = (max(b3t1.c1)))
-> Sort
- Sort Key: c2.c1
- -> CTE Scan on c2
+ Sort Key: (max(b2t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b2t1.c1 = b2t2.c1)
+ -> Hash Join
+ Hash Cond: (b2t1.c1 = b2t3.c1)
+ -> Tid Scan on t1 b2t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b2t3.c1 = b2t4.c1)
+ -> Sort
+ Sort Key: b2t3.c1
+ -> Tid Scan on t3 b2t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b2t4.c1
+ -> Tid Scan on t4 b2t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b2t2
+ Filter: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c3.c1
- -> CTE Scan on c3
+ Sort Key: (max(b3t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b3t1.c1 = b3t3.c1)
+ -> Hash Join
+ Hash Cond: (b3t2.c1 = b3t1.c1)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b3t1.c1 = b3t4.c1)
+ -> Sort
+ Sort Key: b3t1.c1
+ -> Tid Scan on t1 b3t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b3t4.c1
+ -> Tid Scan on t4 b3t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t3 b3t3
+ TID Cond: (ctid = '(1,1)'::tid)
-> Hash
- -> CTE Scan on c1
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join
+ Hash Cond: (b1t4.c1 = b1t2.c1)
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b1t2.c1 = b1t3.c1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t3.c1
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 bmt1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Sort
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-(97 rows)
+ -> 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
SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1
)
;
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
+ -> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
- -> Result
- InitPlan 5 (returns $4)
- -> Limit
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 <> $5) AND (c1 = 1))
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(28 rows)
+ Filter: ((c1 <> $3) AND (c1 = 1))
+ -> Result
+ One-Time Filter: ($4 = 1)
+ InitPlan 5 (returns $4)
+ -> Limit
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
+(26 rows)
/*+
Leading(c1 bmt1)
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Aggregate
- CTE c1
+ InitPlan 2 (returns $1)
-> Result
InitPlan 1 (returns $0)
-> Limit
- -> Tid Scan on t1 b1t1
+ -> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
InitPlan 4 (returns $3)
-> Result
InitPlan 3 (returns $2)
-> Limit
- -> Tid Scan on t1 b2t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 6 (returns $5)
- -> Result
- InitPlan 5 (returns $4)
- -> Limit
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
-> Nested Loop
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: ((c1 <> $5) AND (c1 = 1))
- -> CTE Scan on c1
- Filter: (c1 = 1)
-(28 rows)
+ Filter: ((c1 <> $3) AND (c1 = 1))
+ -> Result
+ One-Time Filter: ($4 = 1)
+ InitPlan 5 (returns $4)
+ -> Limit
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ Filter: ((c1 IS NOT NULL) AND (c1 = 1))
+(26 rows)
-- No. J-2-2-2
EXPLAIN (COSTS false)
QUERY PLAN
-----------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ -> 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)
- -> Materialize
- -> Seq Scan on t2 b3t2
- Filter: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b3t2
+ Filter: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
- -> CTE Scan on c1
-(38 rows)
+ 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)
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Merge Join
- Merge Cond: (b1t1.c1 = b1t2.c1)
- -> Sort
- Sort Key: b1t1.c1
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Sort
- Sort Key: b1t2.c1
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (b2t1.c1 = b2t2.c1)
Sort Key: b2t2.c1
-> Seq Scan on t2 b2t2
Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (b3t1.c1 = b3t2.c1)
Hash Cond: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
+ Filter: (c1 <> $1)
-> Hash
-> Merge Join
- Merge Cond: (bmt2.c1 = c1.c1)
+ Merge Cond: (bmt2.c1 = (max(b1t1.c1)))
-> Sort
Sort Key: bmt2.c1
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
-(52 rows)
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Merge Join
+ Merge Cond: (b1t1.c1 = b1t2.c1)
+ -> Sort
+ Sort Key: b1t1.c1
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+(50 rows)
-- No. J-2-2-3
EXPLAIN (COSTS false)
QUERY PLAN
-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Nested Loop
Join Filter: (b2t1.c1 = b2t4.c1)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ -> 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)
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b3t1.c1 = b3t3.c1)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b3t1.c1 = b3t2.c1)
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b3t2
- Filter: (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)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt3.c1)
- -> Tid Scan on t3 bmt3
- TID Cond: (ctid = '(1,1)'::tid)
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
+ Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
- -> CTE Scan on c1
-(70 rows)
+ 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)
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Merge Join
- Merge Cond: (b1t2.c1 = b1t1.c1)
- -> Sort
- Sort Key: b1t2.c1
- -> Nested Loop
- Join Filter: (b1t3.c1 = b1t2.c1)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- -> 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)
- -> Sort
- Sort Key: b1t1.c1
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Aggregate
-> Merge Join
Merge Cond: (b2t2.c1 = b2t1.c1)
Sort Key: b2t2.c1
-> Nested Loop
Join Filter: (b2t3.c1 = b2t2.c1)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b2t3.c1 = b2t4.c1)
-> Tid Scan on t3 b2t3
-> 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 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Aggregate
-> Merge Join
Merge Cond: (b3t2.c1 = b3t1.c1)
Sort Key: b3t2.c1
-> Nested Loop
Join Filter: (b3t3.c1 = b3t2.c1)
- -> Seq Scan on t2 b3t2
- Filter: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b3t3.c1 = b3t4.c1)
-> Tid Scan on t3 b3t3
-> 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
Sort Key: bmt2.c1
-> Nested Loop
Join Filter: (bmt3.c1 = bmt2.c1)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
-> 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 = c1.c1)
+ Merge Cond: (bmt4.c1 = (max(b1t1.c1)))
-> Sort
Sort Key: bmt4.c1
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Merge Join
+ Merge Cond: (b1t2.c1 = b1t1.c1)
+ -> Sort
+ Sort Key: b1t2.c1
+ -> Nested Loop
+ Join Filter: (b1t3.c1 = b1t2.c1)
+ -> Hash Join
+ Hash Cond: (b1t3.c1 = b1t4.c1)
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t1.c1
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
-> Sort
Sort Key: bmt1.c1
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $2)
-(91 rows)
+ Filter: (c1 <> $1)
+(89 rows)
-- No. J-2-2-4
EXPLAIN (COSTS false)
QUERY PLAN
-----------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t4.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t3.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Nested Loop
- Join Filter: (b1t1.c1 = b1t2.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $4)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt4.c1)
- -> Tid Scan on t4 bmt4
- TID Cond: (ctid = '(1,1)'::tid)
+ Join Filter: (bmt1.c1 = (max(b1t1.c1)))
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt3.c1)
- -> Tid Scan on t3 bmt3
- TID Cond: (ctid = '(1,1)'::tid)
+ Join Filter: (bmt1.c1 = bmt4.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = bmt2.c1)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
+ Join Filter: (bmt1.c1 = bmt3.c1)
-> Nested Loop
- Join Filter: (bmt1.c1 = c1.c1)
+ Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $4)
- -> CTE Scan on c1
-(47 rows)
+ 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)
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
Aggregate
- CTE c1
- -> Aggregate
- -> Nested Loop
- Join Filter: (b1t2.c1 = b1t1.c1)
- -> Tid Scan on t1 b1t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> 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)
- InitPlan 3 (returns $2)
+ InitPlan 2 (returns $1)
-> Result
- InitPlan 2 (returns $1)
+ InitPlan 1 (returns $0)
-> Limit
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $4)
+ InitPlan 3 (returns $3)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Sort Key: bmt2.c1
-> Nested Loop
Join Filter: (bmt3.c1 = bmt2.c1)
- -> Seq Scan on t2 bmt2
- Filter: (ctid = '(1,1)'::tid)
-> 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 = c1.c1)
+ Merge Cond: (bmt4.c1 = (max(b1t1.c1)))
-> Sort
Sort Key: bmt4.c1
-> Tid Scan on t4 bmt4
TID Cond: (ctid = '(1,1)'::tid)
-> Sort
- Sort Key: c1.c1
- -> CTE Scan on c1
+ Sort Key: (max(b1t1.c1))
+ -> Aggregate
+ -> Nested Loop
+ Join Filter: (b1t2.c1 = b1t1.c1)
+ -> Hash Join
+ Hash Cond: (b1t2.c1 = b1t3.c1)
+ -> Seq Scan on t2 b1t2
+ Filter: (ctid = '(1,1)'::tid)
+ -> Hash
+ -> Merge Join
+ Merge Cond: (b1t3.c1 = b1t4.c1)
+ -> Sort
+ Sort Key: b1t3.c1
+ -> Tid Scan on t3 b1t3
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Sort
+ Sort Key: b1t4.c1
+ -> Tid Scan on t4 b1t4
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Tid Scan on t1 b1t1
+ TID Cond: (ctid = '(1,1)'::tid)
+ -> Seq Scan on t2 bmt2
+ Filter: (ctid = '(1,1)'::tid)
-> Sort
Sort Key: bmt1.c1
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $4)
-(60 rows)
+ Filter: (c1 <> $3)
+(58 rows)
----
---- No. J-2-3 RULE or VIEW
Aggregate
-> Nested Loop
Join Filter: (t1.c1 = t4.c1)
- -> Tid Scan on t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
- -> Tid Scan on t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
/*+
Sort Key: t4.c1
-> Tid Scan on t4
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2
- Filter: (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)
-(25 rows)
+(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)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t3.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t2.c1)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
/*+
Sort Key: b1t4.c1
-> Tid Scan on t4 b1t4
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2 b1t2
- Filter: (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)
-(25 rows)
+(24 rows)
-- No. J-2-3-2
EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
Aggregate
-> Nested Loop
Join Filter: (t1.c1 = t4.c1)
- -> Tid Scan on t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
- -> Tid Scan on t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
- -> Tid Scan on t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
- -> Tid Scan on t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
/*+
Sort Key: t4.c1
-> Tid Scan on t4
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2
- Filter: (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)
Sort Key: t4.c1
-> Tid Scan on t4
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2
- Filter: (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)
-(51 rows)
+(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)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t3.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t2.c1)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
/*+
-> Nested Loop
-> Nested Loop
Join Filter: (b1t1.c1 = b1t4.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b1t3.c1 = b1t1.c1)
-> Tid Scan on t3 b1t3
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)
-> Nested Loop
-> Nested Loop
Join Filter: (b2t1.c1 = b2t4.c1)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b2t3.c1 = b2t1.c1)
-> Tid Scan on t3 b2t3
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)
Aggregate
-> Nested Loop
Join Filter: (t1.c1 = t4.c1)
- -> Tid Scan on t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
- -> Tid Scan on t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
- -> Tid Scan on t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
- -> Tid Scan on t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
- -> Tid Scan on t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t3.c1)
- -> Tid Scan on t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (t1.c1 = t2.c1)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
/*+
Sort Key: t4.c1
-> Tid Scan on t4
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2
- Filter: (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)
Sort Key: t4.c1
-> Tid Scan on t4
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2
- Filter: (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)
Sort Key: t4.c1
-> Tid Scan on t4
TID Cond: (ctid = '(1,1)'::tid)
- -> Materialize
- -> Seq Scan on t2
- Filter: (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)
-(77 rows)
+(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)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t3.c1)
- -> Tid Scan on t3 b1t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b1t1.c1 = b1t2.c1)
- -> Seq Scan on t2 b1t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t3.c1)
- -> Tid Scan on t3 b2t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b2t1.c1 = b2t2.c1)
- -> Seq Scan on t2 b2t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b3t1.c1 = b3t3.c1)
- -> Tid Scan on t3 b3t3
- TID Cond: (ctid = '(1,1)'::tid)
-> Nested Loop
Join Filter: (b3t1.c1 = b3t2.c1)
- -> Seq Scan on t2 b3t2
- Filter: (ctid = '(1,1)'::tid)
-> 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)
/*+
-> Nested Loop
-> Nested Loop
Join Filter: (b1t1.c1 = b1t4.c1)
- -> Tid Scan on t4 b1t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b1t3.c1 = b1t1.c1)
-> Tid Scan on t3 b1t3
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)
-> Nested Loop
-> Nested Loop
Join Filter: (b2t1.c1 = b2t4.c1)
- -> Tid Scan on t4 b2t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b2t3.c1 = b2t1.c1)
-> Tid Scan on t3 b2t3
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)
-> Nested Loop
-> Nested Loop
Join Filter: (b3t1.c1 = b3t4.c1)
- -> Tid Scan on t4 b3t4
- TID Cond: (ctid = '(1,1)'::tid)
-> Hash Join
Hash Cond: (b3t3.c1 = b3t1.c1)
-> Tid Scan on t3 b3t3
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)
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
------------------------------------------------
+ QUERY PLAN
+-----------------------------------------
Nested Loop
Join Filter: (t1.c1 = t2.c1)
+ -> Tid Scan on t1
+ TID Cond: (ctid = '(1,1)'::tid)
-> Seq Scan on t2
- -> Materialize
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
-(6 rows)
+(5 rows)
/*+NoNestLoop(t1 t2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';;
---- No. J-3-2 join inherit tables
----
EXPLAIN (COSTS false) SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+-----------------------------------------
Hash Join
Hash Cond: (p2.c1 = p1.c1)
-> Append
- -> Seq Scan on p2
- -> Seq Scan on p2c1
- -> Seq Scan on p2c2
- -> Seq Scan on p2c3
- -> Seq Scan on p2c1c1
- -> Seq Scan on p2c1c2
- -> Seq Scan on p2c2c1
- -> Seq Scan on p2c2c2
- -> Seq Scan on p2c3c1
- -> Seq Scan on p2c3c2
+ -> Seq Scan on p2 p2_1
+ -> Seq Scan on p2c1 p2_2
+ -> Seq Scan on p2c2 p2_3
+ -> Seq Scan on p2c3 p2_4
+ -> Seq Scan on p2c1c1 p2_5
+ -> Seq Scan on p2c1c2 p2_6
+ -> Seq Scan on p2c2c1 p2_7
+ -> Seq Scan on p2c2c2 p2_8
+ -> Seq Scan on p2c3c1 p2_9
+ -> Seq Scan on p2c3c2 p2_10
-> Hash
-> Append
- -> Seq Scan on p1
- -> Seq Scan on p1c1
- -> Seq Scan on p1c2
- -> Seq Scan on p1c3
+ -> Seq Scan on p1 p1_1
+ -> Seq Scan on p1c1 p1_2
+ -> Seq Scan on p1c2 p1_3
+ -> Seq Scan on p1c3 p1_4
(19 rows)
-- No. J-3-2-1
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+--------------------------------------------
Merge Join
Merge Cond: (p1.c1 = p2.c1)
-> Sort
Sort Key: p1.c1
-> Append
- -> Seq Scan on p1
- -> Seq Scan on p1c1
- -> Seq Scan on p1c2
- -> Seq Scan on p1c3
+ -> 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
- -> Seq Scan on p2c1
- -> Seq Scan on p2c2
- -> Seq Scan on p2c3
- -> Seq Scan on p2c1c1
- -> Seq Scan on p2c1c2
- -> Seq Scan on p2c2c1
- -> Seq Scan on p2c2c2
- -> Seq Scan on p2c3c1
- -> Seq Scan on p2c3c2
+ -> Seq Scan on p2 p2_1
+ -> Seq Scan on p2c1 p2_2
+ -> Seq Scan on p2c2 p2_3
+ -> Seq Scan on p2c3 p2_4
+ -> Seq Scan on p2c1c1 p2_5
+ -> Seq Scan on p2c1c2 p2_6
+ -> Seq Scan on p2c2c1 p2_7
+ -> Seq Scan on p2c2c2 p2_8
+ -> Seq Scan on p2c3c1 p2_9
+ -> Seq Scan on p2c3c2 p2_10
(22 rows)
-- No. J-3-2-2
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+-----------------------------------------
Hash Join
Hash Cond: (p2.c1 = p1.c1)
-> Append
- -> Seq Scan on p2
- -> Seq Scan on p2c1
- -> Seq Scan on p2c2
- -> Seq Scan on p2c3
- -> Seq Scan on p2c1c1
- -> Seq Scan on p2c1c2
- -> Seq Scan on p2c2c1
- -> Seq Scan on p2c2c2
- -> Seq Scan on p2c3c1
- -> Seq Scan on p2c3c2
+ -> Seq Scan on p2 p2_1
+ -> Seq Scan on p2c1 p2_2
+ -> Seq Scan on p2c2 p2_3
+ -> Seq Scan on p2c3 p2_4
+ -> Seq Scan on p2c1c1 p2_5
+ -> Seq Scan on p2c1c2 p2_6
+ -> Seq Scan on p2c2c1 p2_7
+ -> Seq Scan on p2c2c2 p2_8
+ -> Seq Scan on p2c3c1 p2_9
+ -> Seq Scan on p2c3c2 p2_10
-> Hash
-> Append
- -> Seq Scan on p1
- -> Seq Scan on p1c1
- -> Seq Scan on p1c2
- -> Seq Scan on p1c3
+ -> 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;
\o
\! sql/maskout.sh results/ut-J.tmpout
- QUERY PLAN
---------------------------------------------------------------------------
- Hash Full Join (cost=xxx rows=1000 width=xxx)
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Hash Full Join (cost={inf}..{inf} rows=1000 width=xxx)
Hash Cond: (t1.c1 = t2.c1)
- -> Seq Scan on t1 (cost=xxx rows=1000 width=xxx)
- -> Hash (cost=xxx rows=100 width=xxx)
- -> Seq Scan on t2 (cost=xxx rows=100 width=xxx)
-(5 rows)
+ -> 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