hypopg（虚拟索引）_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

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

前提条件

在使用hypopg插件前，您需要知道：
- 需要优化哪些查询。
- 需要尝试使用哪些索引类型。
支持的PolarDB PostgreSQL版（兼容Oracle）的版本如下：
- Oracle语法兼容 2.0（内核小版本2.0.14.1.0及以上）
- Oracle语法兼容 1.0（内核小版本1.1.28及以上）
说明
您可通过如下语句查看PolarDB PostgreSQL版（兼容Oracle）的内核小版本号：
```
SHOW polar_version;
```

简介

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

说明

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

btree：B-树索引。
brin：块级索引。
hash：哈希索引。
bloom：布隆索引（需要先安装bloom插件）。

使用方法

安装插件。

安装hypopg插件。
```
CREATE EXTENSION hypopg;
```

查看插件是否已被安装。

\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)

配置参数。

参数

说明

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会请求一个真实的标识符，这将需要获得更多的锁资源，且不能在备用服务器上使用，但允许使用所有标识符。具体使用方法请参见虚拟索引操作方法。
说明
切换该参数状态不需要重置虚拟索引标识符，真实标识符和非真实标识符可以共存。

卸载插件。
```
DROP EXTENSION hypopg;
```

说明

更多使用方法，请参见虚拟索引操作方法。

示例测试

创建表并插入部分数据，该表上没有任何索引。示例如下：

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不存在索引，所以查询使用的是顺序扫描。

创建虚拟索引。示例如下：
```
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。

运行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)

说明

数据库使用了该类型的索引。

运行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)
```

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