PostgreSQL hstore 列性能提升一例

概述PostgreSQL 支持hstore 来存放KEY->VALUE这类数据， 其实也类似于ARRAY或者JSON类型。  要高效的使用这类数据，当然离不开高效的索引。我们今天就来看看两类不同的索引对于同一种检索请求的性能问题。 假如我们有这样一个原始表，基于str1字段有一个BTREE索引。 t_girl=# \d status_check; Table "ytt.status 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','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,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,svals(str1_str2) from status_check_hstore where array_to_string(akeys(str1_str2),') in ('10','33');         is_yes | skeys |        svals         --------+-------+---------------------- t      | 10    | d3d9446802a44259755d t      | 23    | 37693cfc748049e45d87 f      | 33    | 182be0c5cdcd5072bb18(3 rows)Time: 0.727 ms

这次和原始查询速度一样快了。 总结

以上是内存溢出为你收集整理的PostgreSQL hstore 列性能提升一例全部内容，希望文章能够帮你解决PostgreSQL hstore 列性能提升一例所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。