PostgreSQL GIN 单列聚集索引应用

digoal 2017-02-21

640

TAG 15 , TAG 18

作者

digoal

日期

2017-02-21

背景

聚集存储比较好理解，数据按照聚集KEY存储在一个或相邻的数据块中，对聚集KEY的轨迹、行为数据检索可以大幅减少IO。

那么聚集索引呢？我们知道通常BTREE索引中存储的是KEY以及对应数据的堆表行号，每条记录一个索引条目。

而GIN索引也是树结构，只不过它对于单个KEY只存储一个条目，所有行号会存储到一个posting list或者posting tree中。

因此这样的场景能体现GIN的几个优势

1. 对于带重复KEY的轨迹、行为数据，建立GIN索引的话，可以节约一些空间。

2. 在使用GIN索引扫描单个KEY时，如果要输出所有的轨迹数据，也能获得非常好的查询效率。

我们使用这里同样的例子，来测试一下就知道了

《PostgreSQL 聚集存储与 BRIN索引 - 高并发行为、轨迹类大吞吐数据查询场景解说》

正文

1. 构建离散存储测试数据，创建GIN索引

```
create unlogged table test(id int, info text, crt_time timestamp);

insert into test select generate_series(1,10000), md5(id::text), clock_timestamp() from generate_series(1,10000) t(id);

set maintenance_work_mem='32GB';

create index idx_test_id on test using gin (id);

\di+ idx_test_id

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test where id=1;
QUERY PLAN

Bitmap Heap Scan on public.test (cost=84.79..12541.74 rows=9767 width=45) (actual time=3.808..17.915 rows=10000 loops=1)
Output: id, info, crt_time
Recheck Cond: (test.id = 1)
Heap Blocks: exact=10000
Buffers: shared hit=10008
-> Bitmap Index Scan on idx_test_id (cost=0.00..82.35 rows=9767 width=0) (actual time=1.962..1.962 rows=10000 loops=1)
Index Cond: (test.id = 1)
Buffers: shared hit=8
Planning time: 0.092 ms
Execution time: 18.480 ms
(10 rows)
```

测试

```
$ vi test.sql

\set id random(1,10000)
select * from test where id=:id;

$ pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100000
```

2. 构建聚集存储测试数据，创建GIN索引

```
create unlogged table cluster_test_gin (like test);

insert into cluster_test_gin select * from test order by id;

set maintenance_work_mem ='32GB';

create index idx_cluster_test_gin_id on cluster_test_gin using gin (id);

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from cluster_test_gin where id=1;
QUERY PLAN

Bitmap Heap Scan on public.cluster_test_gin (cost=90.83..13732.45 rows=10714 width=45) (actual time=1.037..2.236 rows=10000 loops=1)
Output: id, info, crt_time
Recheck Cond: (cluster_test_gin.id = 1)
Heap Blocks: exact=94
Buffers: shared hit=100
-> Bitmap Index Scan on idx_cluster_test_gin_id (cost=0.00..88.16 rows=10714 width=0) (actual time=1.010..1.010 rows=10000 loops=1)
Index Cond: (cluster_test_gin.id = 1)
Buffers: shared hit=6
Planning time: 0.092 ms
Execution time: 2.791 ms
(10 rows)
```

测试

```
$ vi test.sql

\set id random(1,10000)
select * from cluster_test_gin where id=:id;

$ pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100000
```

测试结果

存储格式 | 按KEY查询轨迹 TPS | 输出吞吐 | CPU利用率 | 索引大小 | 表大小
---|---|---|---|---|---
离散存储 BTREE索引 | 2184 | 2184 万行/s | 99.8% | 2.1 GB | 7.3 GB
离散存储 GIN索引 | 1620 | 1620 万行/s | 99.8% | 391 MB | 7.3 GB
聚集存储 BTREE索引 | 4000 | 4000 万行/s | 99.8% | 2.1 GB | 7.3 GB
聚集存储 GIN索引 | 3770 | 3770 万行/s | 99.8% | 391 MB | 7.3 GB
聚集存储 BRIN索引 | 2255 | 2255 万行/s | 99.8% | 232 KB | 7.3 GB
行列变换 array | 850 | 850 行/s | 99.8% | 248 KB | 4.5 GB
行列变换 jsonb | 1650 | 1650 行/s | 99.8% | 248 KB | 4.5 GB