AnalyticDB for PostgreSQLでは、ヒントを使用して実行計画を変更できます。 たとえば、ヒントを使用して、クエリステートメントのORCAオプティマイザを有効または無効にしたり、JOINの順序とタイプを変更したりできます。
前提条件
V6.3.7.0以降のAnalyticDB for PostgreSQLインスタンスが作成されます。 インスタンスのマイナーバージョンを表示および更新する方法の詳細については、「マイナーエンジンバージョンの更新」をご参照ください。
pg_hint_plan拡張機能がインストールされています。 詳細については、「ヒント機能の使用」をご参照ください。
クエリ文のORCAオプティマイザの有効化または無効化
ヒントを有効にして実行計画を変更し、ヒント登録機能を有効にします。
SET pg_hint_plan.enable_hint to on; SET pg_hint_plan.enable_hint_table to on;テーブルt1およびt2を作成する。
CREATE TABLE t1 (id int PRIMARY KEY, val int); CREATE TABLE t2 (id int PRIMARY KEY, val int);テーブルt1とt2にデータを挿入する。
INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t; INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;テーブルt1とt2のインデックスを作成します。
CREATE INDEX t1_val ON t1 (val); CREATE INDEX t2_val ON t2 (val);ORCAオプティマイザを無効にします。
SELECT * FROM hint_plan.insert_hint_table($$/*+ SET(optimizer off) */EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val WHERE t1.id = 1;$$);次の情報が返されます。
-[ RECORD 1 ]---------+----------------------------------------------------- id | 1 norm_query_string | explain select * from t1 join t2 on t1.val = t2.val; application_name | hints | set(optimizer off) query_hash | -2169095602568752481 enable | t prepare_param_strings | {}実行プランを表示します。
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val WHERE t1.id = 3;Postgresクエリオプティマイザーは、実行計画で使用されます。 次の情報が返されます。
QUERY PLAN ------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice2; segments: 3) (cost=8.25..27.61 rows=11 width=16) -> Hash Join (cost=8.25..27.61 rows=4 width=16) Hash Cond: (t2.val = t1.val) -> Seq Scan on t2 (cost=0.00..13.00 rows=334 width=8) -> Hash (cost=8.22..8.22 rows=1 width=8) -> Broadcast Motion 1:3 (slice1; segments: 1) (cost=0.16..8.22 rows=3 width=8) -> Index Scan using t1_pkey on t1 (cost=0.16..8.18 rows=1 width=8) Index Cond: (id = 3) Optimizer: Postgres query optimizer (9 rows)ORCAオプティマイザを有効にします。
SELECT * FROM hint_plan.insert_hint_table($$/*+ SET(optimizer on) SET(rds_optimizer_options 0) */SELECT * FROM t1 WHERE t1.id = 1;$$);次の情報が返されます。
-[ RECORD 1 ]---------+------------------------------------------------ id | 2 norm_query_string | select * from t1 where t1.id = $1; application_name | hints | set(optimizer on) set(rds_optimizer_options 0) query_hash | -8281826471521807124 enable | t prepare_param_strings | {}実行プランを表示します。
EXPLAIN SELECT * FROM t1 WHERE t1.id = 2;ORCAオプティマイザは実行計画で使用されます。 次の情報が返されます。
QUERY PLAN ---------------------------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.00 rows=1 width=8) -> Index Scan using t1_pkey on t1 (cost=0.00..6.00 rows=1 width=8) Index Cond: (id = 1) Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0 (4 rows)
JOINタイプとJOIN順序の変更
ヒントを有効にして実行計画を変更し、ヒント登録機能を有効にします。
SET optimizer to off; SET pg_hint_plan.enable_hint to on; SET pg_hint_plan.enable_hint_table to on;テーブルt1およびt2を作成する。
CREATE TABLE t1 (id int PRIMARY KEY, val int); CREATE TABLE t2 (id int PRIMARY KEY, val int);テーブルt1とt2にデータを挿入する。
INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t; INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;テーブルt1とt2のインデックスを作成します。
CREATE INDEX t1_val ON t1 (val); CREATE INDEX t2_val ON t2 (val);実行プランを表示します。
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;デフォルトの実行プランが表示され、JOINタイプはハッシュ結合、join順序は
(t1 t2)です。 次の情報が返されます。QUERY PLAN ---------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=25.50..208.00 rows=800 width=16) -> Hash Join (cost=25.50..208.00 rows=267 width=16) Hash Cond: (t1.id = t2.id) -> Seq Scan on t1 (cost=0.00..137.00 rows=3334 width=8) Filter: (val < 100) -> Hash (cost=15.50..15.50 rows=267 width=8) -> Seq Scan on t2 (cost=0.00..15.50 rows=267 width=8) Filter: (val > 1) Optimizer: Postgres query optimizer次のいずれかの方法を使用して、JOINタイプとJOIN順序を変更します。
実行計画ステートメントの先頭にヒントを追加します。
/*+ MergeJoin(t1 t2) Leading((t2 t1)) */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;JOINタイプはハッシュ結合からマージ結合に変わり、join順序は (t1 t2) から (t2 t1) に変わります。 次の情報が返されます。
QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.31..93.54 rows=800 width=16) -> Merge Join (cost=0.31..93.54 rows=267 width=16) Merge Cond: (t2.id = t1.id) -> Index Scan using t2_pkey on t2 (cost=0.15..52.90 rows=267 width=8) Filter: (val > 1) -> Index Scan using t1_pkey on t1 (cost=0.16..279.91 rows=3334 width=8) Filter: (val < 100) Optimizer: Postgres query optimizer (8 rows)ヒントを登録して実行プランを表示します。
ヒントをSQLパターンに登録します。
SELECT * FROM hint_plan.insert_hint_table($$/*+ MergeJoin(t1 t2) Leading((t2 t1)) */SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);次の情報が返されます。
-[ RECORD 1 ]---------+-------------------------------------------------------------------------- id | 1 norm_query_string | SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; application_name | hints | MergeJoin(t1 t2) Leading((t2 t1)) query_hash | -4733464863014584191 enable | t prepare_param_strings | {}実行プランを表示します。
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;ヒントが有効になります。 JOINタイプはハッシュ結合からマージ結合に変わり、join順序は (t1 t2) から (t2 t1) に変わります。 次の情報が返されます。
QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.31..93.54 rows=800 width=16) -> Merge Join (cost=0.31..93.54 rows=267 width=16) Merge Cond: (t2.id = t1.id) -> Index Scan using t2_pkey on t2 (cost=0.15..52.90 rows=267 width=8) Filter: (val > 1) -> Index Scan using t1_pkey on t1 (cost=0.16..279.91 rows=3334 width=8) Filter: (val < 100) Optimizer: Postgres query optimizer (8 rows)同じSQLパターンを使用するクエリ文の実行プランを表示します。
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 80 and t2.val > 20;次の情報が返されます。 ヒントが有効になり、同じSQLパターンを使用するクエリ文の実行プランが変更されます。
QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=8.25..38.24 rows=13 width=16) -> Merge Join (cost=8.25..38.24 rows=5 width=16) Merge Cond: (t1.id = t2.id) -> Index Scan using t1_pkey on t1 (cost=0.16..279.91 rows=2667 width=8) Filter: (val < 80) -> Sort (cost=8.09..8.09 rows=1 width=8) Sort Key: t2.id -> Index Scan using t2_val on t2 (cost=0.15..8.08 rows=1 width=8) Index Cond: (val > 20) Optimizer: Postgres query optimizer (10 rows)(オプション) SQLパターンからヒントを削除します。
SELECT * FROM hint_plan.delete_all_hint_table($$SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);
クエリ文で固定定数が存在するときにヒントを登録し、実行プランを表示します。
PREPAREステートメントを使用して、ヒントをSQLパターンに登録します。
SELECT * FROM hint_plan.insert_hint_table($$/*+ MergeJoin(t1 t2) Leading((t2 t1)) */PREPARE a AS SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > $1;$$);次の情報が返されます。
-[ RECORD 1 ]---------+-------------------------------------------------------------------------- id | 1 norm_query_string | PREPARE a AS SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < $1 and t2.val > $2; application_name | hints | MergeJoin(t1 t2) Leading((t2 t1)) query_hash | -4733464863014584191 enable | t prepare_param_strings | {100,$1}パラメーター化された定数が変更されたクエリ文の実行プランを表示します。
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 2;パラメータ化された定数が変更された後も、ヒントは有効になります。 JOINタイプはハッシュ結合からマージ結合に変わり、join順序は (t1 t2) から (t2 t1) に変わります。 次の情報が返されます。
QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.31..93.54 rows=800 width=16) -> Merge Join (cost=0.31..93.54 rows=267 width=16) Merge Cond: (t1.id = t2.id) -> Index Scan using t1_pkey on t1 (cost=0.16..279.91 rows=3334 width=8) Filter: (val < 100) -> Index Scan using t2_pkey on t2 (cost=0.15..52.90 rows=267 width=8) Filter: (val > 2) Optimizer: Postgres query optimizer (8 rows)固定定数を変更したクエリ文の実行プランを表示します。
EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 80 and t2.val > 20;固定定数が変更された後、ヒントは有効になりません。 次の情報が返されます。
QUERY PLAN ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=8.09..175.22 rows=13 width=16) -> Hash Join (cost=8.09..175.22 rows=5 width=16) Hash Cond: (t1.id = t2.id) -> Seq Scan on t1 (cost=0.00..137.00 rows=2667 width=8) Filter: (val < 80) -> Hash (cost=8.08..8.08 rows=1 width=8) -> Index Scan using t2_val on t2 (cost=0.15..8.08 rows=1 width=8) Index Cond: (val > 20) Optimizer: Postgres query optimizer (9 rows)(オプション) SQLパターンからヒントを削除します。
SELECT * FROM hint_plan.delete_all_hint_table($$SELECT * FROM t1, t2 WHERE t1.id = t2.id and t1.val < 100 and t2.val > 1;$$);