PostgreSQL hstore 列效能提升一例

PostgreSQL 支援hstore 來存放KEY->VALUE這類資料， 其實也類似於ARRAY或者JSON類型。  要高效的使用這類資料，當然離不開高效的索引。我們今天就來看看兩類不同的索引對於同一種檢索請求的效能問題。


假如我們有這樣一個原始表，基於str1欄位有一個BTREE索引。

t_girl=# \d status_check;          Table "ytt.status_check" Column |         Type          | Modifiers --------+-----------------------+----------- is_yes | boolean               | not null str1   | character varying(20) | not null str2   | character varying(20) | not nullIndexes:    "index_status_check_str1" btree (str1) 

裡面有10W條記錄。 資料大概如下，

t_girl=# select * from status_check limit 2; is_yes | str1 |         str2         --------+------+---------------------- f      | 0    | cfcd208495d565ef66e7 t      | 1    | c4ca4238a0b923820dcc(2 rows)Time: 0.617 mst_girl=# 

存放hstore類型的status_check_hstore 表結構，基於str1_str2欄位有一個GIST索引。

 Table "ytt.status_check_hstore"  Column   |  Type   | Modifiers -----------+---------+----------- is_yes    | boolean |  str1_str2 | hstore  | Indexes:    "idx_str_str2_gist" gist (str1_str2) 

t_girl=# select * from status_check_hstore limit 2; is_yes |          str1_str2          --------+----------------------------- f      | "0"=>"cfcd208495d565ef66e7" t      | "1"=>"c4ca4238a0b923820dcc"(2 rows)Time: 39.874 ms

接下來我們要得到跟查詢原始表一樣的結果，當然原始表的查詢非常高效。 表語句以及結果如下，

t_girl=# select * from status_check where str1 in ('10','23','33');         is_yes | str1 |         str2         --------+------+---------------------- t      | 10   | d3d9446802a44259755d t      | 23   | 37693cfc748049e45d87 f      | 33   | 182be0c5cdcd5072bb18(3 rows)Time: 0.690 ms

上面的語句用了不到1毫秒。


接下來我們對hstore表進行查詢，

t_girl=# select is_yes,skeys(str1_str2),svals(str1_str2) from status_check_hstore where str1_str2 ?| array['10','23','33']; is_yes | skeys |        svals         --------+-------+---------------------- t      | 10    | d3d9446802a44259755d t      | 23    | 37693cfc748049e45d87 f      | 33    | 182be0c5cdcd5072bb18(3 rows)Time: 40.256 ms

我的天，比原始表的查詢慢了幾十倍。


看下查詢計劃，把所有行都掃描了一遍。

                                    QUERY PLAN                                     ----------------------------------------------------------------------------------- Bitmap Heap Scan on status_check_hstore  (cost=5.06..790.12 rows=100000 width=38)   Recheck Cond: (str1_str2 ?| '{10,23,33}'::text[])   ->  Bitmap Index Scan on idx_str_str2_gist  (cost=0.00..5.03 rows=100 width=0)         Index Cond: (str1_str2 ?| '{10,23,33}'::text[])(4 rows)Time: 0.688 ms

我們想辦法來最佳化這條語句， 如果把這條語句變成跟原始語句一樣的話，那麼是否就可以用到BTREE索引了？
接下來，建立一個基於BTREE的函數索引，

t_girl=# create index idx_str1_str2_akeys on status_check_hstore using btree (array_to_string(akeys(str1_str2),','));CREATE INDEXTime: 394.123 ms

OK,變化語句來執行下同樣的檢索，

t_girl=# select is_yes,skeys(str1_str2),svals(str1_str2) from status_check_hstore where array_to_string(akeys(str1_str2),',') in ('10','23','33');         is_yes | skeys |        svals         --------+-------+---------------------- t      | 10    | d3d9446802a44259755d t      | 23    | 37693cfc748049e45d87 f      | 33    | 182be0c5cdcd5072bb18(3 rows)Time: 0.727 ms

這次和原始查詢速度一樣快了。