Postgresql HStore 插件试用小结

一，     安装

环境介绍：官方说postgresql 9.3 版本之后支持HStore 插件，目前最新版本10.3

本次测试版本：10.1 或 9.6.2

进入psql 运行环境，使用管理员（高级别用户）进入服务器，连接某个应用数据库。

 

# \c 命令，数据库名 ， 高级别用户

 \c databasename postgres;

# 启用插件

create extension hstore;

# 切换普通用户

\c databasename ir_user;

# 察看Hstore 使用情况

\dT hstore;

二，     使用Hstore

新增列（Hstore）

alter table tbl_name add column column_name hstore;

给列赋值

update tbl_name set column_name =('exp=>' || exp || ', wealth=>' || wealth )::hstore;

查询

Select * from tbl_name where column_name ? 'exp’

修改

Select * from tbl_name where column_name || ‘exp=>new_value’::hstore

新增

Select * from tbl_name where column_name || ‘exp2=>value2’::hstore

删除

Select * from tbl_name where column_name - ‘exp2’

 

 

三，     性能测试

a)      数据准备，分拣平台2kw条子域名数据，列（20个）

数据表 1  ： select * from test.tmp_jackical_hstore2  limit 2;

数据表2  ： select * from test.bt_sub_domain limit 2;

                             

       测试sql1（hstore）    ： select * from test.tmp_jackical_hstore2 where domain_hstore2 ? 'zcrbjx.cn.china.cn';

       测试sql2       ：select * from test.bt_sub_domain where domain_name = 'zcrbjx.cn.china.cn';

 

       两个表都没有加索引     　语句1 ： 17.561 s

                                 　　　　语句2：  5.219  s

 

b)      追加索引

给 hstore 列加上 gist 索引

CREATE INDEX ***** ON test.tmp_jackical_hstore2 USING gist(domain_hstore2);

给 没有hstore 的表加 btree 索引 ：  

CREATE INDEX **** ON test.bt_sub_domain USING btree(domain_name );

 

再次测试      语句1 ：   7 s 左右

                  语句2 ：   0.122 s  

 

被正常数据秒杀了。。。

继续优化 .... 

 

 

c)      再次给Hstore查询条件加索引

给 hstore 列继续 加上 btree  索引 ：   CREATE INDEX ****  ON test.tmp_jackical_hstore2 USING btree(hstore_keys(domain_hstore2));

              到目前未知，己给hstore 这列加了，gist 与 btree索引 了，来看看性能吧。

             

测试语句1： select * from test.tmp_jackical_hstore2 where hstore_keys(domain_hstore2) = 'zcrbjx.cn.china.cn';

  测试语句2： select * from test.bt_sub_domain where domain_name = 'zcrbjx.cn.china.cn';

  再再次测试 语句1 ： 0.122s

                     语句2 ： 0.122s

 

 

结论：Hstore 提供key-value 保存形式，但性能不大好，需要加入有针对性查询的索引才能提高性能。

 

 附函数：hstore_keys

CREATE OR REPLACE FUNCTION "test"."hstore_keys"("field" hstore)
RETURNS "pg_catalog"."text" AS $BODY$
select array_to_string(akeys($1),',');
$BODY$
LANGUAGE 'sql' IMMUTABLE COST 100
;
ALTER FUNCTION "test"."hstore_keys"("field" hstore) OWNER TO "ir_user";

 

 

官方语法：https://www.postgresql.org/docs/9.6/static/hstore.html