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, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
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)
----
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Merge Join
Merge Cond: (t1.c1 = t2.c1)
-> Index Scan using t1_i1 on t1
-> Sort
Sort Key: t2.c1
- -> Hash Join
- Hash Cond: (t3.c1 = t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Hash Join
- Hash Cond: (t4.c1 = t2.c1)
- -> Seq Scan on t4
- -> Hash
- -> Seq Scan on t2
+ -> Nested Loop
+ Join Filter: (t2.c1 = t3.c1)
+ -> Hash Join
+ Hash Cond: (t4.c1 = t2.c1)
+ -> Seq Scan on t4
+ -> Hash
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t4.c1)
(14 rows)
-- No. L-1-1-2
QUERY PLAN
----------------------------------------------------
- Merge Join
- Merge Cond: (t_1.c1 = t_2.c1)
+ Nested Loop
-> Merge Join
- Merge Cond: (t_1.c1 = t_4.c1)
+ Merge Cond: (t_1.c1 = t_2.c1)
-> Merge Join
Merge Cond: (t_1.c1 = t_3.c1)
-> Index Scan using t1_i1 on t1 t_1
-> Index Scan using t3_i1 on t3 t_3
- -> Index Scan using t4_i1 on t4 t_4
- -> Sort
- Sort Key: t_2.c1
- -> Seq Scan on t2 t_2
+ -> Sort
+ Sort Key: t_2.c1
+ -> Seq Scan on t2 t_2
+ -> Index Scan using t4_i1 on t4 t_4
+ Index Cond: (c1 = t_1.c1)
(12 rows)
-- No. L-1-1-3
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------
Merge Join
Merge Cond: (t_1.c1 = t_2.c1)
-> Index Scan using t1_i1 on t1 t_1
-> Sort
Sort Key: t_2.c1
- -> Hash Join
- Hash Cond: (t_3.c1 = t_2.c1)
- -> Seq Scan on t3 t_3
- -> Hash
- -> Hash Join
- Hash Cond: (t_4.c1 = t_2.c1)
- -> Seq Scan on t4 t_4
- -> Hash
- -> Seq Scan on t2 t_2
+ -> Nested Loop
+ Join Filter: (t_2.c1 = t_3.c1)
+ -> Hash Join
+ Hash Cond: (t_4.c1 = t_2.c1)
+ -> Seq Scan on t4 t_4
+ -> Hash
+ -> Seq Scan on t2 t_2
+ -> Index Scan using t3_i1 on t3 t_3
+ Index Cond: (c1 = t_4.c1)
(14 rows)
----
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Merge Join
Merge Cond: (t1.c1 = t2.c1)
-> Index Scan using t1_i1 on t1
-> Sort
Sort Key: t2.c1
- -> Hash Join
- Hash Cond: (t3.c1 = t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Hash Join
- Hash Cond: (t4.c1 = t2.c1)
- -> Seq Scan on t4
- -> Hash
- -> Seq Scan on t2
+ -> Nested Loop
+ Join Filter: (t2.c1 = t3.c1)
+ -> Hash Join
+ Hash Cond: (t4.c1 = t2.c1)
+ -> Seq Scan on t4
+ -> Hash
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t4.c1)
(14 rows)
-- No. L-1-2-2
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)
----
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Merge Join
Merge Cond: (t1.c1 = t2.c1)
-> Index Scan using t1_i1 on t1
-> Sort
Sort Key: t2.c1
- -> Hash Join
- Hash Cond: (t3.c1 = t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Hash Join
- Hash Cond: (t4.c1 = t2.c1)
- -> Seq Scan on t4
- -> Hash
- -> Seq Scan on t2
+ -> Nested Loop
+ Join Filter: (t2.c1 = t3.c1)
+ -> Hash Join
+ Hash Cond: (t4.c1 = t2.c1)
+ -> Seq Scan on t4
+ -> Hash
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t4.c1)
(14 rows)
-- No. L-1-3-2
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)
----
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Merge Join
Merge Cond: (t1.c1 = t2.c1)
-> Index Scan using t1_i1 on t1
-> Sort
Sort Key: t2.c1
- -> Hash Join
- Hash Cond: (t3.c1 = t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Hash Join
- Hash Cond: (t4.c1 = t2.c1)
- -> Seq Scan on t4
- -> Hash
- -> Seq Scan on t2
+ -> Nested Loop
+ Join Filter: (t2.c1 = t3.c1)
+ -> Hash Join
+ Hash Cond: (t4.c1 = t2.c1)
+ -> Seq Scan on t4
+ -> Hash
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t4.c1)
(14 rows)
-- No. L-1-4-2
QUERY PLAN
------------------------------------------------
Merge Join
- Merge Cond: (s1.t1.c1 = s2.t1.c1)
+ Merge Cond: (t1.c1 = t1_1.c1)
-> Merge Join
- Merge Cond: (s1.t1.c1 = t2.c1)
+ Merge Cond: (t1.c1 = t2.c1)
-> Merge Join
- Merge Cond: (s1.t1.c1 = t3.c1)
+ Merge Cond: (t1.c1 = t3.c1)
-> Index Scan using t1_i1 on t1
-> Index Scan using t3_i1 on t3
- -> Index Scan using t2_i1 on t2
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
-> Sort
- Sort Key: s2.t1.c1
- -> Seq Scan on t1
-(12 rows)
+ Sort Key: t1_1.c1
+ -> Seq Scan on t1 t1_1
+(14 rows)
/*+Leading(t1 t2 t3 t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s2.t1 WHERE s1.t1.c1 = t2.c1 AND s1.t1.c1 = t3.c1 AND s1.t1.c1 = s2.t1.c1;
-INFO: hint syntax error at or near "Leading(t1 t2 t3 t1)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t1 t2 t3 t1)"
DETAIL: Relation name "t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
------------------------------------------------
Merge Join
- Merge Cond: (s1.t1.c1 = s2.t1.c1)
+ Merge Cond: (t1.c1 = t1_1.c1)
-> Merge Join
- Merge Cond: (s1.t1.c1 = t2.c1)
+ Merge Cond: (t1.c1 = t2.c1)
-> Merge Join
- Merge Cond: (s1.t1.c1 = t3.c1)
+ Merge Cond: (t1.c1 = t3.c1)
-> Index Scan using t1_i1 on t1
-> Index Scan using t3_i1 on t3
- -> Index Scan using t2_i1 on t2
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
-> Sort
- Sort Key: s2.t1.c1
- -> Seq Scan on t1
-(12 rows)
+ Sort Key: t1_1.c1
+ -> Seq Scan on t1 t1_1
+(14 rows)
/*+Leading(s1.t1 t2 t3 s2.t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s2.t1 WHERE s1.t1.c1 = t2.c1 AND s1.t1.c1 = t3.c1 AND s1.t1.c1 = s2.t1.c1;
QUERY PLAN
------------------------------------------------
Merge Join
- Merge Cond: (s1.t1.c1 = s2.t1.c1)
+ Merge Cond: (t1.c1 = t1_1.c1)
-> Merge Join
- Merge Cond: (s1.t1.c1 = t2.c1)
+ Merge Cond: (t1.c1 = t2.c1)
-> Merge Join
- Merge Cond: (s1.t1.c1 = t3.c1)
+ Merge Cond: (t1.c1 = t3.c1)
-> Index Scan using t1_i1 on t1
-> Index Scan using t3_i1 on t3
- -> Index Scan using t2_i1 on t2
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
-> Sort
- Sort Key: s2.t1.c1
- -> Seq Scan on t1
-(12 rows)
+ Sort Key: t1_1.c1
+ -> Seq Scan on t1 t1_1
+(14 rows)
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s2.t1 s2t1 WHERE s1.t1.c1 = t2.c1 AND s1.t1.c1 = t3.c1 AND s1.t1.c1 = s2t1.c1;
QUERY PLAN
Merge Cond: (t1.c1 = t3.c1)
-> Index Scan using t1_i1 on t1
-> Index Scan using t3_i1 on t3
- -> Index Scan using t2_i1 on t2
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
-> Sort
Sort Key: s2t1.c1
-> Seq Scan on t1 s2t1
-(12 rows)
+(14 rows)
/*+Leading(s2t1 t1 t3 t2)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s2.t1 s2t1 WHERE s1.t1.c1 = t2.c1 AND s1.t1.c1 = t3.c1 AND s1.t1.c1 = s2t1.c1;
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Merge Join
Merge Cond: (t1.c1 = t2.c1)
-> Nested Loop
-> Merge Join
Merge Cond: (t1.c1 = s2t1.c1)
-> Index Scan using t1_i1 on t1
- -> Index Scan using t1_pkey on t1 s2t1
+ -> Sort
+ Sort Key: s2t1.c1
+ -> Seq Scan on t1 s2t1
-> Index Scan using t3_i1 on t3
Index Cond: (c1 = t1.c1)
-> Sort
Sort Key: t2.c1
-> Seq Scan on t2
-(12 rows)
+(14 rows)
-- No. L-1-4-3
EXPLAIN (COSTS false) SELECT *, (SELECT max(t1.c1) FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1) FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
- QUERY PLAN
--------------------------------------------------------------------
- Merge Join
- Merge Cond: (s1.t1.c1 = s1.t2.c1)
- InitPlan 1 (returns $0)
+ QUERY PLAN
+------------------------------------------------------------------------
+ Nested Loop
+ InitPlan 1 (returns $1)
-> Aggregate
- -> Merge Join
- Merge Cond: (s1.t1.c1 = s1.t2.c1)
+ -> Nested Loop
-> Merge Join
- Merge Cond: (s1.t1.c1 = s1.t4.c1)
+ Merge Cond: (t1_1.c1 = t2_1.c1)
-> Merge Join
- Merge Cond: (s1.t1.c1 = s1.t3.c1)
- -> Index Only Scan using t1_i1 on t1
- -> Index Only Scan using t3_i1 on t3
- -> Index Only Scan using t4_i1 on t4
- -> Sort
- Sort Key: s1.t2.c1
- -> Seq Scan on t2
+ Merge Cond: (t1_1.c1 = t3_1.c1)
+ -> Index Only Scan using t1_i1 on t1 t1_1
+ -> Index Only Scan using t3_i1 on t3 t3_1
+ -> Sort
+ Sort Key: t2_1.c1
+ -> Seq Scan on t2 t2_1
+ -> Index Only Scan using t4_i1 on t4 t4_1
+ Index Cond: (c1 = t1_1.c1)
-> Merge Join
- Merge Cond: (s1.t1.c1 = s1.t4.c1)
+ Merge Cond: (t1.c1 = t2.c1)
-> Merge Join
- Merge Cond: (s1.t1.c1 = s1.t3.c1)
+ 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: s1.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)
(26 rows)
/*+Leading(t4 t2 t3 t1)*/
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Merge Join
- Merge Cond: (s1.t1.c1 = s1.t2.c1)
- InitPlan 1 (returns $0)
+ Merge Cond: (t1.c1 = t2.c1)
+ InitPlan 1 (returns $1)
-> Aggregate
-> Merge Join
- Merge Cond: (s1.t1.c1 = s1.t2.c1)
- -> Index Only Scan using t1_i1 on t1
+ Merge Cond: (t1_1.c1 = t2_1.c1)
+ -> Index Only Scan using t1_i1 on t1 t1_1
-> Sort
- Sort Key: s1.t2.c1
- -> Hash Join
- Hash Cond: (s1.t3.c1 = s1.t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Hash Join
- Hash Cond: (s1.t4.c1 = s1.t2.c1)
- -> Seq Scan on t4
- -> Hash
- -> Seq Scan on t2
+ Sort Key: t2_1.c1
+ -> Nested Loop
+ Join Filter: (t2_1.c1 = t3_1.c1)
+ -> Hash Join
+ Hash Cond: (t4_1.c1 = t2_1.c1)
+ -> Seq Scan on t4 t4_1
+ -> Hash
+ -> Seq Scan on t2 t2_1
+ -> Index Only Scan using t3_i1 on t3 t3_1
+ Index Cond: (c1 = t4_1.c1)
-> Index Scan using t1_i1 on t1
-> Sort
- Sort Key: s1.t2.c1
- -> Hash Join
- Hash Cond: (s1.t3.c1 = s1.t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Hash Join
- Hash Cond: (s1.t4.c1 = s1.t2.c1)
- -> Seq Scan on t4
- -> Hash
- -> Seq Scan on t2
+ Sort Key: t2.c1
+ -> Nested Loop
+ Join Filter: (t2.c1 = t3.c1)
+ -> Hash Join
+ Hash Cond: (t4.c1 = t2.c1)
+ -> Seq Scan on t4
+ -> Hash
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t4.c1)
(30 rows)
/*+Leading(st1 st2 st3 st4)Leading(t4 t2 t3 t1)*/
-> Index Scan using t1_i1 on t1
-> Sort
Sort Key: t2.c1
- -> Hash Join
- Hash Cond: (t3.c1 = t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Hash Join
- Hash Cond: (t4.c1 = t2.c1)
- -> Seq Scan on t4
- -> Hash
- -> Seq Scan on t2
+ -> Nested Loop
+ Join Filter: (t2.c1 = t3.c1)
+ -> Hash Join
+ Hash Cond: (t4.c1 = t2.c1)
+ -> Seq Scan on t4
+ -> Hash
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t4.c1)
SubPlan 1
-> Aggregate
-> Result
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Merge Join
Merge Cond: (t1.c1 = t2.c1)
-> Index Scan using t1_i1 on t1
-> Sort
Sort Key: t2.c1
- -> Hash Join
- Hash Cond: (t3.c1 = t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Hash Join
- Hash Cond: (t4.c1 = t2.c1)
- -> Seq Scan on t4
- -> Hash
- -> Seq Scan on t2
+ -> Nested Loop
+ Join Filter: (t2.c1 = t3.c1)
+ -> Hash Join
+ Hash Cond: (t4.c1 = t2.c1)
+ -> Seq Scan on t4
+ -> Hash
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t4.c1)
(14 rows)
-- No. L-1-5-2
/*+Leading(t4 t2 t3 t1 t4)*/
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 "Leading(t4 t2 t3 t1 t4)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t4 t2 t3 t1 t4)"
DETAIL: Relation name "t4" is duplicated.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
------------------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ Nested Loop
-> Merge Join
- Merge Cond: (t1.c1 = t4.c1)
+ Merge Cond: (t1.c1 = t2.c1)
-> Merge Join
Merge Cond: (t1.c1 = t3.c1)
-> Index Scan using t1_i1 on t1
-> Index Scan using t3_i1 on t3
- -> Index Scan using t4_i1 on t4
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t4_i1 on t4
+ Index Cond: (c1 = t1.c1)
(12 rows)
/*+Leading(t4 t2 t3 t4)*/
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 "Leading(t4 t2 t3 t4)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t4 t2 t3 t4)"
DETAIL: Relation name "t4" is duplicated.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
------------------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ Nested Loop
-> Merge Join
- Merge Cond: (t1.c1 = t4.c1)
+ Merge Cond: (t1.c1 = t2.c1)
-> Merge Join
Merge Cond: (t1.c1 = t3.c1)
-> Index Scan using t1_i1 on t1
-> Index Scan using t3_i1 on t3
- -> Index Scan using t4_i1 on t4
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t4_i1 on t4
+ Index Cond: (c1 = t1.c1)
(12 rows)
-- No. L-1-5-3
/*+Leading(t4 t2 t3 t1 t4 t2 t3 t1)*/
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 "Leading(t4 t2 t3 t1 t4 t2 t3 t1)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t4 t2 t3 t1 t4 t2 t3 t1)"
DETAIL: Relation name "t4" is duplicated.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
------------------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ Nested Loop
-> Merge Join
- Merge Cond: (t1.c1 = t4.c1)
+ Merge Cond: (t1.c1 = t2.c1)
-> Merge Join
Merge Cond: (t1.c1 = t3.c1)
-> Index Scan using t1_i1 on t1
-> Index Scan using t3_i1 on t3
- -> Index Scan using t4_i1 on t4
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t4_i1 on t4
+ Index Cond: (c1 = t1.c1)
(12 rows)
/*+Leading(t4 t2 t2 t4)*/
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 "Leading(t4 t2 t2 t4)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t4 t2 t2 t4)"
DETAIL: Relation name "t2" is duplicated.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
------------------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ Nested Loop
-> Merge Join
- Merge Cond: (t1.c1 = t4.c1)
+ Merge Cond: (t1.c1 = t2.c1)
-> Merge Join
Merge Cond: (t1.c1 = t3.c1)
-> Index Scan using t1_i1 on t1
-> Index Scan using t3_i1 on t3
- -> Index Scan using t4_i1 on t4
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t4_i1 on t4
+ Index Cond: (c1 = t1.c1)
(12 rows)
----
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Merge Join
Merge Cond: (t1.c1 = t2.c1)
-> Index Scan using t1_i1 on t1
-> Sort
Sort Key: t2.c1
- -> Hash Join
- Hash Cond: (t3.c1 = t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Hash Join
- Hash Cond: (t4.c1 = t2.c1)
- -> Seq Scan on t4
- -> Hash
- -> Seq Scan on t2
+ -> Nested Loop
+ Join Filter: (t2.c1 = t3.c1)
+ -> Hash Join
+ Hash Cond: (t4.c1 = t2.c1)
+ -> Seq Scan on t4
+ -> Hash
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t4.c1)
(14 rows)
-- No. L-1-6-2
EXPLAIN (COSTS false) SELECT * FROM s1.p1 t1, s1.p1 t2, s1.p1 t3, s1.p1 t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
- QUERY PLAN
----------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------
Hash Join
Hash Cond: (t1.c1 = t4.c1)
-> Hash Join
Hash Cond: (t1.c1 = t2.c1)
-> Append
-> Seq Scan on p1 t1
- -> Seq Scan on p1c1 t1
- -> Seq Scan on p1c2 t1
- -> Seq Scan on p1c3 t1
+ -> Seq Scan on p1c1 t1_1
+ -> Seq Scan on p1c2 t1_2
+ -> Seq Scan on p1c3 t1_3
-> Hash
-> Append
-> Seq Scan on p1 t2
- -> Seq Scan on p1c1 t2
- -> Seq Scan on p1c2 t2
- -> Seq Scan on p1c3 t2
+ -> Seq Scan on p1c1 t2_1
+ -> Seq Scan on p1c2 t2_2
+ -> Seq Scan on p1c3 t2_3
-> Hash
-> Append
-> Seq Scan on p1 t3
- -> Seq Scan on p1c1 t3
- -> Seq Scan on p1c2 t3
- -> Seq Scan on p1c3 t3
+ -> Seq Scan on p1c1 t3_1
+ -> Seq Scan on p1c2 t3_2
+ -> Seq Scan on p1c3 t3_3
-> Hash
-> Append
-> Seq Scan on p1 t4
- -> Seq Scan on p1c1 t4
- -> Seq Scan on p1c2 t4
- -> Seq Scan on p1c3 t4
+ -> Seq Scan on p1c1 t4_1
+ -> Seq Scan on p1c2 t4_2
+ -> Seq Scan on p1c3 t4_3
(29 rows)
/*+Leading(t4 t3 t2 t1)*/
duplication hint:
error hint:
- QUERY PLAN
----------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------
Hash Join
Hash Cond: (t2.c1 = t1.c1)
-> Hash Join
Hash Cond: (t3.c1 = t4.c1)
-> Append
-> Seq Scan on p1 t3
- -> Seq Scan on p1c1 t3
- -> Seq Scan on p1c2 t3
- -> Seq Scan on p1c3 t3
+ -> Seq Scan on p1c1 t3_1
+ -> Seq Scan on p1c2 t3_2
+ -> Seq Scan on p1c3 t3_3
-> Hash
-> Append
-> Seq Scan on p1 t4
- -> Seq Scan on p1c1 t4
- -> Seq Scan on p1c2 t4
- -> Seq Scan on p1c3 t4
+ -> Seq Scan on p1c1 t4_1
+ -> Seq Scan on p1c2 t4_2
+ -> Seq Scan on p1c3 t4_3
-> Hash
-> Append
-> Seq Scan on p1 t2
- -> Seq Scan on p1c1 t2
- -> Seq Scan on p1c2 t2
- -> Seq Scan on p1c3 t2
+ -> Seq Scan on p1c1 t2_1
+ -> Seq Scan on p1c2 t2_2
+ -> Seq Scan on p1c3 t2_3
-> Hash
-> Append
-> Seq Scan on p1 t1
- -> Seq Scan on p1c1 t1
- -> Seq Scan on p1c2 t1
- -> Seq Scan on p1c3 t1
+ -> Seq Scan on p1c1 t1_1
+ -> Seq Scan on p1c2 t1_2
+ -> Seq Scan on p1c3 t1_3
(29 rows)
-- No. L-1-6-3
(10 rows)
-- No. L-1-6-8
-EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
QUERY PLAN
-------------------------------------------------------
Nested Loop
(13 rows)
/*+Leading(t4 t3 t2 t1)*/
-EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
+EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t1 (c1, c2, c3, c4), s1.t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
LOG: pg_hint_plan:
used hint:
not used hint:
-- No. L-1-6-10
EXPLAIN (COSTS false) SELECT * FROM s1.v1 t1, s1.v1 t2, s1.v1 t3, s1.v1 t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
+ Hash Cond: (v1t1.c1 = v1t1_3.c1)
-> Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
+ Hash Cond: (v1t1.c1 = v1t1_2.c1)
-> 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
-> Hash
- -> Seq Scan on t1 v1t1
+ -> Seq Scan on t1 v1t1_2
-> Hash
- -> Seq Scan on t1 v1t1
+ -> Seq Scan on t1 v1t1_3
(13 rows)
/*+Leading(t4 t3 t2 t1)*/
duplication hint:
error hint:
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
+ Hash Cond: (v1t1.c1 = v1t1_3.c1)
-> Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
+ Hash Cond: (v1t1.c1 = v1t1_2.c1)
-> 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
-> Hash
- -> Seq Scan on t1 v1t1
+ -> Seq Scan on t1 v1t1_2
-> Hash
- -> Seq Scan on t1 v1t1
+ -> Seq Scan on t1 v1t1_3
(13 rows)
EXPLAIN (COSTS false) SELECT * FROM s1.v1 t1, s1.v1_ t2, s1.t3, s1.t4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, (SELECT t4.c1 FROM s1.t4) st4 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = st4.c1;
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 Only 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 Only Scan using t4_i1 on t4
+ Index Cond: (c1 = t1.c1)
(12 rows)
/*+Leading(st4 t2 t3 t1)*/
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 Only 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 Only Scan using t4_i1 on t4
+ Index Cond: (c1 = t1.c1)
(12 rows)
/*+Leading(t4 t2 t3 t1)*/
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Merge Join
Merge Cond: (t1.c1 = t2.c1)
-> Index Scan using t1_i1 on t1
-> Sort
Sort Key: t2.c1
- -> Hash Join
- Hash Cond: (t3.c1 = t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Hash Join
- Hash Cond: (t4.c1 = t2.c1)
- -> Seq Scan on t4
- -> Hash
- -> Seq Scan on t2
+ -> Nested Loop
+ Join Filter: (t2.c1 = t3.c1)
+ -> Hash Join
+ Hash Cond: (t4.c1 = t2.c1)
+ -> Seq Scan on t4
+ -> Hash
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t4.c1)
(14 rows)
----
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $3)
+ InitPlan 4 (returns $4)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Join Filter: (bmt1.c1 = bmt2.c1)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $3)
+ Filter: (c1 <> $4)
-> Seq Scan on t2 bmt2
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t3 bmt3
-> Tid Scan on t1 b2t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: ((c1 IS NOT NULL) AND (c1 = 1))
- InitPlan 4 (returns $3)
+ InitPlan 4 (returns $4)
-> Aggregate
-> Tid Scan on t1 b3t1
TID Cond: (ctid = '(1,1)'::tid)
Filter: (ctid = '(1,1)'::tid)
-> Tid Scan on t1 bmt1
TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 <> $3)
+ Filter: (c1 <> $4)
(46 rows)
----
-- No. L-2-3-4
EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
-> Seq Scan on t1 v1t1
-> Hash
- -> Seq Scan on t1 v1t1
+ -> Seq Scan on t1 v1t1_1
(5 rows)
/*+Leading(v1t1 v1t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
-INFO: hint syntax error at or near "Leading(v1t1 v1t1)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(v1t1 v1t1)"
DETAIL: Relation name "v1t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
error hint:
Leading(v1t1 v1t1)
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
-> Seq Scan on t1 v1t1
-> Hash
- -> Seq Scan on t1 v1t1
+ -> Seq Scan on t1 v1t1_1
(5 rows)
-- No. L-2-3-5
-- No. L-2-3-6
EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (r4t1.c1 = r4t1.c1)
+ Hash Cond: (r4t1.c1 = r4t1_1.c1)
-> Seq Scan on t1 r4t1
-> Hash
- -> Seq Scan on t1 r4t1
+ -> Seq Scan on t1 r4t1_1
(5 rows)
/*+Leading(r4t1 r4t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "Leading(r4t1 r4t1)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(r4t1 r4t1)"
DETAIL: Relation name "r4t1" is ambiguous.
LOG: pg_hint_plan:
used hint:
error hint:
Leading(r4t1 r4t1)
- QUERY PLAN
-----------------------------------
+ QUERY PLAN
+------------------------------------
Hash Join
- Hash Cond: (r4t1.c1 = r4t1.c1)
+ Hash Cond: (r4t1.c1 = r4t1_1.c1)
-> Seq Scan on t1 r4t1
-> Hash
- -> Seq Scan on t1 r4t1
+ -> Seq Scan on t1 r4t1_1
(5 rows)
-- No. L-2-3-7
---- No. L-2-4 VALUES clause
----
-- No. L-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;
+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;
QUERY PLAN
-------------------------------------------------
Nested Loop
(8 rows)
/*+ Leading(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;
+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:
not used hint:
(8 rows)
/*+ Leading(*VALUES* t1 t2) */
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
LOG: pg_hint_plan:
used hint:
Leading(*VALUES* t1 t2)
(7 rows)
-- No. L-2-4-2
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
QUERY PLAN
-------------------------------------------------------
Nested Loop
- Join Filter: (t1.c1 = "*VALUES*".column1)
-> Nested Loop
+ Join Filter: (t2.c1 = "*VALUES*_1".column1)
-> Hash Join
Hash Cond: (t2.c1 = "*VALUES*".column1)
-> Seq Scan on t2
-> Hash
-> Values Scan on "*VALUES*"
- -> Index Scan using t1_i1 on t1
- Index Cond: (c1 = t2.c1)
- -> Values Scan on "*VALUES*"
-(11 rows)
+ -> Materialize
+ -> Values Scan on "*VALUES*_1"
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(12 rows)
/*+ Leading(t4 t3 t2 t1) */
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
LOG: pg_hint_plan:
used hint:
not used hint:
QUERY PLAN
-------------------------------------------------------
Nested Loop
- Join Filter: (t1.c1 = "*VALUES*".column1)
-> Nested Loop
+ Join Filter: (t2.c1 = "*VALUES*_1".column1)
-> Hash Join
Hash Cond: (t2.c1 = "*VALUES*".column1)
-> Seq Scan on t2
-> Hash
-> Values Scan on "*VALUES*"
- -> Index Scan using t1_i1 on t1
- Index Cond: (c1 = t2.c1)
- -> Values Scan on "*VALUES*"
-(11 rows)
+ -> Materialize
+ -> Values Scan on "*VALUES*_1"
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(12 rows)
/*+ Leading(*VALUES* t3 t2 t1) */
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1;
-INFO: hint syntax error at or near "Leading(*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 "Leading(*VALUES* t3 t2 t1) "
DETAIL: Relation name "*VALUES*" is ambiguous.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
-------------------------------------------------------
Nested Loop
- Join Filter: (t1.c1 = "*VALUES*".column1)
-> Nested Loop
+ Join Filter: (t2.c1 = "*VALUES*_1".column1)
-> Hash Join
Hash Cond: (t2.c1 = "*VALUES*".column1)
-> Seq Scan on t2
-> Hash
-> Values Scan on "*VALUES*"
- -> Index Scan using t1_i1 on t1
- Index Cond: (c1 = t2.c1)
- -> Values Scan on "*VALUES*"
-(11 rows)
+ -> Materialize
+ -> Values Scan on "*VALUES*_1"
+ -> Index Scan using t1_i1 on t1
+ Index Cond: (c1 = t2.c1)
+(12 rows)
----
---- No. L-3-1 leading the order of table joins
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------
- Hash Join
- Hash Cond: (t3.c1 = t1.c1)
- -> Seq Scan on t3
- -> Hash
- -> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
-(10 rows)
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t1.c1)
+(9 rows)
----
---- No. L-3-2 GUC parameter to disable hints
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------
- Hash Join
- Hash Cond: (t3.c1 = t1.c1)
- -> Seq Scan on t3
- -> Hash
- -> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
-(10 rows)
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t1.c1)
+(9 rows)
Reset geqo_threshold;
-- No. L-3-2-3
EXPLAIN (COSTS false) SELECT * FROM s1.t3
JOIN s1.t2 ON (t3.c1 = t2.c1)
JOIN s1.t1 ON (t1.c1 = t3.c1);
- QUERY PLAN
-------------------------------------------------
- Hash Join
- Hash Cond: (t3.c1 = t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ Join Filter: (t2.c1 = t3.c1)
+ -> Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t1.c1)
(10 rows)
/*+Leading(t1 t2 t3)*/
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------
- Hash Join
- Hash Cond: (t3.c1 = t2.c1)
- -> Seq Scan on t3
- -> Hash
- -> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ Join Filter: (t2.c1 = t3.c1)
+ -> Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t1.c1)
(10 rows)
Reset join_collapse_limit;
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Hash Join
Hash Cond: (t1.c1 = t3.c1)
-> Hash Join
Hash Cond: (t1.c1 = t2.c1)
-> Append
-> Seq Scan on p2c1 t1
- -> Seq Scan on p2c1c1 t1
- -> Seq Scan on p2c1c2 t1
+ -> Seq Scan on p2c1c1 t1_1
+ -> Seq Scan on p2c1c2 t1_2
-> Hash
-> Append
-> Seq Scan on p2c2 t2
- -> Seq Scan on p2c2c1 t2
- -> Seq Scan on p2c2c2 t2
+ -> Seq Scan on p2c2c1 t2_1
+ -> Seq Scan on p2c2c2 t2_2
-> Hash
-> Append
-> Seq Scan on p2c3 t3
- -> Seq Scan on p2c3c1 t3
- -> Seq Scan on p2c3c2 t3
+ -> Seq Scan on p2c3c1 t3_1
+ -> Seq Scan on p2c3c2 t3_2
(18 rows)
-- No. L-3-3-2
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Hash Join
Hash Cond: (t1.c1 = t3.c1)
-> Hash Join
Hash Cond: (t1.c1 = t2.c1)
-> Append
-> Seq Scan on p2c1 t1
- -> Seq Scan on p2c1c1 t1
- -> Seq Scan on p2c1c2 t1
+ -> Seq Scan on p2c1c1 t1_1
+ -> Seq Scan on p2c1c2 t1_2
-> Hash
-> Append
-> Seq Scan on p2c2 t2
- -> Seq Scan on p2c2c1 t2
- -> Seq Scan on p2c2c2 t2
+ -> Seq Scan on p2c2c1 t2_1
+ -> Seq Scan on p2c2c2 t2_2
-> Hash
-> Append
-> Seq Scan on p2c3 t3
- -> Seq Scan on p2c3c1 t3
- -> Seq Scan on p2c3c2 t3
+ -> Seq Scan on p2c3c1 t3_1
+ -> Seq Scan on p2c3c2 t3_2
(18 rows)
----
EXPLAIN (COSTS false) SELECT * FROM s1.t1
JOIN s1.t2 ON (t1.c1 = t2.c1)
JOIN s1.t3 ON (t1.c1 = t3.c1);
-INFO: hint syntax error at or near "Leading(t2 t3 t1)Leading(t1 t2 t3)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t2 t3 t1)Leading(t1 t2 t3)"
DETAIL: Conflict leading hint.
LOG: pg_hint_plan:
used hint:
Leading(t2 t3 t1)
error hint:
- QUERY PLAN
-------------------------------------------------
- Hash Join
- Hash Cond: (t3.c1 = t1.c1)
- -> Seq Scan on t3
- -> Hash
- -> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
-(10 rows)
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t1.c1)
+(9 rows)
-- No. L-3-4-2
/*+Leading(t3 t1 t2)Leading(t2 t3 t1)Leading(t1 t2 t3)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1
JOIN s1.t2 ON (t1.c1 = t2.c1)
JOIN s1.t3 ON (t1.c1 = t3.c1);
-INFO: hint syntax error at or near "Leading(t3 t1 t2)Leading(t2 t3 t1)Leading(t1 t2 t3)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t3 t1 t2)Leading(t2 t3 t1)Leading(t1 t2 t3)"
DETAIL: Conflict leading hint.
-INFO: hint syntax error at or near "Leading(t2 t3 t1)Leading(t1 t2 t3)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t2 t3 t1)Leading(t1 t2 t3)"
DETAIL: Conflict leading hint.
LOG: pg_hint_plan:
used hint:
Leading(t2 t3 t1)
error hint:
- QUERY PLAN
-------------------------------------------------
- Hash Join
- Hash Cond: (t3.c1 = t1.c1)
- -> Seq Scan on t3
- -> Hash
- -> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
-(10 rows)
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t1.c1)
+(9 rows)
-- No. L-3-4-3
/*+Leading(t2 t3 t1)Leading()*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1
JOIN s1.t2 ON (t1.c1 = t2.c1)
JOIN s1.t3 ON (t1.c1 = t3.c1);
-INFO: hint syntax error at or near "Leading()"
+INFO: pg_hint_plan: hint syntax error at or near "Leading()"
DETAIL: Leading hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
EXPLAIN (COSTS false) SELECT * FROM s1.t1
JOIN s1.t2 ON (t1.c1 = t2.c1)
JOIN s1.t3 ON (t1.c1 = t3.c1);
-INFO: hint syntax error at or near "Leading()"
+INFO: pg_hint_plan: hint syntax error at or near "Leading()"
DETAIL: Leading hint requires at least two relations.
-INFO: hint syntax error at or near "Leading(t3 t1 t2)Leading(t2 t3 t1)Leading()"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t3 t1 t2)Leading(t2 t3 t1)Leading()"
DETAIL: Conflict leading hint.
LOG: pg_hint_plan:
used hint:
EXPLAIN (COSTS false) SELECT * FROM s1.t1
JOIN s1.t2 ON (t1.c1 = t2.c1)
JOIN s1.t3 ON (t1.c1 = t3.c1);
-INFO: hint syntax error at or near "Leading()"
+INFO: pg_hint_plan: hint syntax error at or near "Leading()"
DETAIL: Leading hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
EXPLAIN (COSTS false) SELECT * FROM s1.t1
JOIN s1.t2 ON (t1.c1 = t2.c1)
JOIN s1.t3 ON (t1.c1 = t3.c1);
-INFO: hint syntax error at or near "Leading(t1)"
+INFO: pg_hint_plan: hint syntax error at or near "Leading(t1)"
DETAIL: Leading hint requires at least two relations.
LOG: pg_hint_plan:
used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------------
- Hash Join
- Hash Cond: (t3.c1 = t1.c1)
- -> Seq Scan on t3
- -> Hash
- -> Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
-(10 rows)
+ QUERY PLAN
+------------------------------------------
+ Nested Loop
+ -> Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t1.c1)
+(9 rows)
----
---- No. L-3-6 specified Inner/Outer side
-- No. L-3-6-1
/*+Leading((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 "Leading((t2))"
+INFO: pg_hint_plan: hint syntax error at or near "Leading((t2))"
DETAIL: Leading hint requires two sets of relations when parentheses nests.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
------------------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ Nested Loop
-> Merge Join
- Merge Cond: (t1.c1 = t4.c1)
+ Merge Cond: (t1.c1 = t2.c1)
-> Merge Join
Merge Cond: (t1.c1 = t3.c1)
-> Index Scan using t1_i1 on t1
-> Index Scan using t3_i1 on t3
- -> Index Scan using t4_i1 on t4
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t4_i1 on t4
+ Index Cond: (c1 = t1.c1)
(12 rows)
-- No. L-3-6-2
Join Filter: (t1.c1 = t4.c1)
-> Nested Loop
Join Filter: (t2.c1 = t1.c1)
- -> Merge Join
- Merge Cond: (t2.c1 = t3.c1)
+ -> Nested Loop
-> Index Scan using t2_i1 on t2
-> Index Scan using t3_i1 on t3
+ Index Cond: (c1 = t2.c1)
-> Index Scan using t1_i1 on t1
Index Cond: (c1 = t3.c1)
-> Index Scan using t4_i1 on t4
-- No. L-3-6-3
/*+Leading((t2 t3 t4))*/
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 "Leading((t2 t3 t4))"
+INFO: pg_hint_plan: hint syntax error at or near "Leading((t2 t3 t4))"
DETAIL: Leading hint requires two sets of relations when parentheses nests.
LOG: pg_hint_plan:
used hint:
QUERY PLAN
------------------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ Nested Loop
-> Merge Join
- Merge Cond: (t1.c1 = t4.c1)
+ Merge Cond: (t1.c1 = t2.c1)
-> Merge Join
Merge Cond: (t1.c1 = t3.c1)
-> Index Scan using t1_i1 on t1
-> Index Scan using t3_i1 on t3
- -> Index Scan using t4_i1 on t4
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+ -> Index Scan using t4_i1 on t4
+ Index Cond: (c1 = t1.c1)
(12 rows)
-- No. L-3-6-4
-- No. L-3-6-5
/*+Leading((((t1 t3) t4) 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 ")"
+INFO: pg_hint_plan: hint syntax error at or near ")"
DETAIL: Unrecognized hint keyword ")".
LOG: pg_hint_plan:
used hint: