PostgreSQL的hstore初步学习

  1. 云栖社区>
  2. 博客>
  3. 正文

PostgreSQL的hstore初步学习

rudy_gao 2015-10-22 10:43:55 浏览648
展开阅读全文

安装hstore扩展:

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

 

进行测试:

建表:

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"
CREATE TABLE
postgres=# 

 

插入数据:

复制代码
postgres=# INSERT INTO hstore_test (data) VALUES ('"key1"=>"value1", "key2"=>"value2", "key3"=>"value3"');
INSERT 0 1
postgres=# select * from hstore_test;
 item_id |                         data                         
---------+------------------------------------------------------
       1 | "key1"=>"value1", "key2"=>"value2", "key3"=>"value3"
(1 row)

postgres=# 
复制代码

 

修改数据:

复制代码
postgres=# UPDATE hstore_test SET data = delete(data, 'key2')
postgres-# ;
UPDATE 1
postgres=# select * from hstore_test;
 item_id |                data                
---------+------------------------------------
       1 | "key1"=>"value1", "key3"=>"value3"
(1 row)

postgres=# 
复制代码

 

复制代码
postgres=# UPDATE hstore_test SET data = data || '"key4"=>"some value"'::hstore;
UPDATE 1
postgres=# select * from hstore_test;
 item_id |                           data                           
---------+----------------------------------------------------------
       1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)

postgres=# 
复制代码

 

按Key值查询:

复制代码
postgres=# SELECT * FROM hstore_test WHERE data ? 'key4';
 item_id |                           data                           
---------+----------------------------------------------------------
       1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)

postgres=# 
postgres=# SELECT * FROM hstore_test WHERE NOT data ? 'key5';
 item_id |                           data                           
---------+----------------------------------------------------------
       1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)

postgres=# SELECT * FROM hstore_test WHERE data @> '"key4"=>"some value"'::hstore;
 item_id |                           data                           
---------+----------------------------------------------------------
       1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)

postgres=# SELECT data -> 'key4' FROM hstore_test;
  ?column?  
------------
 some value
(1 row)

postgres=# SELECT item_id, (each(data)).* FROM hstore_test WHERE item_id = 2;
 item_id | key | value 
---------+-----+-------
(0 rows)

postgres=# SELECT item_id, (each(data)).* FROM hstore_test WHERE item_id = 1;
 item_id | key  |   value    
---------+------+------------
       1 | key1 | value1
       1 | key3 | value3
       1 | key4 | some value

(3 rows)

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


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


  1. t_girl=# \d status_check;  
  2.           Table "ytt.status_check"  
  3.  Column |         Type          | Modifiers   
  4. --------+-----------------------+-----------  
  5.  is_yes | boolean               | not null  
  6.  str1   | character varying(20) | not null  
  7.  str2   | character varying(20) | not null  
  8. Indexes:  
  9.     "index_status_check_str1" btree (str1)   



里面有10W条记录。 数据大概如下,
  1. t_girl=# select * from status_check limit 2;  
  2.  is_yes | str1 |         str2           
  3. --------+------+----------------------  
  4.  f      | 0    | cfcd208495d565ef66e7  
  5.  t      | 1    | c4ca4238a0b923820dcc  
  6. (2 rows)  
  7.   
  8.   
  9. Time: 0.617 ms  
  10. t_girl=#   




存放hstore类型的status_check_hstore 表结构,基于str1_str2字段有一个GIST索引。
  1.  Table "ytt.status_check_hstore"  
  2.   Column   |  Type   | Modifiers   
  3. -----------+---------+-----------  
  4.  is_yes    | boolean |   
  5.  str1_str2 | hstore  |   
  6. Indexes:  
  7.     "idx_str_str2_gist" gist (str1_str2)   



  1. t_girl=# select * from status_check_hstore limit 2;  
  2.  is_yes |          str1_str2            
  3. --------+-----------------------------  
  4.  f      | "0"=>"cfcd208495d565ef66e7"  
  5.  t      | "1"=>"c4ca4238a0b923820dcc"  
  6. (2 rows)  
  7.   
  8.   
  9. Time: 39.874 ms  




接下来我们要得到跟查询原始表一样的结果,当然原始表的查询非常高效。 表语句以及结果如下,
  1. t_girl=# select * from status_check where str1 in ('10','23','33');          
  2.  is_yes | str1 |         str2           
  3. --------+------+----------------------  
  4.  t      | 10   | d3d9446802a44259755d  
  5.  t      | 23   | 37693cfc748049e45d87  
  6.  f      | 33   | 182be0c5cdcd5072bb18  
  7. (3 rows)  
  8.   
  9.   
  10. Time: 0.690 ms  


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


接下来我们对hstore表进行查询,

=# 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

我的天,比原始表的查询慢了几十倍。


看下查询计划,把所有行都扫描了一遍。
  1.                                     QUERY PLAN                                       
  2. -----------------------------------------------------------------------------------  
  3.  Bitmap Heap Scan on status_check_hstore  (cost=5.06..790.12 rows=100000 width=38)  
  4.    Recheck Cond: (str1_str2 ?| '{10,23,33}'::text[])  
  5.    ->  Bitmap Index Scan on idx_str_str2_gist  (cost=0.00..5.03 rows=100 width=0)  
  6.          Index Cond: (str1_str2 ?| '{10,23,33}'::text[])  
  7. (4 rows)  
  8.   
  9.   
  10. 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 INDEX Time: 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

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

网友评论

登录后评论
0/500
评论
rudy_gao
+ 关注