云原生数据库 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)