作者
digoal
日期
2017-10-30
标签
PostgreSQL , 虚拟索引 , 虚假索引 , HypoPG , hypothetical index
背景
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/
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
