ApsaraDB RDS:index_adviser拡張機能を使用する

index_adviser拡張のコンポーネント

index_adviser拡張の作成に使用されるステートメントを実行すると、index_advisoryテーブル、show_index_advisory() 関数、およびselect_index_advisoryビューも作成されます。

拡張機能を使用するUse the extension

1. index_adviser拡張機能を作成します。

   postgres=# create extension index_adviser;
   拡張の作成 

2. index_adviser拡張をロードします。

   postgres=# LOAD 'index_adviser ';
   ロード 

   説明

   上記のステートメントは、現在のセッションに対してのみ有効です。 すべてのセッションでindex_adviser拡張機能をデフォルトでロードする場合は、shared_preload_librariesパラメーターを再設定し、RDSインスタンスを再起動する必要があります。 ただし、これはRDSインスタンスのパフォーマンスに影響を与える可能性があります。

   shared_preload_libraries='index_adviser'

例

• テーブルを作成します。

  CREATE TABLE t( a INT, b INT );
  INSERT INTO t SELECT s, 99999 - s FROM generate_series(0,99999) AS s;
  分析t;
  テーブルには次の行が含まれます。
     a | b
  ------ --------
       0 | 99999
       1 | 99998
       2 | 99997
       3 | 99996
         .
         .
         .
   99997 | 2
   99998 | 1
   99999 | 0 

• 1つのSQLステートメントのインデックス作成の提案を照会します。

  • index_adviser拡張機能を使用してクエリを分析し、インデックス作成の提案を取得したいが、クエリを実行したくない場合は、EXPLAINキーワードをSQLステートメントのプレフィックスとして使用します。 例：

    postgres=# EXPLAIN SELECT * FROM a < 10000;
                                       クエリ計画
    ---------------------------------------------------------------------------------
     tのSeqスキャン (コスト=0.00 .. 1693.00行=9983幅=8)
       フィルター :( a < 10000)
     結果 (コスト=0.00 .. 0.00行=0 width=0)
       ワンタイムフィルター: '**計画 (インデックスアドバイザーを使用)**'::text
       -> tに「 <1>t_a_idx」を使用したインデックススキャン (コスト=0.42 .. 256.52行=9983幅=8)
             Index Cond :( a < 10000)
    (6行) 

    postgres=# EXPLAIN SELECT * FROM t WHERE a = 100;
                                     クエリ計画
    ----------------------------------------------------------------------------
     tのSeqスキャン (コスト=0.00 .. 1693.00行=1幅=8)
       フィルター :( a = 100)
     結果 (コスト=0.00 .. 0.00行=0 width=0)
       ワンタイムフィルター: '**計画 (インデックスアドバイザーを使用)**'::text
       -> tに「 <1>t_a_idx」を使用したインデックススキャン (コスト=0.42 .. 2.64行=1幅=8)
             Index Cond :( a = 100)
    (6行) 

    postgres=# EXPLAIN SELECT * FROM t WHERE b = 10000;
                                     クエリ計画
    ----------------------------------------------------------------------------
     tのSeqスキャン (コスト=0.00 .. 1693.00行=1幅=8)
       フィルター :( b = 10000)
     結果 (コスト=0.00 .. 0.00行=0 width=0)
       ワンタイムフィルター: '**計画 (インデックスアドバイザーを使用)**'::text
       -> tに「 <1>t_b_idx」を使用したインデックススキャン (コスト=0.42 .. 2.64行=1幅=8)
             Index Cond :( b = 10000)
    (6行) 

  • PostgreSQL CLIを使用して、index_advisoryテーブルからインデックスの提案を照会できます。 例：

    postgres=# SELECT * FROM index_advisory;
     reloid | relname | attrs | ベネフィット | original_cost | new_cost | index_size | backend_pid | タイムスタンプ
    ------- ---------- --------------------------------------------------------------------------------- ----------------------------------
      16438 | t | {1} | 1337.43 | 1693 | 355.575 | 2624 | 79370 | 18-JUN-21 08:55:51.492388 + 00:00
      16438 | t | {1} | 1684.56 | 1693 | 8.435 | 2624 | 79370 | 18-JUN-21 08:59:00.319336 + 00:00
      16438 | t | {2} | 1684.56 | 1693 | 8.435 | 2624 | 79370 | 18-JUN-21 08:59:07.814453 + 00
    (3行) 

    フィールド	データ型	説明
    reloid	oid	インデックスのテーブルのOID。
    relname	name	インデックスのテーブルの名前。
    attrs	integer[]	インデックス作成の提案が生成される列。 列はIDによって識別される。
    特典	real	インデックスを使用してクエリを高速化する利点。
    original_cost	real	インデックスを使用してクエリを高速化する前にSQL文を実行するのに必要な平均時間。
    new_cost	real	インデックスを使用してクエリを高速化した後、SQL文を実行するのに必要な平均時間。
    index_size	integer	ディスクページ内の推定インデックスサイズ。
    backend_pid	integer	この提案を生成したプロセスのID。
    timestamp	timestamp	この提案が生成された日時。

  • SQL文のプレフィックスにEXPLAINキーワードが付いていない場合、クエリの実行中にindex_adviser拡張機能がSQL文を分析し、インデックスの提案を記録します。

  説明

  読み取り専用トランザクションでは、index_adviser拡張機能を使用しないでください。

• 指定したワークロードのインデックス作成提案を照会します。

  • show_index_advisory() 関数を使用して、セッションのインデックス候補を取得します。

    この関数は、セッションのインデックス作成提案を取得するために使用されます。 セッションは、そのバックエンドプロセスIDによって識別される。 この関数は、セッションのプロセスIDを指定して呼び出すことができます。

    SELECT show_index_advisory( pid );

    説明

    pidは、現在のセッションのプロセスIDを示します。 プロセスIDは、index_advisoryテーブルのbackend_pidパラメーターを使用して取得できます。 渡された値としてnullを指定して、現在のセッションの結果セットを返すこともできます。

    postgres=# SELECT show_index_advisory(null);
                                                                 show_index_advisory
    ----------------------------------------------------------------------------------------------------------------------------------------------------
     public.t(a) にインデックスidx_t_aを作成します。/* サイズ: 2624 KB、特典: 3021.99、ゲイン: 1.15167301457103、original_cost: 1693、new_cost: 182.005006313324 * /
     public.t(b) にインデックスidx_t_bを作成します。/* サイズ: 2624 KB、特典: 1684.56、ゲイン: 0.641983590474943、original_cost: 1693、new_cost: 8.4350004196167 * /
    (2行) 

    説明

    次の説明は、結果セットの各行の意味を示しています。

    • インデックス作成候補からインデックスを作成するために使用されるSQL文。

    • インデックスページの推定サイズ。

    • インデックスを使用してクエリを高速化する利点。

    • インデックスを使用した場合のゲイン。 インデックスの利得を計算するために、以下の式が使用される。インデックスを使用する利得=インデックスを使用する利益 /インデックスの消費サイズ。

    • インデックスを使用してクエリを高速化する前にSQL文を実行するのに必要な平均時間。

    • インデックスを使用してクエリを高速化した後、SQL文を実行するのに必要な平均時間。

  • select_index_advisoryビューを使用して、セッションのインデックス作成の提案を取得します。

    このビューには、計算されたメトリックとCREATE INDEXステートメントが含まれ、index_advisoryテーブルのすべてのセッションのインデックス作成の提案が表示されます。 次の例は、テーブルtの列aと列bのインデックス作成の提案を示しています。

    postgres=# SELECT * からselect_index_advisory;
     backend_pid | show_index_advisory
    ------------- ----------------------------------------------------------------------------------------------------------------------------------------------------
           79370 | public.t(a) にインデックスt_a_idxを作成します。/* サイズ: 2624 KB、特典: 3021.99、ゲイン: 1.15167301457103、original_cost: 1693、new_cost: 182.005006313324 * /
           79370 | public.t(b) にインデックスt_b_idxを作成します。/* サイズ: 2624 KB、特典: 1684.56、ゲイン: 0.641983590474943、original_cost: 1693、new_cost: 8.4350004196167 * /
    (2行) 

    各セッションにおいて、同じ索引付け提案から利益を得るすべてのクエリの結果は、索引付け提案のためのメトリックのセットに組み合わされる。 メトリックは、benefitというフィールドとgainというフィールドで表されます。 次の式は、2つのフィールドの値を計算する方法を示しています。

    size = MAX (すべてのクエリのインデックスサイズ)
    benefit = SUM (各クエリのメリット)
    gain = SUM (各クエリのメリット) / MAX (すべてのクエリのインデックスサイズ) 

    説明

    インデックス作成の提案で、1つのSQL文に対して複数のインデックスを作成することが推奨されている場合、index_advisoryテーブルのnew_costフィールドには、複数のインデックスが作成された後のコストが記録されます。

    ゲインフィールドは、指定されたセッション中に異なる推奨インデックス間の利点を比較するのに役立ちます。 ゲインフィールドの値が大きいほど、推奨インデックスの利益が高いことを示す。 この利点は、推奨インデックスが消費するディスク容量を相殺することができます。