PostgreSQL BRIN索引 日志型数据索引神器

作者 bluse wang 日期 2020-06-29
PostgreSQL BRIN索引 日志型数据索引神器

一张随着时间增长的表。当表的体积达到数GB或数十GB后。你会发现BTREE索引也也有数GB或数十GB;BTREE索引尺寸有时能达到表本身尺寸的70%左右!

当索引的尺寸接近或超过系统内存一半时。查询使用了索引,速度也会慢到令人无法忍受!

此时一般的想法是:分表!将表按不同时间段拆开。这样查询时就不需要扫描整个索引了!速度就上来了。

其实,此时PostgreSQL还有个更有意思的索引,简直是从根本上重新定义了这个问题!它就是:

BRIN索引

通过BRIN索引的官方介绍,得知,它的索引实现是完全另一种思路:

BRIN索引,是按写入磁盘的数据块,做索引的。它记录这张表,索引字段在这个数据块上的最大值与最小值;也叫这个字段的区间。

这就大大减少了索引的复杂度,提高了查询速度。

来具体体验一下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
cashier=> \dt+ log.table_name
关联列表
架构模式 | 名称 | 类型 | 拥有者 | 大小 | 描述
----------+------------+--------+----------+---------+---------
log | table_name | 数据表 | postgres | 6908 MB | gdb应用
(1 行记录)

cashier=> \di+ log.table_name_log_at_idx
关联列表
架构模式 | 名称 | 类型 | 拥有者 | 数据表 | 大小 | 描述
----------+-----------------------+------+----------+------------+--------+------
log | table_name_log_at_idx | 索引 | postgres | table_name | 272 kB |
(1 行记录)

cashier=> explain analyze select count(1) from log.table_name where log_at between '2020-01-01 12:00:00' and '2020-01-01 12:01:00';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=15547.84..15547.85 rows=1 width=8) (actual time=46.691..46.700 rows=1 loops=1)
-> Bitmap Heap Scan on table_name (cost=109.06..15547.55 rows=116 width=0) (actual time=3.063..46.153 rows=113 loops=1)
Recheck Cond: ((log_at >= '2020-01-01 12:00:00+08'::timestamp with time zone) AND (log_at <= '2020-01-01 12:01:00+08'::timestamp with time zone))
Rows Removed by Index Recheck: 124960
Heap Blocks: lossy=3848
-> Bitmap Index Scan on table_name_log_at_idx (cost=0.00..109.03 rows=4059 width=0) (actual time=1.384..1.389 rows=39680 loops=1)
Index Cond: ((log_at >= '2020-01-01 12:00:00+08'::timestamp with time zone) AND (log_at <= '2020-01-01 12:01:00+08'::timestamp with time zone))
Planning Time: 0.106 ms
Execution Time: 46.797 ms
(9 行记录)

cashier=> explain analyze select count(1) from log.table_name where log_at between '2020-01-01 12:00:00' and '2020-01-01 12:01:00' and body_size>100;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=15557.94..15557.95 rows=1 width=8) (actual time=49.396..49.405 rows=1 loops=1)
-> Bitmap Heap Scan on table_name (cost=109.06..15557.69 rows=97 width=0) (actual time=3.574..48.848 rows=101 loops=1)
Recheck Cond: ((log_at >= '2020-01-01 12:00:00+08'::timestamp with time zone) AND (log_at <= '2020-01-01 12:01:00+08'::timestamp with time zone))
Rows Removed by Index Recheck: 125418
Filter: (body_size > 100)
Rows Removed by Filter: 12
Heap Blocks: lossy=3862
-> Bitmap Index Scan on table_name_log_at_idx (cost=0.00..109.03 rows=4059 width=0) (actual time=2.176..2.180 rows=39680 loops=1)
Index Cond: ((log_at >= '2020-01-01 12:00:00+08'::timestamp with time zone) AND (log_at <= '2020-01-01 12:01:00+08'::timestamp with time zone))
Planning Time: 0.901 ms
Execution Time: 49.624 ms
(11 行记录)

可见:

  • 在一张6.9GB数据的表上,按创建时间建BRIN索引,建成后,索引只有272KB!
  • 在做中远距离的between查询。只要46ms
  • 带时间段的多条件查询,也只要49ms