PolarDB:Hypopg (仮想インデックス)

概要

使用方法

1. 拡張機能をインストールします。

   1. hypopg 拡張機能をインストールします。

      CREATE EXTENSION hypopg;

   2. 拡張機能がインストールされているかどうかを確認します。

      \dx hypopg

      期待される出力：

                        List of installed extensions
        Name  | Version | Schema |             Description
      --------+---------+--------+-------------------------------------
       hypopg | 1.3.1   | public | Hypothetical indexes for PostgreSQL
      (1 row)

      説明

      • 上記の出力は、hypopg バージョン 1.3.1 がインストールされていることを示します。

      • SQL ステートメントを使用して pg_extension テーブルをクエリし、hypopg がインストールされているかを確認することもできます。例：

        SELECT * FROM pg_extension WHERE extname = 'hypopg';

        期待される出力：

        extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
        --------+----------+--------------+----------------+------------+-----------+--------------
         hypopg |       10 |        2200 | t               | 1.3.1      |           |
        (1 row)

2. パラメーターを設定します。

   パラメーター	説明
   hypopg.enabled	デフォルト値： on。有効値： on： hypopg 拡張機能を有効にします。 off： hypopg 拡張機能を無効にします。 説明 hypopg 拡張機能が無効になっている場合、仮想インデックスは使用されませんが、既存の仮想インデックスは削除されません。
   hypopg.use_real_oids	デフォルト値： off。有効値： off： hypopg は実際のオブジェクト識別子 (OID) を使用しません。代わりに、空き範囲から識別子を選択します。これらの識別子は、将来のバージョンで使用するためにデータベースによって予約されています。空き識別子範囲は、hypopg が初めて使用されるときに動的に計算され、スタンバイサーバーで使用できるという利点があるため、これにより問題が発生することはありません。 説明 デフォルト値が off の場合、デメリットとして、同時に約 2,500 個を超える仮想インデックスを持つことはできません。既存の仮想インデックスの数が最大値を超えると、新しい仮想インデックスの作成に非常に長い時間がかかります。この場合、hypopg_reset() 関数を呼び出すことで、この問題を解決できます。詳細については、「仮想インデックスの操作」をご参照ください。 on： hypopg は実際のオブジェクト識別子 (OID) を使用できます。[hypopg.use_real_oids] は、インデックス数が最大値を超えた場合の新しい仮想インデックスの作成が遅くなる問題を回避します。hypopg は実際の識別子を要求しますが、これにはより多くのロックリソースが必要であり、スタンバイサーバーでは使用できません。しかし、すべての識別子を使用できるようになります。詳細については、「仮想インデックスの操作」をご参照ください。 説明 このパラメーターを切り替えても、仮想インデックスの識別子をリセットする必要はありません。実際の識別子とそうでない識別子は共存できます。

3. 拡張機能をアンインストールします。

   DROP EXTENSION hypopg;

例

1. テーブルを作成し、データを挿入します。テーブルにはインデックスはありません。例：

   CREATE TABLE hypo (id integer, val text);
   INSERT INTO hypo SELECT i, 'line ' || i FROM generate_series(1, 100000) i;
   VACUUM ANALYZE hypo;

   インデックスが単純なクエリにメリットをもたらすかどうかを確認します。例：

   EXPLAIN SELECT val FROM hypo WHERE id = 1;

   期待される出力：

                          QUERY PLAN
   --------------------------------------------------------
    Seq Scan on hypo  (cost=0.00..1791.00 rows=1 width=10)
      Filter: (id = 1)
   (2 rows)

   説明

   hypo テーブルにインデックスが存在しないため、クエリはシーケンシャルスキャンを使用します。

2. 仮想インデックスを作成します。例：

   SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');

   期待される出力：

   indexrelid |      indexname
   ------------+----------------------
         13925 | <13925>btree_hypo_id
   (1 row)

   パラメーターの説明：

   パラメーター	説明
   13925	仮想インデックスの識別子。
   <13925>btree_hypo_id	生成された仮想インデックスの名前。

   説明

   • id 列の単純な B-tree インデックスは、このクエリにメリットをもたらします。

   • hypopg_create_index() 関数は、標準の CREATE INDEX 文を受け取り (この関数に渡された他の文は無視されます)、文ごとに仮想インデックスを作成します。

   • 識別子は動的に生成されます。この例では、13925 です。

3. EXPLAIN ステートメントを実行して、データベースがインデックスを使用するかどうかを確認します。例：

   EXPLAIN SELECT val FROM hypo WHERE id = 1;

   期待される出力：

                                        QUERY PLAN
   ------------------------------------------------------------------------------------
    Index Scan using "<13925>btree_hypo_id" on hypo  (cost=0.04..8.06 rows=1 width=10)
      Index Cond: (id = 1)
   (2 rows)

   説明

   データベースはこのタイプのインデックスを使用します。

4. EXPLAIN ステートメントを実行して、実際の実行中にデータベースが仮想インデックスを使用するかどうかを確認します。例：

   EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;

   期待される出力：

                                               QUERY PLAN
   ---------------------------------------------------------------------------------------------------
    Seq Scan on hypo  (cost=0.00..1791.00 rows=1 width=10) (actual time=0.030..15.439 rows=1 loops=1)
      Filter: (id = 1)
      Rows Removed by Filter: 99999
    Planning Time: 0.066 ms
    Execution Time: 15.492 ms
   (5 rows)

   説明

   実際の実行中にさらに詳しく調べると、データベースは仮想インデックスを使用しません。

仮想インデックスの操作

hypopg 拡張機能は、いくつかの便利な関数とビューも提供します。

• hypopg_list_indexes ビュー：作成済みのすべての仮想インデックスを一覧表示します。例：

  SELECT * FROM hypopg_list_indexes;

  期待される出力：

   indexrelid |      index_name      | schema_name | table_name | am_name
  ------------+----------------------+-------------+------------+---------
        13925 | <13925>btree_hypo_id | public      | hypo       | btree
  (1 row)

• hypopg() 関数： pg_index と同じ形式で、作成済みのすべての仮想インデックスを一覧表示します。例：

  SELECT * FROM hypopg();

  期待される出力：

        indexname       | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid
  ----------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
   <13925>btree_hypo_id |      13925 |    16450 |       1 | f           | 1      | 0            | 1978     |           |          |         |  403
  (1 row)

• hypopg_get_indexdef(oid) 関数：仮想インデックスの識別子に基づいて、実際の CREATE INDEX コマンドを返します。例：

  SELECT index_name, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes;

  期待される出力：

        index_name      |             hypopg_get_indexdef
  ----------------------+----------------------------------------------
   <13925>btree_hypo_id | CREATE INDEX ON public.hypo USING btree (id)
  (1 row)

• hypopg_relation_size(oid) 関数：仮想インデックスのサイズを推定します。例：

  SELECT index_name, pg_size_pretty(hypopg_relation_size(indexrelid))
  FROM hypopg_list_indexes;

  期待される出力：

        index_name      | pg_size_pretty
  ----------------------+----------------
   <13925>btree_hypo_id | 2544 kB
  (1 row)

• hypopg_drop_index(oid) 関数：指定された識別子の仮想インデックスを削除します。例：

  SELECT hypopg_drop_index(13925);

  期待される出力：

   hypopg_drop_index
  -------------------
   t
  (1 row)

• hypopg_reset() 関数：すべての仮想インデックスを削除します。例：

  SELECT hypopg_reset();

  期待される出力：

   hypopg_reset
  --------------
  
  (1 row)