LOAD 'pg_hint_plan'; SET pg_hint_plan.enable_hint TO on; SET pg_hint_plan.debug_print TO on; SET client_min_messages TO LOG; SET search_path TO public; \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) ---- ---- No. R-1-1 specified pattern of the object name ---- -- No. R-1-1-1 \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-1-1-2 \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.t1 t_1, s1.t2 t_2 WHERE t_1.c1 = t_2.c1; LOG: pg_hint_plan: used hint: not used hint: Rows(t1 t2 #1) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t_1.c1 = t_2.c1) -> Index Scan using t1_i1 on t1 t_1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t_2.c1 -> Seq Scan on t2 t_2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-1-1-3 \o results/ut-R.tmpout /*+Rows(t_1 t_2 #1)*/ EXPLAIN SELECT * FROM s1.t1 t_1, s1.t2 t_2 WHERE t_1.c1 = t_2.c1; LOG: pg_hint_plan: used hint: Rows(t_1 t_2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t_1.c1 = t_2.c1) -> Index Scan using t1_i1 on t1 t_1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t_2.c1 -> Seq Scan on t2 t_2 (cost=xxx rows=100 width=xxx) (6 rows) ---- ---- No. R-1-2 specified schema name in the hint option ---- -- No. R-1-2-1 \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-1-2-2 \o results/ut-R.tmpout /*+Rows(s1.t1 s1.t2 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: not used hint: Rows(s1.t1 s1.t2 #1) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) ---- ---- No. R-1-3 table doesn't exist in the hint option ---- -- No. R-1-3-1 \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-1-3-2 \o results/ut-R.tmpout /*+Rows(t3 t4 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: not used hint: Rows(t3 t4 #1) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) ---- ---- No. R-1-4 conflict table name ---- -- No. R-1-4-1 \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-1-4-2 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t1_1.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t1_1.c1 -> Seq Scan on t1 t1_1 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout /*+Rows(t1 t1 #1)*/ EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1; INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t1 #1)" DETAIL: Relation name "t1" is ambiguous. LOG: pg_hint_plan: used hint: not used hint: duplication hint: error hint: Rows(t1 t1 #1) \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t1_1.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t1_1.c1 -> Seq Scan on t1 t1_1 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout /*+Rows(s1.t1 s2.t1 #1)*/ EXPLAIN SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = s2.t1.c1; LOG: pg_hint_plan: used hint: not used hint: Rows(s1.t1 s2.t1 #1) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t1_1.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t1_1.c1 -> Seq Scan on t1 t1_1 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = s2t1.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = s2t1.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: s2t1.c1 -> Seq Scan on t1 s2t1 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout /*+Rows(t1 s2t1 #1)*/ EXPLAIN SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = s2t1.c1; LOG: pg_hint_plan: used hint: Rows(s2t1 t1 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = s2t1.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: s2t1.c1 -> Seq Scan on t1 s2t1 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-1-4-3 \o results/ut-R.tmpout EXPLAIN SELECT *, (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) InitPlan 1 (returns $0) -> Aggregate (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1_1.c1 = t2_1.c1) -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2_1.c1 -> Seq Scan on t2 t2_1 (cost=xxx rows=100 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (14 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT *, (SELECT max(t1.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) InitPlan 1 (returns $0) -> Aggregate (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1_1.c1 = t2_1.c1) -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2_1.c1 -> Seq Scan on t2 t2_1 (cost=xxx rows=100 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (14 rows) \o results/ut-R.tmpout /*+Rows(st1 st2 #1)Rows(t1 t2 #1)*/ EXPLAIN SELECT *, (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1) FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(st1 st2 #1) Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) InitPlan 1 (returns $0) -> Aggregate (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (st1.c1 = st2.c1) -> Index Only Scan using t1_i1 on t1 st1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: st2.c1 -> Seq Scan on t2 st2 (cost=xxx rows=100 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (14 rows) ---- ---- No. R-1-5 conflict table name ---- -- No. R-1-5-1 \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-1-5-2 \o results/ut-R.tmpout /*+Rows(t1 t1 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t1 #1)" DETAIL: Relation name "t1" is duplicated. LOG: pg_hint_plan: used hint: not used hint: duplication hint: error hint: Rows(t1 t1 #1) \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-1-5-3 \o results/ut-R.tmpout /*+(t1 t1)(t2 t2)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "(t1 t1)(t2 t2)" DETAIL: Unrecognized hint keyword "". \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.t1, s1.t2, s1.t3 WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (t1.c1 = t3.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Index Scan using t3_i1 on t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (9 rows) \o results/ut-R.tmpout /*+(t1 t2 t1 t2)*/ EXPLAIN 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: pg_hint_plan: hint syntax error at or near "(t1 t2 t1 t2)" DETAIL: Unrecognized hint keyword "". \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (t1.c1 = t3.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Index Scan using t3_i1 on t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) -> Index Scan using t4_i1 on t4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = t1.c1) (12 rows) ---- ---- No. R-1-6 object type for the hint ---- -- No. R-1-6-1 \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-1-6-2 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.p1 t1, s1.p1 t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------ Hash Join (cost=xxx rows=301 width=xxx) Hash Cond: (t1.c1 = t2.c1) -> Append (cost=xxx rows=301 width=xxx) -> Seq Scan on p1 t1 (cost=xxx rows=1 width=xxx) -> Seq Scan on p1c1 t1_1 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c2 t1_2 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c3 t1_3 (cost=xxx rows=100 width=xxx) -> Hash (cost=xxx rows=301 width=xxx) -> Append (cost=xxx rows=301 width=xxx) -> Seq Scan on p1 t2 (cost=xxx rows=1 width=xxx) -> Seq Scan on p1c1 t2_1 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c2 t2_2 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c3 t2_3 (cost=xxx rows=100 width=xxx) (13 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.p1 t1, s1.p1 t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------ Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (t1.c1 = t2.c1) -> Append (cost=xxx rows=301 width=xxx) -> Seq Scan on p1 t1 (cost=xxx rows=1 width=xxx) -> Seq Scan on p1c1 t1_1 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c2 t1_2 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c3 t1_3 (cost=xxx rows=100 width=xxx) -> Hash (cost=xxx rows=301 width=xxx) -> Append (cost=xxx rows=301 width=xxx) -> Seq Scan on p1 t2 (cost=xxx rows=1 width=xxx) -> Seq Scan on p1c1 t2_1 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c2 t2_2 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c3 t2_3 (cost=xxx rows=100 width=xxx) (13 rows) -- No. R-1-6-3 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.ul1 t1, s1.ul1 t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------- Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (t1.c1 = t2.c1) -> Seq Scan on ul1 t1 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on ul1 t2 (cost=xxx rows=1130 width=xxx) (5 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.ul1 t1, s1.ul1 t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------- Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (t1.c1 = t2.c1) -> Seq Scan on ul1 t1 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on ul1 t2 (cost=xxx rows=1130 width=xxx) (5 rows) -- No. R-1-6-4 CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL); \o results/ut-R.tmpout EXPLAIN SELECT * FROM tm1 t1, tm1 t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------- Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (t1.c1 = t2.c1) -> Seq Scan on tm1 t1 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on tm1 t2 (cost=xxx rows=1130 width=xxx) (5 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM tm1 t1, tm1 t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------- Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (t1.c1 = t2.c1) -> Seq Scan on tm1 t1 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on tm1 t2 (cost=xxx rows=1130 width=xxx) (5 rows) -- No. R-1-6-5 CREATE TEMP TABLE t_pg_class WITH OIDS AS SELECT * from pg_class LIMIT 100; \o results/ut-R.tmpout EXPLAIN SELECT * FROM t_pg_class t1, t_pg_class t2 WHERE t1.oid = t2.oid; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------ Hash Join (cost=xxx rows=310 width=xxx) Hash Cond: (t1.oid = t2.oid) -> Seq Scan on t_pg_class t1 (cost=xxx rows=310 width=xxx) -> Hash (cost=xxx rows=310 width=xxx) -> Seq Scan on t_pg_class t2 (cost=xxx rows=310 width=xxx) (5 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM t_pg_class t1, t_pg_class t2 WHERE t1.oid = t2.oid; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------ Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (t1.oid = t2.oid) -> Seq Scan on t_pg_class t1 (cost=xxx rows=310 width=xxx) -> Hash (cost=xxx rows=310 width=xxx) -> Seq Scan on t_pg_class t2 (cost=xxx rows=310 width=xxx) (5 rows) -- No. R-1-6-6 -- refer ut-fdw.sql -- No. R-1-6-7 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.f1() t1, s1.f1() t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t2.c1) -> Function Scan on f1 t1 (cost=xxx rows=1 width=xxx) -> Function Scan on f1 t2 (cost=xxx rows=1 width=xxx) (4 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.f1() t1, s1.f1() t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t2.c1) -> Function Scan on f1 t1 (cost=xxx rows=1 width=xxx) -> Function Scan on f1 t2 (cost=xxx rows=1 width=xxx) (4 rows) -- No. R-1-6-8 \o results/ut-R.tmpout EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------- Hash Join (cost=xxx rows=3 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) -> Hash (cost=xxx rows=3 width=xxx) -> Values Scan on "*VALUES*" (cost=xxx rows=3 width=xxx) (5 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: not used hint: Rows(t1 t2 #1) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------- Hash Join (cost=xxx rows=3 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) -> Hash (cost=xxx rows=3 width=xxx) -> Values Scan on "*VALUES*" (cost=xxx rows=3 width=xxx) (5 rows) \o results/ut-R.tmpout /*+Rows(*VALUES* t2 #1)*/ EXPLAIN SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS t1 (c1, c2, c3, c4), s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(*VALUES* t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------- Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) -> Hash (cost=xxx rows=3 width=xxx) -> Values Scan on "*VALUES*" (cost=xxx rows=3 width=xxx) (5 rows) -- No. R-1-6-9 \o results/ut-R.tmpout EXPLAIN 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; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) CTE c1 -> Aggregate (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1_1.c1 = t2.c1) -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = c1.c1) (12 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)Rows(t1 c1 +1)*/ EXPLAIN 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; LOG: pg_hint_plan: used hint: Rows(c1 t1 +1) Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=2 width=xxx) CTE c1 -> Aggregate (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1_1.c1 = t2.c1) -> Index Only Scan using t1_i1 on t1 t1_1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = c1.c1) (12 rows) -- No. R-1-6-10 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.v1 t1, s1.v1 t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------- Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (v1t1.c1 = v1t1_1.c1) -> Seq Scan on t1 v1t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 v1t1_1 (cost=xxx rows=1000 width=xxx) (5 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.v1 t1, s1.v1 t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: not used hint: Rows(t1 t2 #1) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------- Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (v1t1.c1 = v1t1_1.c1) -> Seq Scan on t1 v1t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 v1t1_1 (cost=xxx rows=1000 width=xxx) (5 rows) \o results/ut-R.tmpout /*+Rows(v1t1 v1t1_ #1)*/ EXPLAIN SELECT * FROM s1.v1 t1, s1.v1_ t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(v1t1 v1t1_ #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------- Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (v1t1.c1 = v1t1_.c1) -> Seq Scan on t1 v1t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 v1t1_ (cost=xxx rows=1000 width=xxx) (5 rows) -- No. R-1-6-11 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.c1 = (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1); \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) InitPlan 1 (returns $0) -> Aggregate (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (st1.c1 = st2.c1) -> Index Only Scan using t1_i1 on t1 st1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: st2.c1 -> Seq Scan on t2 st2 (cost=xxx rows=100 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = $0) -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (c1 = $0) (13 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)Rows(st1 st2 #1)*/ EXPLAIN (COSTS true) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.c1 = (SELECT max(st1.c1) FROM s1.t1 st1, s1.t2 st2 WHERE st1.c1 = st2.c1); LOG: pg_hint_plan: used hint: Rows(st1 st2 #1) Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) InitPlan 1 (returns $0) -> Aggregate (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (st1.c1 = st2.c1) -> Index Only Scan using t1_i1 on t1 st1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: st2.c1 -> Seq Scan on t2 st2 (cost=xxx rows=100 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = $0) -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (c1 = $0) (13 rows) -- -- There are cases where difference in the measured value and predicted value -- depending upon the version of PostgreSQL -- \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout /*+Rows(t1 st2 #1)*/ EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1; LOG: pg_hint_plan: used hint: not used hint: Rows(st2 t1 #1) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.t1, (SELECT t2.c1 FROM s1.t2) st2 WHERE t1.c1 = st2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) ---- ---- No. R-1-7 specified number of conditions ---- -- No. R-1-7-1 \o results/ut-R.tmpout /*+Rows(t1 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "" DETAIL: Rows hint requires at least two relations. LOG: pg_hint_plan: used hint: not used hint: duplication hint: error hint: Rows(t1 #1) \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-1-7-2 \o results/ut-R.tmpout /*+Rows(t1 t2 1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "1" DETAIL: Unrecognized rows value type notation. LOG: pg_hint_plan: used hint: not used hint: duplication hint: error hint: Rows(t1 t2 1) \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-1-7-3 \o results/ut-R.tmpout /*+Rows(t1 t2 #notrows)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "notrows" DETAIL: Rows hint requires valid number as rows estimation. LOG: pg_hint_plan: used hint: not used hint: duplication hint: error hint: Rows(t1 t2 #notrows) \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) ---- ---- No. R-2-1 some complexity query blocks ---- -- No. R-2-1-1 \o results/ut-R.tmpout /*+ Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) */ EXPLAIN SELECT max(bmt1.c1), ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ), ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 ; LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(bmt1 bmt2) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(bmt1 bmt2 bmt3) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(bmt1 bmt2 bmt3 bmt4) Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) InitPlan 1 (returns $1) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (43 rows) \o results/ut-R.tmpout /*+ Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1) Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1) Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1) */ EXPLAIN SELECT max(bmt1.c1), ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ), ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 ; LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(bmt1 bmt2) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(bmt1 bmt2 bmt3) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(bmt1 bmt2 bmt3 bmt4) Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Rows(b1t2 b1t3 #1) Rows(b2t3 b2t4 #1) Rows(bmt1 bmt2 #1) Rows(b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t3 b2t4 #1) Rows(bmt1 bmt2 bmt3 #1) Rows(b1t1 b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t2 b2t3 b2t4 #1) Rows(bmt1 bmt2 bmt3 bmt4 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) InitPlan 1 (returns $1) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b2t1.c1 = b2t3.c1) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (43 rows) -- No. R-2-1-2 \o results/ut-R.tmpout /*+ Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4) */ EXPLAIN SELECT max(bmt1.c1), ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ), ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ), ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1 ) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 ; LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(b3t1 b3t4) MergeJoin(bmt1 bmt2) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(b3t1 b3t2 b3t4) HashJoin(bmt1 bmt2 bmt3) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(b3t1 b3t2 b3t3 b3t4) NestLoop(bmt1 bmt2 bmt3 bmt4) Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) InitPlan 1 (returns $1) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) InitPlan 3 (returns $5) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b3t1.c1 = b3t2.c1) -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b3t1.c1) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (56 rows) \o results/ut-R.tmpout /*+ Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4) Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1) Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1) Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1) Rows(b3t4 b3t1 #1)Rows(b3t4 b3t1 b3t2 #1)Rows(b3t1 b3t2 b3t3 b3t4 #1) */ EXPLAIN SELECT max(bmt1.c1), ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ), ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ), ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1 ) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 ; LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(b3t1 b3t4) MergeJoin(bmt1 bmt2) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(b3t1 b3t2 b3t4) HashJoin(bmt1 bmt2 bmt3) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(b3t1 b3t2 b3t3 b3t4) NestLoop(bmt1 bmt2 bmt3 bmt4) Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) Rows(b1t2 b1t3 #1) Rows(b2t3 b2t4 #1) Rows(b3t1 b3t4 #1) Rows(bmt1 bmt2 #1) Rows(b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t3 b2t4 #1) Rows(b3t1 b3t2 b3t4 #1) Rows(bmt1 bmt2 bmt3 #1) Rows(b1t1 b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t2 b2t3 b2t4 #1) Rows(b3t1 b3t2 b3t3 b3t4 #1) Rows(bmt1 bmt2 bmt3 bmt4 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) InitPlan 1 (returns $1) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b2t1.c1 = b2t3.c1) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) InitPlan 3 (returns $5) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b3t1.c1 = b3t2.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b3t1.c1) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (56 rows) -- No. R-2-1-3 \o results/ut-R.tmpout /*+ Leading(bmt4 bmt3 bmt2 bmt1) */ EXPLAIN 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.c1 = bmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; LOG: pg_hint_plan: used hint: Leading(bmt4 bmt3 bmt2 bmt1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt2.c1) -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) (15 rows) \o results/ut-R.tmpout /*+ Leading(bmt4 bmt3 bmt2 bmt1) Rows(bmt4 bmt3 #1)Rows(bmt4 bmt3 bmt2 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1) */ EXPLAIN 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.c1 = bmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; LOG: pg_hint_plan: used hint: Leading(bmt4 bmt3 bmt2 bmt1) Rows(bmt3 bmt4 #1) Rows(bmt2 bmt3 bmt4 #1) Rows(bmt1 bmt2 bmt3 bmt4 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (bmt2.c1 = bmt1.c1) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) (13 rows) -- No. R-2-1-4 \o results/ut-R.tmpout /*+ Leading(bmt4 bmt3 bmt2 bmt1) */ EXPLAIN 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.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; LOG: pg_hint_plan: used hint: Leading(bmt4 bmt3 bmt2 bmt1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt2.c1) -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) (15 rows) \o results/ut-R.tmpout /*+ Leading(bmt4 bmt3 bmt2 bmt1) Rows(bmt4 bmt3 #1)Rows(bmt4 bmt3 bmt2 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1) */ EXPLAIN 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.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; LOG: pg_hint_plan: used hint: Leading(bmt4 bmt3 bmt2 bmt1) Rows(bmt3 bmt4 #1) Rows(bmt2 bmt3 bmt4 #1) Rows(bmt1 bmt2 bmt3 bmt4 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (bmt2.c1 = bmt1.c1) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) (13 rows) -- No. R-2-1-5 \o results/ut-R.tmpout /*+ Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) */ EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 <> ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) AND bmt1.c1 <> ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ); LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(bmt1 bmt2) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(bmt1 bmt2 bmt3) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(bmt1 bmt2 bmt3 bmt4) Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) InitPlan 1 (returns $1) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=998 width=xxx) Filter: ((c1 <> $1) AND (c1 <> $3)) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (44 rows) \o results/ut-R.tmpout /*+ Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1) Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1) Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1) */ EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 <> ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) AND bmt1.c1 <> ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ) ; LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(bmt1 bmt2) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(bmt1 bmt2 bmt3) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(bmt1 bmt2 bmt3 bmt4) Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Rows(b1t2 b1t3 #1) Rows(b2t3 b2t4 #1) Rows(bmt1 bmt2 #1) Rows(b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t3 b2t4 #1) Rows(bmt1 bmt2 bmt3 #1) Rows(b1t1 b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t2 b2t3 b2t4 #1) Rows(bmt1 bmt2 bmt3 bmt4 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) InitPlan 1 (returns $1) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b2t1.c1 = b2t3.c1) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=998 width=xxx) Filter: ((c1 <> $1) AND (c1 <> $3)) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (44 rows) -- No. R-2-1-6 \o results/ut-R.tmpout /*+ Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4) */ EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 <> ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) AND bmt1.c1 <> ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ) AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1 ) ; LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(b3t1 b3t4) MergeJoin(bmt1 bmt2) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(b3t1 b3t2 b3t4) HashJoin(bmt1 bmt2 bmt3) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(b3t1 b3t2 b3t3 b3t4) NestLoop(bmt1 bmt2 bmt3 bmt4) Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) InitPlan 1 (returns $1) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) InitPlan 3 (returns $5) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b3t1.c1 = b3t2.c1) -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b3t1.c1) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=997 width=xxx) Filter: ((c1 <> $1) AND (c1 <> $3) AND (c1 <> $5)) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (57 rows) \o results/ut-R.tmpout /*+ Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) MergeJoin(bmt1 bmt2)HashJoin(bmt1 bmt2 bmt3)NestLoop(bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4) Rows(bmt1 bmt2 #1)Rows(bmt1 bmt2 bmt3 #1)Rows(bmt1 bmt2 bmt3 bmt4 #1) Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1) Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1) Rows(b3t4 b3t1 #1)Rows(b3t4 b3t1 b3t2 #1)Rows(b3t1 b3t2 b3t3 b3t4 #1) */ EXPLAIN SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 <> ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) AND bmt1.c1 <> ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ) AND bmt1.c1 <> ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1 ) ; LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(b3t1 b3t4) MergeJoin(bmt1 bmt2) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(b3t1 b3t2 b3t4) HashJoin(bmt1 bmt2 bmt3) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(b3t1 b3t2 b3t3 b3t4) NestLoop(bmt1 bmt2 bmt3 bmt4) Leading(bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) Rows(b1t2 b1t3 #1) Rows(b2t3 b2t4 #1) Rows(b3t1 b3t4 #1) Rows(bmt1 bmt2 #1) Rows(b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t3 b2t4 #1) Rows(b3t1 b3t2 b3t4 #1) Rows(bmt1 bmt2 bmt3 #1) Rows(b1t1 b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t2 b2t3 b2t4 #1) Rows(b3t1 b3t2 b3t3 b3t4 #1) Rows(bmt1 bmt2 bmt3 bmt4 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) InitPlan 1 (returns $1) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b2t1.c1 = b2t3.c1) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) InitPlan 3 (returns $5) -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b3t1.c1 = b3t2.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b3t1.c1) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=997 width=xxx) Filter: ((c1 <> $1) AND (c1 <> $3) AND (c1 <> $5)) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (57 rows) -- No. R-2-1-7 \o results/ut-R.tmpout /*+ Leading(c2 c1 bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) MergeJoin(c2 c1)HashJoin(c2 c1 bmt1)NestLoop(c2 c1 bmt1 bmt2)MergeJoin(c2 c1 bmt1 bmt2 bmt3)HashJoin(c2 c1 bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) */ EXPLAIN WITH c1 (c1) AS ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) , c2 (c1) AS ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 , c1, c2 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1 AND bmt1.c1 = c2.c1 ; LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(c1 c2) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(bmt1 c1 c2) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(bmt1 bmt2 c1 c2) MergeJoin(bmt1 bmt2 bmt3 c1 c2) HashJoin(bmt1 bmt2 bmt3 bmt4 c1 c2) Leading(c2 c1 bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) CTE c1 -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) CTE c2 -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt4.c1 = bmt1.c1) -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt3.c1 = bmt1.c1) -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: bmt1.c1 -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt1.c1 = c1.c1) -> Seq Scan on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (c1.c1 = c2.c1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c2.c1 -> CTE Scan on c2 (cost=xxx rows=1 width=xxx) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (54 rows) \o results/ut-R.tmpout /*+ Leading(c2 c1 bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) MergeJoin(c2 c1)HashJoin(c2 c1 bmt1)NestLoop(c2 c1 bmt1 bmt2)MergeJoin(c2 c1 bmt1 bmt2 bmt3)HashJoin(c2 c1 bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) Rows(c2 c1 #1)Rows(c2 c1 bmt1 #1)Rows(c2 c1 bmt1 bmt2 #1)Rows(c2 c1 bmt1 bmt2 bmt3 #1)Rows(c2 c1 bmt1 bmt2 bmt3 bmt4 #1) Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1) Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1) */ EXPLAIN WITH c1 (c1) AS ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) , c2 (c1) AS ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 , c1, c2 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1 AND bmt1.c1 = c2.c1; LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(c1 c2) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(bmt1 c1 c2) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(bmt1 bmt2 c1 c2) MergeJoin(bmt1 bmt2 bmt3 c1 c2) HashJoin(bmt1 bmt2 bmt3 bmt4 c1 c2) Leading(c2 c1 bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Rows(b1t2 b1t3 #1) Rows(b2t3 b2t4 #1) Rows(c1 c2 #1) Rows(b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t3 b2t4 #1) Rows(bmt1 c1 c2 #1) Rows(b1t1 b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t2 b2t3 b2t4 #1) Rows(bmt1 bmt2 c1 c2 #1) Rows(bmt1 bmt2 bmt3 c1 c2 #1) Rows(bmt1 bmt2 bmt3 bmt4 c1 c2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) CTE c1 -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) CTE c2 -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b2t1.c1 = b2t3.c1) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt4.c1 = bmt1.c1) -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt3.c1 = bmt1.c1) -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: bmt1.c1 -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt1.c1 = c1.c1) -> Seq Scan on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (c1.c1 = c2.c1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c2.c1 -> CTE Scan on c2 (cost=xxx rows=1 width=xxx) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (54 rows) -- No. R-2-1-8 \o results/ut-R.tmpout /*+ Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) MergeJoin(c3 c2)HashJoin(c3 c2 c1)NestLoop(c3 c2 c1 bmt1)MergeJoin(c3 c2 c1 bmt1 bmt2)HashJoin(c3 c2 c1 bmt1 bmt2 bmt3)NestLoop(c3 c2 c1 bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4) */ EXPLAIN WITH c1 (c1) AS ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) , c2 (c1) AS ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ) , c3 (c1) AS ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1 ) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 , c1, c2, c3 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1 AND bmt1.c1 = c2.c1 AND bmt1.c1 = c3.c1; LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(b3t1 b3t4) MergeJoin(c2 c3) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(b3t1 b3t2 b3t4) HashJoin(c1 c2 c3) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(b3t1 b3t2 b3t3 b3t4) NestLoop(bmt1 c1 c2 c3) MergeJoin(bmt1 bmt2 c1 c2 c3) HashJoin(bmt1 bmt2 bmt3 c1 c2 c3) NestLoop(bmt1 bmt2 bmt3 bmt4 c1 c2 c3) Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) CTE c1 -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) CTE c2 -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (b2t3.c1 = b2t1.c1) -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) CTE c3 -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b3t1.c1 = b3t2.c1) -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b3t1.c1) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: bmt1.c1 -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (c2.c1 = c1.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (c2.c1 = c3.c1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c2.c1 -> CTE Scan on c2 (cost=xxx rows=1 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c3.c1 -> CTE Scan on c3 (cost=xxx rows=1 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = c1.c1) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (72 rows) \o results/ut-R.tmpout /*+ Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) MergeJoin(c3 c2)HashJoin(c3 c2 c1)NestLoop(c3 c2 c1 bmt1)MergeJoin(c3 c2 c1 bmt1 bmt2)HashJoin(c3 c2 c1 bmt1 bmt2 bmt3)NestLoop(c3 c2 c1 bmt1 bmt2 bmt3 bmt4) MergeJoin(b1t2 b1t3)HashJoin(b1t2 b1t3 b1t4)NestLoop(b1t2 b1t3 b1t4 b1t1) MergeJoin(b2t3 b2t4)HashJoin(b2t3 b2t4 b2t1)NestLoop(b2t3 b2t4 b2t1 b2t2) MergeJoin(b3t4 b3t1)HashJoin(b3t4 b3t1 b3t2)NestLoop(b3t1 b3t2 b3t3 b3t4) Rows(c3 c2 #1)Rows(c3 c2 c1 #1)Rows(c3 c2 c1 bmt1 #1)Rows(c3 c2 c1 bmt1 bmt2 #1)Rows(c3 c2 c1 bmt1 bmt2 bmt3 #1)Rows(c3 c2 c1 bmt1 bmt2 bmt3 bmt4 #1) Rows(b1t2 b1t3 #1)Rows(b1t2 b1t3 b1t4 #1)Rows(b1t2 b1t3 b1t4 b1t1 #1) Rows(b2t3 b2t4 #1)Rows(b2t3 b2t4 b2t1 #1)Rows(b2t3 b2t4 b2t1 b2t2 #1) Rows(b3t4 b3t1 #1)Rows(b3t4 b3t1 b3t2 #1)Rows(b3t1 b3t2 b3t3 b3t4 #1) */ EXPLAIN WITH c1 (c1) AS ( SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) , c2 (c1) AS ( SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ) , c3 (c1) AS ( SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1 ) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 , c1, c2, c3 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1 AND bmt1.c1 = c2.c1 AND bmt1.c1 = c3.c1; LOG: pg_hint_plan: used hint: MergeJoin(b1t2 b1t3) MergeJoin(b2t3 b2t4) MergeJoin(b3t1 b3t4) MergeJoin(c2 c3) HashJoin(b1t2 b1t3 b1t4) HashJoin(b2t1 b2t3 b2t4) HashJoin(b3t1 b3t2 b3t4) HashJoin(c1 c2 c3) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(b2t1 b2t2 b2t3 b2t4) NestLoop(b3t1 b3t2 b3t3 b3t4) NestLoop(bmt1 c1 c2 c3) MergeJoin(bmt1 bmt2 c1 c2 c3) HashJoin(bmt1 bmt2 bmt3 c1 c2 c3) NestLoop(bmt1 bmt2 bmt3 bmt4 c1 c2 c3) Leading(c3 c2 c1 bmt1 bmt2 bmt3 bmt4) Leading(b1t2 b1t3 b1t4 b1t1) Leading(b2t3 b2t4 b2t1 b2t2) Leading(b3t4 b3t1 b3t2 b3t3) Rows(b1t2 b1t3 #1) Rows(b2t3 b2t4 #1) Rows(b3t1 b3t4 #1) Rows(c2 c3 #1) Rows(b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t3 b2t4 #1) Rows(b3t1 b3t2 b3t4 #1) Rows(c1 c2 c3 #1) Rows(b1t1 b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t2 b2t3 b2t4 #1) Rows(b3t1 b3t2 b3t3 b3t4 #1) Rows(bmt1 c1 c2 c3 #1) Rows(bmt1 bmt2 c1 c2 c3 #1) Rows(bmt1 bmt2 bmt3 c1 c2 c3 #1) Rows(bmt1 bmt2 bmt3 bmt4 c1 c2 c3 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) CTE c1 -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t4.c1 = b1t2.c1) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t2.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) CTE c2 -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b2t1.c1 = b2t3.c1) -> Seq Scan on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t3.c1 = b2t4.c1) -> Index Only Scan using t3_i1 on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t1.c1) CTE c3 -> Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b3t1.c1 = b3t2.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b3t1.c1 = b3t4.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t4_i1 on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t3_i1 on t3 b3t3 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b3t1.c1) -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt1.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: bmt1.c1 -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (c2.c1 = c1.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (c2.c1 = c3.c1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c2.c1 -> CTE Scan on c2 (cost=xxx rows=1 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c3.c1 -> CTE Scan on c3 (cost=xxx rows=1 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = c1.c1) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (72 rows) ---- ---- No. R-2-2 the number of the tables per quiry block ---- -- No. R-2-2-1 \o results/ut-R.tmpout /*+ Leading(c1 bmt1) */ EXPLAIN WITH c1 (c1) AS ( SELECT b1t1.c1 FROM s1.t1 b1t1 WHERE b1t1.c1 = 1 ) SELECT bmt1.c1, ( SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1 ) FROM s1.t1 bmt1, c1 WHERE bmt1.c1 = 1 AND bmt1.c1 = c1.c1 AND bmt1.c1 <> ( SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1 ); LOG: pg_hint_plan: used hint: Leading(c1 bmt1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ---------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) CTE c1 -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = 1) InitPlan 2 (returns $1) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = 1) InitPlan 3 (returns $2) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = 1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = 1) Filter: (c1 <> $2) -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) Filter: (c1 = 1) (15 rows) \o results/ut-R.tmpout /*+ Leading(c1 bmt1) Rows(bmt1 c1 #1) Rows(b1t1 c1 #1) Rows(b2t1 c1 #1) Rows(b3t1 c1 #1) */ EXPLAIN WITH c1 (c1) AS ( SELECT b1t1.c1 FROM s1.t1 b1t1 WHERE b1t1.c1 = 1 ) SELECT bmt1.c1, ( SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1 ) FROM s1.t1 bmt1, c1 WHERE bmt1.c1 = 1 AND bmt1.c1 = c1.c1 AND bmt1.c1 <> ( SELECT b3t1.c1 FROM s1.t1 b3t1 WHERE b3t1.c1 = 1 ); LOG: pg_hint_plan: used hint: Leading(c1 bmt1) Rows(bmt1 c1 #1) not used hint: Rows(b1t1 c1 #1) Rows(b2t1 c1 #1) Rows(b3t1 c1 #1) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ---------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) CTE c1 -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = 1) InitPlan 2 (returns $1) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = 1) InitPlan 3 (returns $2) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = 1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = 1) Filter: (c1 <> $2) -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) Filter: (c1 = 1) (15 rows) -- No. R-2-2-2 \o results/ut-R.tmpout /*+ Leading(c1 bmt2 bmt1) Leading(b1t2 b1t1) Leading(b2t2 b2t1) Leading(b3t2 b3t1) MergeJoin(c1 bmt2) HashJoin(c1 bmt1 bmt2) MergeJoin(b1t1 b1t2) MergeJoin(b2t1 b2t2) MergeJoin(b3t1 b3t2) */ EXPLAIN WITH c1 (c1) AS ( SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.c1 = b1t2.c1 ) SELECT bmt1.c1, ( SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.c1 = b2t2.c1 ) FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = c1.c1 AND bmt1.c1 <> ( SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.c1 = b3t2.c1 ); LOG: pg_hint_plan: used hint: MergeJoin(b1t1 b1t2) MergeJoin(b2t1 b2t2) MergeJoin(b3t1 b3t2) MergeJoin(bmt2 c1) HashJoin(bmt1 bmt2 c1) Leading(c1 bmt2 bmt1) Leading(b1t2 b1t1) Leading(b2t2 b2t1) Leading(b3t2 b3t1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------- Hash Join (cost=xxx rows=10 width=xxx) Hash Cond: (bmt1.c1 = bmt2.c1) CTE c1 -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t1.c1 = b1t2.c1) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) InitPlan 2 (returns $1) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b2t1.c1 = b2t2.c1) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b2t2.c1 -> Seq Scan on t2 b2t2 (cost=xxx rows=100 width=xxx) InitPlan 3 (returns $2) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b3t1.c1 = b3t2.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b3t2.c1 -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Seq Scan on t1 bmt1 (cost=xxx rows=999 width=xxx) Filter: (c1 <> $2) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt2.c1 = c1.c1) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=100 width=xxx) (34 rows) \o results/ut-R.tmpout /*+ Leading(c1 bmt2 bmt1) Leading(b1t2 b1t1) Leading(b2t2 b2t1) Leading(b3t2 b3t1) MergeJoin(c1 bmt2) HashJoin(c1 bmt1 bmt2) MergeJoin(b1t1 b1t2) MergeJoin(b2t1 b2t2) MergeJoin(b3t1 b3t2) Rows(c1 bmt2 #1) Rows(c1 bmt1 bmt2 #1) Rows(b1t1 b1t2 #1) Rows(b2t1 b2t2 #1) Rows(b3t1 b3t2 #1) */ EXPLAIN WITH c1 (c1) AS ( SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.c1 = b1t2.c1 ) SELECT bmt1.c1, ( SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.c1 = b2t2.c1 ) FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = c1.c1 AND bmt1.c1 <> ( SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.c1 = b3t2.c1 ) ; LOG: pg_hint_plan: used hint: MergeJoin(b1t1 b1t2) MergeJoin(b2t1 b2t2) MergeJoin(b3t1 b3t2) MergeJoin(bmt2 c1) HashJoin(bmt1 bmt2 c1) Leading(c1 bmt2 bmt1) Leading(b1t2 b1t1) Leading(b2t2 b2t1) Leading(b3t2 b3t1) Rows(b1t1 b1t2 #1) Rows(b2t1 b2t2 #1) Rows(b3t1 b3t2 #1) Rows(bmt2 c1 #1) Rows(bmt1 bmt2 c1 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------------- Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt1.c1 = bmt2.c1) CTE c1 -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t1.c1 = b1t2.c1) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) InitPlan 2 (returns $1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t1.c1 = b2t2.c1) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b2t2.c1 -> Seq Scan on t2 b2t2 (cost=xxx rows=100 width=xxx) InitPlan 3 (returns $2) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b3t1.c1 = b3t2.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b3t2.c1 -> Seq Scan on t2 b3t2 (cost=xxx rows=100 width=xxx) -> Seq Scan on t1 bmt1 (cost=xxx rows=999 width=xxx) Filter: (c1 <> $2) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt2.c1 = c1.c1) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) (34 rows) -- No. R-2-2-3 \o results/ut-R.tmpout /*+ Leading(c1 bmt4 bmt3 bmt2 bmt1) Leading(b1t4 b1t3 b1t2 b1t1) Leading(b2t4 b2t3 b2t2 b2t1) Leading(b3t4 b3t3 b3t2 b3t1) MergeJoin(c1 bmt4) HashJoin(c1 bmt4 bmt3) NestLoop(c1 bmt4 bmt3 bmt2) MergeJoin(c1 bmt4 bmt3 bmt2 bmt1) HashJoin(b1t4 b1t3) NestLoop(b1t4 b1t3 b1t2) MergeJoin(b1t4 b1t3 b1t2 b1t1) HashJoin(b2t4 b2t3) NestLoop(b2t4 b2t3 b2t2) MergeJoin(b2t4 b2t3 b2t2 b2t1) HashJoin(b3t4 b3t3) NestLoop(b3t4 b3t3 b3t2) MergeJoin(b3t4 b3t3 b3t2 b3t1) */ EXPLAIN WITH c1 (c1) AS ( SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) SELECT bmt1.c1, ( SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1 AND bmt1.c1 <> ( SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1 ); LOG: pg_hint_plan: used hint: HashJoin(b1t3 b1t4) HashJoin(b2t3 b2t4) HashJoin(b3t3 b3t4) MergeJoin(bmt4 c1) NestLoop(b1t2 b1t3 b1t4) NestLoop(b2t2 b2t3 b2t4) NestLoop(b3t2 b3t3 b3t4) HashJoin(bmt3 bmt4 c1) MergeJoin(b1t1 b1t2 b1t3 b1t4) MergeJoin(b2t1 b2t2 b2t3 b2t4) MergeJoin(b3t1 b3t2 b3t3 b3t4) NestLoop(bmt2 bmt3 bmt4 c1) MergeJoin(bmt1 bmt2 bmt3 bmt4 c1) Leading(c1 bmt4 bmt3 bmt2 bmt1) Leading(b1t4 b1t3 b1t2 b1t1) Leading(b2t4 b2t3 b2t2 b2t1) Leading(b3t4 b3t3 b3t2 b3t1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Merge Join (cost=xxx rows=10 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) CTE c1 -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b1t1.c1 = b1t2.c1) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b1t2.c1 -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (b1t3.c1 = b1t4.c1) -> Seq Scan on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b2t1.c1 = b2t2.c1) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b2t2.c1 -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (b2t3.c1 = b2t4.c1) -> Seq Scan on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t3.c1) InitPlan 3 (returns $5) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (b3t1.c1 = b3t2.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: b3t2.c1 -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (b3t3.c1 = b3t4.c1) -> Seq Scan on t3 b3t3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b3t3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=999 width=xxx) Filter: (c1 <> $5) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt4.c1 = c1.c1) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) (61 rows) \o results/ut-R.tmpout /*+ Leading(c1 bmt4 bmt3 bmt2 bmt1) Leading(b1t4 b1t3 b1t2 b1t1) Leading(b2t4 b2t3 b2t2 b2t1) Leading(b3t4 b3t3 b3t2 b3t1) MergeJoin(c1 bmt4) HashJoin(c1 bmt4 bmt3) NestLoop(c1 bmt4 bmt3 bmt2) MergeJoin(c1 bmt4 bmt3 bmt2 bmt1) HashJoin(b1t4 b1t3) NestLoop(b1t4 b1t3 b1t2) MergeJoin(b1t4 b1t3 b1t2 b1t1) HashJoin(b2t4 b2t3) NestLoop(b2t4 b2t3 b2t2) MergeJoin(b2t4 b2t3 b2t2 b2t1) HashJoin(b3t4 b3t3) NestLoop(b3t4 b3t3 b3t2) MergeJoin(b3t4 b3t3 b3t2 b3t1) Rows(c1 bmt4 #1) Rows(c1 bmt4 bmt3 #1) Rows(c1 bmt4 bmt3 bmt2 #1) Rows(c1 bmt4 bmt3 bmt2 bmt1 #1) Rows(b1t4 b1t3 #1) Rows(b1t4 b1t3 b1t2 #1) Rows(b1t4 b1t3 b1t2 b1t1 #1) Rows(b2t4 b2t3 #1) Rows(b2t4 b2t3 b2t2 #1) Rows(b2t4 b2t3 b2t2 b2t1 #1) Rows(b3t4 b3t3 #1) Rows(b3t4 b3t3 b3t2 #1) Rows(b3t4 b3t3 b3t2 b3t1 #1) */ EXPLAIN WITH c1 (c1) AS ( SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) SELECT bmt1.c1, ( SELECT b2t1.c1 FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1 ) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1 AND bmt1.c1 <> ( SELECT b3t1.c1 FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.c1 = b3t2.c1 AND b3t1.c1 = b3t3.c1 AND b3t1.c1 = b3t4.c1 ); LOG: pg_hint_plan: used hint: HashJoin(b1t3 b1t4) HashJoin(b2t3 b2t4) HashJoin(b3t3 b3t4) MergeJoin(bmt4 c1) NestLoop(b1t2 b1t3 b1t4) NestLoop(b2t2 b2t3 b2t4) NestLoop(b3t2 b3t3 b3t4) HashJoin(bmt3 bmt4 c1) MergeJoin(b1t1 b1t2 b1t3 b1t4) MergeJoin(b2t1 b2t2 b2t3 b2t4) MergeJoin(b3t1 b3t2 b3t3 b3t4) NestLoop(bmt2 bmt3 bmt4 c1) MergeJoin(bmt1 bmt2 bmt3 bmt4 c1) Leading(c1 bmt4 bmt3 bmt2 bmt1) Leading(b1t4 b1t3 b1t2 b1t1) Leading(b2t4 b2t3 b2t2 b2t1) Leading(b3t4 b3t3 b3t2 b3t1) Rows(b1t3 b1t4 #1) Rows(b2t3 b2t4 #1) Rows(b3t3 b3t4 #1) Rows(bmt4 c1 #1) Rows(b1t2 b1t3 b1t4 #1) Rows(b2t2 b2t3 b2t4 #1) Rows(b3t2 b3t3 b3t4 #1) Rows(bmt3 bmt4 c1 #1) Rows(b1t1 b1t2 b1t3 b1t4 #1) Rows(b2t1 b2t2 b2t3 b2t4 #1) Rows(b3t1 b3t2 b3t3 b3t4 #1) Rows(bmt2 bmt3 bmt4 c1 #1) Rows(bmt1 bmt2 bmt3 bmt4 c1 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) CTE c1 -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t1.c1 = b1t2.c1) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: b1t2.c1 -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t3.c1 = b1t4.c1) -> Seq Scan on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b1t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $3) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t1.c1 = b2t2.c1) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: b2t2.c1 -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b2t3.c1 = b2t4.c1) -> Seq Scan on t3 b2t3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 b2t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b2t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b2t3.c1) InitPlan 3 (returns $5) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b3t1.c1 = b3t2.c1) -> Index Only Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: b3t2.c1 -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b3t3.c1 = b3t4.c1) -> Seq Scan on t3 b3t3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 b3t4 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t2_i1 on t2 b3t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b3t3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=999 width=xxx) Filter: (c1 <> $5) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: bmt2.c1 -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt4.c1 = c1.c1) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) (61 rows) -- No. R-2-2-4 \o results/ut-R.tmpout /*+ Leading(c1 bmt4 bmt3 bmt2 bmt1) Leading(b1t4 b1t3 b1t2 b1t1) MergeJoin(c1 bmt4) HashJoin(c1 bmt4 bmt3) NestLoop(c1 bmt4 bmt3 bmt2) MergeJoin(c1 bmt4 bmt3 bmt2 bmt1) MergeJoin(b1t4 b1t3) HashJoin(b1t4 b1t3 b1t2) NestLoop(b1t4 b1t3 b1t2 b1t1) */ EXPLAIN WITH c1 (c1) AS ( SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) SELECT bmt1.c1, ( SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1 ) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1 AND bmt1.c1 <> ( SELECT b3t1.c1 FROM s1.t1 b3t1 ); LOG: pg_hint_plan: used hint: MergeJoin(b1t3 b1t4) MergeJoin(bmt4 c1) HashJoin(b1t2 b1t3 b1t4) HashJoin(bmt3 bmt4 c1) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(bmt2 bmt3 bmt4 c1) MergeJoin(bmt1 bmt2 bmt3 bmt4 c1) Leading(c1 bmt4 bmt3 bmt2 bmt1) Leading(b1t4 b1t3 b1t2 b1t1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Merge Join (cost=xxx rows=10 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) CTE c1 -> Nested Loop (cost=xxx rows=100 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (b1t3.c1 = b1t2.c1) -> Merge Join (cost=xxx rows=1130 width=xxx) Merge Cond: (b1t3.c1 = b1t4.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $2) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = 1) InitPlan 3 (returns $3) -> Seq Scan on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=999 width=xxx) Filter: (c1 <> $3) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Nested Loop (cost=xxx rows=100 width=xxx) -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt4.c1 = c1.c1) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) (37 rows) \o results/ut-R.tmpout /*+ Leading(c1 bmt4 bmt3 bmt2 bmt1) Leading(b1t4 b1t3 b1t2 b1t1) MergeJoin(c1 bmt4) HashJoin(c1 bmt4 bmt3) NestLoop(c1 bmt4 bmt3 bmt2) MergeJoin(c1 bmt4 bmt3 bmt2 bmt1) MergeJoin(b1t4 b1t3) HashJoin(b1t4 b1t3 b1t2) NestLoop(b1t4 b1t3 b1t2 b1t1) Rows(c1 bmt4 #1) Rows(c1 bmt4 bmt3 #1) Rows(c1 bmt4 bmt3 bmt2 #1) Rows(c1 bmt4 bmt3 bmt2 bmt1 #1) Rows(b1t4 b1t3 #1) Rows(b1t4 b1t3 b1t2 #1) Rows(b1t4 b1t3 b1t2 b1t1 #1) */ EXPLAIN WITH c1 (c1) AS ( SELECT b1t1.c1 FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1 ) SELECT bmt1.c1, ( SELECT b2t1.c1 FROM s1.t1 b2t1 WHERE b2t1.c1 = 1 ) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1 AND bmt1.c1 = c1.c1 AND bmt1.c1 <> ( SELECT b3t1.c1 FROM s1.t1 b3t1 ); LOG: pg_hint_plan: used hint: MergeJoin(b1t3 b1t4) MergeJoin(bmt4 c1) HashJoin(b1t2 b1t3 b1t4) HashJoin(bmt3 bmt4 c1) NestLoop(b1t1 b1t2 b1t3 b1t4) NestLoop(bmt2 bmt3 bmt4 c1) MergeJoin(bmt1 bmt2 bmt3 bmt4 c1) Leading(c1 bmt4 bmt3 bmt2 bmt1) Leading(b1t4 b1t3 b1t2 b1t1) Rows(b1t3 b1t4 #1) Rows(bmt4 c1 #1) Rows(b1t2 b1t3 b1t4 #1) Rows(bmt3 bmt4 c1 #1) Rows(b1t1 b1t2 b1t3 b1t4 #1) Rows(bmt2 bmt3 bmt4 c1 #1) Rows(bmt1 bmt2 bmt3 bmt4 c1 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) CTE c1 -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t2.c1 = b1t1.c1) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (b1t3.c1 = b1t2.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t3.c1 = b1t4.c1) -> Index Only Scan using t3_i1 on t3 b1t3 (cost=xxx rows=1130 width=xxx) -> Index Only Scan using t4_i1 on t4 b1t4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 b1t2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = b1t3.c1) InitPlan 2 (returns $2) -> Index Only Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = 1) InitPlan 3 (returns $3) -> Seq Scan on t1 b3t1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=999 width=xxx) Filter: (c1 <> $3) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: bmt2.c1 -> Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1 width=xxx) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (bmt4.c1 = c1.c1) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: c1.c1 -> CTE Scan on c1 (cost=xxx rows=1 width=xxx) -> Index Only Scan using t2_i1 on t2 bmt2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt3.c1) (37 rows) ---- ---- No. R-2-3 RULE or VIEW ---- -- No. R-2-3-1 \o results/ut-R.tmpout /*+ Leading(r1 t1 t2 t3 t4) */ EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r1 t1 t2 t3 t4) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r1 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) (21 rows) \o results/ut-R.tmpout /*+ Leading(r1 t1 t2 t3 t4) Rows(r1 t1 t2 t3 t4 #2) Rows(r1 t1 t2 t3 #2) Rows(r1 t1 t2 #2) Rows(r1 t1 #2) */ EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r1 t1 t2 t3 t4) Rows(r1 t1 #2) Rows(r1 t1 t2 #2) Rows(r1 t1 t2 t3 #2) Rows(r1 t1 t2 t3 t4 #2) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r1 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) (18 rows) \o results/ut-R.tmpout /*+ Leading(r1_ b1t1 b1t2 b1t3 b1t4) */ EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r1_ b1t1 b1t2 b1t3 b1t4) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ---------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t1.c1 = b1t4.c1) -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t1.c1 = b1t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t1.c1 = b1t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r1_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) (21 rows) \o results/ut-R.tmpout /*+ Leading(r1_ b1t1 b1t2 b1t3 b1t4) Rows(r1_ b1t1 b1t2 b1t3 b1t4 #2) Rows(r1_ b1t1 b1t2 b1t3 #2) Rows(r1_ b1t1 b1t2 #2) Rows(r1_ b1t1 #2) */ EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r1_ b1t1 b1t2 b1t3 b1t4) Rows(b1t1 r1_ #2) Rows(b1t1 b1t2 r1_ #2) Rows(b1t1 b1t2 b1t3 r1_ #2) Rows(b1t1 b1t2 b1t3 b1t4 r1_ #2) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b1t1.c1 = b1t4.c1) -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b1t1.c1 = b1t3.c1) -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b1t1.c1 = b1t2.c1) -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 b1t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r1_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) (18 rows) -- No. R-2-3-2 \o results/ut-R.tmpout /*+ Leading(r2 t1 t2 t3 t4) */ EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r2 t1 t2 t3 t4) not used hint: duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r2 t1 t2 t3 t4) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r2 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r2 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) (43 rows) \o results/ut-R.tmpout /*+ Leading(r2 t1 t2 t3 t4) Rows(r2 t1 t2 t3 t4 #2) Rows(r2 t1 t2 t3 #2) Rows(r2 t1 t2 #2) Rows(r2 t1 #2) */ EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r2 t1 t2 t3 t4) Rows(r2 t1 #2) Rows(r2 t1 t2 #2) Rows(r2 t1 t2 t3 #2) Rows(r2 t1 t2 t3 t4 #2) not used hint: duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r2 t1 t2 t3 t4) Rows(r2 t1 #2) Rows(r2 t1 t2 #2) Rows(r2 t1 t2 t3 #2) Rows(r2 t1 t2 t3 t4 #2) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r2 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r2 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) (37 rows) \o results/ut-R.tmpout /*+ Leading(r2_ b1t1 b1t2 b1t3 b1t4) Leading(r2_ b2t1 b2t2 b2t3 b2t4) */ EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r2_ b1t1 b1t2 b1t3 b1t4) not used hint: Leading(r2_ b2t1 b2t2 b2t3 b2t4) duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r2_ b2t1 b2t2 b2t3 b2t4) not used hint: Leading(r2_ b1t1 b1t2 b1t3 b1t4) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ---------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t1.c1 = b1t4.c1) -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t1.c1 = b1t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t1.c1 = b1t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r2_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b2t1.c1 = b2t4.c1) -> Tid Scan on t4 b2t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b2t1.c1 = b2t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t1.c1 = b2t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r2_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: b2t2.c1 -> Seq Scan on t2 b2t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 b2t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) (43 rows) \o results/ut-R.tmpout /*+ Leading(r2_ b1t1 b1t2 b1t3 b1t4) Leading(r2_ b2t1 b2t2 b2t3 b2t4) Rows(r2_ b1t1 #2) Rows(r2_ b1t1 b1t2 #2) Rows(r2_ b1t1 b1t2 b1t3 #2) Rows(r2_ b1t1 b1t2 b1t3 b1t4 #2) Rows(r2_ b2t1 #2) Rows(r2_ b2t1 b2t2 #2) Rows(r2_ b2t1 b2t2 b2t3 #2) Rows(r2_ b2t1 b2t2 b2t3 b2t4 #2) */ EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r2_ b1t1 b1t2 b1t3 b1t4) Rows(b1t1 r2_ #2) Rows(b1t1 b1t2 r2_ #2) Rows(b1t1 b1t2 b1t3 r2_ #2) Rows(b1t1 b1t2 b1t3 b1t4 r2_ #2) not used hint: Leading(r2_ b2t1 b2t2 b2t3 b2t4) Rows(b2t1 r2_ #2) Rows(b2t1 b2t2 r2_ #2) Rows(b2t1 b2t2 b2t3 r2_ #2) Rows(b2t1 b2t2 b2t3 b2t4 r2_ #2) duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r2_ b2t1 b2t2 b2t3 b2t4) Rows(b2t1 r2_ #2) Rows(b2t1 b2t2 r2_ #2) Rows(b2t1 b2t2 b2t3 r2_ #2) Rows(b2t1 b2t2 b2t3 b2t4 r2_ #2) not used hint: Leading(r2_ b1t1 b1t2 b1t3 b1t4) Rows(b1t1 r2_ #2) Rows(b1t1 b1t2 r2_ #2) Rows(b1t1 b1t2 b1t3 r2_ #2) Rows(b1t1 b1t2 b1t3 b1t4 r2_ #2) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b1t1.c1 = b1t4.c1) -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b1t1.c1 = b1t3.c1) -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b1t1.c1 = b1t2.c1) -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 b1t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r2_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b2t1.c1 = b2t4.c1) -> Tid Scan on t4 b2t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b2t1.c1 = b2t3.c1) -> Tid Scan on t3 b2t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b2t1.c1 = b2t2.c1) -> Seq Scan on t2 b2t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 b2t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r2_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) (37 rows) -- No. R-2-3-3 \o results/ut-R.tmpout /*+ Leading(r3 t1 t2 t3 t4) */ EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r3 t1 t2 t3 t4) not used hint: duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r3 t1 t2 t3 t4) not used hint: duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r3 t1 t2 t3 t4) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r3 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r3 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r3 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) (65 rows) \o results/ut-R.tmpout /*+ Leading(r3 t1 t2 t3 t4) Rows(r3 t1 t2 t3 t4 #2) Rows(r3 t1 t2 t3 #2) Rows(r3 t1 t2 #2) Rows(r3 t1 #2) */ EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r3 t1 t2 t3 t4) Rows(r3 t1 #2) Rows(r3 t1 t2 #2) Rows(r3 t1 t2 t3 #2) Rows(r3 t1 t2 t3 t4 #2) not used hint: duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r3 t1 t2 t3 t4) Rows(r3 t1 #2) Rows(r3 t1 t2 #2) Rows(r3 t1 t2 t3 #2) Rows(r3 t1 t2 t3 t4 #2) not used hint: duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r3 t1 t2 t3 t4) Rows(r3 t1 #2) Rows(r3 t1 t2 #2) Rows(r3 t1 t2 t3 #2) Rows(r3 t1 t2 t3 t4 #2) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r3 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r3 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t4.c1) -> Tid Scan on t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t3.c1) -> Tid Scan on t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (t1.c1 = t2.c1) -> Seq Scan on t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r3 (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) (56 rows) \o results/ut-R.tmpout /*+ Leading(r3_ b1t1 b1t2 b1t3 b1t4) Leading(r3_ b2t1 b2t2 b2t3 b2t4) Leading(r3_ b3t1 b3t2 b3t3 b3t4) */ EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r3_ b1t1 b1t2 b1t3 b1t4) not used hint: Leading(r3_ b2t1 b2t2 b2t3 b2t4) Leading(r3_ b3t1 b3t2 b3t3 b3t4) duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r3_ b2t1 b2t2 b2t3 b2t4) not used hint: Leading(r3_ b1t1 b1t2 b1t3 b1t4) Leading(r3_ b3t1 b3t2 b3t3 b3t4) duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r3_ b3t1 b3t2 b3t3 b3t4) not used hint: Leading(r3_ b1t1 b1t2 b1t3 b1t4) Leading(r3_ b2t1 b2t2 b2t3 b2t4) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ---------------------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t1.c1 = b1t4.c1) -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b1t1.c1 = b1t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b1t1.c1 = b1t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r3_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: b1t2.c1 -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b2t1.c1 = b2t4.c1) -> Tid Scan on t4 b2t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b2t1.c1 = b2t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b2t1.c1 = b2t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r3_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: b2t2.c1 -> Seq Scan on t2 b2t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 b2t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b3t1.c1 = b3t4.c1) -> Tid Scan on t4 b3t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (b3t1.c1 = b3t3.c1) -> Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (b3t1.c1 = b3t2.c1) -> Nested Loop (cost=xxx rows=6 width=xxx) -> Index Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Seq Scan on r3_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) -> Sort (cost=xxx rows=1 width=xxx) Sort Key: b3t2.c1 -> Seq Scan on t2 b3t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Materialize (cost=xxx rows=1 width=xxx) -> Tid Scan on t3 b3t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) (65 rows) \o results/ut-R.tmpout /*+ Leading(r3_ b1t1 b1t2 b1t3 b1t4) Leading(r3_ b2t1 b2t2 b2t3 b2t4) Leading(r3_ b3t1 b3t2 b3t3 b3t4) Rows(r3_ b1t1 #2) Rows(r3_ b1t1 b1t2 #2) Rows(r3_ b1t1 b1t2 b1t3 #2) Rows(r3_ b1t1 b1t2 b1t3 b1t4 #2) Rows(r3_ b2t1 #2) Rows(r3_ b2t1 b2t2 #2) Rows(r3_ b2t1 b2t2 b2t3 #2) Rows(r3_ b2t1 b2t2 b2t3 b2t4 #2) Rows(r3_ b3t1 #2) Rows(r3_ b3t1 b3t2 #2) Rows(r3_ b3t1 b3t2 b3t3 #2) Rows(r3_ b3t1 b3t2 b3t3 b3t4 #2) */ EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1; LOG: pg_hint_plan: used hint: Leading(r3_ b1t1 b1t2 b1t3 b1t4) Rows(b1t1 r3_ #2) Rows(b1t1 b1t2 r3_ #2) Rows(b1t1 b1t2 b1t3 r3_ #2) Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2) not used hint: Leading(r3_ b2t1 b2t2 b2t3 b2t4) Leading(r3_ b3t1 b3t2 b3t3 b3t4) Rows(b2t1 r3_ #2) Rows(b3t1 r3_ #2) Rows(b2t1 b2t2 r3_ #2) Rows(b3t1 b3t2 r3_ #2) Rows(b2t1 b2t2 b2t3 r3_ #2) Rows(b3t1 b3t2 b3t3 r3_ #2) Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2) Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2) duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r3_ b2t1 b2t2 b2t3 b2t4) Rows(b2t1 r3_ #2) Rows(b2t1 b2t2 r3_ #2) Rows(b2t1 b2t2 b2t3 r3_ #2) Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2) not used hint: Leading(r3_ b1t1 b1t2 b1t3 b1t4) Leading(r3_ b3t1 b3t2 b3t3 b3t4) Rows(b1t1 r3_ #2) Rows(b3t1 r3_ #2) Rows(b1t1 b1t2 r3_ #2) Rows(b3t1 b3t2 r3_ #2) Rows(b1t1 b1t2 b1t3 r3_ #2) Rows(b3t1 b3t2 b3t3 r3_ #2) Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2) Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2) duplication hint: error hint: LOG: pg_hint_plan: used hint: Leading(r3_ b3t1 b3t2 b3t3 b3t4) Rows(b3t1 r3_ #2) Rows(b3t1 b3t2 r3_ #2) Rows(b3t1 b3t2 b3t3 r3_ #2) Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2) not used hint: Leading(r3_ b1t1 b1t2 b1t3 b1t4) Leading(r3_ b2t1 b2t2 b2t3 b2t4) Rows(b1t1 r3_ #2) Rows(b2t1 r3_ #2) Rows(b1t1 b1t2 r3_ #2) Rows(b2t1 b2t2 r3_ #2) Rows(b1t1 b1t2 b1t3 r3_ #2) Rows(b2t1 b2t2 b2t3 r3_ #2) Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2) Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------- Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b1t1.c1 = b1t4.c1) -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b1t1.c1 = b1t3.c1) -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b1t1.c1 = b1t2.c1) -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 b1t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r3_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b2t1.c1 = b2t4.c1) -> Tid Scan on t4 b2t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b2t1.c1 = b2t3.c1) -> Tid Scan on t3 b2t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b2t1.c1 = b2t2.c1) -> Seq Scan on t2 b2t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 b2t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r3_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b3t1.c1 = b3t4.c1) -> Tid Scan on t4 b3t4 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b3t1.c1 = b3t3.c1) -> Tid Scan on t3 b3t3 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) Join Filter: (b3t1.c1 = b3t2.c1) -> Seq Scan on t2 b3t2 (cost=xxx rows=1 width=xxx) Filter: (ctid = '(1,1)'::tid) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Tid Scan on t1 b3t1 (cost=xxx rows=1 width=xxx) TID Cond: (ctid = '(1,1)'::tid) -> Seq Scan on r3_ (cost=xxx rows=6 width=xxx) Filter: (c1 = 1) (56 rows) -- No. R-2-3-4 \o results/ut-R.tmpout /*+HashJoin(v1t1 v1t1)*/ EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1; INFO: pg_hint_plan: hint syntax error at or near "HashJoin(v1t1 v1t1)" DETAIL: Relation name "v1t1" is ambiguous. LOG: pg_hint_plan: used hint: not used hint: duplication hint: error hint: HashJoin(v1t1 v1t1) \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------- Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (v1t1.c1 = v1t1_1.c1) -> Seq Scan on t1 v1t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 v1t1_1 (cost=xxx rows=1000 width=xxx) (5 rows) \o results/ut-R.tmpout /*+HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)*/ EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1; INFO: pg_hint_plan: hint syntax error at or near "HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)" DETAIL: Relation name "v1t1" is ambiguous. INFO: pg_hint_plan: hint syntax error at or near "Rows(v1t1 v1t1 #1)" DETAIL: Relation name "v1t1" is ambiguous. LOG: pg_hint_plan: used hint: not used hint: duplication hint: error hint: HashJoin(v1t1 v1t1) Rows(v1t1 v1t1 #1) \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------- Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (v1t1.c1 = v1t1_1.c1) -> Seq Scan on t1 v1t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 v1t1_1 (cost=xxx rows=1000 width=xxx) (5 rows) -- No. R-2-3-5 \o results/ut-R.tmpout /*+NestLoop(v1t1 v1t1_)*/ EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1; LOG: pg_hint_plan: used hint: NestLoop(v1t1 v1t1_) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 v1t1 (cost=xxx rows=1000 width=xxx) -> Index Scan using t1_i1 on t1 v1t1_ (cost=xxx rows=1 width=xxx) Index Cond: (c1 = v1t1.c1) (4 rows) \o results/ut-R.tmpout /*+NestLoop(v1t1 v1t1_)Rows(v1t1 v1t1_ #1)*/ EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1; LOG: pg_hint_plan: used hint: NestLoop(v1t1 v1t1_) Rows(v1t1 v1t1_ #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) -> Seq Scan on t1 v1t1 (cost=xxx rows=1000 width=xxx) -> Index Scan using t1_i1 on t1 v1t1_ (cost=xxx rows=1 width=xxx) Index Cond: (c1 = v1t1.c1) (4 rows) -- No. R-2-3-6 \o results/ut-R.tmpout /*+RowsHashJoin(r4t1 r4t1)*/ EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "RowsHashJoin(r4t1 r4t1)" DETAIL: Unrecognized hint keyword "RowsHashJoin". \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------- Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (r4t1.c1 = r4t1_1.c1) -> Seq Scan on t1 r4t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 r4t1_1 (cost=xxx rows=1000 width=xxx) (5 rows) \o results/ut-R.tmpout /*+RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)*/ EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)" DETAIL: Unrecognized hint keyword "RowsHashJoin". \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------- Hash Join (cost=xxx rows=1000 width=xxx) Hash Cond: (r4t1.c1 = r4t1_1.c1) -> Seq Scan on t1 r4t1 (cost=xxx rows=1000 width=xxx) -> Hash (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 r4t1_1 (cost=xxx rows=1000 width=xxx) (5 rows) -- No. R-2-3-7 \o results/ut-R.tmpout /*+NestLoop(r4t1 r5t1)*/ EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: NestLoop(r4t1 r5t1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ---------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1000 width=xxx) -> Seq Scan on t1 r4t1 (cost=xxx rows=1000 width=xxx) -> Index Scan using t1_i1 on t1 r5t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = r4t1.c1) (4 rows) \o results/ut-R.tmpout /*+NestLoop(r4t1 r5t1)Rows(r4t1 r5t1 #1)*/ EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: NestLoop(r4t1 r5t1) Rows(r4t1 r5t1 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ---------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) -> Seq Scan on t1 r4t1 (cost=xxx rows=1000 width=xxx) -> Index Scan using t1_i1 on t1 r5t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = r4t1.c1) (4 rows) ---- ---- No. R-2-4 VALUES clause ---- -- No. R-2-4-1 \o results/ut-R.tmpout EXPLAIN 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; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=2 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) -> Hash (cost=xxx rows=2 width=xxx) -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = t2.c1) (8 rows) \o results/ut-R.tmpout /*+ Leading(t3 t1 t2) Rows(t3 t1 #2)Rows(t3 t1 t2 #2)*/ EXPLAIN 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: Leading(t3 t1 t2) Rows(t1 t3 #2) Rows(t1 t2 t3 #2) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) -> Hash Join (cost=xxx rows=2 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) -> Hash (cost=xxx rows=2 width=xxx) -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = t2.c1) (8 rows) \o results/ut-R.tmpout /*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #20)*/ EXPLAIN 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) Rows(*VALUES* t1 #2) Rows(*VALUES* t1 t2 #20) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------------- Nested Loop (cost=xxx rows=20 width=xxx) -> Nested Loop (cost=xxx rows=2 width=xxx) -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = "*VALUES*".column1) -> Index Scan using t2_i1 on t2 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = t1.c1) (7 rows) -- No. R-2-4-2 \o results/ut-R.tmpout EXPLAIN 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; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t2.c1 = "*VALUES*_1".column1) -> Hash Join (cost=xxx rows=2 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) -> Hash (cost=xxx rows=2 width=xxx) -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) -> Materialize (cost=xxx rows=2 width=xxx) -> Values Scan on "*VALUES*_1" (cost=xxx rows=2 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = t2.c1) (12 rows) \o results/ut-R.tmpout /*+ Leading(t4 t3 t2 t1) Rows(t4 t3 #2) Rows(t4 t3 t2 #2)Rows(t4 t3 t2 t1 #2)*/ EXPLAIN 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: Leading(t4 t3 t2 t1) Rows(t3 t4 #2) Rows(t2 t3 t4 #2) Rows(t1 t2 t3 t4 #2) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t2.c1 = "*VALUES*_1".column1) -> Hash Join (cost=xxx rows=2 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) -> Hash (cost=xxx rows=2 width=xxx) -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) -> Materialize (cost=xxx rows=2 width=xxx) -> Values Scan on "*VALUES*_1" (cost=xxx rows=2 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = t2.c1) (12 rows) \o results/ut-R.tmpout /*+ Leading(*VALUES* t3 t2 t1) Rows(t4 t3 #2)Rows(*VALUES* t3 t2 #2)Rows(*VALUES* t3 t2 t1 #2)*/ EXPLAIN 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 "Rows(*VALUES* t3 t2 #2)Rows(*VALUES* t3 t2 t1 #2)" DETAIL: Relation name "*VALUES*" is ambiguous. INFO: pg_hint_plan: hint syntax error at or near "Rows(*VALUES* t3 t2 t1 #2)" DETAIL: Relation name "*VALUES*" is ambiguous. INFO: pg_hint_plan: hint syntax error at or near "Leading(*VALUES* t3 t2 t1) Rows(t4 t3 #2)Rows(*VALUES* t3 t2 #2)Rows(*VALUES* t3 t2 t1 #2)" DETAIL: Relation name "*VALUES*" is ambiguous. LOG: pg_hint_plan: used hint: not used hint: Rows(t3 t4 #2) duplication hint: error hint: Leading(*VALUES* t3 t2 t1) Rows(*VALUES* t2 t3 #2) Rows(*VALUES* t1 t2 t3 #2) \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=1 width=xxx) Join Filter: (t2.c1 = "*VALUES*_1".column1) -> Hash Join (cost=xxx rows=2 width=xxx) Hash Cond: (t2.c1 = "*VALUES*".column1) -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) -> Hash (cost=xxx rows=2 width=xxx) -> Values Scan on "*VALUES*" (cost=xxx rows=2 width=xxx) -> Materialize (cost=xxx rows=2 width=xxx) -> Values Scan on "*VALUES*_1" (cost=xxx rows=2 width=xxx) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = t2.c1) (12 rows) ---- ---- No. R-2-5 ---- -- No. R-2-5-1 \o results/ut-R.tmpout EXPLAIN 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.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------------------------ Aggregate (cost=xxx rows=1 width=xxx) -> Nested Loop (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (13 rows) \o results/ut-R.tmpout /*+ Leading(bmt4 bmt3 bmt2 bmt1) Rows(bmt1 bmt2 bmt3 bmt4 *0.7) */ EXPLAIN SELECT 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.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; LOG: pg_hint_plan: used hint: Leading(bmt4 bmt3 bmt2 bmt1) Rows(bmt1 bmt2 bmt3 bmt4 *0.7) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------------------------- Merge Join (cost=xxx rows=70 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt2.c1) -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) (14 rows) -- No. R-2-5-2 \o results/ut-R.tmpout EXPLAIN SELECT 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.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------------------ Nested Loop (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (12 rows) \o results/ut-R.tmpout /*+ Leading(bmt4 bmt3 bmt2 bmt1) Rows(bmt4 bmt3 *0.6) */ EXPLAIN SELECT 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.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; LOG: pg_hint_plan: used hint: Leading(bmt4 bmt3 bmt2 bmt1) Rows(bmt3 bmt4 *0.6) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------------------------- Merge Join (cost=xxx rows=60 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=60 width=xxx) Sort Key: bmt2.c1 -> Hash Join (cost=xxx rows=60 width=xxx) Hash Cond: (bmt3.c1 = bmt2.c1) -> Hash Join (cost=xxx rows=678 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) (14 rows) -- No. R-2-5-3 \o results/ut-R.tmpout EXPLAIN SELECT 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.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------------------------------ Nested Loop (cost=xxx rows=100 width=xxx) -> Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Merge Join (cost=xxx rows=1000 width=xxx) Merge Cond: (bmt1.c1 = bmt3.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Index Only Scan using t3_i1 on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) -> Index Only Scan using t4_i1 on t4 bmt4 (cost=xxx rows=1 width=xxx) Index Cond: (c1 = bmt1.c1) (12 rows) \o results/ut-R.tmpout /*+ Leading(bmt4 bmt3 bmt2 bmt1) Rows(bmt4 bmt1 *0.5) */ EXPLAIN SELECT 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.c1 = sbmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1; LOG: pg_hint_plan: used hint: Leading(bmt4 bmt3 bmt2 bmt1) Rows(bmt1 bmt4 *0.5) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ----------------------------------------------------------------------------------------- Merge Join (cost=xxx rows=50 width=xxx) Merge Cond: (bmt1.c1 = bmt2.c1) -> Index Only Scan using t1_i1 on t1 bmt1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: bmt2.c1 -> Hash Join (cost=xxx rows=100 width=xxx) Hash Cond: (bmt3.c1 = bmt2.c1) -> Hash Join (cost=xxx rows=1130 width=xxx) Hash Cond: (bmt3.c1 = bmt4.c1) -> Seq Scan on t3 bmt3 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=1130 width=xxx) -> Seq Scan on t4 bmt4 (cost=xxx rows=1130 width=xxx) -> Hash (cost=xxx rows=100 width=xxx) -> Seq Scan on t2 bmt2 (cost=xxx rows=100 width=xxx) (14 rows) ---- ---- No. R-3-1 abusolute value ---- -- No. R-3-1-1 \o results/ut-R.tmpout /*+Rows(t1 t2 #0)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; WARNING: Force estimate to be at least one row, to avoid possible divide-by-zero when interpolating costs : Rows(t1 t2 #0) LOG: pg_hint_plan: used hint: Rows(t1 t2 #0) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-3-1-2 \o results/ut-R.tmpout /*+Rows(t1 t2 #5)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 #5) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=5 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) ---- ---- No. R-3-2 increase or decrease value ---- -- No. R-3-2-1 \o results/ut-R.tmpout /*+Rows(t1 t2 +1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 +1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=101 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-3-2-2 \o results/ut-R.tmpout /*+Rows(t1 t2 -1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 -1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=99 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-3-2-3 \o results/ut-R.tmpout /*+Rows(t1 t2 -1000)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; WARNING: Force estimate to be at least one row, to avoid possible divide-by-zero when interpolating costs : Rows(t1 t2 -1000) LOG: pg_hint_plan: used hint: Rows(t1 t2 -1000) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) ---- ---- No. R-3-3 multiple ---- -- No. R-3-3-1 \o results/ut-R.tmpout /*+Rows(t1 t2 *0)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; WARNING: Force estimate to be at least one row, to avoid possible divide-by-zero when interpolating costs : Rows(t1 t2 *0) LOG: pg_hint_plan: used hint: Rows(t1 t2 *0) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-3-3-2 \o results/ut-R.tmpout /*+Rows(t1 t2 *2)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 *2) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=200 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-3-3-3 \o results/ut-R.tmpout /*+Rows(t1 t2 *0.1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; LOG: pg_hint_plan: used hint: Rows(t1 t2 *0.1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=10 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) ---- ---- No. R-3-4 join inherit tables ---- -- No. R-3-4-1 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------- Hash Join (cost=xxx rows=301 width=xxx) Hash Cond: (p2.c1 = p1.c1) -> Append (cost=xxx rows=304 width=xxx) -> Seq Scan on p2 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c1 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c2 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c3 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c1c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c1c2 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c2c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c2c2 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c3c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c3c2 (cost=xxx rows=50 width=xxx) -> Hash (cost=xxx rows=301 width=xxx) -> Append (cost=xxx rows=301 width=xxx) -> Seq Scan on p1 (cost=xxx rows=1 width=xxx) -> Seq Scan on p1c1 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c2 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c3 (cost=xxx rows=100 width=xxx) (19 rows) \o results/ut-R.tmpout /*+Rows(p1 p2 #1)*/ EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1; LOG: pg_hint_plan: used hint: Rows(p1 p2 #1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------- Hash Join (cost=xxx rows=1 width=xxx) Hash Cond: (p2.c1 = p1.c1) -> Append (cost=xxx rows=304 width=xxx) -> Seq Scan on p2 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c1 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c2 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c3 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c1c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c1c2 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c2c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c2c2 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c3c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c3c2 (cost=xxx rows=50 width=xxx) -> Hash (cost=xxx rows=301 width=xxx) -> Append (cost=xxx rows=301 width=xxx) -> Seq Scan on p1 (cost=xxx rows=1 width=xxx) -> Seq Scan on p1c1 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c2 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c3 (cost=xxx rows=100 width=xxx) (19 rows) -- No. R-3-4-2 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------- Hash Join (cost=xxx rows=301 width=xxx) Hash Cond: (p2.c1 = p1.c1) -> Append (cost=xxx rows=304 width=xxx) -> Seq Scan on p2 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c1 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c2 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c3 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c1c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c1c2 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c2c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c2c2 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c3c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c3c2 (cost=xxx rows=50 width=xxx) -> Hash (cost=xxx rows=301 width=xxx) -> Append (cost=xxx rows=301 width=xxx) -> Seq Scan on p1 (cost=xxx rows=1 width=xxx) -> Seq Scan on p1c1 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c2 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c3 (cost=xxx rows=100 width=xxx) (19 rows) \o results/ut-R.tmpout /*+Rows(p1c1 p2c1 #1)*/ EXPLAIN SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1; LOG: pg_hint_plan: used hint: not used hint: Rows(p1c1 p2c1 #1) duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN ------------------------------------------------------------------------- Hash Join (cost=xxx rows=301 width=xxx) Hash Cond: (p2.c1 = p1.c1) -> Append (cost=xxx rows=304 width=xxx) -> Seq Scan on p2 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c1 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c2 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c3 (cost=xxx rows=1 width=xxx) -> Seq Scan on p2c1c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c1c2 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c2c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c2c2 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c3c1 (cost=xxx rows=50 width=xxx) -> Seq Scan on p2c3c2 (cost=xxx rows=50 width=xxx) -> Hash (cost=xxx rows=301 width=xxx) -> Append (cost=xxx rows=301 width=xxx) -> Seq Scan on p1 (cost=xxx rows=1 width=xxx) -> Seq Scan on p1c1 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c2 (cost=xxx rows=100 width=xxx) -> Seq Scan on p1c3 (cost=xxx rows=100 width=xxx) (19 rows) ---- ---- No. R-3-5 conflict join method hint ---- -- No. R-3-5-1 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t2 #1)Rows(t1 t2 #1)" DETAIL: Conflict rows hint. LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: Rows(t1 t2 #1) error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-3-5-2 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)Rows(t1 t2 #1)Rows(t1 t2 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t2 #1)Rows(t1 t2 #1)Rows(t1 t2 #1)" DETAIL: Conflict rows hint. INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t2 #1)Rows(t1 t2 #1)" DETAIL: Conflict rows hint. LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: Rows(t1 t2 #1) Rows(t1 t2 #1) error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-3-5-3 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 #1)Rows(t2 t1 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t2 #1)Rows(t2 t1 #1)" DETAIL: Conflict rows hint. LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: Rows(t1 t2 #1) error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) -- No. R-3-5-4 \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout /*+Rows(t2 t1 #1)Rows(t1 t2 #1)Rows(t2 t1 #1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; INFO: pg_hint_plan: hint syntax error at or near "Rows(t2 t1 #1)Rows(t1 t2 #1)Rows(t2 t1 #1)" DETAIL: Conflict rows hint. INFO: pg_hint_plan: hint syntax error at or near "Rows(t1 t2 #1)Rows(t2 t1 #1)" DETAIL: Conflict rows hint. LOG: pg_hint_plan: used hint: Rows(t1 t2 #1) not used hint: duplication hint: Rows(t1 t2 #1) Rows(t1 t2 #1) error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=1 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) ---- ---- No. R-3-6 hint state output ---- -- No. R-3-6-1 SET client_min_messages TO DEBUG1; \o results/ut-R.tmpout EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=100 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) \o results/ut-R.tmpout /*+Rows(t1 t2 +1)*/ EXPLAIN SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; DEBUG: adjusted rows 100 to 101 LOG: pg_hint_plan: used hint: Rows(t1 t2 +1) not used hint: duplication hint: error hint: \o \! sql/maskout.sh results/ut-R.tmpout QUERY PLAN --------------------------------------------------------------------------- Merge Join (cost=xxx rows=101 width=xxx) Merge Cond: (t1.c1 = t2.c1) -> Index Scan using t1_i1 on t1 (cost=xxx rows=1000 width=xxx) -> Sort (cost=xxx rows=100 width=xxx) Sort Key: t2.c1 -> Seq Scan on t2 (cost=xxx rows=100 width=xxx) (6 rows) \! rm results/ut-R.tmpout