상세 페이지
SQL이 '인덱스를 사용한다'는 사실보다 얼마나 읽고, 버리고, 반복했는지를 살펴본다. Index Cond·Filter·actual rows·loops·Buffers는 병목 단서이지 단독 정답은 아니다.
난이도: 중급~중상급. SECTION 01~09를 이해한 다음 학습한다.
계획의 상태: 아래 계획은 제공 교재·기존 원문에서 가져온 예상·개념적 실행계획이며, PostgreSQL에서 이번 SQL을 실행해 측정한 결과가 아니다. 성능 개선을 단정하지 말고 인덱스·자료형·결과 동등성을 확인한다.
PART A. 무엇을 얼마나 읽는가?
인덱스·필터·루프의 처리 범위를 먼저 읽고, 필요할 때 Hash Join과 Buffers까지 확장한다.
REMINDER 11-01. Index Cond와 Filter를 구분하자
상황: 특정 매장 S281의 2022년 11월 픽업 완료 주문을 조회한다.
검토할 SQL / BAD 후보
-- 비교 기준: 매장만 검색할 수 있는 인덱스
-- 제공 실습: tr_ord_big_x02 (shop_id)
SELECT o.ord_no, o.ord_dtm
FROM tr_ord_big AS o
WHERE o.shop_id = 'S281'
AND o.ord_st = 'PKUP'
AND o.pkup_dtm >= TIMESTAMP '2022-11-01'
AND o.pkup_dtm < TIMESTAMP '2022-12-01';
SQL
복사
개선 또는 비교 SQL / GOOD 후보
-- 같은 SQL, 인덱스 설계를 검토한다.
-- 아래 DDL은 교육용 '추가 후보'이며 실제 생성하지 않았음
CREATE INDEX tr_ord_big_review01
ON tr_ord_big (shop_id, ord_st, pkup_dtm);
SELECT o.ord_no, o.ord_dtm
FROM tr_ord_big AS o
WHERE o.shop_id = 'S281'
AND o.ord_st = 'PKUP'
AND o.pkup_dtm >= TIMESTAMP '2022-11-01'
AND o.pkup_dtm < TIMESTAMP '2022-12-01';
SQL
복사
예상 실행계획 — 실측 아님
[x02(shop_id) 선택 시 가능한 계획]
Index Scan using tr_ord_big_x02 on tr_ord_big
Index Cond: (shop_id = 'S281')
Filter: (ord_st = 'PKUP' AND pkup_dtm >= ... AND pkup_dtm < ...)
→ shop_id로 찾은 후보 행에서 다른 조건을 확인
[review01 선택 시 가능한 계획]
Index Scan using tr_ord_big_review01 on tr_ord_big
Index Cond: (shop_id = 'S281' AND ord_st = 'PKUP'
AND pkup_dtm >= ... AND pkup_dtm < ...)
→ 세 조건이 인덱스 검색에 함께 활용될 수 있음
Plain Text
복사
분석의 핵심: Index Cond는 인덱스로 검색·검사하는 조건, Filter는 스캔 노드의 추가 필터다. 인덱스 컬럼이 아닌 조건 때문에 많은 후보 행에 접근한다면 인덱스 구성을 점검해야 한다.
판단의 예외: Filter가 있다고 해서 무조건 느린 SQL은 아니다. 컬럼 변형·표현식과 접근 방식에 따라 Filter가 테이블 이전에 검사되는 경우도 있다. 교육용 신규 인덱스를 적용할지는 쓰기 부하·공간·다른 SQL까지 고려해 결정한다.
원본 규칙: 개편 전 04-01.
REMINDER 11-02. INCLUDE와 Index Only Scan은 읽는 컬럼을 함께 고려하자
상황: 회원 M00861의 특정 기간 주문금액을 매장별로 집계한다.
검토할 SQL / BAD 후보
-- 인덱스 x04 (mbr_id,ord_dtm) : 집계 컬럼 미포함
SELECT o.shop_id, SUM(o.ord_amt) AS ord_amt
FROM tr_ord_big AS o
WHERE o.mbr_id = 'M00861'
AND o.ord_dtm >= TIMESTAMP '2023-01-01'
AND o.ord_dtm < TIMESTAMP '2024-01-01'
GROUP BY o.shop_id;
SQL
복사
개선 또는 비교 SQL / GOOD 후보
-- 같은 SELECT, 제공 실습의 커버링 인덱스 후보
CREATE INDEX tr_ord_big_x06
ON tr_ord_big (mbr_id, ord_dtm)
INCLUDE (shop_id, ord_amt);
SELECT o.shop_id, SUM(o.ord_amt) AS ord_amt
FROM tr_ord_big AS o
WHERE o.mbr_id = 'M00861'
AND o.ord_dtm >= TIMESTAMP '2023-01-01'
AND o.ord_dtm < TIMESTAMP '2024-01-01'
GROUP BY o.shop_id;
SQL
복사
예상 실행계획 — 실측 아님
[x04를 사용하는 경우]
HashAggregate 또는 GroupAggregate
-> Index Scan using tr_ord_big_x04
Index Cond: (mbr_id = ... AND ord_dtm >= ... AND ord_dtm < ...)
→ shop_id, ord_amt를 얻기 위한 힙 접근이 필요
[x06을 사용하는 경우]
HashAggregate 또는 GroupAggregate
-> Index Only Scan using tr_ord_big_x06
Index Cond: (mbr_id = ... AND ord_dtm >= ... AND ord_dtm < ...)
Heap Fetches: 실행 환경에 따라 0 또는 0 초과
Plain Text
복사
분석의 핵심: INCLUDE 컬럼은 인덱스 키의 탐색 순서를 바꾸지 않으면서 SELECT·집계에 필요한 값을 인덱스에 포함할 수 있다. 조건과 출력 컬럼이 모두 인덱스에 있으면 Index Only Scan을 검토할 수 있다.
판단의 예외: INCLUDE가 있다고 늘 Index Only Scan을 선택하는 것은 아니다. visibility map의 all-visible 상태에 따라 힙 확인(Heap Fetches)이 발생한다. 큰 인덱스는 공간·쓰기 비용을 증가시킨다. x06 DDL은 실습 자료에 실제 명시되어 있으나 여기서 실행하지 않았다.
원본 규칙: 개편 전 04-06.
REMINDER 11-03. Bitmap Index Scan과 Index Scan은 역할이 다르다
상황: 매장 S033의 2023년 3월 주문을 주문일시 순으로 검색한다.
검토할 SQL / BAD 후보
-- 동일한 SQL의 가능한 접근 방법 A : Bitmap 계열
SELECT o.ord_no, o.ord_dtm
FROM tr_ord_big AS o
WHERE o.shop_id = 'S033'
AND o.ord_dtm >= TIMESTAMP '2023-03-01'
AND o.ord_dtm < TIMESTAMP '2023-04-01'
ORDER BY o.ord_dtm, o.ord_no;
SQL
복사
개선 또는 비교 SQL / GOOD 후보
-- 동일한 SQL의 가능한 접근 방법 B : B-tree 순서 활용
-- 두 계획 중 하나를 무조건 강제하는 것이 목적은 아니다.
SELECT o.ord_no, o.ord_dtm
FROM tr_ord_big AS o
WHERE o.shop_id = 'S033'
AND o.ord_dtm >= TIMESTAMP '2023-03-01'
AND o.ord_dtm < TIMESTAMP '2023-04-01'
ORDER BY o.ord_dtm, o.ord_no;
SQL
복사
예상 실행계획 — 실측 아님
[가능한 접근 A - x02(shop_id), x01(ord_dtm) 조합]
Sort
Sort Key: ord_dtm, ord_no
-> Bitmap Heap Scan on tr_ord_big
Recheck Cond: ...
-> BitmapAnd
-> Bitmap Index Scan using tr_ord_big_x02
-> Bitmap Index Scan using tr_ord_big_x01
[가능한 접근 B - 적절한 복합 인덱스가 추가로 있다면]
Index Scan using <shop_id,ord_dtm,ord_no 인덱스>
Index Cond: (shop_id = ... AND ord_dtm >= ... AND ord_dtm < ...)
→ 정렬을 생략할 수 있는 경우
Plain Text
복사
분석의 핵심: Bitmap 방식은 여러 인덱스의 결과를 합치거나 힙 블록 접근을 묶는 데 유리할 수 있다. 그러나 Bitmap Heap Scan이 반환하는 행은 인덱스 정렬 순서를 보장하지 않으므로 ORDER BY를 위해 Sort가 필요할 수 있다.
판단의 예외: BitmapAnd가 항상 등장하는 것은 아니다. x02와 x01을 둘 다 갖고 있어도 옵티마이저는 한 개 인덱스 또는 Seq Scan을 선택할 수 있다. GOOD은 성능 보장이 아닌 정렬 인덱스의 가능성 설명이다.
원본 규칙: 개편 전 04-08.
REMINDER 11-04. NL JOIN에서 Driven 테이블의 반복 접근과 인덱스를 확인하자
상황: PLAT·INV 회원과 2023-12-21~25 주문을 결합하여 회원별 주문금액을 합산한다.
검토할 SQL / BAD 후보
-- 동일 SQL, 회원ID 조인 인덱스가 없거나 부적합한 상태
SELECT m.mbr_id, SUM(o.ord_amt) AS ord_amt
FROM ms_mbr_big AS m
JOIN tr_ord_big AS o
ON o.mbr_id = m.mbr_id
WHERE m.mbr_gd = 'PLAT'
AND m.join_tp = 'INV'
AND o.ord_dtm >= TIMESTAMP '2023-12-21'
AND o.ord_dtm < TIMESTAMP '2023-12-26'
GROUP BY m.mbr_id;
SQL
복사
개선 또는 비교 SQL / GOOD 후보
-- 인덱스 후보: 교재 [SQL-CRIX-TR_ORD_BIG_X11]
-- CREATE INDEX tr_ord_big_x11 ON tr_ord_big (mbr_id);
-- 동일 SELECT: 인덱스가 있을 때 반복 접근 비용 비교
SELECT m.mbr_id, SUM(o.ord_amt) AS ord_amt
FROM ms_mbr_big AS m
JOIN tr_ord_big AS o
ON o.mbr_id = m.mbr_id
WHERE m.mbr_gd = 'PLAT'
AND m.join_tp = 'INV'
AND o.ord_dtm >= TIMESTAMP '2023-12-21'
AND o.ord_dtm < TIMESTAMP '2023-12-26'
GROUP BY m.mbr_id;
SQL
복사
예상 실행계획 — 실측 아님
BAD (NL JOIN을 선택한 상황의 가능한 계획)
GroupAggregate
-> Nested Loop
Join Filter: (o.mbr_id = m.mbr_id)
-> 회원 집합 스캔
-> Materialize
-> Seq Scan on tr_ord_big (날짜 Filter)
→ Materialize를 회원 행 수만큼 반복 조회할 수 있음
GOOD (NL JOIN + x11 선택 시)
GroupAggregate
-> Nested Loop
-> 회원 집합 스캔
-> Index Scan using tr_ord_big_x11 on tr_ord_big
Index Cond: (mbr_id = m.mbr_id)
Filter: (ord_dtm >= ... AND ord_dtm < ...)
→ Driven 인덱스 검색이 외부 회원 수만큼 반복될 수 있음
Plain Text
복사
분석의 핵심: Nested Loop는 외부 집합(Driving)의 각 행을 기준으로 안쪽 집합(Driven)을 검색한다. Driven에 조인 컬럼 인덱스가 없으면 동일 데이터를 반복 확인할 위험이 있다.
판단의 예외: 위 BAD 계획은 NL JOIN을 전제로 한 특정 시나리오다. 일반 SQL에는 pg_hint_plan 힌트가 없다면 PostgreSQL이 Hash Join 등을 선택할 수 있다. 원본 교재는 실제로 pg_hint_plan의 Leading/NestLoop 힌트를 써서 비교했으나, 여기서는 확장 설치 여부가 확인되지 않아 힌트를 SQL에 넣지 않았다.
원본 규칙: 개편 전 05-07.
REMINDER 11-05. Driven의 조인 컬럼뿐 아니라 필터 컬럼도 함께 점검하자
상황: 05-07과 동일한 SQL을 회원ID 단일 인덱스와 회원ID·주문일시 복합 인덱스로 비교한다.
검토할 SQL / BAD 후보
-- x11(mbr_id)만 사용하는 상황
SELECT m.mbr_id, SUM(o.ord_amt) AS ord_amt
FROM ms_mbr_big AS m
JOIN tr_ord_big AS o
ON o.mbr_id = m.mbr_id
WHERE m.mbr_gd = 'PLAT'
AND m.join_tp = 'INV'
AND o.ord_dtm >= TIMESTAMP '2023-12-21'
AND o.ord_dtm < TIMESTAMP '2023-12-26'
GROUP BY m.mbr_id;
SQL
복사
개선 또는 비교 SQL / GOOD 후보
-- 교재의 복합 인덱스 후보 x12
-- CREATE INDEX tr_ord_big_x12 ON tr_ord_big (mbr_id, ord_dtm);
SELECT m.mbr_id, SUM(o.ord_amt) AS ord_amt
FROM ms_mbr_big AS m
JOIN tr_ord_big AS o
ON o.mbr_id = m.mbr_id
WHERE m.mbr_gd = 'PLAT'
AND m.join_tp = 'INV'
AND o.ord_dtm >= TIMESTAMP '2023-12-21'
AND o.ord_dtm < TIMESTAMP '2023-12-26'
GROUP BY m.mbr_id;
SQL
복사
예상 실행계획 — 실측 아님
[x11 (mbr_id)로 NL JOIN하는 경우]
Nested Loop
-> 회원 집합
-> Index Scan using tr_ord_big_x11
Index Cond: (mbr_id = m.mbr_id)
Filter: (ord_dtm >= ... AND ord_dtm < ...)
→ 회원의 다른 날짜 주문도 후보가 되어 힙 접근 가능
[x12 (mbr_id,ord_dtm)로 NL JOIN하는 경우]
Nested Loop
-> 회원 집합
-> Index Scan using tr_ord_big_x12
Index Cond: (mbr_id = m.mbr_id
AND ord_dtm >= ... AND ord_dtm < ...)
→ 회원 + 주문일시를 함께 인덱스에서 제한
Plain Text
복사
분석의 핵심: NL Join의 반복 검색에서 조인 조건만 사용하는 인덱스보다, 조인 조건과 필터 범위가 맞는 복합 인덱스가 테이블 접근을 크게 줄일 수 있다.
판단의 예외: PG 교재에서 x11/x12를 비교한 논리를 따른 예상 계획이다. 실제 데이터 분포와 이미 존재하는 다른 인덱스를 고려해야 하며, 복합 인덱스 추가가 무조건 답은 아니다. 복합 인덱스 설계 원리는 아래 PART B의 선택 심화에서 다룬다.
원본 규칙: 개편 전 05-08.
REMINDER 11-06. Hash Join에서는 Build·Probe 입력의 필터 조건을 점검하자
상황: DRCT 매장의 2023년 1~3월 준비 완료된 픽업 주문을 월별 매장 개설월로 집계한다. (원본 교재의 조인 사례를 간소화)
검토할 SQL / BAD 후보
SELECT SUBSTRING(s.shop_start_ymd FROM 1 FOR 6) AS open_ym
,COUNT(*) AS ord_cnt
FROM ms_shop_big AS s
JOIN tr_ord_big AS o
ON o.shop_id = s.shop_id
WHERE s.shop_oper_tp = 'DRCT'
AND o.ord_st = 'PKUP'
AND o.prep_cmp_dtm >= TIMESTAMP '2023-01-01'
AND o.prep_cmp_dtm < TIMESTAMP '2023-04-01'
GROUP BY SUBSTRING(s.shop_start_ymd FROM 1 FOR 6);
SQL
복사
개선 또는 비교 SQL / GOOD 후보
-- 동일 SQL, 주문의 기간 조건을 지원하는 인덱스가
-- 적절한지 검토 (교재 x16: prep_cmp_dtm)
-- CREATE INDEX tr_ord_big_x16 ON tr_ord_big (prep_cmp_dtm);
EXPLAIN (COSTS OFF)
SELECT SUBSTRING(s.shop_start_ymd FROM 1 FOR 6) AS open_ym
,COUNT(*) AS ord_cnt
FROM ms_shop_big AS s
JOIN tr_ord_big AS o
ON o.shop_id = s.shop_id
WHERE s.shop_oper_tp = 'DRCT'
AND o.ord_st = 'PKUP'
AND o.prep_cmp_dtm >= TIMESTAMP '2023-01-01'
AND o.prep_cmp_dtm < TIMESTAMP '2023-04-01'
GROUP BY SUBSTRING(s.shop_start_ymd FROM 1 FOR 6);
SQL
복사
예상 실행계획 — 실측 아님
[가능한 Hash Join 계획]
HashAggregate
-> Hash Join
Hash Cond: (o.shop_id = s.shop_id)
-> Seq Scan / Index Scan on tr_ord_big
Filter 또는 Index Cond: (ord_st / prep_cmp_dtm)
-> Hash ← Build 입력
-> Seq Scan on ms_shop_big
Filter: (shop_oper_tp = 'DRCT')
[주의]
Hash 노드 아래가 Build, 다른 입력은 Probe.
Hash Join이라고 해서 조인 키 인덱스만으로 속도가 결정되지 않음.
Plain Text
복사
분석의 핵심: Hash Join은 한 입력 집합을 해시 테이블로 만들고(Build), 반대쪽으로 조회(Probe)한다. 조인 전에 각 입력 집합을 적절히 필터링하는 것이 중요하며, 조인 키 인덱스를 사용하는 NL Join과 판단 기준이 다를 수 있다.
판단의 예외: Hash의 Build 입력은 실제 계획에서 Hash 노드가 위치한 쪽으로 판단한다. 일반적으로 작을수록 유리하지만 통계와 메모리, I/O에 따라 선택이 달라진다. 인덱스 x16은 교재의 실험용이며 필요 데이터가 많으면 Seq Scan이 더 합리적일 수 있다. 원본 교재는 Hash Join을 강제·비교한 부분이 있어 기본 옵티마이저 선택과 구분한다.
원본 규칙: 개편 전 05-10.
REMINDER 11-07. Seq Scan은 이름보다 읽는 행과 필터를 보자
상황: 하루 주문만 필요한데 테이블의 대부분을 읽는지를 확인한다.
검토할 SQL / BAD 후보
SELECT COUNT(*) AS ord_cnt
FROM tr_ord_big AS o
WHERE TO_CHAR(o.ord_dtm, 'YYYYMMDD') = '20230301';
SQL
복사
개선 또는 비교 SQL / GOOD 후보
SELECT COUNT(*) AS ord_cnt
FROM tr_ord_big AS o
WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
AND o.ord_dtm < TIMESTAMP '2023-03-02';
SQL
복사
예상 실행계획 — 실측 아님
BAD (날짜 조건이 Index Cond가 되지 못한 가능한 형태)
Aggregate
-> Seq Scan on tr_ord_big
Filter: (to_char(ord_dtm, 'YYYYMMDD') = '20230301')
Rows Removed by Filter: [측정 시 확인]
→ 많은 후보 행을 검사할 위험
GOOD (tr_ord_big_x01(ord_dtm)이 있고 선택 시)
Aggregate
-> Index Only Scan / Index Scan using tr_ord_big_x01
Index Cond: (ord_dtm >= ... AND ord_dtm < ...)
→ 날짜 구간으로 후보 행을 줄일 가능성
Plain Text
복사
분석의 핵심: Seq Scan 자체가 잘못된 계획은 아니다. 필요한 행은 극히 적은데 전체 행을 확인하고 대부분 버리는지가 핵심이다.
판단의 예외: Rows Removed by Filter는 필터에서 제외된 후보 건수를 보여준다. 실제로 반환된 행 수와 함께 보고, 반복된 노드라면 루프 수도 함께 확인한다. 반면 테이블의 대부분이 필요한 SQL에서는 Seq Scan이 합리적일 수 있다.
원본 규칙: 개편 전 12-02.
REMINDER 11-08. 추정 rows와 실제 rows가 크게 다르면 통계와 조건을 확인하자
상황: 회원 상태 INAC는 소수, ACTV는 다수일 수 있다. 옵티마이저가 이 분포를 제대로 추정하는지 확인한다.
검토할 SQL / BAD 후보
-- 실제 SQL 수행 없이 통계 기반 추정만 확인
EXPLAIN
SELECT m.mbr_id, m.mbr_gd
FROM ms_mbr_big AS m
WHERE m.mbr_st = 'INAC';
SQL
복사
개선 또는 비교 SQL / GOOD 후보
EXPLAIN (ANALYZE, BUFFERS)
SELECT m.mbr_id, m.mbr_gd
FROM ms_mbr_big AS m
WHERE m.mbr_st = 'INAC';
-- 같은 조건의 분포 통계 확인
SELECT tablename, attname, n_distinct,
most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = current_schema()
AND tablename = 'ms_mbr_big'
AND attname = 'mbr_st';
SQL
복사
예상 실행계획 — 실측 아님
EXPLAIN (선택성이 높다고 판단할 때)
Index Scan using ms_mbr_big_x11 on ms_mbr_big
Index Cond: (mbr_st = 'INAC')
rows = [통계 기반 예상 반환 행 수]
EXPLAIN ANALYZE (실제 실행한 경우에만)
Index Scan / Seq Scan on ms_mbr_big
actual rows = [측정된 루프당 평균 출력 행 수]
loops = [측정된 반복 횟수]
Buffers = [측정된 접근량]
예상 rows와 actual rows를 비교해
선택도 오판·낡은 통계·파라미터 값 편차 여부를 검토
Plain Text
복사
분석의 핵심: 계획의 rows는 옵티마이저가 추정한 결과 건수다. 실제와 큰 차이가 지속되면 JOIN 방법이나 인덱스 선택이 불리해질 수 있다.
판단의 예외: 예상·실제 건수의 차이가 있다고 모두 통계 오류는 아니다. 파라미터 값의 분포, 컬럼 간 상관관계, 다중 조건, 계획 캐시 등도 원인일 수 있다. ANALYZE는 통계를 갱신하지만 원하는 인덱스를 반드시 선택하도록 강제하지 않는다. 집계 후 1행만 표시되는 노드와 집계 아래 스캔 노드를 구분해서 본다.
원본 규칙: 개편 전 12-05.
REMINDER 11-09. Buffers를 통해 실제 데이터 접근량을 확인하자
상황: 회원등급 PLAT의 주문 총금액을 조회할 때 메모리 적중과 페이지 읽기량을 알고 싶다.
검토할 SQL / BAD 후보
EXPLAIN
SELECT SUM(o.ord_amt) AS total_ord_amt
FROM ms_mbr_big AS m
JOIN tr_ord_big AS o ON o.mbr_id = m.mbr_id
WHERE m.mbr_gd = 'PLAT';
SQL
복사
개선 또는 비교 SQL / GOOD 후보
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT SUM(o.ord_amt) AS total_ord_amt
FROM ms_mbr_big AS m
JOIN tr_ord_big AS o ON o.mbr_id = m.mbr_id
WHERE m.mbr_gd = 'PLAT';
SQL
복사
예상 실행계획 — 실측 아님
BAD (가능한 추정 계획)
Aggregate
-> Hash Join
Hash Cond: (o.mbr_id = m.mbr_id)
-> tr_ord_big 스캔
-> Hash
-> ms_mbr_big 스캔 (mbr_gd = 'PLAT')
→ 실제 페이지 접근 정보는 알 수 없음
GOOD (같은 조인 형태가 선택된 경우의 측정 항목)
Aggregate
Buffers: shared hit/read = [실측값]
-> Hash Join
Buffers: shared hit/read = [실측값]
-> tr_ord_big 스캔
Buffers: shared hit/read = [실측값]
-> Hash
-> ms_mbr_big 스캔
Buffers: shared hit/read = [실측값]
shared hit: PostgreSQL 공유 버퍼에서 찾은 블록
shared read: 공유 버퍼로 읽어 들인 블록
temp read/written: 임시 파일 I/O 발생 단서
[실측 예시 수치는 만들지 않음]
Plain Text
복사
분석의 핵심: 실행시간은 시스템 상태·캐시·동시 부하의 영향을 받는다. 어떤 테이블이나 인덱스에서 읽기를 많이 했는지 확인하면 반복 스캔·필터·과도한 JOIN의 근거를 찾기 쉽다.
판단의 예외: shared read는 PostgreSQL 공유 버퍼에 없던 페이지를 읽는 작업이며, OS 파일 캐시에서 충족될 수도 있어 매번 저장장치의 물리 읽기가 발생했다는 뜻은 아니다. 부모 노드의 Buffers에는 하위 노드 처리가 반영될 수 있으므로, 부모와 자식의 Buffers를 모두 합산해 전체 I/O로 계산하지 않는다. hit가 많아도 반복 접근 문제는 여전히 존재할 수 있다.
원본 규칙: 개편 전 12-09.
PART B. 인덱스 구성과 접근 경로 — 선택 심화 (기존 SECTION 04 통합)
선택 학습: 아래 04-01~04-04는 기존 교재의 tr_ord_big 예제를 옮긴 것으로, 이 페이지에서 새로 실측하거나 인덱스를 생성한 결과가 아니다. SQL 문장 자체의 BAD/GOOD이 아니라 같은 SQL에서 선택되는 인덱스와 실행계획을 비교하는 학습이다. 기본 강의에서는 Index Cond·Filter·스캔 범위의 의미만 설명하고 복합 인덱스 설계는 심화로 다룬다.
선택 심화 (기존 04-01). 한 컬럼 인덱스와 복합 인덱스의 차이를 알자
상황: 회원 M00019의 2023년 3월 주문을 상태별로 집계한다.
BAD — 개선·검토 대상
-- 인덱스 구성: tr_ord_big_x01 (ord_dtm)
SELECT o.ord_st, COUNT(*) AS cnt
FROM tr_ord_big o
WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
AND o.ord_dtm < TIMESTAMP '2023-04-01'
AND o.mbr_id = 'M00019'
GROUP BY o.ord_st;
SQL
복사
GOOD — 권장 또는 비교할 SQL
-- 같은 SQL에서 복합 인덱스 x03을 비교
-- 기존 실습 정의: tr_ord_big_x03 (ord_dtm, mbr_id)
SELECT o.ord_st, COUNT(*) AS cnt
FROM tr_ord_big o
WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
AND o.ord_dtm < TIMESTAMP '2023-04-01'
AND o.mbr_id = 'M00019'
GROUP BY o.ord_st;
SQL
복사
핵심: 여러 조건이 인덱스에 함께 존재하면 후행 조건을 인덱스 내부에서 확인할 수 있어 테이블 접근이 줄어드는 경우가 있다.
예외·주의: 위 두 쿼리는 SQL 텍스트가 같으며 성능 차이는 인덱스 선택에서 온다. x03의 mbr_id가 Index Cond에 표시되어도 다음 04-03에서 설명할 '탐색 범위 축소'까지 보장되는 것은 아니다. 실측 수치는 본 문서에 옮기지 않았다.
선택 심화 (기존 04-02). 복합 인덱스는 동등 조건과 범위 조건의 순서를 생각하자
상황: 04-02와 정확히 같은 회원·기간 조회를 x03과 x04로 비교한다.
BAD — 개선·검토 대상
-- 비교 인덱스 x03 (ord_dtm, mbr_id) : 범위가 선두
SELECT o.ord_no, o.ord_dtm, o.mbr_id
FROM tr_ord_big o
WHERE o.mbr_id = 'M00019'
AND o.ord_dtm >= TIMESTAMP '2023-03-01'
AND o.ord_dtm < TIMESTAMP '2023-04-01';
SQL
복사
GOOD — 권장 또는 비교할 SQL
-- 비교 인덱스 x04 (mbr_id, ord_dtm) : 동등 조건이 선두
SELECT o.ord_no, o.ord_dtm, o.mbr_id
FROM tr_ord_big o
WHERE o.mbr_id = 'M00019'
AND o.ord_dtm >= TIMESTAMP '2023-03-01'
AND o.ord_dtm < TIMESTAMP '2023-04-01';
SQL
복사
핵심: PG 실습의 x03과 x04 비교는 Index Cond에 같은 조건이 보여도 스캔한 인덱스 리프의 범위가 다를 수 있다는 핵심 사례다. 보통 동등 조건 키를 앞에 두고 범위 조건 키를 뒤에 두는 구성이 해당 검색에 유리하다.
예외·주의: 이 규칙은 B-tree 복합 인덱스의 기본적인 설계 방향이다. 조건 선택성, 다른 주요 SQL, PostgreSQL 버전별 최적화에 따라 예외가 있다. x03을 반드시 제거하거나 x04가 모든 SQL에서 더 좋다는 뜻은 아니다.
선택 심화 (기존 04-03). 복합 인덱스의 선두 조건이 빠졌는지 확인하자
상황: 회원 ID 조건 없이 하루 동안의 모든 주문 건수를 조회한다.
BAD — 개선·검토 대상
-- 비교 대상: (mbr_id, ord_dtm)만 있는 경우
SELECT COUNT(*) AS cnt
FROM tr_ord_big o
WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
AND o.ord_dtm < TIMESTAMP '2023-03-02';
SQL
복사
GOOD — 권장 또는 비교할 SQL
-- 같은 SQL을 날짜 선두 인덱스 x01과 비교
-- x01: CREATE INDEX tr_ord_big_x01 ON tr_ord_big(ord_dtm);
SELECT COUNT(*) AS cnt
FROM tr_ord_big o
WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
AND o.ord_dtm < TIMESTAMP '2023-03-02';
SQL
복사
핵심: Index Cond가 나타났다고 효율적 구간 탐색이 보장되지는 않는다. 특히 선두 키를 사용하지 않는 SQL은 그 인덱스가 해당 검색 패턴에 적합한지 확인해야 한다.
예외·주의: 제공된 p.128은 힌트로 x04를 강제한 특수 실험이며, PostgreSQL 계획에 'INDEX FULL SCAN'이라는 별도 노드가 표시되는 것은 아니다. 보통 쿼리는 옵티마이저가 다른 인덱스·Seq Scan을 선택할 수 있다. PG 버전과 B-tree 최적화의 영향도 고려한다.
선택 심화 (기존 04-04). 대량 반환 SQL에서는 Seq Scan이 오히려 자연스럽다
상황: 2년 범위에서 픽업 완료·카드 결제 주문을 대량 조회한다.
BAD — 개선·검토 대상
-- BAD는 SQL 자체가 아니라 '인덱스를 반드시 타야 한다'는 판단
SELECT o.*
FROM tr_ord_big o
WHERE o.ord_dtm >= TIMESTAMP '2023-01-01'
AND o.ord_dtm < TIMESTAMP '2025-01-01'
AND o.ord_st = 'PKUP'
AND o.pay_tp = 'CARD';
SQL
복사
GOOD — 권장 또는 비교할 SQL
-- GOOD: 같은 SQL의 예상 비용과 반환 비율을 확인
EXPLAIN (COSTS OFF)
SELECT o.*
FROM tr_ord_big o
WHERE o.ord_dtm >= TIMESTAMP '2023-01-01'
AND o.ord_dtm < TIMESTAMP '2025-01-01'
AND o.ord_st = 'PKUP'
AND o.pay_tp = 'CARD';
SQL
복사
핵심: 결과가 테이블의 큰 부분을 차지하면 인덱스에서 수많은 행 위치를 찾아 테이블을 여러 번 읽는 것보다 Seq Scan이 합리적일 수 있다.
예외·주의: 원본 교재에는 테스트 인덱스 tr_ord_big_test(ord_st,pay_tp,ord_dtm)를 만든 뒤에도 성능을 비교하도록 되어 있다. 여기서는 원본의 측정 시간을 복사하지 않고, 인덱스 사용 여부 자체를 성공 기준으로 삼지 않는 규칙만 추출했다.
개발자를 위한 인덱스 추가 전 주의사항 (기존 04-06)
인덱스는 SQL 하나만 보고 무한정 추가하지 말자. 새 인덱스는 다른 SQL의 계획, 저장 공간, INSERT·UPDATE·DELETE 성능에도 영향을 준다. 주요 조회 패턴과 기존 인덱스의 키 컬럼·순서·중복 여부를 먼저 확인한다.
-- PostgreSQL에서 기존 인덱스 목록 확인
SELECT i.indexname, i.indexdef
FROM pg_indexes i
WHERE i.schemaname = 'startdbpg'
AND i.tablename = 'tr_ord'
ORDER BY i.indexname;
SQL
복사
원본 실습의 tr_ord_big_x04(mbr_id, ord_dtm)와 tr_ord_big_x12(mbr_id, ord_dtm)는 서로 다른 실습 챕터에 등장하는 동일 키 구성의 예시다. 실제 DB에 두 인덱스가 동시에 존재한다는 뜻은 아니다. 인덱스 생성·삭제는 별도 검토 후 진행한다.
분석 후 점검
Index Cond와 Filter를 구분했는가?
Index Only Scan의 Heap Fetches를 확인했는가?
Bitmap Scan이 인덱스 정렬 순서를 보존하지 않음을 아는가?
Nested Loop에서 Driven 노드의 loops를 확인했는가?
Hash의 Build·Probe 및 입력 크기를 확인했는가?
actual rows와 예상 rows, loops를 함께 보았는가?
Buffers와 Sort의 temp I/O를 분리해 해석했는가?
관련 원문과 추가 학습
기억할 것: SQL이 '인덱스를 사용한다'는 사실보다 얼마나 읽고, 버리고, 반복했는지를 살펴본다. Index Cond·Filter·actual rows·loops·Buffers는 병목 단서이지 단독 정답은 아니다.