ApsaraDB RDS:index_adviser 拡張機能の使用

index_adviser 拡張機能のコンポーネント

index_adviser 拡張機能を作成するために使用される文を実行すると、index_advisory テーブル、show_index_advisory() 関数、および select_index_advisory ビューも作成されます。

拡張機能の使用

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

   postgres=# create extension index_adviser;
   CREATE EXTENSION

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

   postgres=# LOAD 'index_adviser';
   LOAD

   説明

   上記の文は、現在のセッションでのみ有効です。すべてのセッションでデフォルトで 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;
  ANALYZE t;
  テーブルには次の行が含まれています:
     a   |   b
  -------+-------
       0 | 99999
       1 | 99998
       2 | 99997
       3 | 99996
         .
         .
         .
   99997 |     2
   99998 |     1
   99999 |     0

• 単一の SQL 文のインデックス作成の提案をクエリします。

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

    postgres=# EXPLAIN SELECT * FROM t WHERE a < 10000;
                                       QUERY PLAN
    ---------------------------------------------------------------------------------
     Seq Scan on t  (cost=0.00..1693.00 rows=9983 width=8)
       Filter: (a < 10000)
     Result  (cost=0.00..0.00 rows=0 width=0)
       One-Time Filter: '** plan (using Index Adviser) **'::text
       ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..256.52 rows=9983 width=8)
             Index Cond: (a < 10000)
    (6 rows)

    postgres=# EXPLAIN SELECT * FROM t WHERE a = 100;
                                     QUERY PLAN
    ----------------------------------------------------------------------------
     Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
       Filter: (a = 100)
     Result  (cost=0.00..0.00 rows=0 width=0)
       One-Time Filter: '** plan (using Index Adviser) **'::text
       ->  Index Scan using "<1>t_a_idx" on t  (cost=0.42..2.64 rows=1 width=8)
             Index Cond: (a = 100)
    (6 rows)

    postgres=# EXPLAIN SELECT * FROM t WHERE b = 10000;
                                     QUERY PLAN
    ----------------------------------------------------------------------------
     Seq Scan on t  (cost=0.00..1693.00 rows=1 width=8)
       Filter: (b = 10000)
     Result  (cost=0.00..0.00 rows=0 width=0)
       One-Time Filter: '** plan (using Index Adviser) **'::text
       ->  Index Scan using "<1>t_b_idx" on t  (cost=0.42..2.64 rows=1 width=8)
             Index Cond: (b = 10000)
    (6 rows)

  • PostgreSQL CLI を使用して、index_advisory テーブルからインデックス作成の提案をクエリできます。例:

    postgres=# SELECT * FROM index_advisory;
     reloid | relname | attrs | benefit | original_cost | new_cost | index_size | backend_pid |            timestamp
    --------+---------+-------+---------+---------------+----------+------------+-------------+----------------------------------
      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:00
    (3 rows)

    フィールド	タイプ	説明
    reloid	oid	インデックスのテーブルの OID。
    relname	name	インデックスのテーブルの名前。
    attrs	integer[]	インデックス作成の提案が生成される列。列は ID で識別されます。
    benefit	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
    ----------------------------------------------------------------------------------------------------------------------------------------------------
     create index idx_t_a on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
     create index idx_t_b on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
    (2 rows)

    説明

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

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

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

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

    • インデックスを使用することによるゲイン。インデックスのゲインを計算するには、次の数式が使用されます: インデックスを使用することによるゲイン = インデックスを使用することによる利点/インデックスの消費サイズ。

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

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

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

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

    postgres=# SELECT * FROM select_index_advisory;
     backend_pid |                                                             show_index_advisory
    -------------+----------------------------------------------------------------------------------------------------------------------------------------------------
           79370 | create index t_a_idx on public.t(a);/* size: 2624 KB, benefit: 3021.99, gain: 1.15167301457103, original_cost: 1693, new_cost: 182.005006313324 */
           79370 | create index t_b_idx on public.t(b);/* size: 2624 KB, benefit: 1684.56, gain: 0.641983590474943, original_cost: 1693, new_cost: 8.4350004196167 */
    (2 rows)

    各セッションでは、同じインデックス作成の提案から恩恵を受けるすべてのクエリの結果が、インデックス作成の提案のメトリックのセットに結合されます。メトリックは、benefit という名前のフィールドと gain という名前のフィールドで表されます。次の数式は、2 つのフィールドの値を計算する方法を示しています:

    size = MAX(index size of all queries)
    benefit = SUM(benefit of each query)
    gain = SUM(benefit of each query) / MAX(index size of all queries)

    説明

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

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