このトピックでは、自動インデックス推奨機能の背景と使用方法について説明します。
適用範囲
この機能は、マイナーエンジンバージョンが 2.0.14.10.19.0 以降の PostgreSQL 14 を実行する PolarDB for PostgreSQL クラスターでサポートされています。
PolarDB コンソールで、または
SHOW polardb_version;文を実行することで、マイナーエンジンバージョンを確認できます。ご利用のクラスターのマイナーエンジンバージョンが要件を満たしていない場合は、マイナーエンジンバージョンをアップグレードできます。PolarDB for PostgreSQL 分散クラスターは、自動インデックス推奨機能をサポートしていません。
背景情報
データベースのパフォーマンス最適化は、データベース管理における重要なタスクです。インデックスは、SQL クエリのパフォーマンスを向上させるために不可欠です。優れたインデックスは、データベースがテーブル内の特定の行を迅速に見つけ、ソートされたデータを取得するのに役立ち、クエリ時間を大幅に短縮します。
しかし、すべてのインデックスがパフォーマンスを向上させるわけではありません。適切なインデックスはクエリを大幅に高速化できますが、不適切なインデックスは役に立たないばかりか、メンテナンスのオーバーヘッドによりデータベースの速度を低下させる可能性さえあります。また、インデックスはストレージ領域を使用するため、不要なインデックスが多すぎるとストレージリソースを浪費します。いつ、どの列にインデックスを作成するかを決定するには、技術的なスキルと経験が必要です。この決定には、多くの場合、クエリ実行計画とデータ分布の詳細な分析が必要です。
この課題を解決し、データベースのパフォーマンスを最適化するために、PolarDB for PostgreSQL および は、自動インデックス推奨機能を提供します。この機能はインデックス作成のプロセスを簡素化し、データベースインデックスの開発とメンテナンスを容易にします。EXPLAIN コマンドを使用して低速 SQL 文を分析できます。その後、システムは適切なインデックスを提案し、期待されるパフォーマンス向上を見積もります。
自動インデックス推奨機能の主な利点は次のとおりです：
正確な推奨：この機能は、自動的に収集された統計情報とオプティマイザーのコストモデルを使用して、特定の SQL クエリに対して正しいインデックスを推奨します。これにより、やみくもにインデックスを追加することに伴うパフォーマンスの問題や試行錯誤のコストを回避できます。
効率の向上：この機能は、低速クエリを手動で分析するのに必要な時間を短縮します。これにより、パフォーマンスボトルネックを迅速に発見して修正し、問題の特定と解決にかかる時間を短縮できます。
使いやすさ：簡単な
EXPLAINコマンドでインデックスの推奨が得られます。この機能は、インデックスを作成するための SQL コマンドも提供し、パフォーマンスの向上を予測します。これにより、ワークフローが効率化されます。
利用ガイド
推奨されたインデックスが期待どおりに機能しない場合、または適切なインデックスが推奨されない場合は、お問い合わせいただき、トラブルシューティングと最適化を行ってください。
DMS クライアントは現在 notice プロンプトを表示しないため、以下の操作には psql クライアントを使用してください。
自動インデックスレコメンデーションの構成
polar_advisorをsession_preload_librariesに追加して、すべてのユーザーに対してpolar_advisorをロードします。-- session_preload_libraries パラメーターはデフォルトで空です。このコマンドは polar_advisor を追加します。 alter role all set session_preload_libraries to 'polar_advisor';すべてのユーザーに対して、特定のノードでインデックス推奨を有効にします。読み書き (RW) ノードのコンピューティングリソースを消費しないように、この機能は読み取り専用 (RO) ノードでのみ有効にすることを推奨します。
-- RO ノードでインデックス推奨を有効にします。[推奨] 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)