Chinaunix首页 | 论坛 | 博客
  • 博客访问: 121422
  • 博文数量: 41
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 22
  • 用 户 组: 普通用户
  • 注册时间: 2013-01-25 16:01
文章分类
文章存档

2015年(6)

2014年(22)

2013年(13)

我的朋友

分类: Mysql/postgreSQL

2014-06-04 22:15:32

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. Time: 0.617 ms
  8. 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)


  8. t_girl=# select * from status_check_hstore limit 2;
  9.  is_yes | str1_str2
  10. --------+-----------------------------
  11.  f | "0"=>"cfcd208495d565ef66e7"
  12.  t | "1"=>"c4ca4238a0b923820dcc"
  13. (2 rows)


  14. 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. Time: 0.690 ms



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


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



点击(此处)折叠或打开

  1. t_girl=# select is_yes,skeys(str1_str2),svals(str1_str2) from status_check_hstore where str1_str2 ?| array['10','23','33'];
  2.  is_yes | skeys | svals
  3. --------+-------+----------------------
  4.  t | 10 | d3d9446802a44259755d
  5.  t | 23 | 37693cfc748049e45d87
  6.  f | 33 | 182be0c5cdcd5072bb18
  7. (3 rows)


  8. 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. Time: 0.688 ms





我们想办法来优化这条语句, 如果把这条语句变成跟原始语句一样的话,那么是否就可以用到BTREE索引了?
接下来,建立一个基于BTREE的函数索引,



点击(此处)折叠或打开

  1. t_girl=# create index idx_str1_str2_akeys on status_check_hstore using btree (array_to_string(akeys(str1_str2),','));
  2. CREATE INDEX
  3. Time: 394.123 ms



OK,变化语句来执行下同样的检索,

点击(此处)折叠或打开

  1. 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');
  2.  is_yes | skeys | svals
  3. --------+-------+----------------------
  4.  t | 10 | d3d9446802a44259755d
  5.  t | 23 | 37693cfc748049e45d87
  6.  f | 33 | 182be0c5cdcd5072bb18
  7. (3 rows)


  8. Time: 0.727 ms


这次和原始查询速度一样快了。
阅读(1240) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~