BRIN ( Block Range INdexes) 是 9.5 新的索引类型，相比 btree 索引占用空间更少，号称是对于非常巨大的表能提高性能，目前没有服务器测试巨大的表，依然在虚拟机下简单测试下 BRIN 索引。

环境准备

创建测试表,并插入数据

CREATE TABLE orders (  
id int,  
 order_date timestamptz,  
 item text);  
  
INSERT INTO orders (order_date, item)  
SELECT x,  'dfiojdso'    
FROM generate_series('2015-07-01 00:00:00',  '2015-08-01 00:00:00','2 seconds'::interval) a(x);  
  
fdb=>  select count(*)  from orders;  
 count   
---------  
1339201  
(1 row)

备注：这张表才133 万记录，不算大表。

创建 BTREE 索引

创建 BTREE 索引：花费 14 秒

1
2
3

fdb=> create index idx_orders_time on orders using btree (order_date);  
CREATE INDEX  
Time:  14084.416 ms

BTREE 索引大小: 29 MB

fdb=> /di+ idx_orders_time   
 List of relations  
Schema  | Name |  Type |  Owner  |  Table |  Size |  Description    
--------+-----------------+-------+-------+--------+-------+-------------  
fdb | idx_orders_time | index | fdb | orders |  29 MB |

Btree 索引扫描： 2.757 ms

fdb=> explain analyze select count(*)  from orders WHERE order_date BETWEEN '2015-07-11 00:00:00'  and  '2015-07-11 01:00:00';  
 QUERY PLAN   
  
---------------------------------------------------------------------------------------------------------------------------------  
Aggregate (cost=74.99..75.00 rows=1 width=0)  (actual time=1.866..1.866 rows=1 loops=1)  
 -> Index  Only  Scan  using idx_orders_time on orders (cost=0.43..70.49 rows=1803 width=0)  (actual time=0.037..1.213 rows=1801 loops=1)  
 Index  Cond:  ((order_date >=  '2015-07-11 00:00:00+08'::timestamp with time zone) AND (order_date <=  '2015-07-11 01:00:00+08'  
::timestamp with time zone))  
 Heap  Fetches:  1801  
Planning time:  0.308 ms  
Execution time:  1.908 ms  
(6 rows)  
  
Time:  2.757 ms

创建 BRIN索引

创建 BRIN 索引: 920 ms

1
2
3

fdb=> create index brin_orders_time on orders using BRIN (order_date);  
CREATE INDEX  
Time:  920.145 ms

BRIN 索引扫描： 7.816 ms ms

fdb=> explain analyze select count(*)  from orders WHERE order_date BETWEEN '2015-07-11 00:00:00'  and  '2015-07-11 01:00:00';  
 QUERY PLAN   
  
------------------------------------------------------------------------------------------------------------- 
Aggregate (cost=4334.24..4334.25 rows=1 width=0)  (actual time=6.948..6.949 rows=1 loops=1)  
 -> Bitmap  Heap  Scan on orders (cost=30.48..4329.74 rows=1803 width=0)  (actual time=4.761..6.295 rows=1801 loops=1)  
 Recheck  Cond:  ((order_date >=  '2015-07-11 00:00:00+08'::timestamp with time zone) AND (order_date <=  '2015-07-11 01:00:00+0  
8'::timestamp with time zone))  
 Rows  Removed  by  Index  Recheck:  19959  
 Heap  Blocks: lossy=128  
 -> Bitmap  Index  Scan on idx_orders_time (cost=0.00..30.03 rows=1803 width=0)  (actual time=0.090..0.090 rows=1280 loops=1)  
 Index  Cond:  ((order_date >=  '2015-07-11 00:00:00+08'::timestamp with time zone) AND (order_date <=  '2015-07-11 01:00:  
00+08'::timestamp with time zone))  
Planning time:  0.210 ms  
Execution time:  7.010 ms  
(9 rows)  
  
Time:  7.816 ms

BRIN 索引大小: 48 kB

fdb=> /di+ brin_orders_time   
 List of relations  
Schema  | Name |  Type |  Owner  |  Table |  Size |  Description    
--------+-----------------+-------+-------+--------+-------+-------------  
fdb | brin_orders_time | index | fdb | orders |  48 kB |    
(1 row)

BTREE、BRIN 索引测试统计

以下是在 orders 表，表记录数1339201，做的 Btree 和 BRIN 索引测试统计：

索引类型	索引创建时间	索引大小	时间范围扫描
Btree	14084 ms	29 MB	2.757 ms
BRIN	920 ms	48 kB	7.816 ms

备注：从这项测试来看， BRIN 索引比 Btree 索引小，创建时间也短，但在查询效率上没有 Btree 索引效率高，有条件建议在大表上再测试 BRIN 性能。

参考

原创文章，作者：kepupublish，如若转载，请注明出处：https://blog.ytso.com/tech/database/239643.html

PostgreSQL9.5：BRIN ( Block Range INdexes) 索引

环境准备

创建 BTREE 索引

创建 BRIN索引

BTREE、BRIN 索引测试统计

参考

发表回复

PostgreSQL9.5：BRIN ( Block Range INdexes) 索引

环境准备

创建 BTREE 索引

创建 BRIN索引

BTREE、BRIN 索引测试统计

参考

相关推荐

发表回复