PostgreSQL查询优化：使用HypoPG插件控制索引

HypoPG是支持虚拟索引的PostgreSQL扩展插件。

虚拟索引是实际上不存在的索引，因此不会消耗CPU、磁盘或任何资源来创建。它们有助于了解特定索引是否可以提高查询的性能，因为您可以知道PostgreSQL是否会使用这些索引，而无需花费资源来创建它们。

安装：

#下载地址：https://github.com/HypoPG/hypopg
#将hypopg-1.4.0.tar.gz文件上传至/opt目录后解压
tar zxvf hypopg-1.4.0.tar.gz
#安装
make
make install

案例：

#建表并插入数据
postgres=# CREATE TABLE wytb01 AS SELECT id,'wuyang '|| id AS val FROM generate_series(1,10000) id;
SELECT 10000
#统计信息收集
postgres=# ANALYZE wytb01;
ANALYZE
#当前为全表扫描
postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id =1;
                       QUERY PLAN
---------------------------------------------------------
SeqScan on wytb01  (cost=0.00..152.00 rows=1 width=15)
Filter:(id =1)
(2 rows)
#创建虚拟索引
postgres=# SELECT * FROM hypopg_create_index('CREATE INDEX ON wytb01 (id)');
 indexrelid |       indexname
------------+------------------------
13624|<13624>btree_wytb01_id
(1 row)
#查看虚拟索引创建后的执行计划
postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id =1;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
IndexScanusing"<13624>btree_wytb01_id" on wytb01  (cost=0.04..8.05 rows=1 width=15)
IndexCond:(id =1)
(2 rows)
#只有在不使用`EXPLAIN ANALYZE`时才会使用虚拟索引
postgres=# EXPLAIN ANALYZE SELECT * FROM wytb01 WHERE id =1;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
SeqScan on wytb01  (cost=0.00..180.00 rows=1 width=13)(actual time=0.036..6.072 rows=1 loops=1)
Filter:(id =1)
RowsRemovedbyFilter:9999
Planning time:0.109 ms
Execution time:6.113 ms

案例二：

#继续上述案例，您可以创建真实索引并运行`EXPLAIN`：
postgres=#  SELECT hypopg_reset();
 hypopg_reset
--------------
(1 row)
#创建真实索引，查看执行计划
postgres=# CREATE INDEX ON wytb01(id, val);
CREATE INDEX
postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id =1;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
IndexOnlyScanusing wytb01_id_val_idx on wytb01  (cost=0.29..4.30 rows=1 width=15)
IndexCond:(id =1)
(2 rows)
#查询计划使用了索引。使用`hypopg_hide_index(oid)`隐藏其中一个索引：
postgres=# SELECT hypopg_hide_index('wytb01_id_val_idx'::REGCLASS);
 hypopg_hide_index
-------------------
 t
(1 row)
#隐藏后，全表扫描
postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id =1;
                       QUERY PLAN
---------------------------------------------------------
SeqScan on wytb01  (cost=0.00..152.00 rows=1 width=15)
Filter:(id =1)
(2 rows)
#也可以隐藏虚拟索引
postgres=# SELECT hypopg_create_index('CREATE INDEX ON wytb01(id)');
      hypopg_create_index
--------------------------------
(13625,<13625>btree_wytb01_id)
(1 row)
postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id =1;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
IndexOnlyScanusing wytb01_id_val_idx on wytb01  (cost=0.29..4.30 rows=1 width=15)
IndexCond:(id =1)
(2 rows)
postgres=# SELECT hypopg_hide_index(13625);
 hypopg_hide_index
-------------------
 f
(1 row)
postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id =1;
                    QUERY PLAN
-------------------------------------------------------
SeqScan on wytb01  (cost=0.00..180.00 rows=1 width=13)
Filter:(id =1)
您可以使用`hypopg_hidden_indexes()`或视图检查哪些索引被隐藏

1.hypopg不提供扩展升级脚本，因为在创建的任何对象中没有保存数据。因此，您需要首先删除扩展，然后再次创建它以获取新版本。

2.隐藏现有索引的功能仅适用于当前会话中的EXPLAIN命令，并不会影响其他会话。