PostgreSQL hstore 列性能提升一例

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

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

------------------------------------华丽丽的分割线------------------------------------

CentOS 6.3环境下yum安装PostgreSQL 9.3

PostgreSQL缓存详述

Windows平台编译 PostgreSQL

Ubuntu下LAPP(Linux+Apache+PostgreSQL+PHP)环境的配置与安装

Ubuntu上的phppgAdmin安装及配置

CentOS平台下安装PostgreSQL9.3

PostgreSQL配置Streaming Replication集群

------------------------------------华丽丽的分割线------------------------------------

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 null

Indexes:

"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 ms

t_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','23','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,23,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 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

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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/02a755af4dca91f9ab968ded44fa9365.html