使用hypopg插件

简介

hypopg是一个RDS for PostgreSQL的扩展，有助于了解特定索引是否可以提高问题查询的性能。虚拟索引并不是真实存在的索引，因此创建时不会消耗CPU、磁盘或其他资源，就可以验证索引是否有效。

更多信息可参见hypopg官方文档。

支持的版本说明

PostgreSQL 11及以上版本的最新小版本支持该插件。可通过以下SQL语句查询当前实例是否支持该插件：

SELECT * FROM pg_available_extension_versions WHERE name = 'hypopg';

如果不支持，可通过升级内核小版本或者大版本升级后使用该插件。

RDS for PostgreSQL实例支持的插件，具体请参见支持的插件列表。

插件介绍

hypopg插件是RDSforPostgreSQL支持的一款开源第三方插件，hypopg创建的虚拟索引不会存在于任何系统表中，而是存放在连接的私有内存中。由于虚拟索引实际上并不真正存在于任何物理文件中，因此hypopg保证了虚拟索引只会被一个简单的EXPLAIN语句使用（不包括ANALYZE选项）。虚拟索引并不是真实存在的索引，因此不耗费CPU、磁盘或其他资源。

hypopg插件支持的索引类型如下：

• BTREE：B型树索引。
• BRIN：块级索引。
• HASH：哈希索引。
• BLOOM：布隆索引（需要先安装bloom插件）。

插件安装与卸载

• 安装插件

  SELECT control_extension ('create', 'hypopg');

• 卸载插件

  SELECT control_extension ('drop', 'hypopg');

更多信息，请参见通过界面安装和卸载插件和通过SQL命令安装和卸载插件。

基本使用

1. 安装hypopg插件。

   SELECT control_extension ('create', 'hypopg');

2. 创建表并插入测试数据。

   CREATE TABLE t (id int, col text) ;
   INSERT INTO t select x as id,'col '||x from generate_series(1,100000) as x;

3. 查看默认执行计划。

   EXPLAIN SELECT * FROM t WHERE id = 1;
                          QUERY PLAN
   --------------------------------------------------------
    Seq Scan on t  (cost=0.00..1399.84 rows=344 width=36)
      Filter: (id = 1)
   (2 rows)

4. 创建虚拟索引。

   SELECT hypopg_create_index('CREATE INDEX ON t (id)') ;
    hypopg_create_index    
   ---------------------------
   (14737,<14737>btree_t_id)
   (1 row)

   表1 参数说明

   参数	说明
   14737	虚拟索引的标识符。
   <14737>btree_t_id	生成的虚拟索引名称。

5. 再次运行EXPLAIN查看PostgreSQL会使用此虚拟索引。

   EXPLAIN SELECT * FROM t WHERE id = 1;
                                       QUERY PLAN
   ----------------------------------------------------------------------------------
    Index Scan using "<14737>btree_t_id" on t  (cost=0.04..2.26 rows=1 width=13)
       Index Cond: (id = 1)
   (2 rows)

6. 虚拟索引是“虚拟的”，并不会在实际运行SQL语句时使用。可以查看SQL实际的运行计划。

   EXPLAIN ANALYZE SELECT * FROM t WHERE id = 1;
                                               QUERY PLAN
   ---------------------------------------------------------------------------------------------------
    Seq Scan on t  (cost=0.00..1791.00 rows=1 width=13) (actual time=0.010..5.378 rows=1 loops=1)
      Filter: (id = 1)
      Rows Removed by Filter: 99999
    Planning Time: 0.036 ms
    Execution Time: 5.401 ms
   (5 rows)