Aurora PostgreSQL のクエリプラン管理の概要 - Amazon Aurora
サポートされている SQL ステートメント制約事項用語使用可能なバージョンクエリ計画管理をオンにするクエリ計画管理のアップグレードAurora PostgreSQL のクエリ計画管理をオフにする

Aurora PostgreSQL のクエリプラン管理の概要

Aurora PostgreSQL クエリプラン管理は、クエリプランのリグレッションを引き起こす可能性のあるデータベースの変更に関係なく、計画の安定性を確保するように設計されています。クエリプランのリグレッションは、システムまたはデータベースが変更された後に、オプティマイザが特定の SQL ステートメントに対して最適ではない計画を選択したときに発生します。統計情報、制限事項、環境設定、クエリパラメータのバインディングの変更、PostgreSQL データベースエンジンのアップグレードは、すべて計画のリグレッションの原因になる可能性があります。

Aurora PostgreSQL のクエリプラン管理を使用すると、クエリ実行計画の変更方法と変更時期を制御できます。Aurora PostgreSQL クエリプラン管理には次のような利点があります。

  • オプティマイザに、問題のないことがわかっている少数の計画から強制的に選択させることで、計画の安定性を改善する。

  • 計画を一元的に最適化してから、最善の計画を全体に配布する。

  • 使用されていないインデックスを特定し、インデックスの作成または削除の影響を評価する。

  • オプティマイザが発見した、新しい最小コスト計画を自動的に検出する。

  • パフォーマンスを改善する計画変更のみが承認されるように選択し、新しいオプティマイザの機能を少ないリスクで試す。

クエリプラン管理が提供するツールを積極的に使用して、特定のクエリに最適な計画を指定できます。または、クエリプラン管理を使用して、変化する状況に対応し、計画のリグレッションを回避することもできます。詳細については、「Aurora PostgreSQL クエリ計画管理のベストプラクティス」を参照してください。

サポートされている SQL ステートメント

クエリプラン管理は、次のタイプの SQL ステートメントをサポートします。

  • 複雑性に関係なく、任意の SELECT、INSERT、UPDATE、DELETE ステートメント。

  • プリペアドステートメント。詳細については、PostgreSQL のドキュメントの「PREPARE」を参照してください。

  • 動的ステートメント (即時モードで実行されるものを含む)。詳細については、PostgreSQL ドキュメントの「動的 SQL」と「EXECUTE IMMEDIATE」を参照してください。

  • 埋め込み SQL コマンドとステートメント。詳細については、PostgreSQL ドキュメントの「埋め込み SQL コマンド」を参照してください。

  • 名前付き関数内のステートメント。詳細については、PostgreSQL のドキュメントの「CREATE FUNCTION」を参照してください。

  • 一時テーブルを含むステートメント。

  • プロシージャと DO ブロック内のステートメント。

クエリプラン管理を EXPLAIN と手動モードで使用すると、実際に実行しなくてもプランをキャプチャできます。詳細については、「オプティマイザが選択した計画の分析」を参照してください。クエリプラン管理のモード (手動、自動) の詳細については、「Aurora PostgreSQL 実行計画のキャプチャ」を参照してください。

Aurora PostgreSQL クエリプラン管理は、パーティショニングされたテーブル、継承、行レベルセキュリティ、再帰的なテーブル共通表現 (CTE) など、PostgreSQL のすべての言語機能をサポートします。これらの PostgreSQL 言語機能の詳細については、PostgreSQL ドキュメントの「テーブルパーティショニング」、「行セキュリティポリシー」、「WITH クエリ (共通テーブル式) 」およびその他のトピックを参照してください。

Aurora PostgreSQL のクエリプラン管理機能のさまざまなバージョンの詳細については、『Aurora PostgreSQL のリリースノート』の「Aurora PostgreSQL apg_plan_mgmt 拡張機能バージョン」を参照してください。

クエリプラン管理の制限事項

Aurora PostgreSQL のクエリプラン管理の現在のリリースには、次のような制限があります。

  • システムリレーションを参照するステートメントのプランはキャプチャされません。pg_class など、システムリレーションを参照するステートメントはキャプチャされません。これは設計によるものであり、内部で使用される多数のシステム生成計画がキャプチャされないようにするためです。これはシステムテーブル内部ビューにも当てはまります。

  • Aurora PostgreSQL DB クラスターには、より大きな DB インスタンスクラスが必要になる場合があります。ワークロードによっては、クエリプラン管理に 3 つ以上の vCPU を持つ DB インスタンスクラスが必要になる場合があります。max_worker_processes の数は、DB インスタンスクラスのサイズによって制限されます。2 vCPU の DB インスタンスクラス (db.t3.medium など) によって提供される max_worker_processes の数は、特定のワークロードに対して十分ではない場合があります。クエリプラン管理を使用する場合は、Aurora PostgreSQL DB クラスター用に 3 つ以上の vCPU を備えた DB インスタンスクラスを選択することをお勧めします。

    DB インスタンスクラスがワークロードをサポートできないと、クエリプラン管理は次のようなエラーメッセージをレイズします。

    WARNING: could not register plan insert background process
HINT: You may need to increase max_worker_processes.

    この場合、Aurora PostgreSQL DB クラスターを、より多くのメモリを搭載した DB インスタンスクラスのサイズにスケールアップする必要があります。詳細については、「DB インスタンスクラスでサポートされている DB エンジン」を参照してください。

  • セッションに既に保存されているプランは影響を受けません。クエリプラン管理を使用すると、アプリケーションコードを変更せずにクエリプランに影響を与えることができます。ただし、ジェネリックプランが既存のセッションに既に保存されていて、そのクエリプランを変更する場合は、まず、DB クラスターパラメータグループで plan_cache_mode をforce_custom_plan に設定する必要があります。

  • 次の場合、apg_plan_mgmt.dba_plans と pg_stat_statements の queryid は異なる可能性があります。

    • オブジェクトが apg_plan_mgmt.dba_plan に保存された後、削除され、再作成されたとき。

    • apg_plan_mgmt.plans テーブルが別のクラスターからインポートされたとき。

Aurora PostgreSQL のクエリプラン管理機能のさまざまなバージョンの詳細については、『Aurora PostgreSQL のリリースノート』の「Aurora PostgreSQL apg_plan_mgmt 拡張機能バージョン」を参照してください。

クエリプラン管理用語

このトピックでは、次の用語を使用します。

マネージドステートメント

クエリプラン管理でオプティマイザにより取得された SQL ステートメントです。マネージドステートメントには、apg_plan_mgmt.dba_plans ビューに 1 つ以上のクエリ実行計画が格納されています。

計画ベースライン

特定のマネージドステートメントで承認された計画のセット。つまり、dba_plan ビュー内の status 列が「承認済み」になっているマネージドステートメントのすべての計画です。

計画履歴

特定のマネージドステートメント用に取得された一連のすべての計画。計画履歴には、ステータスに関係なく、ステートメントにキャプチャされたすべての計画が含まれます。

クエリプランのリグレッション

オプティマイザが、新しい PostgreSQL バージョンや統計の変更など、データベース環境への特定の変更前よりも最適でない計画を選択した場合。

Aurora PostgreSQL クエリプラン管理のバージョン

クエリプラン管理は、現在の Aurora PostgreSQL リリースで利用可能なすべての Aurora PostgreSQL リリースでサポートされています。詳細な情報については、Aurora PostgreSQL リリースノート」の「Amazon Aurora PostgreSQL の更新」のリストを参照してください。

apg_plan_mgmt 拡張機能をインストールすると、クエリプラン管理機能が Aurora PostgreSQL DB クラスターに追加されます。Aurora PostgreSQL の異なるバージョンでは、異なるバージョンの apg_plan_mgmt 拡張機能がサポートされます。クエリプラン管理拡張機能を、ご使用の Aurora PostgreSQL バージョンの最新リリースにアップグレードすることをお勧めします。

注記

各 apg_plan_mgmt の拡張機能バージョンのリリースノートについては、Aurora PostgreSQL のリリースノートの Aurora PostgreSQL apg_plan_mgmt 拡張機能バージョンを参照してください。

クラスターで実行されているバージョンを特定するには、psql を使用してインスタンスに接続し、メタコマンド \ dx を使用して次に示すように拡張機能を一覧表示します。

labdb=> \dx
                       List of installed extensions
     Name      | Version |    Schema     |                            Description
---------------+---------+---------------+-------------------------------------------------------------------
 apg_plan_mgmt | 1.0     | apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management
 plpgsql       | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

出力は、このクラスターがエクステンションの 1.0 バージョンを使用していることを示しています。特定の Aurora PostgreSQL バージョンで使用できるのは特定の apg_plan_mgmt バージョンのみです。場合によっては、Aurora PostgreSQL DB クラスターを新しいマイナーリリースにアップグレードするか、最新バージョンのクエリプラン管理にアップグレードできるようにパッチを適用する必要があります。出力に表示される apg_plan_mgmt バージョン 1.0 は Aurora PostgreSQL バージョン 10.17 DB クラスターのもので、新しいバージョンの apg_plan_mgmt は利用できません。この場合、Aurora PostgreSQL DB クラスターをより新しいバージョンの PostgreSQL にアップグレードする必要があります。

Aurora PostgreSQL DB クラスターの新しいバージョンの PostgreSQL へのアップグレードの詳細については、「Amazon Aurora PostgreSQL の更新」を参照してください。

apg_plan_mgmt 拡張機能のアップグレード方法については、Aurora PostgreSQL のクエリ計画管理アップグレードする を参照してください。

Aurora PostgreSQL のクエリプラン管理をオンにする

Aurora PostgreSQL DB クラスターのクエリプラン管理を設定するには、拡張機能をインストールし、いくつかの DB クラスターパラメータ設定を変更する必要があります。apg_plan_mgmt 拡張機能をインストールし、Aurora PostgreSQL DB クラスターの機能を有効にする rds_superuser 権限が必要です。

拡張機能をインストールすると、新しいロール apg_plan_mgmt が作成されます。このロールにより、データベースユーザーはクエリプランを表示、管理、および管理できます。rds_superuser 権限を持つ管理者として、必要に応じてデータベースユーザーに apg_plan_mgmt ロールを付与してください。

次の手順を完了できるのは、rds_superuser ロールを持つユーザーのみです。rds_superuser は、apg_plan_mgmt エクステンションとその apg_plan_mgmt ロールの作成に必要です。apg_plan_mgmt エクステンションを管理するには、ユーザーに apg_plan_mgmt ロールを付与する必要があります。

Aurora PostgreSQL DB クラスターのクエリプラン管理をオンにするには

以下のステップでは、Aurora PostgreSQL DB クラスターに送信されるすべての SQL ステートメントのクエリプラン管理を有効にします。これは自動モードと呼ばれます。モードの違いについての詳細は、「Aurora PostgreSQL 実行計画のキャプチャ」を参照してください。

  1. Amazon RDS コンソール (https://console.aws.amazon.com/rds/) を開きます。

  2. Aurora PostgreSQL DB クラスターに使用する DB クラスターパラメータグループを作成します。クエリプラン管理を有効にし、その動作を設定するには、特定のパラメータを変更する必要があります。詳細については、「Amazon Aurora での DB パラメータグループの作成」を参照してください。

  3. 次の画像に示すように、カスタム DB クラスターパラメータグループを開き、rds.enable_plan_management パラメータを 1 に設定します。

    詳細については、「Amazon Aurora の DB クラスターパラメータグループのパラメータの変更」を参照してください。

  4. インスタンスレベルでクエリプランパラメータを設定するために使用できるカスタム DB パラメータグループを作成します。詳細については、「Amazon Aurora での DB クラスターパラメータグループの作成」を参照してください。

  5. Aurora PostgreSQL DB クラスターのライターインスタンスを変更して、カスタム DB パラメータグループを使用します。詳細については、「DB クラスター内の DB インスタンスの変更」を参照してください。

  6. カスタム DB パラメータグループを使用するには、Aurora PostgreSQL DB クラスターを変更します。詳細については、「コンソール、CLI、API を使用した DB クラスターの変更」を参照してください。

  7. DB インスタンスを再起動してカスタムパラメータグループ設定を有効にします。

  8. psql または pgAdmin を使用して Aurora PostgreSQL DB クラスターの DB インスタンスエンドポイントに接続します。次の例では、postgres ロールにデフォルトの rds_superuser アカウントを使用しています。

    psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db

  9. 次に示すように、DB インスタンス用の apg_plan_mgmt エクステンションを作成します。

    labdb=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION
    ヒント

    アプリケーションのテンプレートデータベースに apg_plan_mgmt エクステンションをインストールします。デフォルトのテンプレートデータベースは、template1 という名前です。詳細については、PostgreSQL ドキュメントの「テンプレートデータベース」を参照してください。

  10. apg_plan_mgmt.capture_plan_baselines パラメータを automatic に変更します。この設定では、オプティマイザは、2 回以上計画または実行されるすべての SQL ステートメントのプランを生成します。

    注記

    クエリプラン管理には、特定の SQL ステートメントに使用できる手動モードもあります。詳細については、「Aurora PostgreSQL 実行計画のキャプチャ」を参照してください。

  11. apg_plan_mgmt.use_plan_baselines パラメータの値を「オン」に変更します。このパラメータを指定すると、オプティマイザは計画ベースラインからステートメントの計画を選択します。詳細については、「Aurora PostgreSQL 管理計画を使用する」を参照してください。

    注記

    インスタンスを再起動しなくても、セッションのこれらの動的パラメータのいずれかの値を変更できます。

クエリプラン管理の設定が完了したら、クエリプランを表示、管理、または維持する必要があるすべてのデータベースユーザーに apg_plan_mgmt ロールを必ず付与してください。

Aurora PostgreSQL のクエリ計画管理アップグレードする

クエリプラン管理拡張機能を、ご使用の Aurora PostgreSQL バージョンの最新リリースにアップグレードすることをお勧めします。

  1. rds_superuser 権限があるユーザーとして、Aurora PostgreSQL DB クラスターのライターインスタンスに接続します。インスタンスの設定時にデフォルトの名前を保持している場合は、postgres として接続します。次の例では、psql の使用方法を示していますが、希望に応じて pgAdmin を使用することもできます。

    psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

  2. 拡張機能をアップグレードするには、次のクエリを実行します。

    ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';

  3. apg_plan_mgmt.validate_plans 関数を使用して、すべての計画のハッシュを更新します。オプティマイザは、承認済み、未承認、拒否済みのすべての計画を検証して、新しいバージョンの拡張機能でも引き続き実行可能な計画であることを確認します。

    SELECT apg_plan_mgmt.validate_plans('update_plan_hash');

    この関数の使用の詳細については、「計画の検証」を参照してください。

  4. apg_plan_mgmt.reload 関数を使用して、共有メモリ内のすべての計画を dba_plans ビューからの検証済み計画で更新します。

    SELECT apg_plan_mgmt.reload();

クエリプラン管理に使用できるすべての機能の詳細については、「Aurora PostgreSQL クエリ計画管理の関数リファレンス 」を参照してください。

Aurora PostgreSQL のクエリプラン管理をオフにする

クエリプラン管理は、apg_plan_mgmt.use_plan_baselines および apg_plan_mgmt.capture_plan_baselines をオフにすることで、いつでも無効にできます。

labdb=> SET apg_plan_mgmt.use_plan_baselines = off;

labdb=> SET apg_plan_mgmt.capture_plan_baselines = off;