PostgreSQL插件HypoPG：支持虚拟索引

yanzongshuaiDBA 2023-06-05

308

通过索引可以加速查询。但是执行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.html

https://github.com/HypoPG/hypopg

https://hypopg.readthedocs.io/en/rel1_stable/

postgresql 索引 select

文章转载自yanzongshuaiDBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL插件HypoPG：支持虚拟索引

轻量级实现：HypoPG

使用方法

参考

评论