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 がインストールされていることを示しています。

      • また、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 が最初に使用されるときに動的に計算されます。このメソッドには、スタンバイサーバーで使用でき、問題を引き起こさないという利点があります。 説明 この設定の欠点は、同時に約 2,500 を超える仮想インデックスを持つことができないことです。既存の仮想インデックスの数が最大制限を超えると、新しいインデックスの作成に時間がかかります。この問題を解決するには、hypopg_reset() 関数を呼び出します。関数の使用方法の詳細については、「仮想インデックスの操作」をご参照ください。 on: hypopg は実際の OID を使用します。[hypopg.use_real_oids] を on に設定すると、インデックスの最大数を超えたときに発生する長い作成時間が回避されます。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 ANALYZE 文を実行して、実際の実行中にデータベースが仮想インデックスを使用するかどうかを判断します。例:

   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)