このトピックでは、自動インデックスレコメンデーション機能の背景情報と使用方法について説明します。
前提条件
PolarDB for PostgreSQL クラスタは、リビジョンバージョン 2.0.14.10.19.0 以降の PolarDB for PostgreSQL 14 を実行しています。
PolarDB コンソールで、または
SHOW polardb_version;文を実行することで、クラスタの リビジョンバージョンを確認できます。リビジョンバージョンが要件を満たしていない場合は、更新してください。PolarDB for PostgreSQL 分散クラスタは、自動インデックスレコメンデーション機能をサポートしていません。
背景情報
データベースパフォーマンスの最適化は、データベース管理の鍵となります。インデックスは、SQL クエリのパフォーマンス向上に重要な役割を果たします。最適化されたインデックスにより、データベースはテーブル内の特定の行をすばやく特定し、ソートされたデータを取得できます。これにより、クエリ時間が大幅に短縮されます。
ただし、すべてのインデックスがデータベースのパフォーマンスを向上させるわけではありません。適切なインデックスは、クエリのパフォーマンスを大幅に向上させることができます。しかし、不適切なインデックスは、データベースの全体的なパフォーマンスを低下させる追加のメンテナンスオーバーヘッドを引き起こす可能性があります。無効なインデックスは、追加のストレージスペースも占有します。どの列にインデックスを作成するかを決定するには、クエリの実行パスとデータ分布を詳細に分析する必要があります。これには、この分野における強力な技術スキルと豊富な経験が必要です。
PolarDB for PostgreSQL は、この課題を克服し、データベースのパフォーマンスを最適化するのに役立つ自動インデックスレコメンデーション機能を提供します。この機能は、インデックス作成のプロセスを簡素化し、インデックスの開発とメンテナンスの難しさを軽減します。EXPLAIN 文を実行して、遅い SQL 文のパフォーマンスを分析できます。システムは、分析に基づいて、適切なインデックスを作成する方法とクエリのパフォーマンス向上の推定に関する提案を提供できます。
自動インデックスレコメンデーション機能には、主に次の利点があります。
正確なレコメンデーション: データベースによって自動的に収集された統計とオプティマイザーコストモデルを使用して、特定の SQL クエリに適切なインデックス構成を推奨します。これは、無効なインデックスによって引き起こされるパフォーマンスの負担と試行錯誤のコストを回避するのに役立ちます。
効率の向上: 遅いクエリの分析に手動で必要な時間を短縮します。これは、パフォーマンスの問題の特定と解決を迅速化するのに役立ちます。
使いやすさ: シンプルな
EXPLAIN文を使用して、インデックスレコメンデーション情報、インデックスを作成するための SQL 文、および推定パフォーマンスの向上を提供します。これにより、後続の操作が最適化されます。
使用方法
推奨インデックスが要件を満たしていない場合、またはインデックスが推奨されていない場合は、トラブルシューティングと最適化について お問い合わせ ください。
DMS クライアントは通知プロンプトを表示しないため、PostgreSQL クライアントを使用して次の操作を実行することをお勧めします。
自動インデックスレコメンデーションの構成
polar_advisorをsession_preload_librariesに追加して、すべてのユーザーに対してpolar_advisorをロードします。-- session_preload_libraries の値はデフォルトでは空です。この例では、polar_advisor が追加されています。 -- session_preload_libraries の値は、デフォルトでは空です。この例では、polar_advisor が追加されています。 alter role all set session_preload_libraries to 'polar_advisor';すべてのユーザーに対して、特定のノードでインデックスレコメンデーションを有効にします。プライマリノードの計算リソースを消費しないように、読み取り専用ノードでのみインデックスレコメンデーションを有効にすることをお勧めします。
-- 読み取り専用ノードでのみインデックスレコメンデーションを有効にします（推奨）。 alter role all set polar_advisor_type to 'index(ro)';-- すべてのノードでインデックスレコメンデーションを有効にします。 alter role all set polar_advisor_type to 'index';
自動インデックスレコメンデーションの使用
ANALYZE 句なしで EXPLAIN 文を実行して、遅い SQL クエリの実行プランを生成します。推奨インデックスが検出された場合、自動インデックスレコメンデーション機能が自動的に実行され、次の情報が返されます。
advise: 推奨インデックスを作成するために使用される文。
old cost: 現在のプランのコスト。
new cost: 推奨インデックスが作成された後のプランの推定コスト。
saved cost: 古いプランと比較して新しいプランで節約されたコスト。
estimate: 推定加速率。
自動インデックスレコメンデーション機能は、次のタイプの演算子をサポートし、B-tree インデックスのみを推奨できます。例:
次の文を使用して、テーブルを作成し、データを挿入します。
create table t( a int,b int);
insert into t select i,i from generate_series(1,10000)i;
analyze t;等価条件に基づいて単一列インデックスを推奨します。
/*FORCE_SLAVE*/ explain select * from t where a = 1; INFO: IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)" NOTICE: IND ADV: old cost 0.00..170.00 NOTICE: IND ADV: new cost 0.04..2.25 NOTICE: IND ADV: saved cost -0.04..167.75 NOTICE: IND ADV: estimate 75.5 times faster QUERY PLAN --------------------------------------------------- Seq Scan on t (cost=0.00..170.00 rows=1 width=8) Filter: (a = 1) (2 rows)比較条件に基づいて単一列インデックスを推奨します。
/*FORCE_SLAVE*/ explain select * from t where a > 10; -- 返されるデータ量は少ないです。インデックスは推奨されません。 QUERY PLAN ------------------------------------------------------ Seq Scan on t (cost=0.00..170.00 rows=9990 width=8) Filter: (a > 10) (2 rows) /*FORCE_SLAVE*/ explain select * from t where a < 10; INFO: IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)" NOTICE: IND ADV: old cost 0.00..170.00 NOTICE: IND ADV: new cost 0.04..2.39 NOTICE: IND ADV: saved cost -0.04..167.61 NOTICE: IND ADV: estimate 71.1 times faster QUERY PLAN --------------------------------------------------- Seq Scan on t (cost=0.00..170.00 rows=9 width=8) Filter: (a < 10) (2 rows)複数の等価条件または比較条件に基づいて複合インデックスを推奨します。
/*FORCE_SLAVE*/ explain select * from t where a = 1 and b = 1; INFO: IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (b, a)" NOTICE: IND ADV: old cost 0.00..195.00 NOTICE: IND ADV: new cost 0.04..1.16 NOTICE: IND ADV: saved cost -0.04..193.84 NOTICE: IND ADV: estimate 168.8 times faster QUERY PLAN --------------------------------------------------- Seq Scan on t (cost=0.00..195.00 rows=1 width=8) Filter: ((a = 1) AND (b = 1)) (2 rows)Order BY演算子に基づいて単一列インデックスを推奨します。/*FORCE_SLAVE*/ explain select * from t order by a limit 10; INFO: IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)" NOTICE: IND ADV: old cost 361.10..361.12 NOTICE: IND ADV: new cost 0.04..0.26 NOTICE: IND ADV: saved cost 361.06..360.86 NOTICE: IND ADV: estimate 1366.8 times faster QUERY PLAN ------------------------------------------------------------------- Limit (cost=361.10..361.12 rows=10 width=8) -> Sort (cost=361.10..386.10 rows=10000 width=8) Sort Key: a -> Seq Scan on t (cost=0.00..145.00 rows=10000 width=8) (4 rows)Group BY演算子に基づいて単一列インデックスを推奨します。/*FORCE_SLAVE*/ explain select a,sum(b) from t group by a having a < 10 ; INFO: IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)" NOTICE: IND ADV: old cost 170.14..170.30 NOTICE: IND ADV: new cost 0.04..2.53 NOTICE: IND ADV: saved cost 170.11..167.77 NOTICE: IND ADV: estimate 67.4 times faster QUERY PLAN --------------------------------------------------------------- GroupAggregate (cost=170.14..170.30 rows=9 width=12) Group Key: a -> Sort (cost=170.14..170.17 rows=9 width=8) Sort Key: a -> Seq Scan on t (cost=0.00..170.00 rows=9 width=8) Filter: (a < 10) (6 rows)等結合操作に基づいて単一列インデックスを推奨します。
/*FORCE_SLAVE*/ explain select * from t t1,t t2 where t1.a = t2.a limit 10; INFO: IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)" NOTICE: IND ADV: old cost 270.00..270.28 NOTICE: IND ADV: new cost 0.07..0.70 NOTICE: IND ADV: saved cost 269.93..269.58 NOTICE: IND ADV: estimate 384.3 times faster QUERY PLAN ---------------------------------------------------------------------------- Limit (cost=270.00..270.28 rows=10 width=16) -> Hash Join (cost=270.00..552.50 rows=10000 width=16) Hash Cond: (t1.a = t2.a) -> Seq Scan on t t1 (cost=0.00..145.00 rows=10000 width=8) -> Hash (cost=145.00..145.00 rows=10000 width=8) -> Seq Scan on t t2 (cost=0.00..145.00 rows=10000 width=8) (6 rows)