虚拟索引插件之HypoPG

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

[postgres@xuyuyu data]$ cd hypopg-1.3.1/[postgres@xuyuyu hypopg-1.3.1]$ lltotal 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.mddrwxrwxr-x. 4 postgres postgres   158 Jun 21 06:26 debiandrwxrwxr-x. 2 postgres postgres   209 Jun 21 06:26 docsdrwxrwxr-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.cdrwxrwxr-x. 2 postgres postgres    58 Jun 21 06:26 importdrwxrwxr-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.mddrwxrwxr-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]$ makegcc -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.cgcc -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.cgcc -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.cgcc -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.cgcc -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 ~]$ psqlpsql (13.3)Type 'help' for help.postgres=# CREATE EXTENSION hypopg;CREATE EXTENSIONpostgres=# 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.enabled：默认为on. 使用此参数全局启用或禁用 HypoPG。禁用 HypoPG 时，不会使用假设索引，但不会删除定义的假设索引。hypopg.use_real_oids：默认为off. 默认情况下，HypoPG 不会使用“真实”对象标识符，而是从 ~ 14000 / 16384（分别是低于 FirstNormalObjectId 和 FirstNormalObjectId 的最低未使用 oid）范围借用，PostgreSQL 保留这些标识符以备将来在未来版本中使用。这不会造成任何问题，因为空闲范围是在第一次连接使用 HypoPG 时动态计算的，并且具有在备用服务器上工作的优势。但缺点是你不能同时拥有大约 2500 个以上的假设索引，并且一旦超过创建的最大对象数，创建新的假设索引将变得非常缓慢，直到hypopg_reset()被调用。如果这些缺点有问题，可以启用此参数。HypoPG 然后会要求一个真实的对象标识符，这将需要获得更多的锁并且不会在备用服务器上工作，但将允许使用完整范围的对象标识符。请注意，切换此参数不需要重启，两者可以同时共存。