PostgreSQL 쿼리 튜닝: LEFT JOIN ... IS NULL → NOT EXISTS로 변환, 인덱스 활용

※ Transforming LEFT JOIN with NULL Filtering to NOT EXISTS for Better Index Utilization.
 
안녕하세요. 듀스트림입니다.
 
오늘 포스팅은 오랜만에 쿼리 튜닝 사례입니다.
 
이번 사례는 LEFT JOIN ... NULL 필터링을 NOT EXISTS로 변환하고, 인덱스를 추가하여 성능 최적화한 사례입니다.
최적화 결과, 실제 운영 쿼리 성능이 약 2배 정도 향상되었습니다.
 

주요 포인트는 다음과 같습니다.

1. LEFT JOIN ... IS NULL → NOT EXISTS로 조인 방식을 변경하여 조인 비용 감소와 락 경합 발생 최소화
2. 필터 조건에 맞는 인덱스 추가로 데이터 접근 방식 최적화: Seq Scan(전체 스캔) → Bitmap Index Scan(인덱스 활용)
3. 서브쿼리를 직관적으로 SQL 구조 개선

그럼 예시를 통해 자세히 살펴보겠습니다.

---

AS-IS

-- LEFT JOIN ... IS NULL
EXPLAIN(BUFFERS, ANALYZE)
INSERT INTO test.segment_02
(SEGM_ID, CUST_ID, CUST_INFO)
SELECT
    'C240101', A.CUST_ID, A.CUST_ID_INFO
FROM (
    SELECT B1.CUST_ID, B1.CUST_ID_INFO
    FROM test.segment_01 B1
    LEFT OUTER JOIN test.segment_02 B2
    ON B1.CUST_ID = B2.CUST_ID
    AND B2.SEGM_ID IN ('C240103', 'C240102')
    WHERE B1.SEGM_ID = 'C240101'
    AND B2.CUST_ID IS NULL
) A;

--- QUERY PLAN ---
 Insert on segment_02  (cost=35.46..3277.95 rows=0 width=0) (actual time=635.674..635.677 rows=0 loops=1)
   Buffers: shared hit=478841 dirtied=1510 written=1511
   ->  Hash Anti Join  (cost=35.46..3277.95 rows=99323 width=59) (actual time=0.016..34.397 rows=100000 loops=1)
         Hash Cond: ((b1.cust_id)::text = (b2.cust_id)::text)
         Buffers: shared hit=736
         ->  Seq Scan on segment_01 b1  (cost=0.00..1986.00 rows=100000 width=21) (actual time=0.008..14.092 rows=100000 loops=1)
               Filter: ((segm_id)::text = 'C240101'::text)
               Buffers: shared hit=736
         ->  Hash  (cost=27.00..27.00 rows=677 width=7) (actual time=0.001..0.002 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  Seq Scan on segment_02 b2  (cost=0.00..27.00 rows=677 width=7) (actual time=0.001..0.001 rows=0 loops=1)
                     Filter: ((segm_id)::text = ANY ('{C240103,C240102}'::text[]))
 Planning:
   Buffers: shared hit=58 read=2
 Planning Time: 0.535 ms
 Execution Time: 635.711 ms
(16 rows)

• LEFT JOIN으로 B1.CUST_ID와 B2.CUST_ID를 조인합니다.
→ 두 테이블을 조인하여 조건에 맞는 데이터를 필터링합니다.

• B2.CUST_ID가 NULL인 레코드를 선택합니다.
→ 조인 결과에서 B2.CUST_ID가 존재하지 않는 데이터를 추출합니다.

• 불필요한 조인 연산이 발생하며, 최적화되지 않은 Hash Anti Join을 사용합니다.
→ 조인으로 인해 불필요한 데이터 처리 비용이 발생하고, Hash Anti Join이 비효율적으로 동작합니다.

---

TO-BE

-- 인덱스 생성
CREATE INDEX idx_segment_02_cust_id_segm_id ON test.segment_02 (CUST_ID, SEGM_ID);

-- NOT EXISTS
EXPLAIN(BUFFERS, ANALYZE)
INSERT INTO test.segment_02 (SEGM_ID, CUST_ID, CUST_INFO)
SELECT 
    'C240101', 
    B1.CUST_ID, 
    B1.CUST_ID_INFO
FROM 
    test.segment_01 B1
WHERE 
    B1.SEGM_ID = 'C240101'
    AND NOT EXISTS (
        SELECT 1 
        FROM test.segment_02 B2 
        WHERE 
            B1.CUST_ID = B2.CUST_ID 
            AND B2.SEGM_ID IN ('C240103', 'C240102')
    );
    
--- QUERY PLAN ---
 Insert on segment_02  (cost=249.57..495.19 rows=0 width=0) (actual time=385.595..385.599 rows=0 loops=1)
   Buffers: shared hit=277180 dirtied=754 written=754
   ->  Hash Anti Join  (cost=249.57..495.19 rows=44 width=314) (actual time=31.281..54.928 rows=50000 loops=1)
         Hash Cond: ((b1.cust_id)::text = (b2.cust_id)::text)
         Buffers: shared hit=1734
         ->  Bitmap Heap Scan on segment_01 b1  (cost=9.07..253.52 rows=85 width=276) (actual time=4.553..16.653 rows=100000 loops=1)
               Recheck Cond: ((segm_id)::text = 'C240101'::text)
               Heap Blocks: exact=736
               Buffers: shared hit=1122
               ->  Bitmap Index Scan on idx_segment_01_segm_id_cust_id  (cost=0.00..9.05 rows=85 width=0) (actual time=4.443..4.444 rows=100000 loops=1)
                     Index Cond: ((segm_id)::text = 'C240101'::text)
                     Buffers: shared hit=386
         ->  Hash  (cost=239.30..239.30 rows=96 width=58) (actual time=13.086..13.088 rows=50000 loops=1)
               Buckets: 65536 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 2417kB
               Buffers: shared hit=612
               ->  Bitmap Heap Scan on segment_02 b2  (cost=9.31..239.30 rows=96 width=58) (actual time=2.259..7.386 rows=50000 loops=1)
                     Recheck Cond: ((segm_id)::text = ANY ('{C240103,C240102}'::text[]))
                     Heap Blocks: exact=417
                     Buffers: shared hit=612
                     ->  Bitmap Index Scan on segment_02_pkey  (cost=0.00..9.29 rows=96 width=0) (actual time=2.203..2.204 rows=50000 loops=1)
                           Index Cond: ((segm_id)::text = ANY ('{C240103,C240102}'::text[]))
                           Buffers: shared hit=195
 Planning:
   Buffers: shared hit=26
 Planning Time: 0.242 ms
 Execution Time: 385.655 ms
(26 rows)

• B1.CUST_ID가 B2.CUST_ID에 존재하지 않는 경우를 NOT EXISTS로 체크합니다.
→ NOT EXISTS로 조건을 단순화하여 조인 연산을 줄이고, 필요한 데이터만 효율적으로 확인합니다.

• Hash Anti Join을 사용하며, 조인 연산을 더 간단하게 만듭니다.
→ 최적화된 Hash Anti Join으로 조인 비용을 감소시킵니다.

• 더 효율적인 인덱스를 활용하여 Bitmap Heap Scan을 수행합니다.
→ 새로 생성된 인덱스를 활용해 Seq Scan을 제거하고, 데이터 접근 방식을 최적화합니다.

---

AS-IS와 TO-BE 쿼리 구조의 의미는 동일합니다.

test.segment_01의 CUST_ID가 test.segment_02에 존재하지 않는 경우만 선택

AS-IS: LEFT JOIN ... WHERE B2.CUST_ID IS NULL
TO-BE: NOT EXISTS

 

주요 성능 지표 비교

항목	AS-IS	TO-BE	차이
Execution Time	635.711ms	385.655ms	39% 감소
Seq Scan	✅	❌	제거됨
Bitmap Index Scan	❌	✅	인덱스 사용
Hash Anti Join	✅	✅	동일
Buffers Used	478,841	277,180	42% 감소

 

최적화 효과 정리

최적화 전략	효과
LEFT JOIN ... IS NULL → NOT EXISTS	조인 비용 감소, 중복 연산 제거, 락 최소화
Seq Scan → Bitmap Index Scan	Seq Scan 제거 인덱스 활용
Hash Anti Join 최적화	더 작은 테이블의 해시 테이블 생성으로 메모리 절약
인덱스 추가	빠른 데이터 접근으로 I/O 비용 감소
전체 데이터 접근 감소	필요한 데이터만 읽음으로 디스크 I/O 감소

 

이 쿼리는 불특정한 시간대에 자주 사용되는 쿼리입니다.

또한 외래 키 참조가 없고 데이터 건수가 많지 않은(100만 건 내) 테이블을 대상으로 실행되며, Autovacuum을 사용할 수 없는 환경에서 동작합니다.
이를 해결하기 위해 쿼리 수행 후 ANALYZE를 추가하여 통계 정보를 최신화하고, 쿼리 플래너의 실행 계획 정확도를 향상시키는 로직을 적용하였습니다.

---

그럼 다음 포스팅에서 뵙겠습니다.