SECTION 04 원문 백업

재편집 전 원문 백업 | SECTION 04 | 2026-10-14

원본: SECTION 04. INDEX 활용 (SECTION 08·11로 통합)

인덱스가 존재한다고 효율적인 검색이 보장되는 것은 아니다.

인덱스를 '사용했는가'보다 얼마나 좁게 찾아 읽었는가를 먼저 확인하자.

이 섹션은 실제 PostgreSQL 실습의 tr_ord_big, ms_mbr_big와 tr_ord_big_x01/x02/x03/x04/x06/x07 등의 인덱스 사례를 중심으로 진행한다. SECTION 03이 WHERE 조건 작성 방식을 다뤘다면, 이번 SECTION 04는 동일한 SQL이라도 인덱스 구조와 접근 방식에 따라 처리량이 달라질 수 있는 이유를 다룬다.

예상 실행계획 · 인덱스 DDL 사용 시 주의

•

모든 실행계획은 제공된 교재의 개념·패턴을 바탕으로 새로 구성한 예상 실행계획이다. 이 페이지 작성 과정에서 PostgreSQL을 실행하거나 실제 수치를 측정하지 않았다.

•

원본 자료의 실행시간·Buffers·actual rows를 예상 수치로 옮기거나 조작하지 않는다.

•

동일한 SELECT를 기존 인덱스와 개선 후보 인덱스로 비교하는 항목이 많다. 이때 BAD/GOOD의 차이는 SQL 문법이 아니라 인덱스 적합성·선택이다.

•

CREATE INDEX는 원본 실습 정의 또는 교육용 신규 후보를 표시한 것으로, 이 페이지를 작성하며 실행한 명령이 아니다. 실제 운영 DB에 그대로 실행하지 말 것.

•

SQL 예제의 예상 실행계획은 인덱스 생성 상태, PostgreSQL 버전, 통계·분포, visibility map 등에 따라 달라질 수 있다.

이번 섹션의 핵심 규칙

ID	구분	기억할 규칙
04-01	MUST	Index Cond와 Filter를 구분하자
04-02	MUST	한 컬럼 인덱스와 복합 인덱스의 차이를 알자
04-03	MUST	복합 인덱스는 동등 조건과 범위 조건의 순서를 생각하자
04-04	CHECK	복합 인덱스의 선두 조건이 빠졌는지 확인하자
04-05	MUST	대량 반환 SQL에서는 Seq Scan이 오히려 자연스럽다
04-06	TIP	INCLUDE와 Index Only Scan은 읽는 컬럼을 함께 고려하자
04-07	TIP	부분 인덱스(Partial Index)는 대상 조건이 맞아야 한다
04-08	CHECK	Bitmap Index Scan과 Index Scan은 역할이 다르다
04-09	TIP	ORDER BY LIMIT은 검색과 정렬을 함께 지원하는 인덱스를 생각하자
04-10	MUST	인덱스는 SQL 하나만 보고 무한정 추가하지 말자

구분: MUST — 필수 규칙 / TIP — 실전 활용 패턴 / CHECK — 실제 인덱스·실행계획 확인 필요

이번 섹션에서 비교하는 주요 인덱스

-- 아래 이름과 정의는 제공된 SQL 스크립트에 등장한다.
CREATE INDEX tr_ord_big_x01 ON tr_ord_big (ord_dtm);
CREATE INDEX tr_ord_big_x02 ON tr_ord_big (shop_id);
CREATE INDEX tr_ord_big_x03 ON tr_ord_big (ord_dtm, mbr_id);
CREATE INDEX tr_ord_big_x04 ON tr_ord_big (mbr_id, ord_dtm);
CREATE INDEX tr_ord_big_x06 ON tr_ord_big (mbr_id, ord_dtm) INCLUDE (shop_id, ord_amt);
CREATE INDEX tr_ord_big_x07 ON tr_ord_big (shop_id, prep_cmp_dtm, ord_no);

-- 추가로 등장하는 다른 실습 챕터의 정의
CREATE INDEX tr_ord_big_x12 ON tr_ord_big (mbr_id, ord_dtm);

SQL

복사

위 DDL은 참고용 목록이며 한 번에 실행하는 스크립트가 아니다. 실습에서는 현재 환경에 존재하는 인덱스를 먼저 확인한다.

REMINDER 04-01. Index Cond와 Filter를 구분하자

MUST · 상황: 특정 매장 S281의 2022년 11월 픽업 완료 주문을 조회한다.

BAD — 비효율 가능성이 있는 SQL 또는 인덱스 상황

-- 비교 기준: 매장만 검색할 수 있는 인덱스
-- 제공 실습: 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

복사

GOOD — 개선 후보 또는 비교할 SQL

-- 같은 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까지 고려해 결정한다.

참고한 자료: PG PDF p.97~103, 특히 p.99 [SQL-XPERF1-1]. 기존 실습 x02(shop_id)는 SQL 스크립트에 정의.

REMINDER 04-02. 한 컬럼 인덱스와 복합 인덱스의 차이를 알자

MUST · 상황: 회원 M00019의 2023년 3월 주문을 상태별로 집계한다.

BAD — 비효율 가능성이 있는 SQL 또는 인덱스 상황

-- 인덱스 구성: tr_ord_big_x01 (ord_dtm)
SELECT o.ord_st, COUNT(*) AS cnt
FROM   tr_ord_big AS 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 AS 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

복사

예상 실행계획 — 실제 측정 아님

[x01(ord_dtm) 선택 시]
HashAggregate 또는 Sort + GroupAggregate
  -> Index Scan using tr_ord_big_x01
       Index Cond: (ord_dtm >= ... AND ord_dtm < ...)
       Filter: (mbr_id = 'M00019')

[x03(ord_dtm, mbr_id) 선택 시]
HashAggregate 또는 Sort + GroupAggregate
  -> Index Scan using tr_ord_big_x03
       Index Cond: (ord_dtm >= ... AND ord_dtm < ...
                    AND mbr_id = 'M00019')
  → 적합한 키가 인덱스에 있으면 불필요한 테이블 행 확인을 줄일 수 있음

Plain Text

복사

기억할 이유: 여러 조건이 인덱스에 함께 존재하면 후행 조건을 인덱스 내부에서 확인할 수 있어 테이블 접근이 줄어드는 경우가 있다.

주의·예외: 위 두 쿼리는 SQL 텍스트가 같으며 성능 차이는 인덱스 선택에서 온다. x03의 mbr_id가 Index Cond에 표시되어도 다음 04-03에서 설명할 '탐색 범위 축소'까지 보장되는 것은 아니다. 실측 수치는 본 문서에 옮기지 않았다.

참고한 자료: PG PDF p.107~111, [SQL-XPERF2-1], [SQL-CRIX-TR_ORD_BIG_X01/X03].

REMINDER 04-03. 복합 인덱스는 동등 조건과 범위 조건의 순서를 생각하자

MUST · 상황: 04-02와 정확히 같은 회원·기간 조회를 x03과 x04로 비교한다.

BAD — 비효율 가능성이 있는 SQL 또는 인덱스 상황

-- 비교 인덱스 x03 (ord_dtm, mbr_id) : 범위가 선두
SELECT o.ord_no, o.ord_dtm, o.mbr_id
FROM   tr_ord_big AS 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 AS o
WHERE  o.mbr_id = 'M00019'
  AND  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01';

SQL

복사

예상 실행계획 — 실제 측정 아님

[x03(ord_dtm,mbr_id) 선택 시]
Index Scan using tr_ord_big_x03
  Index Cond: (ord_dtm >= ... AND ord_dtm < ... AND mbr_id = ...)
  → 날짜 구간의 많은 인덱스 엔트리를 검사하고 mbr_id를 확인할 수 있음

[x04(mbr_id,ord_dtm) 선택 시]
Index Scan using tr_ord_big_x04
  Index Cond: (mbr_id = ... AND ord_dtm >= ... AND ord_dtm < ...)
  → 특정 회원 구간 안에서 날짜 범위를 탐색할 수 있음

Plain Text

복사

기억할 이유: PG 실습의 x03과 x04 비교는 Index Cond에 같은 조건이 보여도 스캔한 인덱스 리프의 범위가 다를 수 있다는 핵심 사례다. 보통 동등 조건 키를 앞에 두고 범위 조건 키를 뒤에 두는 구성이 해당 검색에 유리하다.

주의·예외: 이 규칙은 B-tree 복합 인덱스의 기본적인 설계 방향이다. 조건 선택성, 다른 주요 SQL, PostgreSQL 버전별 최적화에 따라 예외가 있다. x03을 반드시 제거하거나 x04가 모든 SQL에서 더 좋다는 뜻은 아니다.

참고한 자료: PG PDF p.115~119, 특히 p.117의 x03/x04 비교. SQL 스크립트 x03(ord_dtm,mbr_id), x04(mbr_id,ord_dtm).

REMINDER 04-04. 복합 인덱스의 선두 조건이 빠졌는지 확인하자

CHECK · 상황: 회원 ID 조건 없이 하루 동안의 모든 주문 건수를 조회한다.

BAD — 비효율 가능성이 있는 SQL 또는 인덱스 상황

-- 비교 대상: (mbr_id, ord_dtm)만 있는 경우
SELECT COUNT(*) AS cnt
FROM   tr_ord_big AS 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 AS o
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-03-02';

SQL

복사

예상 실행계획 — 실제 측정 아님

[x04(mbr_id,ord_dtm)을 강제로 사용한다고 가정한 사례]
Aggregate
  -> Index Scan using tr_ord_big_x04
       Index Cond: (ord_dtm >= ... AND ord_dtm < ...)
       → 선두 mbr_id 조건이 없어 넓은 인덱스 탐색 가능

[x01(ord_dtm)을 선택한 경우]
Aggregate
  -> Index Only Scan 또는 Index Scan using tr_ord_big_x01
       Index Cond: (ord_dtm >= ... AND ord_dtm < ...)
       → 날짜 선두 키로 범위를 좁힐 여지

Plain Text

복사

기억할 이유: Index Cond가 나타났다고 효율적 구간 탐색이 보장되지는 않는다. 특히 선두 키를 사용하지 않는 SQL은 그 인덱스가 해당 검색 패턴에 적합한지 확인해야 한다.

주의·예외: 제공된 p.128은 힌트로 x04를 강제한 특수 실험이며, PostgreSQL 계획에 'INDEX FULL SCAN'이라는 별도 노드가 표시되는 것은 아니다. 보통 쿼리는 옵티마이저가 다른 인덱스·Seq Scan을 선택할 수 있다. PG 버전과 B-tree 최적화의 영향도 고려한다.

참고한 자료: PG PDF p.128~129, [SQL-XPERF2-선두누락].

REMINDER 04-05. 대량 반환 SQL에서는 Seq Scan이 오히려 자연스럽다

MUST · 상황: 2년 범위에서 픽업 완료·카드 결제 주문을 대량 조회한다.

BAD — 비효율 가능성이 있는 SQL 또는 인덱스 상황

-- BAD는 SQL 자체가 아니라 '인덱스를 반드시 타야 한다'는 판단
SELECT o.*
FROM   tr_ord_big AS 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 AS 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 on tr_ord_big
  Filter: (ord_st = 'PKUP' AND pay_tp = 'CARD'
           AND ord_dtm >= ... AND ord_dtm < ...)

[다른 환경에서 인덱스가 선택될 수도 있음]
Bitmap Heap Scan / Index Scan ...
  Index Cond: (... 일부 또는 모든 검색 조건)
  Filter: (... 나머지 조건)

Plain Text

복사

기억할 이유: 결과가 테이블의 큰 부분을 차지하면 인덱스에서 수많은 행 위치를 찾아 테이블을 여러 번 읽는 것보다 Seq Scan이 합리적일 수 있다.

주의·예외: 원본 교재에는 테스트 인덱스 tr_ord_big_test(ord_st,pay_tp,ord_dtm)를 만든 뒤에도 성능을 비교하도록 되어 있다. 여기서는 원본의 측정 시간을 복사하지 않고, 인덱스 사용 여부 자체를 성공 기준으로 삼지 않는 규칙만 추출했다.

참고한 자료: PG PDF p.132~134, [SQL-XPERF2-CX2], [SQL-CRIX-TR_ORD_BIG_TEST].

REMINDER 04-06. INCLUDE와 Index Only Scan은 읽는 컬럼을 함께 고려하자

TIP · 상황: 회원 M00861의 특정 기간 주문금액을 매장별로 집계한다.

BAD — 비효율 가능성이 있는 SQL 또는 인덱스 상황

-- 인덱스 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

복사

GOOD — 개선 후보 또는 비교할 SQL

-- 같은 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은 실습 자료에 실제 명시되어 있으나 여기서 실행하지 않았다.

참고한 자료: PG PDF p.147~149, [SQL-XPERF3-COV1/COV2]; SQL 스크립트 [SQL-CRIX-TR_ORD_BIG_X06].

REMINDER 04-07. 부분 인덱스(Partial Index)는 대상 조건이 맞아야 한다

TIP · 상황: 상태가 INAC인 비활성 회원을 등급별로 집계한다.

BAD — 비효율 가능성이 있는 SQL 또는 인덱스 상황

-- 조건 SQL은 정상이나, 부분 인덱스가 없을 수 있는 상태
SELECT m.mbr_gd, COUNT(*) AS cnt
FROM   ms_mbr_big AS m
WHERE  m.mbr_st = 'INAC'
GROUP BY m.mbr_gd;

SQL

복사

GOOD — 개선 후보 또는 비교할 SQL

-- 제공 실습의 부분 인덱스 설계 사례
CREATE INDEX ms_mbr_big_x02
ON ms_mbr_big (mbr_st, mbr_gd)
WHERE mbr_st = 'INAC';

-- SQL은 그대로 유지
SELECT m.mbr_gd, COUNT(*) AS cnt
FROM   ms_mbr_big AS m
WHERE  m.mbr_st = 'INAC'
GROUP BY m.mbr_gd;

SQL

복사

예상 실행계획 — 실제 측정 아님

[적절한 인덱스가 없는 경우]
HashAggregate
  -> Seq Scan on ms_mbr_big
       Filter: (mbr_st = 'INAC')

[부분 인덱스를 사용할 수 있는 경우]
GroupAggregate 또는 HashAggregate
  -> Index Only Scan using ms_mbr_big_x02
       Index Cond: (없거나 생략될 수 있음)
       → 인덱스 자체가 'INAC'인 행만 포함하는 구조

Plain Text

복사

기억할 이유: 부분 인덱스는 WHERE 조건을 만족하는 행만 인덱스에 담는다. 자주 검색하는 일부 상태에 집중할 때 공간과 읽기 비용을 줄일 여지가 있다.

주의·예외: 파라미터 사용 형태와 plan cache(generic plan)에 따라 부분 인덱스의 조건 함의가 확인되지 않으면 사용되지 않을 수 있다. 정확히 어떤 SQL에서 유효한지는 EXPLAIN으로 확인한다. INAC는 실습에서 사용한 예시 코드다.

참고한 자료: PG SQL 스크립트 [SQL-CRIX-MS_MBR_BIG_X02] 및 [SQL-XPERF3-PARTIAL].

REMINDER 04-08. Bitmap Index Scan과 Index Scan은 역할이 다르다

CHECK · 상황: 매장 S033의 2023년 3월 주문을 주문일시 순으로 검색한다.

BAD — 비효율 가능성이 있는 SQL 또는 인덱스 상황

-- 동일한 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

복사

GOOD — 개선 후보 또는 비교할 SQL

-- 동일한 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은 성능 보장이 아닌 정렬 인덱스의 가능성 설명이다.

참고한 자료: PG PDF p.84~88(Bitmap), p.159~160(인덱스 정렬 vs Bitmap); x01/x02 인덱스 SQL 스크립트.

REMINDER 04-09. ORDER BY LIMIT은 검색과 정렬을 함께 지원하는 인덱스를 생각하자

TIP · 상황: 매장 S100의 일정 기간 주문 중 준비완료일시가 가장 늦은 10건을 조회한다.

BAD — 비효율 가능성이 있는 SQL 또는 인덱스 상황

-- 기본 인덱스: tr_ord_big_x02(shop_id)
SELECT o.ord_no, o.prep_cmp_dtm, o.shop_id
FROM   tr_ord_big AS o
WHERE  o.shop_id = 'S100'
  AND  o.prep_cmp_dtm >= TIMESTAMP '2024-01-01'
  AND  o.prep_cmp_dtm <  TIMESTAMP '2024-04-01'
ORDER BY o.prep_cmp_dtm DESC, o.ord_no DESC
LIMIT 10;

SQL

복사

GOOD — 개선 후보 또는 비교할 SQL

-- 제공 실습의 정렬 대응 인덱스
CREATE INDEX tr_ord_big_x07
ON tr_ord_big (shop_id, prep_cmp_dtm, ord_no);

-- 검색 SQL 자체는 BAD와 동일
SELECT o.ord_no, o.prep_cmp_dtm, o.shop_id
FROM   tr_ord_big AS o
WHERE  o.shop_id = 'S100'
  AND  o.prep_cmp_dtm >= TIMESTAMP '2024-01-01'
  AND  o.prep_cmp_dtm <  TIMESTAMP '2024-04-01'
ORDER BY o.prep_cmp_dtm DESC, o.ord_no DESC
LIMIT 10;

SQL

복사

예상 실행계획 — 실제 측정 아님

[x02(shop_id)만 사용할 경우 - 가능한 계획]
Limit
  -> Sort
       Sort Key: prep_cmp_dtm DESC, ord_no DESC
       -> Bitmap Heap Scan on tr_ord_big
            Recheck Cond: (shop_id = 'S100')
            Filter: (prep_cmp_dtm >= ... AND prep_cmp_dtm < ...)

[x07(shop_id,prep_cmp_dtm,ord_no) 선택 시]
Limit
  -> Index Only Scan Backward / Index Scan Backward using tr_ord_big_x07
       Index Cond: (shop_id = 'S100'
                    AND prep_cmp_dtm >= ... AND prep_cmp_dtm < ...)
  → 필요한 상위 행에 도달한 뒤 멈출 수 있음

Plain Text

복사

기억할 이유: LIMIT을 써도 그 전에 대량 데이터를 찾고 정렬한다면 접근량이 크게 줄지 않는다. 동등 조건·범위 조건·정렬 키가 맞는 인덱스는 Sort를 줄이거나 제거할 수 있다.

주의·예외: 원본 p.163~166은 같은 SQL에 인덱스 x02와 x07을 비교한 사례다. 이 예시는 SELECT 컬럼을 x07에 모두 포함하므로 Index Only Scan도 가능하지만 선택·visibility map에 따라 실제 노드는 달라진다. 이 패턴의 일반적인 페이징·최신 N건 비교는 SECTION 09~10에서 자세히 다룬다.

참고한 자료: PG PDF p.163~166, [SQL-ORDER-LMT1], [SQL-CRIX-TR_ORD_BIG_X07].

REMINDER 04-10. 인덱스는 SQL 하나만 보고 무한정 추가하지 말자

MUST · 상황: 새 복합 인덱스를 요청하기 전 기존 인덱스와 주요 조회 패턴을 확인한다.

BAD — 비효율 가능성이 있는 SQL 또는 인덱스 상황

-- 실습 스크립트의 서로 다른 구간에 아래 두 DDL이 존재
-- 둘은 키 컬럼 및 순서가 같은 인덱스 정의
CREATE INDEX tr_ord_big_x04
    ON tr_ord_big (mbr_id, ord_dtm);

CREATE INDEX tr_ord_big_x12
    ON tr_ord_big (mbr_id, ord_dtm);

SQL

복사

GOOD — 개선 후보 또는 비교할 SQL

-- 먼저 현재 DB에 실제 존재하는 인덱스를 확인
SELECT i.indexname, i.indexdef
FROM   pg_indexes AS i
WHERE  i.schemaname = current_schema()
  AND  i.tablename = 'tr_ord_big'
ORDER BY i.indexname;

-- 추가 판단에 사용할 대표 검색 SQL
SELECT o.mbr_id, MAX(o.ord_dtm) AS last_ord_dtm
FROM   tr_ord_big AS o
WHERE  o.mbr_id = 'M34273'
GROUP BY o.mbr_id;

SQL

복사

예상 실행계획 — 실제 측정 아님

[인덱스가 2개 중복 생성되어도 SELECT의 결과는 같음]
Aggregate
  -> Index Only Scan / Index Scan using (선택된 적절한 인덱스)

[반면 관리·쓰기 측면]
추가 인덱스마다 별도 저장 공간 필요
INSERT / UPDATE / DELETE 시 인덱스 유지 비용 증가
VACUUM 및 REINDEX 등 관리 부담도 커질 수 있음
→ 이것은 EXPLAIN의 SELECT 노드 하나로 판단할 수 없음

Plain Text

복사

기억할 이유: 하나의 SELECT를 빠르게 하려고 인덱스를 늘리면 다른 쿼리와 쓰기 작업까지 영향을 받는다. 기존 인덱스 중복 여부와 업무 전체의 사용 빈도를 우선 확인한다.

주의·예외: x04와 x12는 실제 SQL 스크립트의 서로 다른 실습 챕터에 등장한다. 실제 DB에 두 인덱스가 동시에 존재한다고 단정하지 않는다. 임의로 CREATE/DROP 하지 않고 DB 관리 절차에 따라 검증한다. 교재 역시 여러 SQL의 패턴과 CUD 비용을 고려하라고 강조한다.

참고한 자료: PG PDF p.134~136, [SQL-XPERF2-CX5~CX7], SQL 스크립트의 x04/x12 DDL.

인덱스 점검에 사용할 기본 SQL

-- 1. 실제 인덱스 목록·정의 확인
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  schemaname = current_schema()
  AND  tablename = 'tr_ord_big'
ORDER BY indexname;

-- 2. SQL 실행 없이 옵티마이저의 계획만 확인
EXPLAIN (COSTS OFF)
SELECT o.ord_no, o.ord_dtm
FROM   tr_ord_big AS o
WHERE  o.mbr_id = 'M00019'
  AND  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01';

-- 3. 실제 실습 DB에서 SELECT를 실행하며 측정할 경우에만
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.ord_dtm
FROM   tr_ord_big AS o
WHERE  o.mbr_id = 'M00019'
  AND  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01';

SQL

복사

계획을 볼 때의 우선순위: (1) 검색 범위가 좁은가, (2) Index Cond와 Filter가 무엇인가, (3) 인덱스 리프를 많이 읽었는가, (4) 힙 접근과 Heap Fetches가 있는가, (5) Sort가 필요한가, (6) 반환 행이 많아서 Seq Scan이 타당한가.

EXPLAIN ANALYZE는 실제 SQL을 실행한다. 본 SECTION에서는 수치가 없으므로 '빠르다/느리다'를 확정하지 않고, 확인할 단서를 학습한다.

개발 전 체크리스트

SQL에 적합한 인덱스가 실제로 존재하는가?

Index Cond로 검색하는 조건과 Filter로 남은 조건을 구분했는가?

복합 인덱스에서 동등 조건과 범위 조건의 순서를 확인했는가?

후행 컬럼의 Index Cond만 보고 검색 범위가 좁다고 착각하지 않았는가?

복합 인덱스의 선두 컬럼이 없는 SQL을 점검했는가?

대량 조회에서 Seq Scan을 무조건 오류로 판단하지 않았는가?

INCLUDE 적용 시 읽기 이익과 쓰기 비용을 함께 검토했는가?

Partial Index의 조건과 실제 SQL이 부합하는가?

Bitmap 방식이 ORDER BY 정렬을 보장하지 않는다는 점을 기억하는가?

ORDER BY LIMIT을 효율적으로 지원할 인덱스가 있는가?

기존 인덱스 중복 및 다른 주요 SQL의 영향까지 확인했는가?

참고 자료와 범위

교재·실습 코드에서 확인한 내용

•

StartUPTuning_PG_20250616.pdf: p.73~88(스캔 방식), p.95~104(Index Cond/Filter), p.107~130(단일·복합 인덱스와 선두 누락), p.132~136(선택성 및 여러 SQL 고려), p.147~149(INCLUDE/Index Only Scan), p.159~166(정렬·Bitmap·LIMIT)

•

StartUPTuning_PG_SQL_Script.sql: SQL-XPERF1-1, SQL-XPERF2-1, SQL-XPERF2-선두누락, SQL-XPERF3-COV1, SQL-XPERF3-PARTIAL, SQL-ORDER-LMT1와 위에 표시한 CREATE INDEX 정의

•

: 인덱스 관련 120건으로 분류되어 있으나, '인덱스'라는 표기만 있는 사례는 구체적 추가·활용 여부를 단정할 수 없다.

•

SECTION 01. SQL REMINDER 소개: BIG 계열 공통 ERD

교육용으로 구성한 사항

•

신규 인덱스 tr_ord_big_review01, 다중 Bitmap 결합, 인덱스별 예상 실행계획은 실행하지 않은 비교 시나리오다.

•

자료에 있는 실제 실행계획의 숫자를 복제하는 대신 계획 형태만 설명한다. 각 인덱스·조건의 정확한 성능은 실습 환경에서 검증해야 한다.

•

이 섹션은 인덱스를 어떻게 설계·생성하는 DBA 강의가 아니라, 개발자가 자신의 SQL에 어떤 인덱스가 필요한지 판단하고 실행계획을 읽는 데 필요한 핵심 규칙만 다룬다.

그림 제작 메모 — 나중에 그림 삽입, 현재 미제작

•

그림 A: Index Cond와 Filter — shop_id로 찾아낸 후보 행이 ord_st, pkup_dtm에 의해 제거되는 흐름

•

그림 B: x03과 x04의 인덱스 리프 범위 비교 — (ord_dtm,mbr_id)는 날짜 구간의 많은 키를 읽고 (mbr_id,ord_dtm)는 회원 구간부터 탐색하는 차이

•

그림 C: Index Scan·Index Only Scan·Bitmap Heap Scan — 힙 접근 여부, Bitmap의 블록 묶음 접근을 나란히 비교

•

그림 D: ORDER BY LIMIT과 정렬 대응 인덱스 — x02 + Sort 대 x07 + Index Scan Backward를 같은 결과 집합 기준으로 비교

SECTION 04 한 줄 요약: 인덱스는 '있다/없다'가 아니라, 조건·키 순서·읽는 범위·정렬까지 맞아야 효율적이다.