AnalyticDB:SQLプラン管理

使用上の注意

• SQLプラン管理機能は、V6.3.9.0以降を実行するエラスティックストレージモードのAnalyticDB for PostgreSQLインスタンスに対してのみ使用できます。

• V6.3.8.9以降を実行するインスタンスに拡張機能をインストールまたはアップグレードするには、 して、サービスセンターにお問い合わせください。 チケットを起票

マイナーバージョンの表示と更新方法については、「マイナーエンジンバージョンの表示」および「マイナーエンジンバージョンの更新」をご参照ください。

概要

AnalyticDB for PostgreSQLは、静的ルールの代わりに統計を使用するコストベースのオプティマイザを使用します。 オプティマイザは、SQL文の各実行計画のコストを推定し、実行のための最小コスト計画を選択します。 オプティマイザは最適な実行計画を選択するために最善を尽くしますが、複雑なビジネスシナリオでデータ量とデータ分散特性が急激に変化すると、実行計画が大幅に変化する可能性があります。 さらに、オプティマイザのバージョンの更新と機能の改善により、実行計画も変更される場合があります。 したがって、予期しない実行プランが生成されることがある。 分析シナリオで複雑なレポートクエリが発生した場合、オプティマイザが多数の実行計画から最適な実行計画を取得するのに時間がかかることがあります。

SQLプラン管理機能は、SQLパターンとそれに対応する実行プランを格納します。 ストアドパターンを使用するSQLステートメントが実行された場合、この機能は対応する実行プランを直接呼び出すことができます。 SQLプラン管理機能には、次の利点があります。

• 繰り返し実行計画の生成に必要な時間を短縮します。

• 実行計画を安定させます。 これにより、大量のデータが頻繁に変更されるシナリオでの実行計画の過度の変更を防ぎます。

• パラメータ化された定数をサポートします。 実行計画は、同じパターンを共有するが異なる定数を使用する一連のSQL文に使用できます。

sr_plan拡張を管理する

sr_plan拡張機能をインストールする

SQLプラン管理機能を使用するには、最初に次のステートメントを実行してsr_plan拡張機能をインストールする必要があります。

CREATE EXTENSION sr_plan;

SQLプラン管理機能を一時的に無効にする

現在のセッションでSQLプラン管理機能を一時的に無効にするには、次のステートメントを実行します。

SET sr_plan.enabled to off;

sr_plan拡張機能の削除

SQLプラン管理機能が不要になった場合は、次のステートメントを実行してsr_plan拡張機能を削除できます。

DROP EXTENSION sr_plan;

sr_plansテーブル

sr_plan拡張機能がインストールされると、SQLプランを格納するsr_plansという名前のテーブルが自動的に生成されます。 各SQLプランには、SQLパターンとそれに対応する実行プランが行に含まれています。 次の表に、sr_plansテーブルのスキーマを示します。

GUCパラメータ

次の表に、SQLプラン管理機能を管理するためにAnalyticDB for PostgreSQLによって提供されるグランドユニファイド構成 (GUC) パラメーターを示します。

機能説明

SQLプランの登録

bool sr_plans_register(<query>, <const_list>, <hint_str>)

下表に、各パラメーターを説明します。

例：

• SQLプランを_pモードで登録します。

  SELECT sr_plans_register('SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = _p(11);', NULL, '/*+ Set(optimizer_enable_hashjoin off) */');

• 準備済みモードでSQLプランを登録します。

  SELECT sr_plans_register('SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = $1;', '11', '/*+ MergeJoin(test_table test_table2) Leading((test_table test_table2)) */');

SQLプランの照会

• テキスト形式でSQLプランを照会する

  show_plan(<query_hash>,<plan_hash>,<format>)

  パラメーター	データ型	説明
  query_hash	bigint	パラメーター化されたクエリ文の64ビットハッシュ値。 sr_plansテーブルのquery_hashの値に対応します。
  plan_hash	bigint	パラメータ化された実行プランの64ビットハッシュ値。 sr_plansテーブルのplan_hashの値に対応します。 デフォルト値は NULL です。 これは、有効状態にある最初の実行プランを示します。
  フォーマット	text	クエリ結果が表示される形式。 デフォルト値: 'text' 有効な値： 「テキスト」 'xml' 「json」 「ヤムル」

  例：

  SQLプランを照会し、照会結果を 'text' 形式で表示します。

  SELECT show_plan(-7846983602634689470, 1283098916874729409, 'text');

• プラン構造全体を照会する

  show_plan_node(<query_hash>,<plan_hash>,<pretty>)

  パラメーター	データ型	説明
  query_hash	bigint	パラメーター化されたクエリ文の64ビットハッシュ値。 sr_plansテーブルのquery_hashの値に対応します。
  plan_hash	bigint	パラメータ化された実行プランの64ビットハッシュ値。 sr_plansテーブルのplan_hashの値に対応します。 デフォルト値は NULL です。 これは、有効状態にある最初の実行プランを示します。
  pretty	bool	クエリ結果の表示でテキストの折り返しとインデントを使用するかどうかを指定します。 有効な値： true (デフォルト) false

  例：

  プラン構造全体をクエリし、テキストの折り返しとインデントを使用してクエリ結果を表示します。

  SELECT show_plan_node(-7846983602634689470, 1283098916874729409, on);

• パラメータ化された定数の照会

  show_const_list(<query_hash>,<plan_hash>,<is_list>,<pretty>)

  パラメーター	データ型	説明
  query_hash	bigint	パラメーター化されたクエリ文の64ビットハッシュ値。 sr_plansテーブルのquery_hashの値に対応します。
  plan_hash	bigint	パラメータ化された実行プランの64ビットハッシュ値。 sr_plansテーブルのplan_hashの値に対応します。 デフォルト値は NULL です。 これは、有効状態にある最初の実行プランを示します。
  is_list	bool	オペレータリスト全体を表示するかどうかを指定します。 有効な値： true (デフォルト) false
  pretty	bool	クエリ結果の表示でテキストの折り返しとインデントを使用するかどうかを指定します。 有効な値： true (デフォルト) false

  例：

  パラメーター化された定数を照会し、テキストの折り返しやインデントなしで演算子リスト全体を使用して照会結果を表示します。

  SELECT show_const_list(-7846983602634689470, 1283098916874729409, true, false);

SQLプランの有効化

enable_sr_plans(<query_hash>,<plan_hash>)

例：

SQLプランを有効にします。

SELECT enable_sr_plans(-7846983602634689470, 1283098916874729409);

SQLプランの無効化

disable_sr_plans(<query_hash>,<plan_hash>)

例：

SQLプランを無効にします。

SELECT disable_sr_plans(-7846983602634689470, 1283098916874729409);

SQLプランの削除

delete_sr_plans(<query_hash>,<plan_hash>)

例：

SQLプランを削除します。

SELECT delete_sr_plans(-7846983602634689470, 1283098916874729409);

SQLプランの変更

update_sr_plans(<query_hash>,<plan_node_string>,<plan_hash>)

例

1. test_tableとtest_table2という名前のテーブルを作成し、2つのテーブルにデータを挿入します。

   DROP TABLE IF EXISTS test_table;
   DROP TABLE IF EXISTS test_table2;
   CREATE TABLE test_table(test_attr1 int, test_attr2 int);
   CREATE TABLE test_table2(test_attr1 int, test_attr2 int);
   INSERT INTO test_table SELECT i, i + 1 FROM generate_series(1, 20000) i;
   INSERT INTO test_table2 SELECT i, i + 1 FROM generate_series(1, 20000) i;
   CREATE INDEX test_table_index1 ON test_table (test_attr1);
   CREATE INDEX test_table_index2 ON test_table (test_attr2);
   ANALYZE test_table;
   ANALYZE test_table2;

2. SQLプランを登録して使用するときに、sr_plan拡張機能のログ表示を有効にして通知を表示します。

   SQLプランの登録時にログ表示を有効にすることを推奨します。 これにより、保存済みSQLプランを使用できるかどうかを確認できます。

   SET sr_plan.log_usage = NOTICE;

3. 実行プランを照会します。

   次のステートメントを実行して、実行計画を照会します。

   EXPLAIN SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = 11;

   次の情報が返されます。

                                                 QUERY PLAN
   ------------------------------------------------------------------------------------------------------
    Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..438.43 rows=1 width=16)
      ->  Hash Join  (cost=0.00..438.43 rows=1 width=16)
            Hash Cond: (test_table2.test_attr1 = test_table.test_attr1)
            ->  Seq Scan on test_table2  (cost=0.00..431.14 rows=6667 width=8)
            ->  Hash  (cost=6.00..6.00 rows=1 width=8)
                  ->  Index Scan using test_table_index2 on test_table  (cost=0.00..6.00 rows=1 width=8)
                        Index Cond: (test_attr2 = 11)
    Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
   (8 rows)

4. SQLプランを登録します。

   • _pモードとヒントを使用します。

     SELECT  sr_plans_register('SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = _p(11);', NULL, '/*+ Set(optimizer_enable_hashjoin off) */');

     次の情報が返されます。

     NOTICE:  sr_plan: saved plan for /*+ Set(optimizer_enable_hashjoin off) */SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = _p(11);
     CONTEXT:  SQL statement "/*+ Set(optimizer_enable_hashjoin off) */SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = _p(11);"
     PL/pgSQL function sr_plans_register(text,text,text) line 17 at EXECUTE statement
     corresponding column is : sr_plans_register
      sr_plans_register
     -------------------
      t
     (1 row)

   • 準備されたモードとヒントを使用してください。 ORCAオプティマイザは使用できません。

     SELECT sr_plans_register('SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = $1;', '11', '/*+ MergeJoin(test_table test_table2) Leading((test_table test_table2)) */');

     次の情報が返されます。

     NOTICE:  sr_plan: saved plan for select * from test_table, test_table2 where test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = $1;
     CONTEXT:  SQL statement "/*+ MergeJoin(test_table test_table2) Leading((test_table test_table2)) */execute _sr_plans_register(11)"
     PL/pgSQL function sr_plans_register(text,text,text) line 27 at EXECUTE statement
     corresponding column is : sr_plans_register
      sr_plans_register
     -------------------
      t
     (1 row)

5. sr_plansテーブルに格納されているすべてのSQLプランを照会します。

   SELECT * FROM sr_plans;

   クエリ結果を水平方向に表示するには、psqlで \xコマンドを実行します。 次の情報が返されます。

   -[ RECORD 1 ]-+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   query_hash    | -7846983602634689470
   query_id      | 0
   plan_hash     | 1283098916874729409
   enable        | f
   query         | /*+ Set(optimizer_enable_hashjoin off) */SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = _p(11);
   plan          | \x28b52ffd603f07150d00520b2a31504f0200745b6bf119296929280e2ae3d08e676124f9ca5fc36c629033c7b9a7a9bf766e213a3e4266ce009604c2ffbef32823871cd54a100b835171815211be6dfaa6d4ad563800a7f48bdc2a744c5dc8a4feeba7284366093db00c71a02190f870675a5b137a269a8d96868e83c5f4b733c6b21ecfc0752250e0e15cab5a4b2b33395506c20b063530f08f32c618ff808f4a133af1bbcca857f2dee7c220a5c311d1ecef4d928080a8b0d64000082000f0829199e1361200c234ca2508e40831901063483011c944125018292ceda0be8ff48b9cc45e2e1df50755b915267643696e998066ca9cdbdaae59e2e74e6a40f2efd406378e2636c854b8508978e1ecf58b356ea8bdab1b208ed06a4c2dea383dc365f24e0ed8e4a78ac542b50cf462aecf05650c56bab6f4266ab87c29354a59b2509a3fed448f897e4ccdf0623e9f293f6dbff7773e405f0884b67d8bc8c18f245c4b8a82297074668c19e8b0158d9ecdd3986fa2adddc4ed0cbaad8a1f54de36785a99cc39475c06e36f415872b3801e3a45a10aa91eded2dcddd3cf88abbd9824b0259f271a8692919907
   const_list    | \x28b52ffd2049ad0100540290020100000035010f80000017003001ffffffff040000000100a30000000ba0000000adde05004f9107417606fc3904325002
   reloids       | {40963,40960,40960,40963}
   index_reloids | {40969}
   -[ RECORD 2 ]-+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   query_hash    | -7846983602634689470
   query_id      | 0
   plan_hash     | 8380868479165711144
   enable        | f
   query         | select * from test_table, test_table2 where test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = $1;
   plan          | \x28b52ffd604a09c51000024f383730918200d5a047fa8799c1e341cc05c54480f746007c5f533b0d22256706d75b93bfed805f83bc0a6319ff43a73f4fbf55a3ed96bef73d35272fde92a40102d5171724a5427da08a5ebe361594ce16b5abf26cb13b631f23b0f98a19b4546c293fc0ac6fddf58b68f773dce8e8f337fc13aa2072a113828e4bc8a9041323b3da5577c38012ce0451cc700c172513f439a0146cd8cc7a5b69fd10a1473da9ad4c4a59c752bdd7344d76524ad2ee8add0a043f0fc448813f171ac771fc0f76663a52895fdbf033452e93800649c68479dc2c3c98bef9caa189784930d1c3755902809fa8d1d522400109822001f00281111a2b1200932cc9640c42c000048c314a0015000413418080090c680cc6838e2fd492d1b489b1738684cc0313c3b91659002bb5237c70b22b863bcc54a973f15fe134370df65c634396bf878f89b3658422ac27a9bb02c7d8b83cd81d1f074acaf5a107f14aa5a1d6c9d5c03b4aa7b08ad04359d3fe16bd509d41c12e0c028c55c9a0390794423566ac01d18c7b021016bf8ba100e83144bf84b0e4ad418a313c57df176b143aab601c43cd46c9516f08c7ea00e3d040d35e7d8b50d52462dfd3b03662d2674a78c9e9cc825594d3fbed29f0acdd1ce9caabbeae8a34c54a23879edea532081ef80aac28e26c1e26e6d99915549bcbb8a7d82cf8c2fef988a1c623472cff31c25dc69a69a130bdac655748e0a6ac3abe97d95b431fe104caffe6c083f007
   const_list    | \x28b52ffd2020010100900201000000310100000100000017000000ffffffff0000000095000000adde
   reloids       | {40960,40963}
   index_reloids | {40970}

6. SQLプランを照会します。

   • query_hashパラメーターとplan_hashパラメーターを使用して、最初のSQLプランを照会します。

     SELECT show_plan(-7846983602634689470, 1283098916874729409);

     次の情報が返されます。

                                                         show_plan
     ------------------------------------------------------------------------------------------------------------------
      ("Gather Motion 3:1  (slice1; segments: 3)")
      ("  Output: test_table.test_attr1, test_table.test_attr2, test_table2.test_attr1, test_table2.test_attr2")
      ("  ->  Nested Loop")
      ("        Output: test_table.test_attr1, test_table.test_attr2, test_table2.test_attr1, test_table2.test_attr2")
      ("        Join Filter: true")
      ("        ->  Seq Scan on public.test_table2")
      ("              Output: test_table2.test_attr1, test_table2.test_attr2")
      ("        ->  Index Scan using test_table_index1 on public.test_table")
      ("              Output: test_table.test_attr1, test_table.test_attr2")
      ("              Index Cond: (test_table.test_attr1 = test_table2.test_attr1)")
      ("              Filter: (test_table.test_attr2 = _p(11))")
      ("Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0")
      ("Settings: rds_plan_cache_mode=auto")
     (13 rows)

   • query_hashパラメーターとplan_hashパラメーターを使用して、最初のSQLプランのパラメーター化された定数を照会します。

     SELECT show_const_list(-7846983602634689470, 1283098916874729409, false);

     次の情報が返されます。

           show_const_list
     ----------------------------
      _p(4 [ 11 0 0 0 0 0 0 0 ])
     (1 row)

   • query_hashパラメーターとplan_hashパラメーターを使用して、2番目のSQLプランを照会します。

     SELECT show_plan(-7846983602634689470, 8380868479165711144);

     次の情報が返されます。

                                                         show_plan
     ------------------------------------------------------------------------------------------------------------------
      ("Gather Motion 3:1  (slice1; segments: 3)")
      ("  Output: test_table.test_attr1, test_table.test_attr2, test_table2.test_attr1, test_table2.test_attr2")
      ("  ->  Merge Join")
      ("        Output: test_table.test_attr1, test_table.test_attr2, test_table2.test_attr1, test_table2.test_attr2")
      ("        Merge Cond: (test_table.test_attr1 = test_table2.test_attr1)")
      ("        ->  Sort")
      ("              Output: test_table.test_attr1, test_table.test_attr2")
      ("              Sort Key: test_table.test_attr1")
      ("              ->  Index Scan using test_table_index2 on public.test_table")
      ("                    Output: test_table.test_attr1, test_table.test_attr2")
      ("                    Index Cond: (test_table.test_attr2 = $1)")
      ("        ->  Sort")
      ("              Output: test_table2.test_attr1, test_table2.test_attr2")
      ("              Sort Key: test_table2.test_attr1")
      ("              ->  Seq Scan on public.test_table2")
      ("                    Output: test_table2.test_attr1, test_table2.test_attr2")
      ("Optimizer: Postgres query optimizer")
      ("Settings: rds_plan_cache_mode=auto")
     (18 rows)

   • query_hashパラメーターとplan_hashパラメーターを使用して、2番目のSQLプランのパラメーター化された定数を照会します。

     SELECT show_const_list(-7846983602634689470, 8380868479165711144, false);

     次の情報が返されます。

      show_const_list
     -----------------
      $1
     (1 row)

7. SQLプラン管理機能を使用します。

   • 最初のSQLプランの確認

     1. 最初のSQLプランを有効にします。

        SELECT enable_sr_plans(-7846983602634689470, 1283098916874729409);

     2. 最初のSQLプランが有効になっている間に、パラメータ化された定数を変更して実行プランを照会します。

        EXPLAIN SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = 12;

        次の情報が返されます。

        NOTICE:  sr_plan: cached plan was used for query: explain SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = 12;
                                                     QUERY PLAN
        -----------------------------------------------------------------------------------------------------
         Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..120434.24 rows=8000 width=16)
           ->  Nested Loop  (cost=0.00..120433.77 rows=2667 width=16)
                 Join Filter: true
                 ->  Seq Scan on test_table2  (cost=0.00..431.14 rows=6667 width=8)
                 ->  Index Scan using test_table_index1 on test_table  (cost=0.00..120002.35 rows=1 width=8)
                       Index Cond: (test_attr1 = test_table2.test_attr1)
                       Filter: (test_attr2 = 12)
         Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
        (8 rows)

     3. 最初のSQLプランを無効にします。

        SELECT disable_sr_plans(-7846983602634689470, 1283098916874729409);

   • 2番目のSQLプランの確認

     1. 2番目のSQLプランを有効にします。

        SELECT  enable_sr_plans(-7846983602634689470, 8380868479165711144);

     2. 2番目のSQLプランが有効になっている間に、パラメーター化された定数を変更して実行プランを照会します。

        EXPLAIN SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = 13;

        次の情報が返されます。

        NOTICE:  sr_plan: cached plan was used for query: explain SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = 13;
                                                      QUERY PLAN
        ------------------------------------------------------------------------------------------------------
         Gather Motion 3:1  (slice1; segments: 3)  (cost=1660.96..1760.98 rows=15 width=16)
           ->  Merge Join  (cost=1660.96..1760.98 rows=5 width=16)
                 Merge Cond: (test_table.test_attr1 = test_table2.test_attr1)
                 ->  Sort  (cost=8.19..8.20 rows=1 width=8)
                       Sort Key: test_table.test_attr1
                       ->  Index Scan using test_table_index2 on test_table  (cost=0.16..8.18 rows=1 width=8)
                             Index Cond: (test_attr2 = 13)
                 ->  Sort  (cost=1652.77..1702.77 rows=6667 width=8)
                       Sort Key: test_table2.test_attr1
                       ->  Seq Scan on test_table2  (cost=0.00..224.00 rows=6667 width=8)
         Optimizer: Postgres query optimizer
        (11 rows)

     3. 2番目のSQLプランを無効にします。

        SELECT disable_sr_plans(-7846983602634689470, 8380868479165711144);

8. SQLプランを削除します。

   • 最初のSQLプランを削除します。

     SELECT delete_sr_plans(-7846983602634689470, 1283098916874729409);

   • 2番目のSQLプランを削除します。

     SELECT delete_sr_plans(-7846983602634689470, 8380868479165711144);