(PostgreSQL) BRIN 인덱스 활용하기

BRIN 인덱스

BRIN 인덱스는 Block Range Index의 약자다. 

BRIN 인덱스는 페이지의 메타데이터를 뽑아서 인덱스를 구성한다. 그래서 타임시퀀스한 대용량 데이터를 저장하고, 조회할 때 유용하다. 

테이블은 여러개의 페이지들로 구성되어 있다. 

비슷한 시기에 만들어진 로우는 같은 페이지에 위치하거나, 물리적으로 서로 근접한 위치에 있다. 

BRIN VS B-TREE 

BRIN 인덱스는 B-TREE 인덱스보다 쿼리 퍼포먼스가 좋다.

그리고 BRIN 인덱스는, B-TREE에서 사용하는 용량의 1%만 사용한다. bb

인덱스 생성 속도도 BRIN이 더 빠르다.

쿼리 퍼포펀스 비교해보기

대용량 테이블을 만들고, BRIN / B-TREE 인덱스를 추가해서 각각의 퍼포먼스와 디스크 사용량을 비교해보겠다. 

1. 샘플데이터 만들기

CREATE UNLOGGED TABLE scans (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    scan float NOT NULL,
    created_at timestamptz NOT NULL
);

INSERT INTO scans (scan, created_at)
SELECT random(), x
FROM generate_series('2012-01-01 0:00'::timestamptz,
    '2018-05-03 20:33:20'::timestamptz, '2 seconds'::interval) x;

SELECT count(*) FROM scans;

   count   
-----------
 100000001

2-A. 인덱스 X + 병렬처리 X

인덱스도 없고, 병렬처리도 하지 않는 경우, 데이터를 조회하는 데에 1600ms 정도 걸린다. 

SET max_parallel_workers = 0;

EXPLAIN ANALYZE SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;

Finalize GroupAggregate  (cost=128919.29..294297.77 rows=1147587 width=16) (actual time=1335.080..1587.770 rows=28 loops=1)
    Group Key: (date_trunc('day'::text, created_at))
    ->  Gather Merge  (cost=128919.29..271346.02 rows=1147588 width=40) (actual time=1326.056..1587.715 rows=28 loops=1)
        Workers Planned: 4
        Workers Launched: 0
        ->  Partial GroupAggregate  (cost=127919.23..133657.17 rows=286897 width=40) (actual time=1325.865..1587.464 rows=28 loops=1)
            Group Key: (date_trunc('day'::text, created_at))
            ->  Sort  (cost=127919.23..128636.47 rows=286897 width=16) (actual time=1316.989..1443.944 rows=1188000 loops=1)
                Sort Key: (date_trunc('day'::text, created_at))
                Sort Method: external merge  Disk: 30232kB
                ->  Parallel Seq Scan on scans  (cost=0.00..101911.77 rows=286897 width=16) (actual time=88.985..1026.441 rows=1188000 loops=1)
                    Filter: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
                    Rows Removed by Filter: 8812001
Planning Time: 0.098 ms
Execution Time: 1595.763
 ms

2-B. 인덱스 X + 병렬처리 O 

워커 8개로 설정한 후 쿼리를 날려보면, 응답시간이 500ms 이내로 개선된다. 워커가 일을 나눠가지면서, 워커가 각각 메모리를 사용해서 값을 구한다. 

SET max_parallel_workers = 8;

EXPLAIN ANALYZE SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;

Finalize GroupAggregate  (cost=131287.23..310221.83 rows=1241656 width=16) (actual time=396.036..453.017 rows=28 loops=1)
    Group Key: (date_trunc('day'::text, created_at))
    ->  Gather Merge  (cost=131287.23..285388.71 rows=1241656 width=40) (actual time=392.398..458.091 rows=140 loops=1)
        Workers Planned: 4
        Workers Launched: 4
        ->  Partial GroupAggregate  (cost=130287.17..136495.45 rows=310414 width=40) (actual time=385.556..435.999 rows=28 loops=5)
            Group Key: (date_trunc('day'::text, created_at))
            ->  Sort  (cost=130287.17..131063.21 rows=310414 width=16) (actual time=383.855..401.129 rows=237600 loops=5)
                Sort Key: (date_trunc('day'::text, created_at))
                Sort Method: quicksort  Memory: 24859kB
                Worker 0:  Sort Method: quicksort  Memory: 16919kB
                Worker 1:  Sort Method: quicksort  Memory: 16852kB
                Worker 2:  Sort Method: quicksort  Memory: 17094kB
                Worker 3:  Sort Method: quicksort  Memory: 16830kB
                ->  Parallel Seq Scan on scans  (cost=0.00..101971.47 rows=310414 width=16) (actual time=31.678..334.686 rows=237600 loops=5)
                    Filter: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
                    Rows Removed by Filter: 1762400
Planning Time: 0.128 ms
Execution Time: 459.501 ms

2-C. B-TREE INDEX + 병렬처리 X

BTREE INDEX를 추가하면 1100ms 정도 소요된다. 인덱스가 없던 때에 비하면, 응답시간이 30% 감축했다. 그러나 인덱스 용량이 2GB 정도 된다. 

\timing
CREATE INDEX scans_created_at_idx ON scans (created_at);
Time: 34434.702 ms (00:34.435)

VACUUM FREEZE ANALYZE;

EXPLAIN ANALYZE SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;

GroupAggregate  (cost=175102.37..203032.61 rows=1241344 width=16) (actual time=854.269..1118.307 rows=28 loops=1)
    Group Key: (date_trunc('day'::text, created_at))
    ->  Sort  (cost=175102.37..178205.73 rows=1241344 width=16) (actual time=845.117..971.251 rows=1188000 loops=1)
        Sort Key: (date_trunc('day'::text, created_at))
        Sort Method: external merge  Disk: 30232kB
        ->  Index Scan using scans_created_at_idx on scans  (cost=0.57..49456.81 rows=1241344 width=16) (actual time=0.020..531.775 rows=1188000 loops=1)
            Index Cond: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Planning Time: 0.094 ms
Execution Time: 1126.357 ms

SELECT pg_size_pretty(pg_relation_size('scans_created_at_idx'));

 pg_size_pretty 
----------------
 2142 MB

2-D. BRIN INDEX + 병렬처리 X

BRIN INDEX을 추가하고, 데이터를 조회하면 900ms 정도 소요된다. 쿼리 조회 응답시간은 B-TREE INDEX보다 15%정도 더 빠르다. 

게다가 인덱스 용량은 184kb이다. B-TREE 인덱스 사용량에 비하면.. 인덱스를 거의 안쓰는 수준이다.

DROP INDEX scans_created_at_idx;

CREATE INDEX scans_created_at_brin_idx ON scans USING brin(created_at);
Time: 18396.309 ms (00:18.396)

VACUUM FREEZE ANALYZE;

EXPLAIN ANALYZE SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;

GroupAggregate  (cost=785242.87..810191.48 rows=1108827 width=16) (actual time=703.571..968.501 rows=28 loops=1)
    Group Key: (date_trunc('day'::text, created_at))
    ->  Sort  (cost=785242.87..788014.94 rows=1108827 width=16) (actual time=693.621..821.642 rows=1188000 loops=1)
        Sort Key: (date_trunc('day'::text, created_at))
        Sort Method: external merge  Disk: 30232kB
        ->  Bitmap Heap Scan on scans  (cost=362.00..673913.30 rows=1108827 width=16) (actual time=1.424..416.475 rows=1188000 loops=1)
            Recheck Cond: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
            Rows Removed by Index Recheck: 17760
            Heap Blocks: lossy=7680
            ->  Bitmap Index Scan on scans_created_at_brin_idx  (cost=0.00..84.79 rows=1125176 width=0) (actual time=1.146..1.146 rows=76800 loops=1)
                Index Cond: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Planning Time: 0.111 ms
Execution Time: 975.262 ms

SELECT pg_size_pretty(pg_relation_size('scans_created_at_brin_idx'));

 pg_size_pretty 
----------------
 184 kB

참고

PostgreSQL BRIN Indexes: Big Data Performance With Minimal Storage

generate_series - 숫자 증분시키는 PostgreSQL 함수

UNLOGGED - 대량의 데이터를 빠르게 insert할 때 사용하는 테이블 유형. 아카이브 화일을 만들지 않아서, 속도가 빠르다. 그러나 서버가 비정상적으로 종료되는 경우, 데이터가 유실될 수 있다는 리스크가 있다. 

Key metrics for PostgreSQL monitoring

비트나인 블로그 - PostgreSQL 9.5의 새로운 기능: BRIN 인덱스