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)

      說明

      • 上述結果表示hypopg1.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時動態計算得到的，且有能夠在備用（Standby）伺服器上使用的優勢，因此這樣不會產生任何問題。 說明 在預設值為off情況下的缺點是不能同時擁有超過大約2500個的虛擬索引。如果存在的虛擬索引數量超過最大值，那麼再建立一個新的虛擬索引的時間將會非常漫長。此時可以調用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-樹索引有助於此查詢。

   • 函數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)