PostgreSQL的hstore初步学习

发布时间：2020-12-13 17:13:36 所属栏目：百科来源：网络整理

导读：安装hstore扩展： postgres = # create extension hstore; CREATE EXTENSIONpostgres = # 进行测试：建表： create table hstore_test(item_id serial,data hstore);NOTICE: CREATE TABLE will create implicit sequence "hstore_test_item_id_seq" for ser

安装hstore扩展：

postgres=# create extension hstore;
CREATE EXTENSION
postgres=#

进行测试：

建表：

create table hstore_test(item_id serial,data hstore); NOTICE: CREATE TABLE will create implicit sequence "hstore_test_item_id_seq" for serial column "hstore_test.item_id" TABLE postgres 插入数据：

postgresINSERT INTO hstore_test (data) VALUES ('"key1"=>"value1","key2"=>"value2","key3"=>"value3"');
INSERT 0 1
postgresselect * from hstore_test;
 item_id |                         data                         
---------+------------------------------------------------------
       1 | "key1"=>"value1","key2"=>"value2","key3"=>"value3"
(1 row)

postgres=#

修改数据：

UPDATE hstore_test SET data = delete(data,key2') postgres-# ; UPDATE | data -------+------------------------------------

= data || "key4"=>"some value"'::hstore; | data -------+---------------------------------------------------------- =>"value3","key4"=>"some value" ( 按Key值查询：

(3 rows)

PostgreSQL 支持hstore 来存放KEY->VALUE这类数据，其实也类似于ARRAY或者JSON类型。要高效的使用这类数据，当然离不开高效的索引。我们今天就来看看两类不同的索引对于同一种检索请求的性能问题。

假如我们有这样一个原始表，基于str1字段有一个BTREE索引。

[sql] 
     view plain 
     copy 
      
      
     
t_girl=#dstatus_check;
Table"ytt.status_check"
Column|Type|Modifiers
--------+-----------------------+-----------
is_yes|boolean|notnull
str1|charactervarying(20)|null
str2|Indexes:
"index_status_check_str1"btree(str1)

里面有10W条记录。数据大概如下，

t_girl=#select*fromstatus_checklimit2;

is_yes|str1|str2
--------+------+----------------------
f|0|cfcd208495d565ef66e7
t|1|c4ca4238a0b923820dcc
(2rows)
Time:0.617ms
t_girl=#

存放hstore类型的status_check_hstore 表结构，基于str1_str2字段有一个GIST索引。

Table"ytt.status_check_hstore"

Column|Type|Modifiers
-----------+---------+-----------
is_yes|boolean|
str1_str2|hstore|
"idx_str_str2_gist"gist(str1_str2)

status_check_hstorelimit2;

is_yes|str1_str2
--------+-----------------------------
f|"0"=>"cfcd208495d565ef66e7"
t|"1"=>"c4ca4238a0b923820dcc"
Time:39.874ms

接下来我们要得到跟查询原始表一样的结果，当然原始表的查询非常高效。表语句以及结果如下，

status_check

where

str1

(

'10'

'23'

'33'

);

t|10|d3d9446802a44259755d
t|23|37693cfc748049e45d87
f|33|182be0c5cdcd5072bb18
(3rows)
Time:0.690ms

上面的语句用了不到1毫秒。
接下来我们对hstore表进行查询，

QUERYPLAN

-----------------------------------------------------------------------------------
BitmapHeapScanonstatus_check_hstore(cost=5.06..790.12rows=100000width=38)
RecheckCond:(str1_str2?|'{10,23,33}'::text[])
->BitmapIndexScanonidx_str_str2_gist(cost=0.00..5.03rows=100width=0)
IndexCond:(str1_str2?|'{10,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important">(4Time:0.688ms

我们想办法来优化这条语句，如果把这条语句变成跟原始语句一样的话，那么是否就可以用到BTREE索引了？
接下来，建立一个基于BTREE的函数索引，
t_girl=# create index idx_str1_str2_akeys on status_check_hstore using btree (array_to_string(akeys(str1_str2),',')); CREATE INDEX Time: 394.123 ms
OK,变化语句来执行下同样的检索，

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!