インデックスのチューニング

適用対象: Azure Database for PostgreSQL - フレキシブル サーバー

インデックスのチューニングは、追跡対象のクエリを分析し、インデックスの推奨事項を提供することで、ワークロードのパフォーマンスを自動的に向上させる Azure Database for PostgreSQL フレキシブル サーバーの機能です。

これは Azure Database for PostgreSQL フレキシブル サーバーの組み込みオファリングであり、 クエリ ストア機能を備えた Monitor パフォーマンス に基づいています。 インデックスチューニングは、クエリ ストアによって追跡されるワークロードを分析し、分析されたワークロードのパフォーマンスを向上させたり、重複または未使用のインデックスを削除したりするためのインデックスの推奨事項を生成します。

• インデックスチューニング セッションの間に分析されたクエリが大幅に向上する可能性があるために作成すると有益なインデックスを特定します。
• その存在とメンテナンスがシステムの全体的なパフォーマンスに及ぼす影響を軽減するため、完全に重複していて除去できるインデックスを特定します。
• 除去の候補になる可能性がある、構成可能な期間内に使われていないインデックスを特定します。

インデックス チューニング アルゴリズムの一般的な説明

index_tuning.mode サーバー パラメーターが report に構成されている場合、チューニング セッションは、サーバー パラメーター index_tuning.analysis_interval (分単位) で構成された頻度で自動的に開始されます。

最初のフェーズで、チューニング セッションは、生成される推奨事項がシステム全体のパフォーマンスに大きな影響を与える可能性があると思われるデータベースの一覧を検索します。 そのため、このチューニング セッションの検索対象期間内に実行が取り込まれ、クエリ ストアによって記録されたすべてのクエリが収集されます。 現在、検索間隔の範囲は、チューニング セッションの開始時刻から過去 index_tuning.analysis_interval 分までです。

クエリ ストアに実行が記録されていて、実行時統計がリセットされていない、すべてのユーザー開始クエリについて、システムは集計された合計実行時間に基づいてそれらをランク付けします。 クエリの実行時間に基づき、最も重要度の高いクエリに焦点を当てます。

次のクエリは、その一覧から除外されます。

• システムによって開始されたクエリ。 (つまり、azuresu ロールによって実行されたクエリ)
• システム データベース (azure_sys、template0、template1、azure_maintenance) のコンテキストで実行されたクエリ。

アルゴリズムはターゲットのデータベースを反復処理し、分析対象のワークロードのパフォーマンスを向上させる可能性のあるインデックスを検索します。 また、重複することがわかったため、または構成可能な期間に使われていないために除去できるインデックスも検索します。

CREATE INDEX に関する推奨事項

インデックスの推奨事項を生成するための分析の候補として特定された各データベースで、検索期間中に、その特定のデータベースのコンテキストで実行されたすべての SELECT、UPDATE、INSERT、DELETE クエリが考慮されます。

結果として得られたクエリのセットは、集計された合計実行時間に基づいてランク付けされ、1 番目の index_tuning.max_queries_per_database で可能性のあるインデックスの推奨事項が分析されます。

次の種類のクエリのパフォーマンスを向上させるための推奨事項事項が提供される可能性があります。

• フィルターを含むクエリ (つまり、WHERE 句に述語のあるクエリ)
• 複数の関係を結合するクエリ。結合が JOIN 句で表される構文に従っているか、または結合述語が WHERE 句で表されているかどうか。
• フィルターと結合述語を組み合わせたクエリ。
• グループ化を含むクエリ (GROUP BY 句を使うクエリ)。
• フィルターとグループ化を組み合わせたクエリ。
• 並べ替えを含むクエリ (ORDER BY 句を使うクエリ)。
• フィルターと並べ替えを組み合わせたクエリ。

クエリがテーブルの 1 つの列を参照し、そのテーブルに統計がない場合、クエリ全体がスキップされ、実行を改善するためのインデックスのレコメンデーションは生成されません。

統計を収集するために必要な分析は、ANALYZE コマンドを使用して手動でトリガーすることも、自動バキューム デーモンによって自動的にトリガーすることもできます。

index_tuning.max_indexes_per_table は、推奨できるインデックスの数を指定します。ただし、チューニング セッションの間に任意の数のクエリによって参照された単一のテーブルについて、テーブルに既に存在する可能性があるインデックスは除きます。

index_tuning.max_index_count は、チューニング セッションの間に分析されるデータベースのすべてのテーブルについて生成されるインデックス推奨事項の数を指定します。

インデックス推奨事項が出力されるためには、分析対象のワークロード内の少なくとも 1 つのクエリが、index_tuning.min_improvement_factor で指定された係数だけ改善すると、チューニング エンジンが推定する必要があります。

同様に、すべてのインデックス推奨事項がチェックされて、そのワークロード内のどの 1 つのクエリでも、index_tuning.max_regression_factor で指定された係数の回帰が発生しないことが確認されます。

これまでの段落で説明したすべてのパラメーター、その既定値、有効範囲については、構成オプションに関するセクションで説明されています。

インデックス作成推奨事項と共に生成されるスクリプトは、次のパターンに従います。

create index concurrently {indexName} on {schema}.{table}({column_name}[, ...])

concurrently 句を含みます。 この句の効果について詳しくは、CREATE INDEX に関する PostgreSQL の公式ドキュメントを参照してください。

インデックス チューニングでは、推奨されるインデックスの名前が自動的に生成されます。これは通常、"_" (アンダースコア) によって区切られた異なるキー列の名前と、固定の "_idx" サフィックスで構成されます。 名前の合計長が PostgreSQL の制限を超える場合、または既存の関係と競合する場合、名前は若干異なるものになります。 切り詰められて、名前の末尾に数字が追加される可能性があります。

CREATE INDEX の推奨事項の影響を計算する

インデックスの推奨事項を作成した場合の影響は、IndexSize (メガバイト) と QueryCostImprovement (パーセンテージ) について測定されます。

IndexSize は、テーブルの現在のカーディナリティと、推奨インデックスによって参照される列のサイズを考慮した、インデックスの推定サイズを表す 1 つの値です。

QueryCostImprovement は値の配列で構成され、各要素は、このインデックスが存在した場合にプランのコストが改善すると推定される各クエリについて、プランのコストがどの程度改善されるかを表します。 各要素では、クエリの識別子 (queried) と、推奨事項が実装された場合にプランのコストが改善する割合 (dimensional) が示されています。

DROP INDEX と REINDEX のレコメンデーション

インデックス チューニング機能が決定されたデータベースごとに、新しいセッションを開始する必要があります。また、CREATE INDEX レコメンデーション フェーズの完了後に、次の条件に基づいて既存のインデックスをドロップするか、インデックスを再作成することをお勧めします。

• 他のものと重複していると考えられる場合はドロップします。
• 構成可能な期間に使用されていない場合はドロップします。
• 無効とマークされたインデックスのインデックスを再作成します。

重複するインデックスを削除する

重複するインデックスの削除に関する推奨事項: 最初に、重複しているインデックスを特定します。

重複は、インデックスに起因する可能性のあるさまざまな関数と、その推定サイズに基づいてランク付けされます。

最後に、その参照リーダーより低いランク付けのすべての重複の削除を推奨し、各重複がそのようにランク付けされた理由を説明します。

2 つのインデックスが重複と見なされるためには、それらが次のようになっている必要があります。

• 同じテーブルに対して作成されている。
• まったく同じ型のインデックスである。
• キー列が一致し、複数列のインデックス キーの場合は、それらが参照される順序が一致する。
• その述語の式ツリーが一致する。 部分インデックスにのみ適用される。
• すべての非単純列の参照の式ツリーが一致する。 式で作成されたインデックスにのみ適用される。
• キーで参照される各列の照合順序が一致する。

未使用のインデックスを削除する

未使用のインデックスの削除に関する推奨事項では、次のインデックスが識別されます。

• 少なくとも index_tuning.unused_min_period 日間使われていない。
• インデックスが作成されたテーブルで最小の index_tuning.unused_dml_per_table DML の量 (日単位の平均) を示します。
• インデックスが作成されたテーブルで最小の index_tuning.unused_reads_per_table 読み取りの量 (日単位の平均) を示します。

無効なインデックスのインデックスを再作成する

既存のインデックスのインデックス再作成に関するレコメンデーションで、無効とマークされているインデックスが特定されます。 インデックスが無効とマークされる理由とタイミングの詳細については、PostgreSQL 公式ドキュメントの「REINDEX」を参照してください。

DROP INDEX の推奨事項の影響を計算する

インデックス削除推奨事項の影響は、Benefit (パーセンテージ) と IndexSize (メガバイト) の 2 つのディメンションで測定されます。

この Benefit は、現時点では無視できる 1 つの値です。

IndexSize は、テーブルの現在のカーディナリティと、推奨インデックスによって参照される列のサイズを考慮した、インデックスの推定サイズを表す 1 つの値です。

インデックス チューニングの構成

インデックスのチューニングは、その動作を制御する一連のパラメーターを使用して有効、無効、および構成できます。

インデックス チューニングを有効にすると、index_tuning.analysis_interval サーバー パラメーターで構成された頻度 (既定値は 720 分つまり 12 時間) で起動し、その期間中にクエリ ストアによって記録されたワークロードの分析が開始されます。

index_tuning.analysis_interval の値を変更した場合、スケジュールされている次回の実行の完了後にのみ適用されることに注意してください。 そのため、たとえば、インデックス チューニングをある日の午前 10:00 に有効にした場合、index_tuning.analysis_interval の既定値は 720 分であるため、最初の実行は同じ日の午後 10:00 に開始するようにスケジュールされます。 午前 10:00 から午後 10:00 の間に index_tuning.analysis_interval の値に加えた変更は、その初回スケジュールには影響しません。 スケジュールされた実行が完了したときにのみ、index_tuning.analysis_interval に対して設定されている現在の値が読み取られ、その値に従って次回の実行がスケジュールされます。

次のオプションは、インデックス チューニング パラメーターを構成するために使用できます。

CLI コマンド az postgres flexible-server index-tuning show-settings 使用し、 az postgres flexible-server index-tuning set-settings を使用してインデックス 調整設定のいずれかを表示または変更する場合、 --name パラメーターの引数として受け取る値は、前の表の Parameter 列に示されている値ですが、プレフィックス index_tuning.は含めずに指定します。

インデックス チューニングによって生成される情報

インデックス チューニングによって生成された推奨事項を読み、解釈し、使用する方法に関する記事では、インデックス チューニングによって生成される推奨事項を取得して使用する方法が詳しく説明されています。

制限事項とサポート可能性

インデックス チューニングの制限事項とサポート範囲の一覧を次に示します。

推奨事項の自動削除

推奨事項は、最後に生成された 35 日後に自動的に削除されます。 この自動削除メカニズムを機能させるには、インデックスのチューニングを有効にする必要があります。

hypopg 拡張機能の依存関係

CREATE INDEX に関する推奨事項を生成するインデックスのチューニングでは、hypopg 拡張機能を使用します。

チューニング セッションの開始時に拡張機能が既に存在する場合は、その拡張機能が作成されたスキーマで使用されます。 また、チューニング セッションが終了しても、拡張機能は削除されません。 ただし、pg_catalog スキーマで作成された拡張機能の場合は例外です。 その場合、インデックスのチューニングで拡張機能が削除されます。

拡張機能が元々存在しなかったか、pg_catalog スキーマで作成されたために削除した場合、インデックスのチューニングで ms_temp_recommendations709253 というスキーマで作成され、チューニング セッションが正常に終了すると拡張機能を削除し、スキーマを削除します。

azure_pg_admin ロールのメンバーであるユーザーは、インデックスのチューニング機能によって作成された場合でも、いつでも hypopg 拡張機能を削除できます。 ただし、インデックスのチューニング セッションの実行中にこれを削除すると、そのセッションが失敗し、何の推奨事項も生成されない可能性があります。

サポートされているコンピューティング レベルと SKU

インデックス チューニングは、現在使用可能なすべてのレベル (バースト可能、汎用、メモリ最適化) と、4 つ以上の仮想コアを持ち現在サポートされているコンピューティング SKU でサポートされています。

サポートされている PostgreSQL のバージョン

インデックス チューニングは、Azure Database for PostgreSQL フレキシブル サーバーのメジャー バージョン12 以降でサポートされています。

search_path の使用

インデックス チューニングでは、search_path の列 に保持されている値が使用されるため、各クエリが分析される際に、元のクエリが実行されたときに設定されたものと同じ値の search_path が、考えられるレコメンデーションを分析するために設定されます。

パラメーター化されたクエリ

PREPARE または拡張クエリ プロトコルを使用して作成されたパラメーター化されたクエリは、解析および分析されて、インデックスのレコメンデーションが生成されます。

パラメーター化されたクエリの分析のためには、クエリ ストアがクエリの実行を取り込むときに、インデックス チューニングで pg_qs.parameters_capture_mode を capture_first_sample に設定する必要があります。 また、クエリの実行時にパラメーターがクエリ ストアによって正しく取り込まれる必要があります。 つまり、分析対象のクエリについて、query_store.qs_view の列 parameters_capture_status が succeeded に設定されている必要があります。

読み取り専用モードと読み取りレプリカ

インデックス チューニングは、読み取りレプリカ、または読み取り専用モードのインスタンスではサポートされないクエリ ストアに依存しているため、読み取りレプリカまたは読み取り専用モードのインスタンスではサポートされません。

読み取りレプリカに表示される推奨事項は、プライマリ レプリカで実行されたワークロードのみを分析した後、プライマリ レプリカで生成されました。

コンピューティングのスケールダウン

サーバー上でインデックス チューニングが有効な状態で、そのサーバーのコンピューティングを必要な仮想コアの最小数未満にスケールダウンしても、この機能は有効なままです。 この機能は仮想コア数が 4 未満のサーバーではサポートされていないため、コンピューティングのスケールダウン時に index_tuning.mode が ON に設定されている場合でも、ワークロードを分析して推奨事項を生成するために実行されません。 サーバーが最小要件を満たしていない間は、すべての index_tuning.* サーバー パラメーターにアクセスできません。 最小要件を満たすコンピューティングにサーバーをスケールアップすると、要件を満たさないコンピューティングにスケールダウンする前に設定されていた値で index_tuning.mode が構成されます。

高可用性と読み取りレプリカ

サーバー上で高可用性または読み取りレプリカが構成されている場合は、推奨されるインデックスを実装するときにプライマリ サーバー上に書き込み負荷の高いワークロードが生成されることに関連する影響に注意してください。 推定されるサイズが大きいインデックスの作成には特に注意してください。

関連するコンテンツ

• クエリ ストア。
• インデックスのチューニングを構成する。
• インデックスの推奨事項を使用します。