PostgreSQL - Index B-Tree VS BRIN

목차

1. 지원하는 Index 타입 확인
2. Sample Table 생성
3. 테스트 케이스 1 (인덱스 X, 병렬처리 X)
4. 테스트 케이스 2 (인덱스 X, 병렬처리 O)
5. 테스트 케이스 3 (B-Tree 인덱스, 병렬처리 O)
6. 테스트 케이스 4 (BRIN 인덱스, 병렬처리 O)
7. 테스트 케이스 5 (BRIN 인덱스, 병렬처리 O, pages_per_range 수정)

지원하는 Index 타입 확인

설치된 PostgreSQL에서 지원하는 Index 타입 확인하는 방법은 아래와 같다.

select * from pg_am;

Sample Table 생성

아래의 쿼리로 테스트를 진행할 샘플 테이블을 생성했다.
테스트용으로 대용량 데이터 쓰기를 위해서 Unlogged Table을 사용했다.

CREATE UNLOGGED TABLE test_demo (
    test_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    test_name char(32),
    test_value float NOT NULL,
    test_date timestamptz NOT NULL
);

generate_series을 사용해서 데이터를 생성했다.

INSERT INTO test_demo (test_name, test_value, test_date)
SELECT md5(random()::text), hashtext(random()::text), test_date
FROM generate_series('2023-01-01 0:00'::timestamptz, '2024-12-31 23:59:59'::timestamptz,'1 seconds'::interval) as test_date;

테스트 케이스 1 (인덱스 X, 병렬처리 X)

SET max_parallel_workers = 0;
SET max_parallel_maintenance_workers = 0;

EXPLAIN ANALYZE 
SELECT test_date, test_name, sum(test_value)
FROM test_demo
WHERE test_date >= '2024-01-01'
  and test_date <= '2024-01-31'
GROUP BY test_date, test_name
ORDER BY test_date, test_name;

인덱스도 없고, 병렬처리를 하지 않는 경우 24552.210 ms가 소요된다.

Finalize GroupAggregate  (cost=1149589.30..1172948.59 rows=187908 width=49) (actual time=24371.412..24535.105 rows=172800 loops=1)
"  Group Key: (date_trunc('day'::text, test_date)), test_name"
  ->  Gather Merge  (cost=1149589.30..1169425.31 rows=156590 width=49) (actual time=24371.408..24487.198 rows=172800 loops=1)
        Workers Planned: 2
        Workers Launched: 0
        ->  Partial GroupAggregate  (cost=1148589.28..1150350.92 rows=78295 width=49) (actual time=24371.244..24477.733 rows=172800 loops=1)
"              Group Key: (date_trunc('day'::text, test_date)), test_name"
              ->  Sort  (cost=1148589.28..1148785.02 rows=78295 width=49) (actual time=24371.236..24424.043 rows=172800 loops=1)
"                    Sort Key: (date_trunc('day'::text, test_date)), test_name"
                    Sort Method: external merge  Disk: 11160kB
                    ->  Parallel Seq Scan on test_demo  (cost=0.00..1139547.71 rows=78295 width=49) (actual time=494.892..24230.032 rows=172800 loops=1)
                          Filter: ((test_date >= '2024-01-30 00:00:00+00'::timestamp with time zone) AND (test_date <= '2024-01-31 23:59:59+00'::timestamp with time zone))
                          Rows Removed by Filter: 62985600
Planning Time: 0.129 ms
Execution Time: 24552.210 ms

테스트 케이스 2 (인덱스 X, 병렬처리 O)

SET max_parallel_workers = 8;

EXPLAIN ANALYZE
SELECT date_trunc('day', test_date), test_name, sum(test_value)
FROM test_demo
WHERE test_date >= '2024-01-30 00:00:00'
  and test_date <= '2024-01-31 23:59:59'
GROUP BY 1, 2
ORDER BY 1, 2;

인덱스는 없지만, 병렬처리를 하는 경우 7278.143 ms가 소요된다.

Finalize GroupAggregate  (cost=1149589.30..1172948.59 rows=187908 width=49) (actual time=7123.454..7263.586 rows=172800 loops=1)
"  Group Key: (date_trunc('day'::text, test_date)), test_name"
  ->  Gather Merge  (cost=1149589.30..1169425.31 rows=156590 width=49) (actual time=7123.449..7221.229 rows=172800 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate  (cost=1148589.28..1150350.92 rows=78295 width=49) (actual time=7009.714..7040.742 rows=57600 loops=3)
"              Group Key: (date_trunc('day'::text, test_date)), test_name"
              ->  Sort  (cost=1148589.28..1148785.02 rows=78295 width=49) (actual time=7009.705..7023.835 rows=57600 loops=3)
"                    Sort Key: (date_trunc('day'::text, test_date)), test_name"
                    Sort Method: external merge  Disk: 3216kB
                    Worker 0:  Sort Method: external merge  Disk: 3216kB
                    Worker 1:  Sort Method: external merge  Disk: 4752kB
                    ->  Parallel Seq Scan on test_demo  (cost=0.00..1139547.71 rows=78295 width=49) (actual time=229.448..6945.629 rows=57600 loops=3)
                          Filter: ((test_date >= '2024-01-30 00:00:00+00'::timestamp with time zone) AND (test_date <= '2024-01-31 23:59:59+00'::timestamp with time zone))
                          Rows Removed by Filter: 20995200
Planning Time: 0.135 ms
Execution Time: 7278.143 ms

테스트 케이스 3 (B-Tree 인덱스, 병렬처리 O)

CREATE INDEX test_b_tree_idx ON test_demo (test_date, test_name);

인덱스를 생성하는데 completed in 1 m 20 s 254 ms만큼 소요되었다.

VACUUM FREEZE ANALYZE;
SELECT pg_size_pretty(pg_relation_size('test_b_tree_idx'));

인덱스 용량은 4059 MB정도 된다.

EXPLAIN ANALYZE
SELECT date_trunc('day', test_date), test_name, sum(test_value)
FROM test_demo
WHERE test_date >= '2024-01-30 00:00:00'
  and test_date <= '2024-01-31 23:59:59'
GROUP BY 1, 2
ORDER BY 1, 2;

B-Tree 인덱스를 추가하면 324.670 ms 정도 소요된다.

GroupAggregate  (cost=310448.23..314446.90 rows=177719 width=49) (actual time=229.743..318.625 rows=172800 loops=1)
"  Group Key: (date_trunc('day'::text, test_date)), test_name"
  ->  Sort  (cost=310448.23..310892.52 rows=177719 width=49) (actual time=229.736..262.584 rows=172800 loops=1)
"        Sort Key: (date_trunc('day'::text, test_date)), test_name"
        Sort Method: external merge  Disk: 11160kB
        ->  Index Scan using test_b_tree_idx on test_demo  (cost=0.56..288875.80 rows=177719 width=49) (actual time=2.495..83.365 rows=172800 loops=1)
              Index Cond: ((test_date >= '2024-01-30 00:00:00+00'::timestamp with time zone) AND (test_date <= '2024-01-31 23:59:59+00'::timestamp with time zone))
Planning Time: 12.301 ms
Execution Time: 324.670 ms

테스트 케이스 4 (BRIN 인덱스, 병렬처리 O)

CREATE INDEX test_brin_idx ON test_demo USING brin(test_date, test_name);

인덱스를 생성하는데 completed in 22 s 709 ms만큼 소요되었다.

VACUUM FREEZE ANALYZE;
SELECT pg_size_pretty(pg_relation_size('test_brin_idx'));

인덱스 용량은 560 kB정도 된다.

EXPLAIN ANALYZE
SELECT date_trunc('day', test_date), test_name, sum(test_value)
FROM test_demo
WHERE test_date >= '2024-01-30 00:00:00'
  and test_date <= '2024-01-31 23:59:59'
GROUP BY 1, 2
ORDER BY 1, 2;

BRIN 인덱스를 추가하면 293.568 ms 정도 소요된다.

Finalize GroupAggregate  (cost=729620.32..750195.10 rows=165508 width=49) (actual time=154.738..287.291 rows=172800 loops=1)
"  Group Key: (date_trunc('day'::text, test_date)), test_name"
  ->  Gather Merge  (cost=729620.32..747091.82 rows=137924 width=49) (actual time=154.731..241.458 rows=172800 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate  (cost=728620.30..730171.94 rows=68962 width=49) (actual time=73.483..99.565 rows=57600 loops=3)
"              Group Key: (date_trunc('day'::text, test_date)), test_name"
              ->  Sort  (cost=728620.30..728792.70 rows=68962 width=49) (actual time=73.474..82.143 rows=57600 loops=3)
"                    Sort Key: (date_trunc('day'::text, test_date)), test_name"
                    Sort Method: external merge  Disk: 7624kB
                    Worker 0:  Sort Method: quicksort  Memory: 587kB
                    Worker 1:  Sort Method: external merge  Disk: 3224kB
                    ->  Parallel Bitmap Heap Scan on test_demo  (cost=309.89..720718.99 rows=68962 width=49) (actual time=1.077..18.485 rows=57600 loops=3)
                          Recheck Cond: ((test_date >= '2024-01-30 00:00:00+00'::timestamp with time zone) AND (test_date <= '2024-01-31 23:59:59+00'::timestamp with time zone))
                          Rows Removed by Index Recheck: 4480
                          Heap Blocks: lossy=1309
                          ->  Bitmap Index Scan on test_brin_idx  (cost=0.00..268.51 rows=173620 width=0) (actual time=1.661..1.661 rows=19200 loops=1)
                                Index Cond: ((test_date >= '2024-01-30 00:00:00+00'::timestamp with time zone) AND (test_date <= '2024-01-31 23:59:59+00'::timestamp with time zone))
Planning Time: 0.159 ms
Execution Time: 293.568 ms

테스트 케이스 5 (BRIN 인덱스, 병렬처리 O, pages_per_range 수정)

pages_per_range은 기본값이 128이다.
이 값을 다양한 값으로 변경하여 테스트를 해보려고 한다.

처음에는 64로 변경을 해보았다.

CREATE INDEX test_brin_idx ON test_demo USING brin(test_date, test_name) WITH (pages_per_range=64);

VACUUM FREEZE ANALYZE;
SELECT pg_size_pretty(pg_relation_size('test_brin_idx'));

pages_per_range을 64로 변경할 경우 인덱스 용량은 1104 kB정도 된다.

64로 변경하였을때는 261.137 ms 정도 소요되었다. 요약된 데이터가 좀 더 정확해지고, 인덱스 스캔 중에 더 많은 데이터 블록을 건너 뛰어서 소폭 조회 속도가 빨라졌다.

Finalize GroupAggregate  (cost=772713.86..796606.30 rows=192197 width=49) (actual time=114.417..255.227 rows=172800 loops=1)
"  Group Key: (date_trunc('day'::text, test_date)), test_name"
  ->  Gather Merge  (cost=772713.86..793002.61 rows=160164 width=49) (actual time=114.409..209.847 rows=172800 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate  (cost=771713.84..773515.68 rows=80082 width=49) (actual time=66.951..91.961 rows=57600 loops=3)
"              Group Key: (date_trunc('day'::text, test_date)), test_name"
              ->  Sort  (cost=771713.84..771914.04 rows=80082 width=49) (actual time=66.944..74.350 rows=57600 loops=3)
"                    Sort Key: (date_trunc('day'::text, test_date)), test_name"
                    Sort Method: external merge  Disk: 5952kB
                    Worker 0:  Sort Method: external merge  Disk: 2616kB
                    Worker 1:  Sort Method: external merge  Disk: 2616kB
                    ->  Parallel Bitmap Heap Scan on test_demo  (cost=576.63..762450.98 rows=80082 width=49) (actual time=0.853..17.499 rows=57600 loops=3)
                          Recheck Cond: ((test_date >= '2024-01-30 00:00:00+00'::timestamp with time zone) AND (test_date <= '2024-01-31 23:59:59+00'::timestamp with time zone))
                          Rows Removed by Index Recheck: 2411
                          Heap Blocks: lossy=1024
                          ->  Bitmap Index Scan on test_brin_idx  (cost=0.00..528.58 rows=195539 width=0) (actual time=2.220..2.221 rows=18560 loops=1)
                                Index Cond: ((test_date >= '2024-01-30 00:00:00+00'::timestamp with time zone) AND (test_date <= '2024-01-31 23:59:59+00'::timestamp with time zone))
Planning Time: 0.108 ms
Execution Time: 261.137 ms

그 다음은 32로 변경을 해보았다.

CREATE INDEX test_brin_idx ON test_demo USING brin(test_date, test_name) WITH (pages_per_range=32);

VACUUM FREEZE ANALYZE;
SELECT pg_size_pretty(pg_relation_size('test_brin_idx'));

pages_per_range을 32 변경할 경우 인덱스 용량은 2200 kB정도 된다.

32로 변경하였을때는 382.216 ms 정도 소요되었다.
기본값보다 안좋은 결과가 나왔고, B-Tree보다 안좋은 결과가 나왔다.

Finalize GroupAggregate  (cost=762248.43..785662.02 rows=188345 width=49) (actual time=221.268..382.792 rows=172800 loops=1)
"  Group Key: (date_trunc('day'::text, test_date)), test_name"
  ->  Gather Merge  (cost=762248.43..782130.55 rows=156954 width=49) (actual time=221.256..329.125 rows=172800 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate  (cost=761248.41..763014.14 rows=78477 width=49) (actual time=86.887..117.966 rows=57600 loops=3)
"              Group Key: (date_trunc('day'::text, test_date)), test_name"
              ->  Sort  (cost=761248.41..761444.60 rows=78477 width=49) (actual time=86.880..98.141 rows=57600 loops=3)
"                    Sort Key: (date_trunc('day'::text, test_date)), test_name"
                    Sort Method: external merge  Disk: 7624kB
                    Worker 0:  Sort Method: external merge  Disk: 3552kB
                    Worker 1:  Sort Method: quicksort  Memory: 25kB
                    ->  Parallel Bitmap Heap Scan on test_demo  (cost=1096.65..752183.73 rows=78477 width=49) (actual time=1.662..22.401 rows=57600 loops=3)
                          Recheck Cond: ((test_date >= '2024-01-30 00:00:00+00'::timestamp with time zone) AND (test_date <= '2024-01-31 23:59:59+00'::timestamp with time zone))
                          Rows Removed by Index Recheck: 1376
                          Heap Blocks: lossy=1245
                          ->  Bitmap Index Scan on test_brin_idx  (cost=0.00..1049.56 rows=189810 width=0) (actual time=4.645..4.645 rows=18240 loops=1)
                                Index Cond: ((test_date >= '2024-01-30 00:00:00+00'::timestamp with time zone) AND (test_date <= '2024-01-31 23:59:59+00'::timestamp with time zone))
Planning Time: 13.381 ms
Execution Time: 389.999 ms

마지막으로 16으로 변경을 해보았다.

CREATE INDEX test_brin_idx ON test_demo USING brin(test_date, test_name) WITH (pages_per_range=16);

VACUUM FREEZE ANALYZE;
SELECT pg_size_pretty(pg_relation_size('test_brin_idx'));

pages_per_range을 64로 변경할 경우 인덱스 용량은 4368 kB정도 된다.
B-Tree 인덱스 용량보다 커졌다.

16으로 변경하였을때는 387.908 ms 정도 소요되었다.
역시나 32보다 안좋은 결과를 확인할 수 있었다.

Finalize GroupAggregate  (cost=705059.18..724934.03 rows=159878 width=49) (actual time=212.402..379.386 rows=172800 loops=1)
"  Group Key: (date_trunc('day'::text, test_date)), test_name"
  ->  Gather Merge  (cost=705059.18..721936.32 rows=133232 width=49) (actual time=212.398..324.537 rows=172800 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate  (cost=704059.16..705558.02 rows=66616 width=49) (actual time=112.612..145.726 rows=57600 loops=3)
"              Group Key: (date_trunc('day'::text, test_date)), test_name"
              ->  Sort  (cost=704059.16..704225.70 rows=66616 width=49) (actual time=112.600..122.132 rows=57600 loops=3)
"                    Sort Key: (date_trunc('day'::text, test_date)), test_name"
                    Sort Method: external merge  Disk: 6784kB
                    Worker 0:  Sort Method: quicksort  Memory: 1286kB
                    Worker 1:  Sort Method: external merge  Disk: 3640kB
                    ->  Parallel Bitmap Heap Scan on test_demo  (cost=2122.45..696443.52 rows=66616 width=49) (actual time=4.240..30.279 rows=57600 loops=3)
                          Recheck Cond: ((test_date >= '2024-01-30 00:00:00+00'::timestamp with time zone) AND (test_date <= '2024-01-31 23:59:59+00'::timestamp with time zone))
                          Rows Removed by Index Recheck: 859
                          Heap Blocks: lossy=1103
                          ->  Bitmap Index Scan on test_brin_idx  (cost=0.00..2082.48 rows=161176 width=0) (actual time=12.424..12.424 rows=18080 loops=1)
                                Index Cond: ((test_date >= '2024-01-30 00:00:00+00'::timestamp with time zone) AND (test_date <= '2024-01-31 23:59:59+00'::timestamp with time zone))
Planning Time: 0.184 ms
Execution Time: 387.908 ms

pages_per_range의 값을 작게 하는것은 인덱스 스캔 중에 더 많은 데이터 블록을 건너 뛰는것보다 페이지를 계속 읽는 과정에서 오버헤드가 발생해서 느려진것 같다.

---

Test 결과로는 BRIN 인덱스를 사용하는 것이 합리적이다.
B-Tree 인덱스보다 쿼리 조회 응답시간이 빠르고, 인덱스 용량이 훨씬 적기 때문이다.
하지만 실제 테이블 구성과, 환경 구성에 따라서 결과가 다를 수 있기 때문에 직접 테스트해보고 결정하는것이 좋을 것 같다.