From ae410a85ed681c3e80933e9a07897d59a6188d1f Mon Sep 17 00:00:00 2001 From: Takashi Suzuki Date: Tue, 6 Aug 2013 14:03:35 +0900 Subject: [PATCH] =?utf8?q?Leading=E3=83=92=E3=83=B3=E3=83=88=E5=8F=A5?= =?utf8?q?=E3=81=AE=E4=BB=95=E6=A7=98=E5=A4=89=E6=9B=B4=E3=81=AB=E3=81=9D?= =?utf8?q?=E3=81=A3=E3=81=A6[=E7=B5=90=E5=90=88=E9=A0=86=E5=BA=8F]?= =?utf8?q?=E8=A9=A6=E9=A8=93=E3=81=AEPG9.1=E7=94=A8=E4=BA=88=E6=B8=AC?= =?utf8?q?=E7=B5=90=E6=9E=9C=E3=82=92=E5=A4=89=E6=9B=B4=E3=81=97=E3=81=9F?= =?utf8?q?=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- expected/ut-L-9.1.out | 335 +++++++++++++++++++++++++++++--------------------- 1 file changed, 194 insertions(+), 141 deletions(-) diff --git a/expected/ut-L-9.1.out b/expected/ut-L-9.1.out index f2917dd..ef054da 100644 --- a/expected/ut-L-9.1.out +++ b/expected/ut-L-9.1.out @@ -198,27 +198,26 @@ error hint: 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; LOG: pg_hint_plan: used hint: -Leading(t5 t2 t3 t1) not used hint: +Leading(t5 t2 t3 t1) duplication hint: error hint: QUERY PLAN ------------------------------------------------ - Nested Loop + Merge Join + Merge Cond: (t1.c1 = t2.c1) -> 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 - -> Seq Scan on t2 - -> Index Scan using t4_i1 on t4 - Index Cond: (c1 = t1.c1) -(13 rows) + Merge Cond: (t1.c1 = t4.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 +(12 rows) ---- ---- No. L-1-4 conflict table name @@ -276,9 +275,9 @@ DETAIL: Relation name "t1" is ambiguous. LOG: pg_hint_plan: used hint: not used hint: -Leading(t1 t2 t3 t1) duplication hint: error hint: +Leading(t1 t2 t3 t1) QUERY PLAN ------------------------------------------------ @@ -300,25 +299,25 @@ error hint: 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; LOG: pg_hint_plan: used hint: -Leading(s1.t1 t2 t3 s2.t1) not used hint: +Leading(s1.t1 t2 t3 s2.t1) duplication hint: error hint: QUERY PLAN ------------------------------------------------ - Nested Loop - -> Nested Loop + Merge Join + Merge Cond: (s1.t1.c1 = s2.t1.c1) + -> Merge Join + Merge Cond: (s1.t1.c1 = t2.c1) -> Merge Join - Merge Cond: (t3.c1 = t2.c1) + Merge Cond: (s1.t1.c1 = t3.c1) + -> Index Scan using t1_i1 on t1 -> Index Scan using t3_i1 on t3 - -> Sort - Sort Key: t2.c1 - -> Seq Scan on t2 - -> Index Scan using t1_pkey on t1 - Index Cond: (c1 = t2.c1) - -> Index Scan using t1_i1 on t1 - Index Cond: (c1 = t2.c1) + -> Index Scan using t2_i1 on t2 + -> Sort + Sort Key: s2.t1.c1 + -> Seq Scan on t1 (12 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; @@ -438,11 +437,12 @@ error hint: -> Seq Scan on t2 (30 rows) -/*+Leading(st1 st2 st3 st4 t4 t2 t3 t1)*/ +/*+Leading(st1 st2 st3 st4)Leading(t4 t2 t3 t1)*/ EXPLAIN (COSTS false) SELECT *, (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2, s1.t3 st3, s1.t4 st4 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; LOG: pg_hint_plan: used hint: -Leading(st1 st2 st3 st4 t4 t2 t3 t1) +Leading(st1 st2 st3 st4) +Leading(t4 t2 t3 t1) not used hint: duplication hint: error hint: @@ -513,30 +513,30 @@ error hint: -- 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)" +DETAIL: Relation name "t4" is duplicated. LOG: pg_hint_plan: used hint: -Leading(t4 t2 t3 t1 t4) not used hint: duplication hint: error hint: +Leading(t4 t2 t3 t1 t4) - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +------------------------------------------------ Merge Join Merge Cond: (t1.c1 = t2.c1) - -> Index Scan using t1_i1 on t1 + -> Merge Join + Merge Cond: (t1.c1 = t4.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 - -> 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 -(14 rows) + -> Seq Scan on t2 +(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; @@ -568,30 +568,30 @@ Leading(t4 t2 t3 t4) -- 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)" +DETAIL: Relation name "t4" is duplicated. LOG: pg_hint_plan: used hint: -Leading(t4 t2 t3 t1 t4 t2 t3 t1) not used hint: duplication hint: error hint: +Leading(t4 t2 t3 t1 t4 t2 t3 t1) - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +------------------------------------------------ Merge Join Merge Cond: (t1.c1 = t2.c1) - -> Index Scan using t1_i1 on t1 + -> Merge Join + Merge Cond: (t1.c1 = t4.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 - -> 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 -(14 rows) + -> Seq Scan on t2 +(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; @@ -927,26 +927,25 @@ EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), 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; LOG: pg_hint_plan: used hint: -Leading(t4 t3 t2 t1) not used hint: +Leading(t4 t3 t2 t1) duplication hint: error hint: - QUERY PLAN ---------------------------------------------- + QUERY PLAN +------------------------------------------------------- Nested Loop - Join Filter: (t2.c1 = "*VALUES*".column1) - -> Values Scan on "*VALUES*" - -> Hash Join - Hash Cond: (t3.c1 = t2.c1) + -> Nested Loop -> Hash Join - Hash Cond: (t3.c1 = t4.c1) - -> Seq Scan on t3 - -> Hash - -> Seq Scan on t4 - -> Hash + Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 -(12 rows) + -> Hash + -> Values Scan on "*VALUES*" + -> Index Scan using t3_i1 on t3 + Index Cond: (c1 = "*VALUES*".column1) + -> Index Scan using t4_i1 on t4 + Index Cond: (c1 = "*VALUES*".column1) +(11 rows) -- No. L-1-6-9 EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT st1.c1 FROM s1.t1 st1, s1.t1 st2, s1.t1 st3, s1.t1 st4 WHERE st1.c1 = st2.c1 AND st1.c1 = st3.c1 AND st1.c1 = st4.c1) SELECT * FROM c1 ct1, c1 ct2, c1 ct3, c1 ct4 WHERE ct1.c1 = ct2.c1 AND ct1.c1 = ct3.c1 AND ct1.c1 = ct4.c1; @@ -987,11 +986,12 @@ EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT st1.c1 FROM s1.t1 st1, s1.t1 st2, s -> CTE Scan on c1 ct4 (33 rows) -/*+Leading(ct4 ct3 ct2 ct1 st4 st3 st2 st1)*/ +/*+Leading(ct4 ct3 ct2 ct1)Leading(st4 st3 st2 st1)*/ EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT st1.c1 FROM s1.t1 st1, s1.t1 st2, s1.t1 st3, s1.t1 st4 WHERE st1.c1 = st2.c1 AND st1.c1 = st3.c1 AND st1.c1 = st4.c1) SELECT * FROM c1 ct1, c1 ct2, c1 ct3, c1 ct4 WHERE ct1.c1 = ct2.c1 AND ct1.c1 = ct3.c1 AND ct1.c1 = ct4.c1; LOG: pg_hint_plan: used hint: -Leading(ct4 ct3 ct2 ct1 st4 st3 st2 st1) +Leading(ct4 ct3 ct2 ct1) +Leading(st4 st3 st2 st1) not used hint: duplication hint: error hint: @@ -1145,27 +1145,26 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, (SELECT t4.c1 FROM s1.t 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; LOG: pg_hint_plan: used hint: -Leading(st4 t2 t3 t1) not used hint: +Leading(st4 t2 t3 t1) duplication hint: error hint: QUERY PLAN ------------------------------------------------ - Nested Loop + Merge Join + Merge Cond: (t1.c1 = t2.c1) -> 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 - -> Seq Scan on t2 - -> Index Scan using t4_i1 on t4 - Index Cond: (c1 = t1.c1) -(13 rows) + Merge Cond: (t1.c1 = t4.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 +(12 rows) /*+Leading(t4 t2 t3 t1)*/ 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; @@ -1258,7 +1257,9 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 (47 rows) /*+ -Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2) +Leading(bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) */ EXPLAIN (COSTS false) SELECT max(bmt1.c1), ( @@ -1270,7 +1271,9 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 ; LOG: pg_hint_plan: used hint: -Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2) +Leading(bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) not used hint: duplication hint: error hint: @@ -1405,7 +1408,10 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 (63 rows) /*+ -Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3) +Leading(bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) +Leading(b3t4 b3t1 b3t2 b3t3) */ EXPLAIN (COSTS false) SELECT max(bmt1.c1), ( @@ -1419,7 +1425,10 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 ; LOG: pg_hint_plan: used hint: -Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3) +Leading(bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) +Leading(b3t4 b3t1 b3t2 b3t3) not used hint: duplication hint: error hint: @@ -1655,7 +1664,9 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 (48 rows) /*+ -Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2) +Leading(bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) */ EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' @@ -1667,7 +1678,9 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2 ; LOG: pg_hint_plan: used hint: -Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2) +Leading(bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) not used hint: duplication hint: error hint: @@ -1804,7 +1817,10 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 (64 rows) /*+ -Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3) +Leading(bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) +Leading(b3t4 b3t1 b3t2 b3t3) */ EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' @@ -1818,7 +1834,10 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 ; LOG: pg_hint_plan: used hint: -Leading(bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3) +Leading(bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) +Leading(b3t4 b3t1 b3t2 b3t3) not used hint: duplication hint: error hint: @@ -1963,7 +1982,9 @@ AND bmt1.c1 = c2.c1 (53 rows) /*+ -Leading(c2 c1 bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2) +Leading(c2 c1 bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) */ EXPLAIN (COSTS false) WITH c1 (c1) AS ( @@ -1980,7 +2001,9 @@ AND bmt1.c1 = c2.c1 ; LOG: pg_hint_plan: used hint: -Leading(c2 c1 bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2) +Leading(c2 c1 bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) not used hint: duplication hint: error hint: @@ -2137,7 +2160,10 @@ AND bmt1.c1 = c3.c1 (72 rows) /*+ -Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3) +Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) +Leading(b3t4 b3t1 b3t2 b3t3) */ EXPLAIN (COSTS false) WITH c1 (c1) AS ( @@ -2158,7 +2184,10 @@ AND bmt1.c1 = c3.c1 ; LOG: pg_hint_plan: used hint: -Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4 b1t2 b1t3 b1t4 b1t1 b2t3 b2t4 b2t1 b2t2 b3t4 b3t1 b3t2 b3t3) +Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4) +Leading(b1t2 b1t3 b1t4 b1t1) +Leading(b2t3 b2t4 b2t1 b2t2) +Leading(b3t4 b3t1 b3t2 b3t3) not used hint: duplication hint: error hint: @@ -2287,7 +2316,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1 (27 rows) /*+ -Leading(c1 bmt1 b1t1 b2t1 b3t1) +Leading(c1 bmt1) */ EXPLAIN (COSTS false) WITH c1 (c1) AS ( @@ -2303,7 +2332,7 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1 ; LOG: pg_hint_plan: used hint: -Leading(c1 bmt1 b1t1 b2t1 b3t1) +Leading(c1 bmt1) not used hint: duplication hint: error hint: @@ -2393,7 +2422,10 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3 (35 rows) /*+ -Leading(c1 bmt2 b1t2 b2t2 b3t2 bmt1 b1t1 b2t1 b3t1) +Leading(c1 bmt2 bmt1) +Leading(b1t2 b1t1) +Leading(b2t2 b2t1) +Leading(b3t2 b3t1) */ EXPLAIN (COSTS false) WITH c1 (c1) AS ( @@ -2410,7 +2442,10 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3 ; LOG: pg_hint_plan: used hint: -Leading(c1 bmt2 b1t2 b2t2 b3t2 bmt1 b1t1 b2t1 b3t1) +Leading(c1 bmt2 bmt1) +Leading(b1t2 b1t1) +Leading(b2t2 b2t1) +Leading(b3t2 b3t1) not used hint: duplication hint: error hint: @@ -2539,7 +2574,10 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 (67 rows) /*+ -Leading(c1 bmt4 b1t4 b2t4 b3t4 bmt3 b1t3 b2t3 b3t3 bmt2 b1t2 b2t2 b3t2 bmt1 b1t1 b2t1 b3t1) +Leading(c1 bmt4 bmt3 bmt2 bmt1) +Leading(b1t4 b1t3 b1t2 b1t1) +Leading(b2t4 b2t3 b2t2 b2t1) +Leading(b3t4 b3t3 b3t2 b3t1) */ EXPLAIN (COSTS false) WITH c1 (c1) AS ( @@ -2555,7 +2593,10 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3 ; LOG: pg_hint_plan: used hint: -Leading(c1 bmt4 b1t4 b2t4 b3t4 bmt3 b1t3 b2t3 b3t3 bmt2 b1t2 b2t2 b3t2 bmt1 b1t1 b2t1 b3t1) +Leading(c1 bmt4 bmt3 bmt2 bmt1) +Leading(b1t4 b1t3 b1t2 b1t1) +Leading(b2t4 b2t3 b2t2 b2t1) +Leading(b3t4 b3t3 b3t2 b3t1) not used hint: duplication hint: error hint: @@ -2695,7 +2736,8 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' (46 rows) /*+ -Leading(c1 bmt4 b1t4 bmt3 b1t3 bmt2 b1t2 bmt1 b1t1 b2t1 b3t1) +Leading(c1 bmt4 bmt3 bmt2 bmt1) +Leading(b1t4 b1t3 b1t2 b1t1) */ EXPLAIN (COSTS false) WITH c1 (c1) AS ( @@ -2711,7 +2753,8 @@ SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' ; LOG: pg_hint_plan: used hint: -Leading(c1 bmt4 b1t4 bmt3 b1t3 bmt2 b1t2 bmt1 b1t1 b2t1 b3t1) +Leading(c1 bmt4 bmt3 bmt2 bmt1) +Leading(b1t4 b1t3 b1t2 b1t1) not used hint: duplication hint: error hint: @@ -3030,22 +3073,24 @@ EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; TID Cond: (ctid = '(1,1)'::tid) (39 rows) -/*+Leading( -b1t1 b1t2 b1t3 b1t4 -b2t1 b2t2 b2t3 b2t4 r2_ -) */ +/*+ +Leading(b1t1 b1t2 b1t3 b1t4 r2_) +Leading(b2t1 b2t2 b2t3 b2t4 r2_) +*/ EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: pg_hint_plan: used hint: -Leading(b1t1 b1t2 b1t3 b1t4 b2t1 b2t2 b2t3 b2t4 r2_) +Leading(b1t1 b1t2 b1t3 b1t4 r2_) not used hint: +Leading(b2t1 b2t2 b2t3 b2t4 r2_) duplication hint: error hint: LOG: pg_hint_plan: used hint: -Leading(b1t1 b1t2 b1t3 b1t4 b2t1 b2t2 b2t3 b2t4 r2_) +Leading(b2t1 b2t2 b2t3 b2t4 r2_) not used hint: +Leading(b1t1 b1t2 b1t3 b1t4 r2_) duplication hint: error hint: @@ -3307,30 +3352,36 @@ EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; TID Cond: (ctid = '(1,1)'::tid) (59 rows) -/*+Leading( -b1t1 b1t2 b1t3 b1t4 -b2t1 b2t2 b2t3 b2t4 -b3t1 b3t2 b3t3 b3t4 r3_ -) */ +/*+ +Leading(b1t1 b1t2 b1t3 b1t4 r3_) +Leading(b2t1 b2t2 b2t3 b2t4 r3_) +Leading(b3t1 b3t2 b3t3 b3t4 r3_) +*/ EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)'; LOG: pg_hint_plan: used hint: -Leading(b1t1 b1t2 b1t3 b1t4 b2t1 b2t2 b2t3 b2t4 b3t1 b3t2 b3t3 b3t4 r3_) +Leading(b1t1 b1t2 b1t3 b1t4 r3_) not used hint: +Leading(b2t1 b2t2 b2t3 b2t4 r3_) +Leading(b3t1 b3t2 b3t3 b3t4 r3_) duplication hint: error hint: LOG: pg_hint_plan: used hint: -Leading(b1t1 b1t2 b1t3 b1t4 b2t1 b2t2 b2t3 b2t4 b3t1 b3t2 b3t3 b3t4 r3_) +Leading(b2t1 b2t2 b2t3 b2t4 r3_) not used hint: +Leading(b1t1 b1t2 b1t3 b1t4 r3_) +Leading(b3t1 b3t2 b3t3 b3t4 r3_) duplication hint: error hint: LOG: pg_hint_plan: used hint: -Leading(b1t1 b1t2 b1t3 b1t4 b2t1 b2t2 b2t3 b2t4 b3t1 b3t2 b3t3 b3t4 r3_) +Leading(b3t1 b3t2 b3t3 b3t4 r3_) not used hint: +Leading(b1t1 b1t2 b1t3 b1t4 r3_) +Leading(b2t1 b2t2 b2t3 b2t4 r3_) duplication hint: error hint: @@ -3540,23 +3591,24 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1; LOG: pg_hint_plan: used hint: -Leading(t3 t1 t2) not used hint: +Leading(t3 t1 t2) duplication hint: error hint: - QUERY PLAN ---------------------------------------------- - Nested Loop - Join Filter: (t1.c1 = "*VALUES*".column1) - -> Values Scan on "*VALUES*" - -> Merge Join - Merge Cond: (t1.c1 = t2.c1) - -> Index Scan using t1_i1 on t1 - -> Sort - Sort Key: t2.c1 + 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: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 -(9 rows) + -> Hash + -> Values Scan on "*VALUES*" +(10 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; @@ -3602,26 +3654,27 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t4 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1 AND t1.c1 = t4.c1; LOG: pg_hint_plan: used hint: -Leading(t4 t3 t2 t1) not used hint: +Leading(t4 t3 t2 t1) duplication hint: error hint: - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------- Nested Loop Join Filter: (t1.c1 = "*VALUES*".column1) - -> Nested Loop - Join Filter: ("*VALUES*".column1 = "*VALUES*".column1) - -> Values Scan on "*VALUES*" - -> Values Scan on "*VALUES*" -> Merge Join Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 -> Sort Sort Key: t2.c1 - -> Seq Scan on t2 -(12 rows) + -> Hash Join + Hash Cond: (t2.c1 = "*VALUES*".column1) + -> Seq Scan on t2 + -> Hash + -> Values Scan on "*VALUES*" + -> Values Scan on "*VALUES*" +(13 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; @@ -3630,9 +3683,9 @@ DETAIL: Relation name "*VALUES*" is ambiguous. LOG: pg_hint_plan: used hint: not used hint: -Leading(*VALUES* t3 t2 t1) duplication hint: error hint: +Leading(*VALUES* t3 t2 t1) QUERY PLAN ------------------------------------------------------------- -- 2.11.0