hypopg插件用于帮助您检查某类索引是否有助于一个或者多个查询。

前提条件

  • 在使用hypopg插件前,您需要知道:
    • 需要优化哪些查询。
    • 需要尝试使用哪些索引类型。
  • 支持的PolarDB PostgreSQL版的版本如下:
    • PostgreSQL 14(内核小版本14.5.1.0及以上)
    • PostgreSQL 11(内核小版本1.1.28及以上)
    说明 您可通过如下语句查看PolarDB PostgreSQL版的内核小版本的版本号:
    • PostgreSQL 14
      select version();
    • PostgreSQL 11
      show polar_version;

简介

hypopg插件是PolarDB PostgreSQL版支持的一款开源第三方插件,hypopg创建的虚拟索引不会存在于任何系统表中,而是存放在您连接的私有内存中。由于虚拟索引实际上并不真正存在于任何物理文件中,因此hypopg保证了虚拟索引只会被一个简单的EXPLAIN语句(不包括ANALYZE选项)使用。虚拟索引并不是真实存在的索引,因此不耗费CPU、磁盘或其他资源。
说明 hypopg插件支持的索引类型如下:
  • btree:B-树索引。
  • brin:块级索引。
  • hash:哈希索引。
  • bloom:布隆索引(需要先安装bloom插件)。

使用方法

  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)