虚拟索引插件之HypoPG

作者：徐田原

HypoPG概述

在PG数据库中，如果检查某个索引是否有助于一个或多个查询，HypoPG就能起到关键作用，它是 postgresql 的一个 extension，允许创建虚拟索引，观察优化器是否使用。因此，可以提供需要优化哪些查询，以及想要尝试哪些索引的方法。那么对于一般的使用者，如何更好的判断加索引是否有效呢？
虚拟索引是一个很有用的东西，没有副作用，只是虚拟的索引，建立虚拟索引后，可以通过EXPLAIN来查看加索引后的成本估算，判断是否加索引COST会降低。
此外，HypoPG 将创建的假设索引不存储在任何目录中，而是存储在连接私有内存中。因此，它不会膨胀任何表，也不会影响任何并发连接。
由于假设索引并不真正存在，HypoPG 确保它们只会使用简单的 EXPLAIN 语句（没有 ANALYZE 选项）使用。

HypoPG使用

[postgres@xuyuyu data]$ cd hypopg-1.3.1/
[postgres@xuyuyu hypopg-1.3.1]$ ll
total 172
-rw-rw-r--. 1 postgres postgres  4529 Jun 21 06:26 CHANGELOG.md
-rw-rw-r--. 1 postgres postgres   373 Jun 21 06:26 CONTRIBUTORS.md
drwxrwxr-x. 4 postgres postgres   158 Jun 21 06:26 debian
drwxrwxr-x. 2 postgres postgres   209 Jun 21 06:26 docs
drwxrwxr-x. 2 postgres postgres   147 Jun 21 06:26 expected
-rw-rw-r--. 1 postgres postgres  2119 Jun 21 06:26 hypopg--1.3.1.sql
-rw-rw-r--. 1 postgres postgres 12539 Jun 21 06:26 hypopg.c
-rw-rw-r--. 1 postgres postgres   148 Jun 21 06:26 hypopg.control
-rw-rw-r--. 1 postgres postgres 60710 Jun 21 06:26 hypopg_index.c
drwxrwxr-x. 2 postgres postgres    58 Jun 21 06:26 import
drwxrwxr-x. 2 postgres postgres    96 Jun 21 06:26 include
-rw-rw-r--. 1 postgres postgres  1104 Jun 21 06:26 LICENSE
-rw-rw-r--. 1 postgres postgres  1737 Jun 21 06:26 Makefile
-rw-rw-r--. 1 postgres postgres  1053 Jun 21 06:26 META.json
-rw-rw-r--. 1 postgres postgres  3938 Jun 21 06:26 README.md
drwxrwxr-x. 3 postgres postgres    17 Jun 21 06:26 test
-rw-rw-r--. 1 postgres postgres   860 Jun 21 06:26 TODO.md
-rw-rw-r--. 1 postgres postgres 53904 Jun 21 06:26 typedefs.list
[postgres@xuyuyu hypopg-1.3.1]$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/data/postgresql13/include/server -I/data/postgresql13/include/internal  -D_GNU_SOURCE   -c -o hypopg.o hypopg.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/data/postgresql13/include/server -I/data/postgresql13/include/internal  -D_GNU_SOURCE   -c -o hypopg_index.o hypopg_index.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/data/postgresql13/include/server -I/data/postgresql13/include/internal  -D_GNU_SOURCE   -c -o import/hypopg_import.o import/hypopg_import.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/data/postgresql13/include/server -I/data/postgresql13/include/internal  -D_GNU_SOURCE   -c -o import/hypopg_import_index.o import/hypopg_import_index.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o hypopg.so hypopg.o hypopg_index.o import/hypopg_import.o import/hypopg_import_index.o -L/data/postgresql13/lib    -Wl,--as-needed -Wl,-rpath,'/data/postgresql13/lib',--enable-new-dtags  
[postgres@xuyuyu hypopg-1.3.1]$ make install
/bin/mkdir -p '/data/postgresql13/lib'
/bin/mkdir -p '/data/postgresql13/share/extension'
/bin/mkdir -p '/data/postgresql13/share/extension'
/bin/install -c -m 755  hypopg.so '/data/postgresql13/lib/hypopg.so'
/bin/install -c -m 644 .//hypopg.control '/data/postgresql13/share/extension/'
/bin/install -c -m 644 .//hypopg--1.3.1.sql  '/data/postgresql13/share/extension/'
[postgres@xuyuyu ~]$ psql
psql (13.3)
Type 'help' for help.
postgres=# CREATE EXTENSION hypopg;
CREATE EXTENSION
postgres=# create table t as select x as id,'c1'||x as c1 from generate_series(1,100000) as x;
SELECT 100000

该表没有任何索引。假设我们要检查索引是否有助于简单查询。首先，让我们看看它的行为：
postgres=# explain select * from t where id  = 1000;
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on t  (cost=0.00..1791.00 rows=1 width=11)
   Filter: (id = 1000)
(2 rows)
postgres=# select hypopg_create_index('create index on t(id)');
    hypopg_create_index    
---------------------------
 (13564,<13564>btree_t_id)
(1 row)
该函数返回两列：
- 假设索引的对象标识符
- 生成的假设索引名称
再次运行 EXPLAIN 以查看 PostgreSQL 是否会使用此索引:
postgres=# EXPLAIN SELECT * FROM t WHERE id = 1;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Index Scan using '<13564>btree_t_id' on t  (cost=0.04..8.06 rows=1 width=11)
   Index Cond: (id = 1)
(2 rows)
PostgreSQL 会使用这样的索引。为了确定，让我们检查一下假设索引不会用于实际运行查询：
postgres=# EXPLAIN ANALYZE SELECT * FROM t WHERE id = 1;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1791.00 rows=1 width=11) (actual time=0.010..5.843 rows=1 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 99999
 Planning Time: 0.044 ms
 Execution Time: 5.864 ms
(5 rows)
postgres=# \df hypopg_*
                                                List of functions
 Schema |         Name         | Result data type |                  Argument data types                   | Type 
--------+----------------------+------------------+--------------------------------------------------------+------
 public | hypopg_create_index  | SETOF record     | sql_order text, OUT indexrelid oid, OUT indexname text | func
 public | hypopg_drop_index    | boolean          | indexid oid                                            | func
 public | hypopg_get_indexdef  | text             | indexid oid                                            | func
 public | hypopg_relation_size | bigint           | indexid oid                                            | func
 public | hypopg_reset         | void             |                                                        | func
 public | hypopg_reset_index   | void             |                                                        | func
(6 rows)
postgres=# select hypopg_get_indexdef(13564);
            hypopg_get_indexdef            
-------------------------------------------
 CREATE INDEX ON public.t USING btree (id)
(1 row)
还提供一些其他便利功能：
hypopg_relation_size(oid)：估计假设的指数有多大：
postgres=# select * from hypopg_relation_size(13564);
 hypopg_relation_size 
----------------------
              2605056
(1 row)
Time: 0.706 ms
(1 row)
hypopg_drop_index(oid) : 删除给定的假设索引
hypopg_reset()：删除所有假设索引

HypoPG参数

以下配置参数 (GUC) 可用，并且可以交互更改：

hypopg.enabled：
默认为on. 使用此参数全局启用或禁用 HypoPG。禁用 HypoPG 时，不会使用假设索引，但不会删除定义的假设索引。
hypopg.use_real_oids：
默认为off. 默认情况下，HypoPG 不会使用“真实”对象标识符，而是从 ~ 14000 / 16384（分别是低于 FirstNormalObjectId 和 FirstNormalObjectId 的最低未使用 oid）范围借用，PostgreSQL 保留这些标识符以备将来在未来版本中使用。这不会造成任何问题，因为空闲范围是在第一次连接使用 HypoPG 时动态计算的，并且具有在备用服务器上工作的优势。但缺点是你不能同时拥有大约 2500 个以上的假设索引，并且一旦超过创建的最大对象数，创建新的假设索引将变得非常缓慢，直到hypopg_reset()被调用。
如果这些缺点有问题，可以启用此参数。HypoPG 然后会要求一个真实的对象标识符，这将需要获得更多的锁并且不会在备用服务器上工作，但将允许使用完整范围的对象标识符。
请注意，切换此参数不需要重启，两者可以同时共存。