From 501fdfce138690880661839510642f56cc26e669 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Wed, 19 Jan 2022 15:12:22 +0900 Subject: [PATCH] Add forgotten test files 9e2f8b6 forgot to contain the .sql and expected/.out files for hints_anywhere feature. Add them. --- expected/hints_anywhere.out | 83 +++++++++++++++++++++++++++++++++++++++++++++ sql/hints_anywhere.sql | 24 +++++++++++++ 2 files changed, 107 insertions(+) create mode 100644 expected/hints_anywhere.out create mode 100644 sql/hints_anywhere.sql diff --git a/expected/hints_anywhere.out b/expected/hints_anywhere.out new file mode 100644 index 0000000..e126e75 --- /dev/null +++ b/expected/hints_anywhere.out @@ -0,0 +1,83 @@ +LOAD 'pg_hint_plan'; +SET client_min_messages TO log; +\set SHOW_CONTEXT always +SET pg_hint_plan.debug_print TO on; +explain (costs false) +select * from t1 join t2 on t1.id = t2.id where '/*+HashJoin(t1 t2)*/' <> ''; + QUERY PLAN +-------------------------------------- + Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 +(4 rows) + +set pg_hint_plan.hints_anywhere = on; +explain (costs false) +select * from t1 join t2 on t1.id = t2.id where '/*+HashJoin(t1 t2)*/' <> ''; +LOG: pg_hint_plan: +used hint: +HashJoin(t1 t2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------ + Hash Join + Hash Cond: (t1.id = t2.id) + -> Seq Scan on t1 + -> Hash + -> Seq Scan on t2 +(5 rows) + +set pg_hint_plan.hints_anywhere = off; +explain (costs false) +select * from t1 join t2 on t1.id = t2.id where '/*+HashJoin(t1 t2)*/' <> ''; + QUERY PLAN +-------------------------------------- + Merge Join + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 + -> Index Scan using t2_pkey on t2 +(4 rows) + +set pg_hint_plan.hints_anywhere = on; +/*+ MergeJoin(t1 t2) */ +explain (costs false) +select * from t1 join t2 on t1.val = t2.val where '/*+HashJoin(t1 t2)*/' <> ''; +LOG: pg_hint_plan: +used hint: +MergeJoin(t1 t2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------- + Merge Join + Merge Cond: (t2.val = t1.val) + -> Index Scan using t2_val on t2 + -> Materialize + -> Index Scan using t1_val on t1 +(5 rows) + +/*+ HashJoin(t1 t2) */ +explain (costs false) +select * from t1 join t2 on t1.val = t2.val where '/*+MergeJoin(t1 t2)*/' <> ''; +LOG: pg_hint_plan: +used hint: +HashJoin(t1 t2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Hash Join + Hash Cond: (t2.val = t1.val) + -> Seq Scan on t2 + -> Hash + -> Seq Scan on t1 +(5 rows) + diff --git a/sql/hints_anywhere.sql b/sql/hints_anywhere.sql new file mode 100644 index 0000000..b567df3 --- /dev/null +++ b/sql/hints_anywhere.sql @@ -0,0 +1,24 @@ +LOAD 'pg_hint_plan'; +SET client_min_messages TO log; +\set SHOW_CONTEXT always +SET pg_hint_plan.debug_print TO on; + +explain (costs false) +select * from t1 join t2 on t1.id = t2.id where '/*+HashJoin(t1 t2)*/' <> ''; + +set pg_hint_plan.hints_anywhere = on; +explain (costs false) +select * from t1 join t2 on t1.id = t2.id where '/*+HashJoin(t1 t2)*/' <> ''; + +set pg_hint_plan.hints_anywhere = off; +explain (costs false) +select * from t1 join t2 on t1.id = t2.id where '/*+HashJoin(t1 t2)*/' <> ''; + +set pg_hint_plan.hints_anywhere = on; +/*+ MergeJoin(t1 t2) */ +explain (costs false) +select * from t1 join t2 on t1.val = t2.val where '/*+HashJoin(t1 t2)*/' <> ''; + +/*+ HashJoin(t1 t2) */ +explain (costs false) +select * from t1 join t2 on t1.val = t2.val where '/*+MergeJoin(t1 t2)*/' <> ''; -- 2.11.0