— Oracle 블룸 필터 흉내 내기
공동 작성 | SweetBoss · GPT-6 Astra
•
아이디어·SQL·실험·최종 검수: SweetBoss
•
본문 초안·문장 구성·기술 검수: GPT-6 Astra
실습 준비: 이 글의 SQL을 직접 실습하려면 『SQL TUNER for PostgreSQL 기본원리편』에서 제공하는 실습 데이터베이스를 먼저 구성해야 합니다.
SQL에 적합한 인덱스를 추가할 수 없다면 어떻게 해야 할까? 여기서는 기존 인덱스를 그대로 사용하면서, 조인할 회원 목록을 미리 조건으로 전달해 성능을 개선하는 방법을 살펴보고자 한다. 이미 조인 조건이 있는데도 같은 회원을 선별하는 조건을 하나 더 넣는, 약간의 꼼수다.
테스트를 위한 인덱스
주문 테이블에 아래 인덱스만 있다고 가정하자. 이번 실험에서는 인덱스를 추가하거나 컬럼 순서를 변경할 수 없다.
CREATE INDEX x_tr_ord_big_ord_mbr
ON startdbpg.tr_ord_big(ord_dtm, mbr_id);
SQL
복사
NL 조인으로 회원 10명의 주문 조회
아래 SQL은 회원 10명의 2025년 1월 주문을 조회하여 회원별 주문금액을 집계한다. 주문 테이블은 앞서 생성한 인덱스를 사용하도록 힌트로 고정했다.
주문을 드리븐으로 처리하기에는 (mbr_id, ord_dtm) 인덱스가 적합하지만, 현재 인덱스의 선두 컬럼은 ord_dtm이다. 이 실험에서는 회원 한 명의 주문을 찾을 때마다 한 달 범위의 인덱스 리프를 읽고, mbr_id를 Index Filter로 사용해 해당 회원의 주문만 테이블에서 가져온다.
•
Buffers: shared hit=19,750 / 실행시간: 637.798ms
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT /*+ IndexScan(t2 x_tr_ord_big_ord_mbr) */
t2.mbr_id, SUM(t2.ord_amt) ord_amt_sum
FROM (
SELECT *
FROM startdbpg.ms_mbr_big a
ORDER BY a.mbr_id
LIMIT 10
) t1
INNER JOIN startdbpg.tr_ord_big t2
ON (t1.mbr_id = t2.mbr_id)
WHERE t2.ord_dtm >= '2025-01-01'
AND t2.ord_dtm < '2025-02-01'
GROUP BY t2.mbr_id;
SQL
복사
GroupAggregate (actual time=155.002..632.112 rows=6 loops=1)
Group Key: t2.mbr_id
Buffers: shared hit=19750
-> Nested Loop (actual time=75.525..631.658 rows=28 loops=1)
Buffers: shared hit=19750
-> Limit (actual time=43.069..43.160 rows=10 loops=1)
Buffers: shared hit=3
-> Index Only Scan using ms_mbr_big_pk on ms_mbr_big a (actual time=0.263..0.336 rows=10 loops=1)
Heap Fetches: 0
Buffers: shared hit=3
-> Index Scan using x_tr_ord_big_ord_mbr on tr_ord_big t2 (actual time=34.034..58.794 rows=3 loops=10)
Index Cond: ((ord_dtm >= '2025-01-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2025-02-01 00:00:00'::timestamp without time zone) AND ((mbr_id)::text = (a.mbr_id)::text))
Buffers: shared hit=19747
Planning:
Buffers: shared hit=10
Planning Time: 2.346 ms
Execution Time: 637.798 ms
Plain Text
복사
주문 Index Scan의 Index Cond에는 날짜와 회원 ID가 모두 포함되어 있다. 회원 조건으로 불필요한 테이블 접근은 줄이고 있지만, 회원마다 같은 날짜 범위의 인덱스를 다시 읽는다. 드리븐을 10번 반복한 결과 주문 측 shared hit는 19,747이다.
Hash Join으로 바꾸면?
적절한 드리븐 인덱스가 없으므로 Hash Join을 시도해 볼 수 있다. 회원별로 주문 인덱스를 반복해서 읽는 대신, 주문을 한 번 읽어 회원 10명과 조인하도록 바꾸는 것이다.
•
Buffers: shared hit=98,764 / 실행시간: 510.141ms
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT /*+ IndexScan(t2 x_tr_ord_big_ord_mbr) HashJoin(t1 t2) */
t2.mbr_id, SUM(t2.ord_amt) ord_amt_sum
FROM (
SELECT *
FROM startdbpg.ms_mbr_big a
ORDER BY a.mbr_id
LIMIT 10
) t1
INNER JOIN startdbpg.tr_ord_big t2
ON (t1.mbr_id = t2.mbr_id)
WHERE t2.ord_dtm >= '2025-01-01'
AND t2.ord_dtm < '2025-02-01'
GROUP BY t2.mbr_id;
SQL
복사
GroupAggregate (actual time=508.546..508.562 rows=6 loops=1)
Group Key: t2.mbr_id
Buffers: shared hit=98764
-> Sort (actual time=508.445..508.452 rows=28 loops=1)
Sort Key: t2.mbr_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=98764
-> Hash Join (actual time=316.116..508.337 rows=28 loops=1)
Hash Cond: ((t2.mbr_id)::text = (t1.mbr_id)::text)
Buffers: shared hit=98764
-> Index Scan using x_tr_ord_big_ord_mbr on tr_ord_big t2 (actual time=0.027..141.279 rows=514440 loops=1)
Index Cond: ((ord_dtm >= '2025-01-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2025-02-01 00:00:00'::timestamp without time zone))
Buffers: shared hit=98761
-> Hash (actual time=285.939..285.942 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=3
-> Subquery Scan on t1 (actual time=285.917..285.926 rows=10 loops=1)
Buffers: shared hit=3
-> Limit (actual time=285.912..285.918 rows=10 loops=1)
Buffers: shared hit=3
-> Index Only Scan using ms_mbr_big_pk on ms_mbr_big a (actual time=0.075..0.079 rows=10 loops=1)
Heap Fetches: 0
Buffers: shared hit=3
Planning:
Buffers: shared hit=10
Planning Time: 0.439 ms
Execution Time: 510.141 ms
Plain Text
복사
주문 Index Scan의 loops가 1로 줄었다. 하지만 Index Cond에서 회원 ID 조건이 사라지고 날짜 조건만 남았다. 그 결과 한 달치 주문 514,440건을 테이블에서 가져온 뒤, Hash Join에서 대상 회원의 주문 28건을 선별하여 집계한다.
반복 스캔을 없앤 대신 테이블 접근량이 증가하여, 전체 버퍼 접근량은 기존 NL의 약 5배가 되었다. 측정된 실행시간은 NL보다 짧지만, 읽는 양을 줄이려던 목적은 달성하지 못했다.
회원 목록을 미리 조건으로 전달하기
앞의 NL은 회원 조건으로 테이블 접근을 줄이지만 인덱스 스캔을 반복한다. Hash Join은 반복 스캔을 없애지만, 회원을 선별하기 전에 한 달치 주문을 테이블에서 가져온다.
그렇다면 회원 10명을 인덱스에서 한꺼번에 걸러내면서, 주문 스캔의 반복도 없앨 수 없을까?
회원 목록을 먼저 구해 주문의 조건으로 전달해 보자. 기존 조인은 그대로 두고, 동일한 회원 목록 조건을 추가한다.
•
Buffers: shared hit=2,005 / 실행시간: 80.979ms
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
WITH w01 AS MATERIALIZED (
SELECT string_agg(c.mbr_id, ',') AS mbr_list
FROM (
SELECT b.mbr_id
FROM startdbpg.ms_mbr_big b
ORDER BY b.mbr_id
LIMIT 10
) c
)
SELECT /*+ IndexScan(t2 x_tr_ord_big_ord_mbr) */
t2.mbr_id, SUM(t2.ord_amt) ord_amt_sum
FROM (
SELECT *
FROM startdbpg.ms_mbr_big a
ORDER BY a.mbr_id
LIMIT 10
) t1
INNER JOIN startdbpg.tr_ord_big t2
ON (t1.mbr_id = t2.mbr_id)
WHERE t2.ord_dtm >= '2025-01-01'
AND t2.ord_dtm < '2025-02-01'
AND t2.mbr_id = ANY(string_to_array((SELECT mbr_list FROM w01), ','))
GROUP BY t2.mbr_id;
SQL
복사
GroupAggregate (actual time=80.719..80.787 rows=6 loops=1)
Group Key: t2.mbr_id
Buffers: shared hit=2005
CTE w01
-> Aggregate (actual time=0.101..0.103 rows=1 loops=1)
Buffers: shared hit=3
-> Limit (actual time=0.078..0.086 rows=10 loops=1)
Buffers: shared hit=3
-> Index Only Scan using ms_mbr_big_pk on ms_mbr_big b (actual time=0.078..0.082 rows=10 loops=1)
Heap Fetches: 0
Buffers: shared hit=3
InitPlan 2
-> CTE Scan on w01 (actual time=0.105..0.106 rows=1 loops=1)
Buffers: shared hit=3
-> Nested Loop (actual time=27.100..80.735 rows=28 loops=1)
Join Filter: ((t2.mbr_id)::text = (a.mbr_id)::text)
Rows Removed by Join Filter: 252
Buffers: shared hit=2005
-> Limit (actual time=0.044..0.053 rows=10 loops=1)
Buffers: shared hit=3
-> Index Only Scan using ms_mbr_big_pk on ms_mbr_big a (actual time=0.042..0.048 rows=10 loops=1)
Heap Fetches: 0
Buffers: shared hit=3
-> Materialize (actual time=1.524..8.061 rows=28 loops=10)
Buffers: shared hit=2002
-> Index Scan using x_tr_ord_big_ord_mbr on tr_ord_big t2 (actual time=15.233..80.540 rows=28 loops=1)
Index Cond: ((ord_dtm >= '2025-01-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2025-02-01 00:00:00'::timestamp without time zone) AND ((mbr_id)::text = ANY (string_to_array((InitPlan 2).col1, ','::text))))
Buffers: shared hit=2002
Planning:
Buffers: shared hit=10
Planning Time: 0.985 ms
Execution Time: 80.979 ms
Plain Text
복사
회원 10명을 가져오는 데 필요한 버퍼 접근은 3회로 작다. 이 회원 ID를 string_agg로 묶고, string_to_array로 변환하여 주문의 ANY 조건에 전달해 보자. 기존 조인은 그대로 두고, 동일한 회원 목록 조건을 추가한다.
•
string_agg(c.mbr_id, ','): 여러 행의 회원 ID를 쉼표로 연결하여 'M001,M002,M003'과 같은 하나의 문자열로 만든다.
•
string_to_array(mbr_list, ','): 이 문자열을 쉼표 기준으로 나누어, 회원 ID 각각을 원소로 갖는 배열로 변환한다.
•
t2.mbr_id = ANY(배열): 주문의 회원 ID가 배열의 원소 중 하나와 같으면 조건을 만족한다.
여기서 IN (회원 조회 서브쿼리)로 바꾸면 같은 효과를 얻을 수 있을까? 이 형태는 일반적으로 세미 조인 등으로 변환되어 처리된다. 선택된 계획에 따라 다시 NL 방식으로 주문을 반복 조회하거나, Hash Join처럼 주문을 가져온 뒤 회원을 선별할 수 있다. 항상 NL로 처리되는 것은 아니지만, 회원 목록 전체를 하나의 배열 값으로 만들어 인덱스 조건에 전달하는 지금의 방식과는 다르다.
이 예제의 핵심은 = ANY(배열) 조건이 주문의 Index Cond에 들어가, 회원 10명을 인덱스 단계에서 한꺼번에 선별하도록 하는 것이다. IN (서브쿼리)가 실행계획의 Filter에 남는 경우에도, 그것만으로 이번과 같은 인덱스 단계의 필터링이 되는 것은 아니다.
추가한 조건은 아래와 같다.
AND t2.mbr_id = ANY(
string_to_array((SELECT mbr_list FROM w01), ',')
)
SQL
복사
이 조건이 주문의 Index Cond에 포함된 것을 볼 수 있다. 앞선 NL에서는 회원 ID를 한 명씩 전달했지만, 이제는 회원 10명의 목록을 미리 전달한다. 주문 스캔은 날짜 범위를 처리하면서 대상 회원의 주문을 한꺼번에 선별할 수 있다.
실제로 주문 Index Scan은 loops=1로 한 번 실행되어 28건을 반환했다. 회원 목록을 인덱스에서 검사하므로 Hash Join처럼 한 달치 주문 전체를 테이블에서 가져오지 않으며, 회원마다 주문 스캔을 재실행하지도 않는다.
NL 조인은 그대로 남아 있지만, 반복해서 읽는 대상이 달라졌다. 주문 Index Scan에서 얻은 28건을 Materialize에 보관하고 이를 10번 재사용한다. 주문 인덱스를 반복해서 읽는 작업이, 미리 선별한 작은 결과를 반복해서 읽는 작업으로 바뀐 것이다.
결과 비교
세 가지 실행계획을 비교하면 다음과 같다.
구분 | NL 조인 | Hash Join | 회원 목록 조건 추가 |
주문 Index Scan 실행 횟수 | 10회 | 1회 | 1회 |
주문 스캔에서 회원 필터링 | 회원 1명씩 | 없음 | 회원 10명 한꺼번에 |
전체 Buffers: shared hit | 19,750 | 98,764 | 2,005 |
실행시간 | 637.798ms | 510.141ms | 80.979ms |
회원 목록 조건을 추가하자 전체 버퍼 접근량이 19,750에서 2,005로 줄었다. 인덱스를 새로 만들지 않고도, 반복 스캔을 줄이는 동시에 인덱스 단계의 회원 필터링을 유지한 결과다.
이 예제에서 눈여겨볼 부분은 조건을 하나 더 썼다는 사실 자체가 아니다. 기존 조인 조건이 처리하던 회원 선별을 주문 인덱스에도 미리 전달했다는 점이다. 결과는 같지만, 필요한 주문을 찾아가는 과정이 달라졌다.
Oracle 블룸 필터 흉내 내기
Oracle의 Bloom Filter도 조인할 작은 집합의 키 정보를 먼저 만들고, 큰 집합을 처리할 때 불필요한 데이터를 미리 걸러내는 데 활용한다. 옵티마이저가 적용 여부를 결정하며, 실행계획에서는 JOIN FILTER CREATE, JOIN FILTER USE 등으로 확인할 수 있다.
이번 SQL은 실제 회원 목록을 ANY 조건으로 비교하므로 Bloom Filter를 구현한 것은 아니다. 다만 조인 상대의 키 정보를 미리 준비해 필터로 전달한다는 발상이 비슷하다. 이 예제에서는 그 필터가 주문 인덱스에 적용되어 불필요한 접근을 줄였다.
테스트 과정에서 생성한 인덱스를 정리한다.
DROP INDEX startdbpg.x_tr_ord_big_ord_mbr;
SQL
복사
마무리
이번 방법은 인덱스를 추가하기 어려운 특수한 상황에서 활용할 수 있는, 일종의 꼼수에 가깝다. 조건을 추가하고 처리 구조를 복잡하게 만드는 만큼, 상황에 맞지 않게 사용하면 오히려 성능을 떨어뜨리는 독이 될 수 있다.
따라서 기존 SQL보다 충분히 큰 성능 이득이 확인되는 경우에만 적용해야 한다. 결과가 동일한지는 물론, 실제 실행계획에서 회원 목록 조건이 인덱스에 적용되었는지, 주문 스캔의 반복과 버퍼 접근량이 줄었는지 반드시 확인하자.
이 예제에서 효과가 있었다는 이유만으로 다른 SQL에 그대로 복사해서 사용해서는 안 된다. 작동 원리를 이해하고, 적용할 SQL에서 의도한 실행계획과 성능 이득을 직접 확인한 뒤 사용하자.