PostgreSQL 虚拟 虚假 索引(hypothetical index) - HypoPG

背景

DBA实际上是一种比较稀缺的资源，很多企业甚至没有DBA，或者SA、开发人员兼职DBA，对于一般的使用者，对数据库了解程度有限，特别是在SQL优化方面的知识更加有限。

最常用也是奏效较快的SQL优化手段，通常是加索引，这也是我从很多开发者交流后得知的，很多人的概念是，SQL慢，加索引嘛。

但是加索引有没有效果要针对“SQL、针对数据分布、针对输入条件、针对列的唯一值比例” 来判断：加索引后的降低了多少CPU的FILTER计算开销，降低了多少IO的扫描。同时，加索引带来的副作用是写入IO放大，占用更多空间，写入性能下降。

并且，在加索引时，会堵塞DML（不过还好，PG支持并发加索引，不堵塞DML。 CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]）。

那么对于一般的使用者，如何更好的判断加索引是否有效呢？

虚拟索引是一个很有用的东西，没有副作用，只是虚拟的索引，建立虚拟索引后，可以通过EXPLAIN来查看加索引后的成本估算，判断是否加索引COST会降低。

hypopg 虚拟索引插件

1、安装插件

https://github.com/dalibo/hypopg/

2、建立插件

CREATE EXTENSION hypopg;  

3、建测试表

rjuju=# CREATE TABLE hypo AS SELECT id, 'line ' || id AS val FROM generate_series(1,10000) id;  

4、查看没有索引时，全表扫描的成本

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)  

5、建立虚拟索引

rjuju=# SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');  

6、查看已建立了哪些虚拟索引

rjuju=# SELECT * FROM hypopg_list_indexes();  
 indexrelid |                 indexname                 | nspname | relname | amname  
 -----------+-------------------------------------------+---------+---------+--------  
     205101 | <41072>btree_hypo_id                      | public  | hypo    | btree  

7、查看建立虚拟索引后的执行计划

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)  

8、查看真实的执行计划

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)  

9、清除虚拟索引

调用hypopg_drop_index(indexrelid) 清除单个虚拟索引，调用hypopg_reset() 清除所有虚拟索引。

To remove your backend's hypothetical indexes,   
  
you can use the function hypopg_drop_index(indexrelid) with the OID that hypopg_list_indexes() function returns,   
  
call hypopg_reset() to remove all at once or just close your current connection.  

参考

https://github.com/dalibo/hypopg/

digoal’s 大量PostgreSQL文章入口