通过索引可以加速查询。但是执行SQL时，并不是所有索引都会使用。如果花费很长时间创建一个索引，最后却用不到，岂不是又浪费时间又浪费磁盘空间。那有没有啥方法，可以即不浪费时间又不浪费空间，提前知道这个索引能否可用？HypoPG插件可以帮助创建一个虚拟索引，即不耗费CPU也不耗费磁盘。
轻量级实现：HypoPG
HypoPG作为扩展插件，可拔插，支持PG9.2及其以上版本，无需重新启动服务即可使用。每个后端都有自己的一组虚拟索引，并不会干扰其他连接。另外，虚拟索引存储在内存中，添加/删除大量索引并不会使系统目录膨胀。该插件实现的限制：必须通过用户自定义函数来完成。
使用方法
使用时，需要CREATE EXTENSION hypopg;来加载插件。虚拟索引属于独立后台进程，因此并发时，不同进程的虚拟索引并不会彼此影响。下面是也给例子：
rjuju=# CREATE TABLE hypo AS SELECT id, 'line ' || id AS val FROM generate_series(1,10000) id; rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1; QUERY PLAN ------------------------------------------------------- Seq Scan on hypo (cost=0.00..180.00 rows=1 width=13) Filter: (id = 1) (2 rows)
创建虚拟索引最简单的方法：使用hypopg_create_index带有CREATE INDEX语句作为参数的函数：
rjuju=# SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');
注意，CREATE INDEX语句中某些信息会被忽略，例如索引名。一些被忽略的信息会在未来版本中处理。也可以在自己的后端查看可用的虚拟索引：
rjuju=# SELECT * FROM hypopg_list_indexes ; indexrelid | indexname | nspname | relname | amname -----------+-------------------------------------------+---------+---------+-------- 205101 | <41072>btree_hypo_id | public | hypo | btree
如果需要有关虚拟索引更多信息，hypopg()函数将以类似于pg_index系统目录的方式返回虚拟索引。限制看下之前的EXPLAIN语句是否会使用这样的索引：
rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1; QUERY PLAN ------------------------------------------------------------------------------------ Index Scan using <41072>hypo_btree_hypo_id on hypo (cost=0.29..8.30 rows=1 width=13) Index Cond: (id = 1) (2 rows)
当然，加上ANALYZE后就不会使用了：
rjuju=# EXPLAIN ANALYZE SELECT * FROM hypo WHERE id = 1; QUERY PLAN ------------------------------------------------------------------------------------------------- Seq Scan on hypo (cost=0.00..180.00 rows=1 width=13) (actual time=0.036..6.072 rows=1 loops=1) Filter: (id = 1) Rows Removed by Filter: 9999 Planning time: 0.109 ms Execution time: 6.113 ms (5 rows)
使用hypopg_drop_index(indexrelid)函数将虚拟索引删除，或者关闭当前连接。该插件还提供了隐藏和恢复某个索引的功能：1）先用hypopg_reset()清除其他索引之前的影响：SELECT hypopg_reset();2）创建2个虚拟索引，并执行EXPLAIN
rjuju=# CREATE INDEX ON hypo(id); rjuju=# CREATE INDEX ON hypo(id, val); rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1; QUERY PLAN ---------------------------------------------------------------------------------- Index Only Scan using hypo_id_val_idx on hypo (cost=0.29..8.30 rows=1 width=13) Index Cond: (id = 1) (2 rows)
3）查询计划使用hypo_id_val_idx索引，使用hypopg_hide_index(oid)隐藏其中一个索引：
rjuju=# SELECT hypopg_hide_index('hypo_id_val_idx'::REGCLASS); rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1; QUERY PLAN ------------------------------------------------------------------------- Index Scan using hypo_id_idx on hypo (cost=0.29..8.30 rows=1 width=13) Index Cond: (id = 1) (2 rows)
4）查询计划使用了另一个索引hypo_id_idx。使用hypopg_hide_index(oid)再把它隐藏掉：
rjuju=# SELECT hypopg_hide_index('hypo_id_idx'::REGCLASS); rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1; QUERY PLAN ------------------------------------------------------- Seq Scan on hypo (cost=0.00..180.00 rows=1 width=13) Filter: (id = 1) (2 rows)
查询计划又退回使用SeqScan了。5）使用hypopg_unhide_index(oid)恢复索引：
rjuju=# SELECT hypopg_unhide_index('hypo_id_idx'::regclass); rjuju=# EXPLAIN SELECT * FROM hypo WHERE id = 1; QUERY PLAN ------------------------------------------------------------------------- Index Scan using hypo_id_idx on hypo (cost=0.29..8.30 rows=1 width=13) Index Cond: (id = 1) (2 rows)
6）可以使用函数hypopg_hidden_indexes()或者视图hypopg_hidden_indexes查看隐藏了哪些索引：
rjuju=# SELECT * FROM hypopg_hidden_indexes(); indexid --------- 526604 526603 12659 (3 rows)
rjuju=# SELECT * FROM hypopg_hidden_indexes; indexrelid | index_name | schema_name | table_name | am_name | is_hypo ------------+----------------------+-------------+------------+---------+--------- 12659 | <12659>btree_hypo_id | public | hypo | btree | t 526603 | hypo_id_idx | public | hypo | btree | f 526604 | hypo_id_val_idx | public | hypo | btree | f (3 rows)
7）可以使用函数hypopg_unhide_all_indexes()恢复所有隐藏的虚拟索引。请注意，隐藏现有索引的功能仅适用于当前会话中的EXPLAIN 命令，不会影响其他会话。
参考
https://rjuju.github.io/postgresql/2015/07/02/how-about-hypothetical-indexes.htmlhttps://github.com/HypoPG/hypopghttps://hypopg.readthedocs.io/en/rel1_stable/
