Postgresql之虚拟索引插件hypopg

在oracle中，很早就有virtual index的实现，可以很好判断索引是否可以被使用。今天介绍一款PG虚拟索引的插件hypopg，可以达到同样的效果。

虚拟索引是指实际上并不存在的索引，不需要花费CPU、磁盘等任何资源来创建索引。它可以帮助我们了解特定的索引是否可以使用索引提高查询性能，而不必花费资源来创建它们。

git地址如下：
https://github.com/HypoPG/hypopg

目前支持如下几种索引：
btree
brin
hash (requires PostgreSQL 10 or above)
bloom (requires the bloom extension to be installed)

安装：

#使用已经配置好PG环境的用户安装即可
unzip hypopg-REL1_STABLE.zip 
cd hypopg-REL1_STABLE
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 -g -O2 -fPIC -I. -I./ -I/opt/pgsql13/include/server -I/opt/pgsql13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -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 -g -O2 -fPIC -I. -I./ -I/opt/pgsql13/include/server -I/opt/pgsql13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -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 -g -O2 -fPIC -I. -I./ -I/opt/pgsql13/include/server -I/opt/pgsql13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -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 -g -O2 -fPIC -I. -I./ -I/opt/pgsql13/include/server -I/opt/pgsql13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -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 -g -O2 -fPIC -shared -o hypopg.so hypopg.o hypopg_index.o import/hypopg_import.o import/hypopg_import_index.o -L/opt/pgsql13/lib    -Wl,--as-needed -Wl,-rpath,'/opt/pgsql13/lib',--enable-new-dtags  


make install
/bin/mkdir -p '/opt/pgsql13/lib'
/bin/mkdir -p '/opt/pgsql13/share/extension'
/bin/mkdir -p '/opt/pgsql13/share/extension'
/bin/install -c -m 755  hypopg.so '/opt/pgsql13/lib/hypopg.so'
/bin/install -c -m 644 .//hypopg.control '/opt/pgsql13/share/extension/'
/bin/install -c -m 644 .//hypopg--1.3.1--1.3.2.sql .//hypopg--1.3.1.sql .//hypopg--1.3.2.sql  '/opt/pgsql13/share/extension/'

使用实例：

#连接数据库，安装插件
psql
postgres=# create extension hypopg ;
CREATE EXTENSION

#一个虚拟索引使用的实例
postgres=# \d tb1
                Table "public.tb1"
 Column |  Type  | Collation | Nullable | Default 
--------+--------+-----------+----------+---------
 a      | bigint |           |          | 
 b      | text   |           |          | 

#a字段没索引，使用全表扫描
postgres=# explain select * from tb1 where a =10;
                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on tb1  (cost=0.00..1887.04 rows=1 width=20)
   Filter: (a = 10)
(2 rows)



#创建虚拟索引，可见可以使用该索引
postgres=# SELECT hypopg_create_index('CREATE INDEX idx_tb1_a ON tb1(a);');
    hypopg_create_index     
----------------------------
 (13564,<13564>btree_tb1_a)
(1 row)

postgres=# explain select * from tb1 where a =10;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Index Scan using "<13564>btree_tb1_a" on tb1  (cost=0.04..8.06 rows=1 width=20)
   Index Cond: (a = 10)
(2 rows)

#查看虚拟索引大小
postgres=# SELECT indexname,pg_size_pretty(hypopg_relation_size(indexrelid)) FROM hypopg();
     indexname      | pg_size_pretty 
--------------------+----------------
 <13564>btree_tb1_a | 3056 kB
(1 row)

#查看虚拟索引信息
postgres=# select * from hypopg();
     indexname      | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid 
--------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
 <13564>btree_tb1_a |      13564 |    42982 |       1 | f           | 1      | 0            | 3124     |           |          |         |  403
#删除所有虚拟索引
postgres=# select * from hypopg_reset();
#删除指定的虚拟索引,如
postgres=# select * from hypopg_drop_index(13564);
 hypopg_drop_index 
-------------------
 t
(1 row)
#查看索引和创建虚拟索引的语句，hypopg_list_indexes可以显示虚拟索引信息
postgres=# select  * from hypopg_list_indexes;
 indexrelid |     index_name     | schema_name | table_name | am_name 
------------+--------------------+-------------+------------+---------
      13564 | <13564>btree_tb1_a | public      | tb1        | btree

postgres=# SELECT index_name, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes ;
     index_name     |            hypopg_get_indexdef             
--------------------+--------------------------------------------
 <13564>btree_tb1_a | CREATE INDEX ON public.tb1 USING btree (a)

虚拟索引不能用于EXPLAIN ANALYZE语句，因为语句要真实执行SQL语句。而索引实际上并不存在，它不能使用一个不存在的索引。

更多细节可以参考如下文档：
https://hypopg.readthedocs.io/en/rel1_stable/usage.html#introduction
https://rjuju.github.io/postgresql/2015/07/02/how-about-hypothetical-indexes.html