SECTION 12 원문 백업

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

원본:

좋은 SQL을 작성한 뒤에는, 실행계획으로 스스로 점검하자.

모든 실행계획을 전문적으로 해석할 필요는 없다. 불필요하게 많이 읽는가? 반복 실행하는가? 정렬·집계가 큰가? 세 질문부터 확인하자.

SECTION 12는 SQL REMINDER의 최종 실전 점검 단계다. 개발자가 SQL을 작성한 후 SECTION 01. SQL REMINDER 소개을 대상으로 실행계획을 읽고, SECTION 02~11에서 배운 작성 규칙 중 무엇을 다시 확인할지 결정하는 절차를 제공한다. DBA 수준의 전면적인 실행계획 분석보다 개발자의 1차 자가 진단을 목표로 한다.

실행계획 예제의 상태와 안전 원칙

•

이 페이지의 BAD/GOOD SQL과 계획 노드는 교육용 예제 또는 예상 실행계획이다. 작성 과정에서 PostgreSQL을 실행하거나 실제 성능을 측정하지 않았다.

•

cost, actual rows, loops, Buffers, 실행시간 등의 실측 수치를 임의로 만들지 않는다.

•

EXPLAIN은 옵티마이저의 추정 계획을 보여준다. EXPLAIN ANALYZE는 실제로 SQL을 실행하며, DML이나 잠금 쿼리에는 부작용이 있을 수 있다. 개발·테스트 환경에서 안전성을 확인한 후 사용한다.

•

같은 SQL도 실제 인덱스·통계·데이터량·PostgreSQL 버전·파라미터 값에 따라 달라지므로, 아래의 Index Scan·Seq Scan 노드들은 조건부 가능성을 나타낸다.

•

상위 실행계획 노드의 actual time에서 자식의 time을 단순히 빼 '자체 소비 시간'으로 확정하지 않는다. 노드 시간은 반복·부모 자식 측정 범위·오버헤드가 얽혀 있으므로 병목 단서로 사용한다.

먼저 기억할 실행계획 용어

항목	읽는 방법	흔한 오해
cost	옵티마이저의 상대적 추정 비용	밀리초 단위 실행시간이 아님
rows	해당 노드가 예상하는 출력 행 수	실제 조회 건수가 아님
actual rows	실제 실행 시 루프 1회당 평균 출력 행 수	반드시 총 출력 건수가 아님
loops	해당 노드가 실행된 횟수	무조건 큰 값이 오류는 아님
Index Cond	인덱스에서 평가하는 검색 조건	모든 Index Cond가 탐색 범위를 똑같이 줄이지는 않음
Filter	스캔 결과 중 추가로 검사하는 조건	Filter가 있으면 무조건 비효율인 것은 아님
Rows Removed by Filter	필터 조건에 의해 버려진 후보 행 수(루프당 평균)	테이블 전체 행 수와 동일하지 않음
Buffers: shared hit/read	공유 버퍼 적중/읽기 요청 페이지 정보	read를 물리 디스크 직접 읽기와 항상 동일시할 수 없음
Sort Method	정렬 방식·메모리/디스크 사용의 단서	Sort 노드만으로 반드시 병목이라고 단정 불가

실제 수치가 존재하는 계획을 볼 때: 노드의 actual rows와 Rows Removed는 반복된 계획에서는 루프당 평균으로 표시될 수 있으므로, 누적 처리량을 해석할 때 loops와 함께 본다.

이번 섹션의 10개 자가 점검 규칙

ID	구분	확인할 질문
12-01	MUST	EXPLAIN과 EXPLAIN ANALYZE의 차이를 아는가?
12-02	MUST	Seq Scan에서 불필요하게 많은 후보 행을 읽는가?
12-03	MUST	Index Cond와 Filter가 적절한가?
12-04	CHECK	Index Only Scan에서 Heap Fetches가 발생하는가?
12-05	CHECK	예상 rows와 actual rows의 차이가 큰가?
12-06	CHECK	Nested Loop의 Driven 노드가 지나치게 반복되는가?
12-07	MUST	JOIN 전후의 후보·반환 행 수가 업무 의도와 맞는가?
12-08	CHECK	Sort·Aggregate가 예상보다 많은 데이터를 처리하는가?
12-09	CHECK	Buffers를 보고 실제 데이터 접근량을 평가하는가?
12-10	MUST	수정 전후 결과 동등성과 계획을 함께 검증했는가?

REMINDER 12-01. EXPLAIN과 EXPLAIN ANALYZE를 구분하자

MUST · 상황: 회원 M00019의 주문 조회 SQL이 실제로 어느 인덱스를 사용할지 알고 싶다.

BAD — EXPLAIN의 예상 계획을 측정 결과로 해석

EXPLAIN
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

복사

GOOD — 계획 확인과 실제 측정을 목적에 맞게 구분

-- 1. 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';

-- 2. 실습 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

복사

예상 실행계획

EXPLAIN (x04(mbr_id,ord_dtm)이 선택되는 경우)
Index Scan / Index Only Scan using tr_ord_big_x04
  Index Cond: (mbr_id = 'M00019' AND ord_dtm 범위)
  [cost 및 rows는 추정치]

EXPLAIN ANALYZE (실제 실행 시에만 표시될 추가 항목)
Index Scan / Index Only Scan ...
  actual time = [측정값], actual rows = [측정값], loops = [측정값]
  Buffers: shared hit/read = [측정값]
Planning Time / Execution Time = [측정값]
  [실측 예시 수치 없음]

Plain Text

복사

기억할 이유: EXPLAIN은 비용 기반 옵티마이저의 예상이고, ANALYZE는 실제 SQL 수행 결과다. COSTS OFF는 예상 비용 표시만 생략한다.

주의: EXPLAIN ANALYZE의 Execution Time은 클라이언트가 모든 결과를 가져와 화면에 표시하는 시간과 다를 수 있다. DML은 실제로 변경되므로 안전하지 않은 운영 실행을 피한다.

교재 근거: PG PDF p.33~40, SQL-EXPLAIN, SQL-EXPLAIN-OPER, SQL-EXPLAIN-ANLY, SQL-EXPLAIN-IO.

REMINDER 12-02. Seq Scan은 이름보다 읽는 행과 필터를 보자

MUST · 상황: 하루 주문만 필요한데 테이블의 대부분을 읽는지를 확인한다.

BAD — 날짜 컬럼 변환으로 인덱스 접근 가능성을 낮추는 SQL

SELECT COUNT(*) AS ord_cnt
FROM tr_ord_big AS o
WHERE TO_CHAR(o.ord_dtm, 'YYYYMMDD') = '20230301';

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이 합리적일 수 있다.

교재 근거: PG PDF p.45~47의 tr_ord_big Seq Scan·Filter·Rows Removed 분석 및 p.142 컬럼 변환 사례.

REMINDER 12-03. Index Cond와 Filter를 각각 읽자

MUST · 상황: 특정 매장의 특정 기간 픽업 주문 검색에서 후보 행이 과도한지 확인한다.

BAD 후보 — 매장 인덱스만 사용되어 다른 조건이 Filter에 남는 상황

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 후보 — 검색 조건과 맞는 복합 인덱스가 있는 상황

-- 교육용 인덱스 검토안. 실제 생성·적용하지 않았음
-- CREATE INDEX tr_ord_big_review12
-- ON tr_ord_big (shop_id, ord_st, pkup_dtm);

-- 조회 SQL은 위와 동일
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

복사

예상 실행계획

BAD (x02(shop_id) 선택 시)
Index Scan using tr_ord_big_x02
  Index Cond: (shop_id = 'S281')
  Filter: (ord_st = 'PKUP' AND pkup_dtm 범위)
  Rows Removed by Filter: [측정 시 확인]

GOOD (review12 복합 인덱스 선택 시)
Index Scan using tr_ord_big_review12
  Index Cond: (shop_id = 'S281'
               AND ord_st = 'PKUP'
               AND pkup_dtm 범위)
  → 더 좁은 인덱스 검색이 가능할 수 있음

Plain Text

복사

기억할 이유: Index Cond를 사용하더라도 많은 후보를 찾은 뒤 Filter로 제외한다면 접근 경로를 재검토할 이유가 된다. 검색 범위를 줄이는 선두 키와 인덱스에서 검사하는 후행 키의 역할도 구분한다.

주의: Filter가 있다고 반드시 잘못된 계획이 아니다. 인덱스 신규 생성은 전체 업무 SQL 및 DML 비용을 고려해야 하며, 이미 다른 인덱스를 사용할 수도 있다.

교재 근거: PG PDF p.95~104의 SQL-XPERF1-1 및 Index Cond/Filter 설명을 BIG 모델로 재구성.

REMINDER 12-04. Index Only Scan에서도 Heap Fetches를 확인하자

CHECK · 상황: 닉네임 Moon인 회원의 회원 ID와 닉네임만 필요하다.

BAD 후보 — 필요하지 않은 모든 컬럼까지 출력

SELECT m.*
FROM ms_mbr_big AS m
WHERE m.nick_nm = 'Moon';

SQL

복사

GOOD 후보 — 인덱스에 포함된 컬럼만 조회

-- 제공 실습 인덱스 예:
-- ms_mbr_big_x01 (nick_nm) INCLUDE (mbr_id)
SELECT m.mbr_id, m.nick_nm
FROM ms_mbr_big AS m
WHERE m.nick_nm = 'Moon';

SQL

복사

예상 실행계획

BAD (x01을 선택해도 필요한 나머지 컬럼이 인덱스 밖에 있다면)
Index Scan using ms_mbr_big_x01 on ms_mbr_big
  Index Cond: (nick_nm = 'Moon')
  → 테이블의 나머지 컬럼을 읽기 위한 접근

GOOD (x01을 선택할 때 가능한 형태)
Index Only Scan using ms_mbr_big_x01 on ms_mbr_big
  Index Cond: (nick_nm = 'Moon')
  Heap Fetches: [실측 시 확인]
  → 인덱스만으로 출력 컬럼을 확보할 수 있지만
    visibility map 상태에 따라 힙 접근 가능

Plain Text

복사

기억할 이유: Index Only Scan은 인덱스에 모든 조회 컬럼이 있다는 의미와 함께 힙 가시성 확인을 최소화할 가능성을 제공한다. 그러나 실제로 힙을 전혀 보지 않았는지는 실행계획의 Heap Fetches를 확인해야 한다.

주의: Heap Fetches가 0보다 크다고 항상 문제는 아니다. 실습 테이블의 UPDATE·VACUUM·visibility map 상태에 따라 달라진다. BAD와 GOOD은 필요한 출력 컬럼 범위가 달라지므로 업무에서 두 컬럼만 필요한 경우에 비교한다.

교재 근거: PG PDF p.81~82, SQL-XPERF3-COV2 및 ms_mbr_big_x01 커버링 인덱스 실습.

REMINDER 12-05. 추정 rows와 실제 rows가 크게 다르면 통계와 조건을 확인하자

CHECK · 상황: 회원 상태 INAC는 소수, ACTV는 다수일 수 있다. 옵티마이저가 이 분포를 제대로 추정하는지 확인한다.

BAD — 추정 rows를 실제 건수처럼 해석

-- 실제 SQL 수행 없이 통계 기반 추정만 확인
EXPLAIN
SELECT m.mbr_id, m.mbr_gd
FROM ms_mbr_big AS m
WHERE m.mbr_st = 'INAC';

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행만 표시되는 노드와 집계 아래 스캔 노드를 구분해서 본다.

교재 근거: PG PDF p.33, p.38~40의 추정/실제 구분과 p.284 값 분포·pg_stats 실습, SQL-맛튜닝-값분포1/2.

REMINDER 12-06. Nested Loop의 Driven 반복 횟수를 확인하자

CHECK · 상황: GOLD 회원마다 2023년 3월 주문금액 합계를 표시한다.

BAD 후보 — 외부 회원 수가 많은데 매번 스칼라 SUM을 수행

SELECT m.mbr_id,
       (SELECT SUM(o.ord_amt)
        FROM tr_ord_big AS o
        WHERE o.mbr_id = m.mbr_id
          AND o.ord_dtm >= TIMESTAMP '2023-03-01'
          AND o.ord_dtm < TIMESTAMP '2023-04-01') AS ord_amt
FROM ms_mbr_big AS m
WHERE m.mbr_gd = 'GOLD';

SQL

복사

GOOD 후보 — 기간 주문을 먼저 집계하고 회원과 LEFT JOIN

SELECT m.mbr_id, a.ord_amt
FROM ms_mbr_big AS m
LEFT JOIN (
  SELECT o.mbr_id, SUM(o.ord_amt) AS ord_amt
  FROM tr_ord_big AS o
  WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
  GROUP BY o.mbr_id
) AS a ON a.mbr_id = m.mbr_id
WHERE m.mbr_gd = 'GOLD';

SQL

복사

예상 실행계획

BAD (상관 서브쿼리가 그대로 남는 경우)
Seq Scan on ms_mbr_big  Filter: (mbr_gd = 'GOLD')
  SubPlan 1
    -> Aggregate
         -> Index Scan on tr_ord_big
              Index Cond: (mbr_id = 외부 회원 AND ord_dtm 범위)
  → 회원마다 SubPlan이 반복될 수 있음
  → 실제 실행되면 SubPlan의 loops가 확인 지점

GOOD (기간 주문 집계가 선택되는 경우)
Hash Left Join
  -> GOLD 회원 조회
  -> Hash
       -> HashAggregate / GroupAggregate (mbr_id)
            -> 기간 내 주문 스캔
  → 주문 집합을 한 번 모아 집계한 후 조인할 가능성

Plain Text

복사

기억할 이유: Nested Loop는 외부(Driving) 한 행마다 내부(Driven)를 재탐색할 수 있다. loops가 높은 내부 Scan이나 SubPlan은 반복 비용을 확인할 우선 후보이다.

주의: loops가 크더라도 한 번의 인덱스 검색이 매우 짧고 반환량이 적다면 문제가 아닐 수 있다. PostgreSQL이 BAD를 다른 JOIN으로 최적화하거나 Memoize로 반복 비용을 완화할 수도 있다. GOOD 역시 외부 회원이 몇 명 안 된다면 전체 주문 선집계가 오히려 비효율적일 수 있다. actual time은 반복 노드에서 루프당 평균값으로 표시될 수 있어 노드 시간을 단순 비교하지 않는다.

교재 근거: PG PDF p.38 loops 설명, p.194~202 SQL-JOIN성능-NL1의 Driven 인덱스, p.229~232 스칼라 반복·선집계 사례.

REMINDER 12-07. JOIN 전후의 행 수가 업무 의도와 맞는지 보자

MUST · 상황: 회원 M00019의 전체 주문금액과 이벤트 응모 건수를 함께 보여준다.

BAD — 주문(1:N)과 응모(1:N)를 한꺼번에 펼쳐 결합

SELECT m.mbr_id,
       SUM(o.ord_amt) AS ord_amt,
       COUNT(e.entry_no) AS entry_cnt
FROM ms_mbr_big AS m
LEFT JOIN tr_ord_big AS o ON o.mbr_id = m.mbr_id
LEFT JOIN tr_event_entry_big AS e ON e.mbr_id = m.mbr_id
WHERE m.mbr_id = 'M00019'
GROUP BY m.mbr_id;

SQL

복사

GOOD — 주문·응모의 집계 단위를 맞춘 뒤 결합

SELECT m.mbr_id,
       COALESCE(o.ord_amt, 0) AS ord_amt,
       COALESCE(e.entry_cnt, 0) AS entry_cnt
FROM ms_mbr_big AS m
LEFT JOIN (
  SELECT mbr_id, SUM(ord_amt) AS ord_amt
  FROM tr_ord_big
  WHERE mbr_id = 'M00019'
  GROUP BY mbr_id
) AS o ON o.mbr_id = m.mbr_id
LEFT JOIN (
  SELECT mbr_id, COUNT(*) AS entry_cnt
  FROM tr_event_entry_big
  WHERE mbr_id = 'M00019'
  GROUP BY mbr_id
) AS e ON e.mbr_id = m.mbr_id
WHERE m.mbr_id = 'M00019';

SQL

복사

예상 실행계획

BAD (가능한 계획)
HashAggregate / GroupAggregate
  -> Hash Left Join
       -> Hash Left Join
            -> 회원 1행
            -> 회원 주문 N행
       -> 회원 응모 M행
  → 같은 회원의 주문 × 응모 조합이 N×M 후보 행으로 증폭 가능
  → JOIN 출력 actual rows와 집계 직전 행 수 확인

GOOD (가능한 계획)
Nested Loop Left Join / Hash Left Join
  -> 회원 1행
  -> 주문 회원별 Aggregate (최대 1행)
  -> 응모 회원별 Aggregate (최대 1행)
  → 독립된 측정값을 행 증폭 없이 결합

Plain Text

복사

기억할 이유: 실행계획에서 JOIN의 반환 actual rows가 예상보다 커진다면 조인 조건이나 1:N 관계를 확인한다. 이는 단순한 성능 문제가 아니라 합계와 COUNT까지 잘못될 수 있는 결과 정확성 문제다.

주의: Rows Removed by Join Filter가 높을 때도 불필요한 조합 검사를 의심할 수 있지만, 이 수치가 나오지 않아도 JOIN 결과가 증폭될 수 있다. 이 예시의 GOOD은 주문·응모 모두 회원 기준 별도 집계가 업무 요구사항임을 전제로 한다. entry_no는 원본 BIG ERD의 복합키 일부다.

교재 및 사례 근거: PG PDF p.35~38의 JOIN 오퍼레이션·Rows Removed by Join Filter 설명. 204건 튜닝 사례 0608_06, 0910_12의 M:M 조인 문제를 교육용으로 재구성했다.

REMINDER 12-08. Sort와 Aggregate는 입력 행 수·메모리를 함께 보자

CHECK · 상황: 회원별 주문 목록 중 기간 내 첫 30건만 필요하다.

BAD — 정렬하고 많은 행을 그대로 반환

SELECT o.ord_no, o.ord_dtm, o.mbr_id, o.ord_amt
FROM tr_ord_big AS o
WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND o.ord_dtm < TIMESTAMP '2023-04-01'
ORDER BY o.ord_dtm, o.mbr_id, o.ord_no;

SQL

복사

GOOD — 필요한 30건만 반환하고 정렬 인덱스도 확인

-- 원본 실습 인덱스 x19: (ord_dtm, mbr_id, ord_no)
SELECT o.ord_no, o.ord_dtm, o.mbr_id, o.ord_amt
FROM tr_ord_big AS o
WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND o.ord_dtm < TIMESTAMP '2023-04-01'
ORDER BY o.ord_dtm, o.mbr_id, o.ord_no
LIMIT 30;

SQL

복사

예상 실행계획

BAD (정렬 인덱스가 선택되지 않은 경우)
Sort
  Sort Key: ord_dtm, mbr_id, ord_no
  Sort Method: quicksort 또는 external merge [실측 시 확인]
  -> Bitmap Heap Scan / Seq Scan (기간 주문)
  → 기간 전체 행을 정렬·반환할 가능성

GOOD (정렬 대응 인덱스 x19가 선택되는 경우)
Limit
  -> Index Scan using tr_ord_big_x19
       Index Cond: (ord_dtm 범위)
  → 정렬 생략 및 조기 중단 가능

GOOD (인덱스 미사용이면)
Limit
  -> Sort (Top-N heapsort 가능)
       -> 기간 주문 스캔
  → LIMIT이 있어도 많은 행을 읽는 상황 가능

Plain Text

복사

기억할 이유: Sort와 Aggregate의 존재보다 몇 행이 해당 단계에 입력되고, 메모리 내 처리인지 임시 파일 사용이 발생했는지를 우선 확인한다. Sort Method, Disk, Batches, temp read/written 같은 단서가 유용하다.

주의: LIMIT 30은 전체 주문 목록이 필요한 업무에 무작정 넣을 수 없다. GROUP BY의 HashAggregate는 정렬 없이 집계할 수 있지만 메모리 부족 시 배치·임시 I/O가 발생할 수 있다. Incremental Sort는 앞부분 정렬을 활용할 수 있으며 항상 잘못된 노드가 아니다.

교재 근거: PG PDF p.156~166 SQL-ORDER-LOAD, SQL-ORDER-LMT1, p.288~293 SQL-PAGE-LIMIT, tr_ord_big_x19.

REMINDER 12-09. Buffers를 통해 실제 데이터 접근량을 확인하자

CHECK · 상황: 회원등급 PLAT의 주문 총금액을 조회할 때 메모리 적중과 페이지 읽기량을 알고 싶다.

BAD — 추정 계획만 보고 실제 I/O가 적다고 단정

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

복사

GOOD — 테스트 환경에서 실제 실행한 BUFFERS를 확인

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가 많아도 반복 접근 문제는 여전히 존재할 수 있다.

교재 근거: PG PDF p.39~40, SQL-EXPLAIN-IO의 BUFFERS 설명. 페이지 접근 수치를 통한 병목 확인은 본 강의에서 사용하는 자가 진단 관점으로 정리했다.

REMINDER 12-10. 수정 전후 결과와 실행계획을 모두 비교하자

MUST · 상황: 주문일 조건에서 TO_CHAR를 제거해 원본 ord_dtm 범위 비교로 바꾸려 한다.

BAD — 변경 전 SQL

SELECT o.ord_no, o.ord_dtm, o.ord_amt
FROM tr_ord_big AS o
WHERE TO_CHAR(o.ord_dtm, 'YYYYMMDD') = '20230301';

SQL

복사

GOOD — 변경 후 SQL

SELECT o.ord_no, o.ord_dtm, o.ord_amt
FROM tr_ord_big AS o
WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND o.ord_dtm < TIMESTAMP '2023-03-02';

SQL

복사

예상 실행계획

BAD (가능한 형태)
Seq Scan / Index Only Scan on tr_ord_big
  Filter: (TO_CHAR(ord_dtm, 'YYYYMMDD') = '20230301')
  → 많은 후보 행 확인 위험

GOOD (날짜 인덱스를 사용할 때 가능한 형태)
Index Scan using tr_ord_big_x01
  Index Cond: (ord_dtm >= ... AND ord_dtm < ...)
  → 날짜 범위를 인덱스로 제한할 가능성

Plain Text

복사

우선 수행할 결과 동등성 검증 SQL

-- 같은 SELECT 문장 안에서 결과 값과 중복 개수를 검증
WITH old_result AS (
  SELECT o.ord_no, o.ord_dtm, o.ord_amt
  FROM tr_ord_big AS o
  WHERE TO_CHAR(o.ord_dtm, 'YYYYMMDD') = '20230301'
),
new_result AS (
  SELECT o.ord_no, o.ord_dtm, o.ord_amt
  FROM tr_ord_big AS o
  WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-03-02'
)
SELECT 'OLD_ONLY' AS diff, d.*
FROM (
  SELECT * FROM old_result
  EXCEPT ALL
  SELECT * FROM new_result
) AS d
UNION ALL
SELECT 'NEW_ONLY' AS diff, d.*
FROM (
  SELECT * FROM new_result
  EXCEPT ALL
  SELECT * FROM old_result
) AS d;

SQL

복사

검증 순서: (1) 업무 결과가 동일한지 확인 → (2) 기존·변경 SQL의 EXPLAIN 비교 → (3) 테스트 환경의 EXPLAIN (ANALYZE, BUFFERS) 실측 비교 → (4) 값 분포·동시 실행·인덱스 비용 검토.

기억할 이유: 예상 실행계획이 좋아 보이더라도 결과가 달라지면 튜닝 성공이 아니다. EXCEPT ALL은 중복 행 개수까지 비교할 수 있지만 ORDER BY의 순서는 별도 검증해야 한다.

주의·예외: 이 예제의 두 조건은 ord_dtm이 일반적인 timestamp without time zone일 때 해당 로컬 달력일의 주문을 의미한다. timestamptz나 업무 타임존이 다른 시스템은 시간대 기준을 먼저 맞춰야 한다. 또한 EXCEPT ALL도 많은 결과를 처리할 수 있으므로 대용량 운영 DB에 무작정 실행하지 않는다.

교재 근거: PG PDF p.142 SQL-XPERF3-NOGOOD1, p.39~40 실제 실행계획 확인. 변경 전후 동등성 검증 방식은 PDF p.231의 결과 비교 실습을 참고하되, 위 EXCEPT ALL 양방향 템플릿은 교육용으로 보강했다.

실무용: SQL 작성 후 5분 자가 점검

1단계 — SQL 결과의 의미를 먼저 확인한다.

업무 필수 조건, 조인 키, NULL, 집계 단위, ORDER BY·LIMIT, 결과 중복을 확인한다. 성능보다 결과 정확성이 먼저다.

2단계 — EXPLAIN으로 구조를 빠르게 읽는다.

스캔이 어떤 테이블부터 시작하는지, JOIN이 어떻게 연결되는지, Aggregate, Sort, Limit, SubPlan이 어디에 있는지 본다. 계획은 트리이며 단순한 일렬 실행 순서를 뜻하지 않는다. 특히 Nested Loop는 두 자식의 Driving/Driven 관계로 본다.

3단계 — 불필요한 데이터 접근 단서 4개를 확인한다.

Seq Scan에서 대부분 버리지는 않는가? Index Cond 없이 Filter만 남은 조건은 없는가? JOIN 후 행이 불어나지는 않는가? 서브쿼리가 여러 번 반복되는가?

4단계 — 실측이 가능하면 비용이 아닌 실제 행·I/O를 본다.

테스트 환경에서만 EXPLAIN (ANALYZE, BUFFERS)를 활용한다. actual rows × loops의 의미를 이해하고, Rows Removed, Sort Method, Heap Fetches, shared hit/read, temp read/written을 검토한다. 노드의 자체 소비 시간은 절대값이 아니라 병목의 단서라는 관점을 유지한다.

5단계 — 수정 후 결과가 같고 실제 접근량이 줄었는지 확인한다.

원본·변경 결과 비교, 실제 사용 인덱스·계획 비교, 스캔·정렬·조인 처리량, 운영 부하와 인덱스 변경 영향까지 확인한다.

어떤 문제가 보이면 어느 섹션을 다시 볼까?

실행계획의 단서	먼저 확인할 작성 규칙
Filter로 지나치게 많은 행 제거
Index Cond는 있지만 인덱스 범위가 넓음
JOIN 후 actual rows 과다 또는 반복
SubPlan·집계가 외부 행 수만큼 반복	/
CTE Scan으로 큰 중간 결과 재사용
최신 한 건인데 대량 정렬·집계
Sort·HashAggregate·Limit 위치가 불리함
파티션 전체 스캔·통계/트랜잭션 문제

개발 전 최종 체크리스트

조회하는 컬럼·WHERE 조건이 실제 업무 요구사항에 맞는가?

컬럼 가공·불필요한 형변환으로 Index Cond가 사라지지 않았는가?

업무상 필요한 회원·매장·기간 조건이 포함됐는가?

어떤 인덱스가 실제로 존재하고 선택되는지 확인했는가?

Seq Scan·Index Scan의 반환 행 수와 필터 제거 행을 살펴봤는가?

Index Only Scan에서 Heap Fetches가 필요한지 확인했는가?

추정 rows를 실제 처리 행 수로 오해하지 않았는가?

반복 노드의 loops와 루프당 출력 행 수를 함께 봤는가?

JOIN 키·1:N 관계·집계 값이 정확한가?

JOIN 후 후보 행 수가 비정상적으로 증폭되지 않는가?

SUBPLAN·LATERAL·스칼라 조회가 불필요하게 반복되지 않는가?

Sort·Hash·Aggregate의 입력량과 임시 I/O를 점검했는가?

ORDER BY에 고유한 동률 기준이 있으며 LIMIT 위치가 맞는가?

Buffers를 보고 반복 접근·큰 스캔의 단서를 확보했는가?

SQL을 변경했다면 결과·NULL·중복·정렬이 동일한가?

성능을 측정했다면 테스트 환경과 조건·통계·인덱스를 기록했는가?

실행계획의 차이를 단일 수치가 아닌 업무에 필요한 데이터 접근량으로 설명할 수 있는가?

리뷰 결과 기록용 템플릿

아래 항목은 개발자가 점검 후 직접 채우는 기록 양식이다. 예시 SQL에 대해 실제 값을 기입한 것이 아니다.

[SQL REMINDER — 실행계획 자가 점검 기록]
SQL_ID        : <프로젝트 SQL ID>
업무 목적     : <조회 대상 / 결과 단위 / 기대 건수>
결과 정확성   : <확인 / 확인 필요>
실제 인덱스   : <사용 인덱스와 선두 컬럼>
주요 접근 노드: <Seq Scan / Index Scan / Index Only Scan / Bitmap 등>
조건 분류     : <Index Cond / Filter / Join Filter>
반복 접근     : <SubPlan / Nested Loop / loops>
정렬·집계     : <Sort / Aggregate / temp I/O>
실측 여부     : <예상 계획만 / 실측 완료>
I/O 단서      : <Buffers 관련 확인 결과>
개선 후보     : <SQL 변경 / 인덱스 검토 / 통계 확인 / 조치 불필요>
결과 재검증   : <EXCEPT ALL / 집계 / 정렬 / NULL / 중복>
최종 판단     : <유지 / 수정 후 재점검 / DBA 검토 요청>

Plain Text

복사

자료 근거 및 보강 범위

기존 PostgreSQL 강의에서 직접 확인한 실행계획 해석 요소

•

StartUPTuning_PG_20250616.pdf p.33~40 — EXPLAIN과 EXPLAIN ANALYZE, 노드·자식 관계, Nested Loop·Hash Join의 계획 해석, rows, loops, BUFFERS.

•

같은 교재 p.45~47 — BIG 테이블 Seq Scan의 Filter·Rows Removed by Filter와 후보 행의 의미.

•

같은 교재 p.81~82, p.95~104 — Index Only Scan, Index Cond와 Filter.

•

같은 교재 p.194~202, p.229~232 — NL의 반복 실행과 서브쿼리 집계.

•

같은 교재 p.156~166, p.288~293 — 정렬, LIMIT과 인덱스의 조합.

•

StartUPTuning_PG_SQL_Script.sql — SQL-EXPLAIN, SQL-EXPLAIN-OPER, SQL-EXPLAIN-ANLY, SQL-EXPLAIN-IO, SQL-XPERF1-1, SQL-XPERF3-COV2, SQL-JOIN성능-NL1, SQL-ORDER-LOAD, SQL-PAGE-LIMIT.

•

 — 조인·서브쿼리·인덱스·페이징 문제 유형의 실제 프로젝트 기록. 본문의 SQL은 개별 사례의 원본 SQL 그대로는 아니다.

교육용으로 추가·재구성한 내용

•

12-01~12-10의 BAD/GOOD SQL과 예상 계획은 BIG 계열 모델로 교육용 재구성했다. 수치가 있는 실제 계획을 실행·복제하지 않았다.

•

actual rows·loops의 루프당 평균, Index Only Scan의 visibility map 영향, shared read와 OS 파일 캐시의 구분, EXCEPT ALL 비교 양식은 실행계획 오독을 줄이기 위해 덧붙인 PostgreSQL 일반 동작 설명이다.

•

추가 확인이 필요한 실제 인덱스·테이블 통계·파라미터 바인딩·업무 결과는 임의의 결론으로 확정하지 않았다.

그림 제작 메모 — 실제 이미지 미삽입

•

그림 A · 실행계획 트리 읽기: Root → Join → 자식 Scan의 트리 구조, 자식·부모·형제 노드 구분. 정확한 시간순서도를 의미하지 않는다는 안내 포함

•

그림 B · 후보 행과 Filter: Seq Scan에서 후보 행을 읽고 Filter로 제거하여 최종 반환 행을 만드는 흐름. Rows Removed 의미 강조

•

그림 C · Index Cond vs Filter: 좁은 인덱스 검색과 넓은 후보 후 추가 Filter의 차이

•

그림 D · NL Join과 loops: Driving 외부 행 N건에서 Driven 인덱스를 반복 호출하는 모습을 루프 화살표로 표현

•

그림 E · 메모리/디스크 Sort와 Buffers: quicksort, external merge, shared hit/read, temp I/O를 서로 다른 측정 지표로 표시

•

그림 F · 자가 점검 5단계: 결과 확인 → EXPLAIN → 과다 접근 단서 → 안전한 실측 → 결과·성능 재검증

SQL REMINDER 최종 메시지: 좋은 SQL은 실행계획에서 우연히 만들어지지 않는다. 작성할 때 기본 규칙을 지키고, 작성한 뒤 접근량과 결과 정확성을 스스로 점검하자.