576
PostgreSQL 虛擬|虛假 索引(hypothetical index) - HypoPG
標簽
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/
最後更新：2017-11-12 01:34:27