postgres的brin索引

最新推荐文章于 2025-04-28 15:27:03 发布

文档搬运工

最新推荐文章于 2025-04-28 15:27:03 发布

阅读量333

点赞数

分类专栏： Postgres SQL

本文链接：https://blog.csdn.net/xxzhaobb/article/details/119210699

版权

Postgres SQL 专栏收录该内容

39 篇文章

订阅专栏

参考文档：

65.1. 简介 (postgres.cn)

BRIN表示块范围索引。 BRIN是为处理这样的表而设计的：表的规模非常大，并且其中某些列与它们在表中的物理位置存在某种自然关联。一个 块范围是一组在表中物理上相邻的页面，对于每一个块范围在索引中存储了一些摘要信息。例如，一个存储商店销售订单的表可能有一个日期列记录每个订单产生的时间，并且很多时候较早的订单项也将出现在表中较早的地方。一个存储 ZIP 代码列的表中一个城市的所有代码可能自然地聚在一起。

如果索引中存储的摘要信息与查询条件一致，BRIN 索引可以通过常规的位图索引扫描满足查询，并且将会返回每个范围中所有页面中的所有元组。查询执行器负责再次检查这些元组并且抛弃掉那些不匹配查询条件的元组 — 换句话说，这些索引是有损的。由于一个BRIN 索引很小，扫描这种索引虽然比使用顺序扫描多出了一点点开销，但是可能会避免扫描表中很多已知不包含匹配元组的部分。

一个BRIN索引将存储的特定数据以及该索引将能满足的特定查询，都依赖于为该索引的每一列所选择的操作符类。具有一种线性排序顺序的数据类型的操作符类可以存储在每个块范围内的最小和最大值，例如几何类型可能会存储在块范围内的所有对象的外包盒。

块范围的尺寸在索引创建时由pages_per_range存储参数决定。索引项的数量将等于该关系的尺寸（以页面计）除以为 pages_per_range选择的值。因此，该值越小，索引会变得越大（因为需要存储更多索引项），但是与此同时存储的摘要数据可以更加精确并且在索引扫描期间可以跳过更多数据块。

--创建测试用的表，插入100W行数据

create table t1 (id int);
insert into t1 select generate_series(1,1000000);
analyze t1;

repdb=# create table t1 (id int);
CREATE TABLE
repdb=# insert into t1 select generate_series(1,1000000);
INSERT 0 1000000
repdb=# 
repdb=# analyze t1;
ANALYZE
repdb=#

-- 测试查询

explain(analyze,verbose,costs,timing) select * from t1 where id > 5000 and id < 100000;

-- 不使用索引，查看执行计划,149ms ，执行计划中是seq scan

repdb=# drop index idx_t1_id;
DROP INDEX
repdb=# explain(analyze,verbose,costs,timing) select * from t1 where id > 5000 and id < 100000;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on public.t1  (cost=0.00..19425.00 rows=96561 width=4) (actual time=0.789..142.435 rows=94999 loops=1)
   Output: id
   Filter: ((t1.id > 5000) AND (t1.id < 100000))
   Rows Removed by Filter: 905001
 Planning time: 0.216 ms
 Execution time: 149.917 ms
(6 rows)

repdb=#

-- 使用btee索引，查看执行计划，执行时间38ms，执行计划中使用了index scan

create index idx_t1_id on t1(id);

repdb=# create index idx_t1_id on t1(id);
CREATE INDEX
repdb=# explain(analyze,verbose,costs,timing) select * from t1 where id > 5000 and id < 100000;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_t1_id on public.t1  (cost=0.42..3426.53 rows=96555 width=4) (actual time=0.021..31.600 rows=94999 loops=1)
   Output: id
   Index Cond: ((t1.id > 5000) AND (t1.id < 100000))
   Heap Fetches: 94999
 Planning time: 0.323 ms
 Execution time: 38.646 ms
(6 rows)

repdb=#

-- 使用brin索引，查看执行计划,执行时间32ms ,执行计划中使用了bitmap index scan

drop index idx_t1_id ;
create index idx_t1_id on t1 using brin(id);

repdb=# drop index idx_t1_id ;
DROP INDEX
repdb=# 

repdb=# explain(analyze,verbose,costs,timing) select * from t1 where id > 5000 and id < 100000;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.t1  (cost=36.27..6175.56 rows=96561 width=4) (actual time=0.978..25.214 rows=94999 loops=1)
   Output: id
   Recheck Cond: ((t1.id > 5000) AND (t1.id < 100000))
   Rows Removed by Index Recheck: 20713
   Heap Blocks: lossy=512
   ->  Bitmap Index Scan on idx_t1_id  (cost=0.00..12.13 rows=114286 width=0) (actual time=0.043..0.044 rows=5120 loops=1)
         Index Cond: ((t1.id > 5000) AND (t1.id < 100000))
 Planning time: 0.351 ms
 Execution time: 32.943 ms
(9 rows)

repdb=#

END