前 言
Oracle的位图索引适用于列的基数很少，可枚举，重复值很多，数据不会被经常更新的列。
它的索引结构是一个键值对应很多行(rowid)，对于报表类数据库，重复率高的数据，特定类型的查询例如count、or、and等逻辑操作，只需要进行位运算即可得到我们需要的结果，可以说是相当的效率。
最近项目在进行上云工作，有许多Oracle到PostgreSQL的迁移要做。其中涉及到位图索引，
然而PostgreSQL没有位图索引。怎么改造这种索引，来实现相应的索引场景呢？
今天我们来聊一聊PostgreSQL中的黑科技Brin索引。
索 引 原 理
BRIN索引是块级索引，有别于B-TREE等索引，BRIN记录并不是以行号为单位记录索引明细，而是记录每个数据块或者每段连续的数据块的统计信息。因此BRIN索引空间占用特别的小，对数据写入、更新、删除的影响也很小。
BRIN索引的扫描原理很简单，扫描BRIN的元数据，根据元数据和用户输入的条件进行比较，过滤不符合条件的HEAPPAGE，只扫描需要扫描的HEAPPAGE。
如果数据排列的比较随机时，那么索引效果就非常差。达不到索引快速扫描的效果。
我们创建两张表，一张顺序插入，一张乱序插入：
--顺序插入tab_brin1：
otter_pg=# create table tab_brin1(id int,name varchar(40),c_time timestamp); CREATE TABLE otter_pg=# insert into tab_brin1 select *,md5(random()::text),clock_timestamp() from generate_series(1,10000000); INSERT 0 10000000 |
--乱序插入tab_brin2：
otter_pg=# create table tab_brin2(id int,name varchar(40),c_time timestamp); CREATE TABLE tter_pg=# insert into tab_brin2 select (random()*(10^6))::integer,md5(random()::text),timestamp 2019-01-10 20:00:00 + random() * (timestamp 2019-01-20 20:00:00 - timestamp 2021-01-10 10:00:00) from generate_series(1,10000000); INSERT 0 10000000 |
--两张表都创建BRIN索引和BTREE索引
otter_pg=# create index idx1_tab_brin1 on tab_brin1 using brin(c_time); CREATE INDEX otter_pg=# create index idx1_tab_brin2 on tab_brin2 using brin(c_time); CREATE INDEX otter_pg=# create index idx2_tab_brin1 on tab_brin1 using btree(c_time); CREATE INDEX otter_pg=# create index idx2_tab_brin2 on tab_brin1 using btree(c_time); CREATE INDEX |
--我们看看索引大小
可以看到表为700M，BTREE索引需要214M，而BRIN索引只有40K。
otter_pg=# select pg_size_pretty(pg_relation_size(tab_brin1)); pg_size_pretty ---------------- 730 MB otter_pg=# select pg_size_pretty(pg_relation_size(idx1_tab_brin1)); pg_size_pretty ---------------- 40 kB otter_pg=# select pg_size_pretty(pg_relation_size(idx2_tab_brin1)); pg_size_pretty ---------------- 214 MB |
--我们在来看看BRIN索引的使用。
首先看看两表的离散度,如下可以看出tab_brin1表的逻辑顺序和物理顺序一致性更好些。
otter_pg=# select correlation from pg_stats where tablename=tab_brin1; correlation --------------------- 1 0.0048282277 1 otter_pg=# select correlation from pg_stats where tablename=tab_brin2; correlation --------------------- 0.0010042704 -0.002086642 0.006167772 |
对比下使用两表BRIN索引时的效率，这里我们需要删除前面创建的BTREE索引。
--tab_brin1的执行计划如下：可以看到耗时0.6ms。
--tab_brin2的执行计划如下：可以看到耗时21ms。
经过分析，物理顺序和逻辑顺序越一致，该列更适合建立BRIN索引。
BRIN索引有一个参数pages_per_range可以用来近一步提升Brin索引的性能。
pages_per_range是粒度，默认为128(表示每128个数据块统计一次边界)，它影响BRIN索引的精确度和 BRIN索引的大小。
--精度为1时，耗时46.6ms
otter_pg=# create index idx1_tab_brin1 on tab_brin1 using brin(c_time) with (pages_per_range=1); CREATE INDEX otter_pg=# otter_pg=# explain (analyze ,verbose,timing,costs,buffers) select * from tab_brin1 where c_time between 2019-01-10 20:00:00 and 2020-01-10 20:00:00; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----- Bitmap Heap Scan on public.tab_brin1 (cost=424.40..543.34 rows=1 width=45) (actual time=46.544..46.544 rows=0 loops=1) Output: id, name, c_time Recheck Cond: ((tab_brin1.c_time >= 2019-01-10 20:00:00::timestamp without time zone) AND (tab_brin1.c_time <= 2020-01-10 20:00:00::timestamp without time zone)) Buffers: shared hit=527 -> Bitmap Index Scan on idx1_tab_brin1 (cost=0.00..424.40 rows=107 width=0) (actual time=46.536..46.536 rows=0 loops=1) Index Cond: ((tab_brin1.c_time >= 2019-01-10 20:00:00::timestamp without time zone) AND (tab_brin1.c_time <= 2020-01-10 20:00:00::timestamp without time zo ne)) Buffers: shared hit=527 Planning Time: 0.632 ms Execution Time: 46.639 ms (9 rows) |
--精度为50时，耗时1.18ms
otter_pg=# create index idx1_tab_brin1 on tab_brin1 using brin(c_time) with (pages_per_range=50); CREATE INDEX otter_pg=# explain (analyze ,verbose,timing,costs,buffers) select * from tab_brin1 where c_time between 2019-01-10 20:00:00 and 2020-01-10 20:00:00; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----- Bitmap Heap Scan on public.tab_brin1 (cost=10.91..5688.47 rows=1 width=45) (actual time=1.115..1.115 rows=0 loops=1) Output: id, name, c_time Recheck Cond: ((tab_brin1.c_time >= 2019-01-10 20:00:00::timestamp without time zone) AND (tab_brin1.c_time <= 2020-01-10 20:00:00::timestamp without time zone)) Buffers: shared hit=11 -> Bitmap Index Scan on idx1_tab_brin1 (cost=0.00..10.91 rows=5348 width=0) (actual time=1.105..1.105 rows=0 loops=1) Index Cond: ((tab_brin1.c_time >= 2019-01-10 20:00:00::timestamp without time zone) AND (tab_brin1.c_time <= 2020-01-10 20:00:00::timestamp without time zo ne)) Buffers: shared hit=11 Planning Time: 0.566 ms Execution Time: 1.186 ms (9 rows) |
pages_per_range定义数据块的数量，为BRIN索引的每条记录统计的数据块范围。默认值为128。
如果这个值很大，则索引就会很小，索引扫描就会很迅速，但是后续内存中的Recheck就会很多，因为把大量的不相关数据拉到内存中了。
如果这个值很小，索引的过滤性越好，但索引也会越大。由于每筛选一次字段PostgreSQL 都要扫描全部的BRIN索引，所花费的时间也会变长，因此需要根据表的大小与应用场景去调整其值的大小。
BRIN主要适用于类似时序数据之类的，有着天然的顺序，而且都是添加写的场景。相比于BTREE索引，它的体积小得多，非常适用于大数据量的场景。
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/130048.html
摘要：类型说明根据中的说明，数据类型是用来存储数据的。它们几乎接受完全相同的值集合作为输入。该结构是非强制的，但是有一个可预测的结构会使集合的查询更容易。如中，表示在和这两个位置出现过，在中这些位置实际上就是元组的行号，包括数据块以及。 json 类型 说明 根据RFC 7159中的说明，JSON 数据类型是用来存储 JSON（JavaScript Object Notation）数据的。这...
摘要：类型说明根据中的说明，数据类型是用来存储数据的。它们几乎接受完全相同的值集合作为输入。该结构是非强制的，但是有一个可预测的结构会使集合的查询更容易。如中，表示在和这两个位置出现过，在中这些位置实际上就是元组的行号，包括数据块以及。 json 类型 说明 根据RFC 7159中的说明，JSON 数据类型是用来存储 JSON（JavaScript Object Notation）数据的。这...
摘要：作者谭峰张文升出版日期年月页数页定价元本书特色中国开源软件推进联盟分会特聘专家撰写，国内多位开源数据库专家鼎力推荐。张文升中国开源软件推进联盟分会核心成员之一。 很高兴《PostgreSQL实战》一书终于出版，本书大体上系统总结了笔者 PostgreSQL DBA 职业生涯的经验总结，本书的另一位作者张文升拥有丰富的PostgreSQL运维经验，目前就职于探探科技任首席PostgreS...
阅读 1249·2023-01-11 13:20
阅读 1557·2023-01-11 13:20
阅读 1011·2023-01-11 13:20
阅读 1680·2023-01-11 13:20
阅读 3971·2023-01-11 13:20
阅读 2518·2023-01-11 13:20
阅读 1310·2023-01-11 13:20
阅读 3485·2023-01-11 13:20
