BRINインデックス

DB

 

(5.6)
調べた限りない模様

 

Exadata Storage Index

 

(9.6)

https://www.techscore.com/blog/2016/01/12/postgresql9-5-brin/
https://www.sraoss.co.jp/technology/postgresql/images/20160112_PostgreSQL9.5_report_sraoss.pdf


-- 1.データ準備
drop table tab1 cascade;

create table tab1 (
col1 int
, col2 timestamp
, col3 timestamp
);

alter table tab1 add constraint tab1pk primary key(col1);


insert into tab1
select
a
, '2020-01-01'::timestamp + ( (a + random() * 10)::int || 'sec')::interval
, ('2020-01-01'::timestamp + random() * ('2020-12-13'::timestamp - '2020-01-01'::timestamp) )::timestamp(0)
from generate_series(1,30000000) a
;

select * from tab1 order by col1 limit 30;
select * from tab1 order by col1 desc limit 30;

 

-- 2.B-treeインデックスとの比較

drop index ind1;
drop index ind2;

create index ind1 on tab1 using brin (col2);
create index ind2 on tab1 using brin (col3);

create index ind1 on tab1 using btree (col2);
create index ind2 on tab1 using btree (col3);

analyze tab1;


explain analyze
select * from tab1
where col2 = '2020-02-01 12:34:56'::timestamp
;

explain analyze
select * from tab1
where col2 between '2020-02-01 00:00:00'::timestamp and '2020-02-29 23:59:59'::timestamp
;

explain analyze
select * from tab1
where col3 = '2020-02-01 12:34:56'::timestamp
;

explain analyze
select * from tab1
where col3 between '2020-02-01 00:00:00'::timestamp and '2020-02-29 23:59:59'::timestamp
;

-- 3.測定結果

BRIN,整列データ,等価条件→2.234 ms
BRIN,整列データ,範囲条件→484.803 ms

BRIN,ランダムデータ,等価条件→20559.902 ms
BRIN,ランダムデータ,範囲条件→19033.671 ms

B-tree,整列データ,等価条件→0.043 ms
B-tree,整列データ,範囲条件→587.635 ms

B-tree,ランダムデータ,等価条件→0.031 ms
B-tree,ランダムデータ,範囲条件→19252.300 ms


→BRINは整列データで範囲条件の場合に、B-Treeよりやや改善

-- 4.BRINデータ構造確認

create extension pageinspect;

select * from brin_metapage_info(get_raw_page('ind1', 0));
select * from brin_revmap_data(get_raw_page('ind1', 1)) limit 10;
select * from brin_page_items(get_raw_page('ind1', 3), 'ind1');

select * from brin_metapage_info(get_raw_page('ind2', 0));
select * from brin_revmap_data(get_raw_page('ind2', 1)) limit 10;
select * from brin_page_items(get_raw_page('ind2', 3), 'ind1');

 

(2014)
調べた限りない模様