SECTION 10 원문 백업

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

원본: SECTION 10. 고급 SQL 활용 전략

집계는 정확한 기준으로, 정렬은 필요한 만큼만, 페이징은 DB에서 효율적으로 처리하자.

인덱스를 사용하더라도 대량 정렬·집계·OFFSET이 남아 있으면 성능 문제를 해결하지 못할 수 있다.

학습 범위: SECTION 01. SQL REMINDER 소개에 있는 주문(tr_ord_big), 회원(ms_mbr_big), 매장(ms_shop_big)을 중심으로 GROUP BY, DISTINCT, 조건부 집계, UNION ALL, ROLLUP, ORDER BY, LIMIT/OFFSET과 키셋 페이징을 학습한다.

실행계획·예제 전제

•

이 페이지의 실행계획은 실제 PostgreSQL에서 실행해 측정한 결과가 아닌 예상·개념적 계획이다. actual rows, 실제 시간, Buffers 값이나 개선 배수를 만들어 넣지 않는다.

•

원본 교재의 인덱스 tr_ord_big_x03(ord_dtm,mbr_id), tr_ord_big_x19(ord_dtm,mbr_id,ord_no)를 예제 전제로 사용하지만, 실습 DB에 존재한다고 보장하지 않는다.

•

BAD/GOOD이 SQL 결과의 의미를 바꾸는 예제는 그 차이와 업무 전제를 명시한다. 특히 GROUP BY 기준, UNION 중복, ORDER BY 정렬 기준, LIMIT과 페이징 위치를 검증해야 한다.

•

키셋 페이징은 제공된 교재에 직접 실습으로 확인되지 않은 추가 PostgreSQL 작성 패턴이다. 실측 개선 사례로 인용하지 않는다.

이번 섹션의 핵심 규칙

ID	구분	규칙
10-01	MUST	GROUP BY의 집계 단위와 SELECT 컬럼을 일치시키자
10-02	MUST	중복이 없는데 불필요한 DISTINCT를 사용하지 말자
10-03	TIP	동일 데이터의 여러 집계는 FILTER로 통합하자
10-04	MUST	UNION과 UNION ALL의 중복 제거를 구분하자
10-05	TIP	소계·총계에는 ROLLUP을 검토하자
10-06	MUST	불필요한 대량 정렬과 넓은 SELECT 컬럼을 줄이자
10-07	CHECK	ORDER BY와 인덱스 순서가 실제로 일치하는지 확인하자
10-08	MUST	WAS에 전체 데이터를 넘기지 말고 DB에서 페이징하자
10-09	TIP	깊은 OFFSET에는 키셋 페이징을 검토하자
10-10	CHECK	페이징 완료 후 부가 JOIN을 수행할 수 있는지 검토하자

MUST — 개발자가 지켜야 할 기본 규칙 / TIP — 사용할 수 있는 개선 패턴 / CHECK — 실행계획·업무 결과 검증이 필요한 항목

REMINDER 10-01. GROUP BY의 집계 단위와 SELECT 컬럼을 일치시키자

MUST · 상황: 매장·결제유형별로 주문금액을 집계한다.

BAD — 매장만 GROUP BY하면서 결제유형을 그대로 SELECT

SELECT o.shop_id, o.pay_tp, 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.shop_id;

SQL

복사

GOOD — 필요한 집계 기준을 모두 표시

SELECT o.shop_id, o.pay_tp, 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.shop_id, o.pay_tp;

SQL

복사

예상 실행계획 또는 오류

BAD
ERROR: column "o.pay_tp" must appear in the GROUP BY clause
       or be used in an aggregate function
  → 이 예제는 PostgreSQL에서 정상 실행되지 않으므로 실행계획 없음

GOOD (가능한 형태)
HashAggregate
  Group Key: shop_id, pay_tp
  -> Index Scan / Bitmap Heap Scan / Seq Scan on tr_ord_big
       Index Cond 또는 Filter: ord_dtm 범위

또는 Sort -> GroupAggregate

Plain Text

복사

기억할 이유: GROUP BY shop_id라면 매장당 한 행이고, GROUP BY shop_id,pay_tp라면 매장·결제유형 조합당 한 행이다. 집계 단위 자체가 달라진다.

주의: PostgreSQL은 GROUP BY 컬럼이 SELECT 컬럼을 함수적으로 결정하는 경우(예: 적절한 PK 그룹핑)에 비집계 컬럼 참조를 허용하는 예외가 있다. 위 SQL의 매장별 여러 결제유형은 그 예외가 아니다. 기존 MySQL GUIDE의 '집계 함수 없는 비그룹 컬럼 주의'를 PostgreSQL의 일반적 오류 동작에 맞춰 설명했다.

근거: MySQL GUIDE 슬라이드 14. PostgreSQL 오류·PK 함수 종속 예외는 PostgreSQL 동작에 대한 추가 설명.

REMINDER 10-02. 중복이 없는데 DISTINCT를 사용하지 말자

MUST · 상황: 주문 PK인 ord_no로 주문을 한 건 조회한다.

BAD — PK 단건 조회에 DISTINCT

SELECT DISTINCT o.ord_no, o.ord_dtm, o.shop_id
FROM tr_ord_big AS o
WHERE o.ord_no = 1001;

SQL

복사

GOOD — PK의 유일성을 활용해 DISTINCT 생략

SELECT o.ord_no, o.ord_dtm, o.shop_id
FROM tr_ord_big AS o
WHERE o.ord_no = 1001;

SQL

복사

예상 실행계획

BAD (가능한 형태)
Unique / HashAggregate (DISTINCT를 별도 처리할 경우)
  -> Index Scan using 주문 PK 인덱스
       Index Cond: (ord_no = 1001)

또는 옵티마이저가 중복 불가능을 인식하면
Index Scan만 선택할 수도 있음

GOOD (가능한 형태)
Index Scan using 주문 PK 인덱스
  Index Cond: (ord_no = 1001)

Plain Text

복사

기억할 이유: 유일한 PK로 이미 한 건을 찾는데 DISTINCT를 덧붙이면 SQL의 의도만 불분명해진다. 다른 사례에서 무심코 DISTINCT로 조인 중복 문제를 가리는 실수를 예방하는 것이 중요하다.

주의: DISTINCT가 항상 Sort 또는 HashAggregate를 추가하는 것은 아니다. 또한 주문상세와 1:N JOIN한 결과는 주문 PK가 출력에 있어도 여러 행이 될 수 있다. 중복 제거가 실제로 필요한 JOIN 결과에는 임의로 DISTINCT를 제거하지 않는다. ord_no 숫자 타입은 실습 전제다.

현장 근거: 204건 사례 0701_02 — PK 컬럼인데 DISTINCT를 사용한 SQL의 제거 기록. 위 SQL은 원문이 아닌 재구성 예제.

REMINDER 10-03. 동일한 데이터에서 여러 값을 집계할 때는 FILTER를 활용하자

TIP · 상황: 한 매장의 2023년 3월 전체 주문 수와 픽업 완료 주문 수를 함께 출력한다.

BAD — 동일한 주문 범위를 두 번 조회

SELECT
 (SELECT COUNT(*)
  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') AS all_cnt,
 (SELECT COUNT(*)
  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'
    AND o.ord_st = 'PKUP') AS pkup_cnt;

SQL

복사

GOOD — 한 번의 집계에서 조건별 건수 계산

SELECT COUNT(*) AS all_cnt,
       COUNT(*) FILTER (WHERE o.ord_st = 'PKUP') AS pkup_cnt
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';

SQL

복사

예상 실행계획

BAD (가능한 형태)
Result
  InitPlan 1 -> Aggregate -> 주문 검색 A
  InitPlan 2 -> Aggregate -> 주문 검색 B (PKUP 필터)
  → 같은 매장·기간을 두 번 읽을 수 있음

GOOD (가능한 형태)
Aggregate (COUNT, COUNT FILTER)
  -> Index Scan / Bitmap Heap Scan on tr_ord_big
       Index Cond: shop_id / ord_dtm 범위
  → 입력 행을 한 번 읽으며 두 건수 계산 가능

Plain Text

복사

기억할 이유: 집계 대상이 같고 조건만 다르면 PostgreSQL의 FILTER (WHERE ...)로 여러 집계를 한 번에 표현할 수 있다.

주의: COUNT(CASE WHEN 조건 THEN 1 ELSE 0 END)는 ELSE 0이 NULL이 아니므로 전체 행을 세는 잘못된 작성법이다. 비교할 때는 COUNT(*) FILTER (...) 또는 NULL을 반환하는 CASE를 사용한다. 두 독립 인덱스 조회가 매우 선택적이면 BAD가 더 유리할 수도 있다.

근거: 사례 0928_62의 두 집계 통합·조건부 집계 기록. FILTER 문법은 해당 사례의 원문이 아니라 PostgreSQL에 맞춘 교육용 보강.

REMINDER 10-04. UNION과 UNION ALL의 중복 제거를 구분하자

MUST · 상황: 결제유형이 CARD 또는 CASH인 주문번호를 하나의 목록으로 합친다. 아래 CASH는 예시 코드이며 실제 데이터 코드 확인이 필요하다.

BAD 후보 — 서로 겹칠 수 없는 검색에 불필요한 UNION

SELECT o.ord_no, o.shop_id
FROM tr_ord_big AS o
WHERE o.pay_tp = 'CARD'
UNION
SELECT o.ord_no, o.shop_id
FROM tr_ord_big AS o
WHERE o.pay_tp = 'CASH';

SQL

복사

GOOD 후보 — 중복되지 않음이 보장된 경우 UNION ALL

SELECT o.ord_no, o.shop_id
FROM tr_ord_big AS o
WHERE o.pay_tp = 'CARD'
UNION ALL
SELECT o.ord_no, o.shop_id
FROM tr_ord_big AS o
WHERE o.pay_tp = 'CASH';

SQL

복사

예상 실행계획

BAD (가능한 형태)
HashAggregate 또는 Sort -> Unique
  -> Append
       -> tr_ord_big (pay_tp = 'CARD')
       -> tr_ord_big (pay_tp = 'CASH')
  → 중복 제거 연산 가능

GOOD (가능한 형태)
Append
  -> tr_ord_big (pay_tp = 'CARD')
  -> tr_ord_big (pay_tp = 'CASH')
  → 중복 제거 연산 불필요

Plain Text

복사

기억할 이유: UNION은 중복 행을 제거하고, UNION ALL은 중복을 보존한다. 한 행의 단일 pay_tp 값이 CARD이면서 동시에 CASH일 수는 없으므로 이 두 브랜치는 결과 행이 겹치지 않는다.

주의: 여러 브랜치의 조회 조건이 겹치거나 출력 컬럼을 줄여 서로 같은 투영 행이 생기면 UNION과 UNION ALL의 결과가 달라진다. 중복이 필요한지 먼저 판단한다. 실제로는 이 예제의 조건을 WHERE pay_tp IN ('CARD','CASH')로 단순화하는 방법도 검토할 수 있다. UNION ALL이 무조건 더 빠르다는 뜻은 아니다.

원본 규칙: MySQL GUIDE 슬라이드 18, Oracle GUIDE 슬라이드 15. PostgreSQL BIG 모델로 재작성.

REMINDER 10-05. 소계와 총계에는 ROLLUP을 검토하자

TIP · 상황: 2023년 3월의 매장·결제유형별 건수 / 매장 소계 / 전체 총계를 함께 출력한다.

BAD 후보 — 동일한 월간 주문 테이블을 세 번 집계

SELECT o.shop_id, o.pay_tp, 0 AS grp, 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'
GROUP BY o.shop_id, o.pay_tp
UNION ALL
SELECT o.shop_id, NULL::text AS pay_tp, 1 AS grp, 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'
GROUP BY o.shop_id
UNION ALL
SELECT NULL::text AS shop_id, NULL::text AS pay_tp,
       3 AS grp, 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';

SQL

복사

GOOD 후보 — 한 입력 집합으로 ROLLUP 집계

SELECT o.shop_id, o.pay_tp,
       GROUPING(o.shop_id, o.pay_tp) AS grp,
       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'
GROUP BY ROLLUP (o.shop_id, o.pay_tp);

SQL

복사

예상 실행계획

BAD (가능한 형태)
Append
  -> HashAggregate (shop_id, pay_tp)
       -> 월간 주문 검색
  -> HashAggregate (shop_id)
       -> 월간 주문 검색
  -> Aggregate (전체)
       -> 월간 주문 검색
  → 같은 기간 데이터를 세 갈래로 접근 가능

GOOD (가능한 형태)
MixedAggregate / GroupAggregate
  Grouping Sets: (shop_id,pay_tp), (shop_id), ()
  -> 월간 주문 검색
  → 하나의 입력을 여러 집계 단계에 활용 가능

Plain Text

복사

기억할 이유: 동일한 데이터에서 상세·소계·총계를 출력할 때 ROLLUP은 반복 SQL을 간결하게 표현하고 중복 원본 스캔을 줄일 여지가 있다. GROUPING의 0은 상세, 1은 매장 소계, 3은 전체 총계를 구분한다.

주의: 원본의 shop_id나 pay_tp에 실제 NULL이 있을 수도 있다. 구체화된 소계행의 NULL과 원본 데이터 NULL을 GROUPING 값으로 구분해야 한다. ROLLUP이 항상 메모리 사용량이나 성능 면에서 유리한 것은 아니다. 위 SQL은 두 방식의 그룹 수준을 맞춘 교육용 예제다.

원본 규칙: MySQL GUIDE 슬라이드 20 / Oracle GUIDE 슬라이드 16은 반복 UNION ALL 소계·총계 대신 ROLLUP 검토를 권장한다. GROUPING 예제와 예상 노드는 PostgreSQL 문법으로 보강했다.

REMINDER 10-06. 필요하지 않은 넓은 데이터를 대량 정렬하지 말자

MUST · 상황: 2024년 4~5월 주문 중 결제유형·회원ID 순으로 주문번호와 금액만 출력한다.

BAD — 필요한 정보에 비해 과도하게 넓은 SELECT

SELECT o.*
FROM tr_ord_big AS o
WHERE o.ord_dtm >= TIMESTAMP '2024-04-01'
  AND o.ord_dtm < TIMESTAMP '2024-06-01'
ORDER BY o.pay_tp, o.mbr_id;

SQL

복사

GOOD — 실제 필요한 컬럼만 조회

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

SQL

복사

예상 실행계획

BAD (대량 결과, work_mem을 초과하는 가능한 경우)
Sort
  Sort Key: pay_tp, mbr_id
  Sort Method: external merge  [실제 메모리 부족 시]
  -> Bitmap Heap Scan / Seq Scan on tr_ord_big
       Filter 또는 Index Cond: ord_dtm 범위

GOOD (같은 정렬 기준, 좁은 출력 컬럼)
Sort
  Sort Key: pay_tp, mbr_id
  Sort Method: quicksort / external merge  [환경에 따라]
  -> 기간 조건에 대한 스캔
  → 출력·정렬 행의 폭이 줄어들 가능성

Plain Text

복사

기억할 이유: ORDER BY를 위해 대량 행을 정렬하면 Sort가 발생할 수 있다. 정렬 데이터가 메모리에 들어가지 않으면 임시 디스크 I/O가 늘어날 수 있다. 넓은 SELECT *는 정렬 대상의 행 폭과 결과 전송량을 키운다.

주의: BAD와 GOOD은 출력 컬럼의 범위가 다르다. 필요한 컬럼이 정말 4개일 때만 GOOD으로 바꿀 수 있다. 컬럼을 줄였더라도 정렬이 없어지는 것은 아니다. work_mem을 애플리케이션 요청마다 임의로 크게 설정하는 방식은 여러 동시 Sort/Hash 작업의 메모리 부담을 키울 수 있으므로 운영 정책과 함께 검토한다.

교재 근거: StartUP Tuning PG PDF p.156~158, SQL-ORDER-LOAD은 대량 ORDER BY가 external merge 정렬을 유발하고 work_mem 조건에 따라 메모리 정렬과 디스크 정렬이 달라질 수 있음을 실제 실행계획으로 설명한다. 여기에는 측정 수치를 복사하지 않았다.

REMINDER 10-07. ORDER BY와 인덱스 컬럼·방향이 일치하는지 확인하자

CHECK · 상황: 2023년 3월 주문을 ord_dtm, mbr_id, ord_no 순으로 조회한다.

BAD 후보 — 날짜 단독 인덱스로 검색하거나 Bitmap으로 읽는 상황

-- 인덱스 환경 A (교재): x01(ord_dtm)
SELECT o.ord_no, o.ord_dtm, o.mbr_id, o.shop_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

복사

GOOD 후보 — WHERE와 ORDER BY를 함께 지원하는 인덱스

-- 기존 실습 DDL (실제 이 페이지에서는 생성하지 않음)
-- CREATE INDEX tr_ord_big_x19
-- ON tr_ord_big (ord_dtm, mbr_id, ord_no);

-- SQL 텍스트는 위와 동일하고 인덱스 선택이 달라지는 비교
SELECT o.ord_no, o.ord_dtm, o.mbr_id, o.shop_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 (x01 + 정렬을 선택하는 가능한 계획)
Limit
  -> Sort / Incremental Sort
       Sort Key: ord_dtm, mbr_id, ord_no
       -> Bitmap Heap Scan / Index Scan using tr_ord_big_x01
            Index Cond: ord_dtm 범위

GOOD (x19, 동일 정렬 방향에서 가능한 계획)
Limit
  -> Index Scan using tr_ord_big_x19
       Index Cond: ord_dtm 범위
  -> 인덱스 순서대로 읽어 조건에 맞는 30건에서 중지 가능

Plain Text

복사

기억할 이유: B-tree 인덱스는 키 순서를 보유한다. WHERE의 범위 검색과 ORDER BY가 실제 인덱스 키 순서에 맞으면 별도 Sort 없이 상위 N건을 빨리 찾을 가능성이 있다.

주의: Bitmap Index Scan과 Bitmap Heap Scan은 인덱스 정렬 순서를 출력에 보존하지 않는다. 또한 혼합 정렬 방향(예: ord_dtm ASC, mbr_id DESC)은 단순 역방향 전체 탐색만으로 처리할 수 없을 수 있어 별도 인덱스 정의·Sort가 필요하다. x19가 있더라도 대량 행 반환·히프 접근 비용에 따라 다른 계획을 선택할 수 있다.

교재 근거: PDF p.159~166의 Sort 제거 및 Bitmap 비교, p.291~292의 tr_ord_big_x19 인덱스, SQL-PAGE-LIMIT.

REMINDER 10-08. WAS에서 자르지 말고 DB에서 페이징하자

MUST · 상황: 2023년 3월 주문 중 첫 30건을 사용자에게 보여준다.

BAD — 전체 주문 결과를 WAS로 보내 30건만 화면에 표시

-- 애플리케이션이 결과 전체를 받은 뒤 앞 30건만 사용
SELECT o.ord_no, o.ord_dtm, o.shop_id, 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 — PostgreSQL에서 먼저 30건 제한

SELECT o.ord_no, o.ord_dtm, o.shop_id, 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
  -> 기간 내 주문 집합 스캔
  → 전체 주문 결과를 클라이언트로 전송

GOOD (x19 정렬 인덱스가 활용되는 경우)
Limit
  -> Index Scan using tr_ord_big_x19
       Index Cond: ord_dtm 범위
  → 인덱스에서 30건 정도만 읽고 종료할 가능성

GOOD (인덱스가 불충분하면)
Limit
  -> Sort (Top-N heapsort 가능)
       -> 기간 내 주문 집합 스캔
  → 전송량은 줄여도 DB 내부 검색·정렬은 많이 남을 수 있음

Plain Text

복사

기억할 이유: 서버·네트워크·애플리케이션 자원을 낭비하면서 전체 결과를 받은 뒤 자르지 말자. 교재는 이를 WAS 페이징 / DB 페이징 / DB-INDEX 페이징으로 구분한다. 특히 DB-INDEX 페이징의 핵심은 인덱스가 사용됐다는 사실이 아니라 화면에 필요한 만큼만 읽는 것이다.

주의: 첫 30건을 얻으려면 ORDER BY가 반드시 동일해야 한다. 마지막 정렬 키로 유일한 ord_no를 둬 재현성을 높였다. 특정 조회 조건에서 Index Scan을 사용하더라도 이미 많은 인덱스 엔트리를 읽고 마지막에 LIMIT한다면 교재 정의의 DB-INDEX 페이징에 가깝지 않을 수 있다.

교재 근거: PDF p.288~292, SQL-PAGE-NOPAGE, SQL-PAGE-RNUM, SQL-PAGE-LIMIT, SQL-CRIX-TR_ORD_BIG_X19. 원본에는 2023년 3월 주문의 실제 건수와 실행계획이 있으나 여기서는 그 측정값을 재사용하지 않는다.

REMINDER 10-09. 깊은 OFFSET에서는 키셋 페이징을 검토하자

TIP · 상황: 2023년 3월 주문 목록에서 20만 건 이상 뒤쪽 페이지를 열고 싶다.

BAD 후보 — 깊은 OFFSET을 건너뛰는 방식

SELECT o.ord_no, o.ord_dtm, o.mbr_id, o.shop_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 OFFSET 200000;

SQL

복사

GOOD 후보 — 이전 페이지 마지막 키를 기준으로 이어서 탐색

-- 아래 세 값은 '직전 페이지 마지막 행'의 실제 키로 바꾸어 사용
-- ord_no가 숫자형, 정렬 키가 모두 NOT NULL인 환경을 전제
SELECT o.ord_no, o.ord_dtm, o.mbr_id, o.shop_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'
  AND (o.ord_dtm, o.mbr_id, o.ord_no)
      > (TIMESTAMP '2023-03-10 12:00:00', 'M00019', 123456)
ORDER BY o.ord_dtm, o.mbr_id, o.ord_no
LIMIT 30;

SQL

복사

예상 실행계획

BAD (정렬 인덱스 x19가 있더라도)
Limit
  -> Index Scan using tr_ord_big_x19
       Index Cond: ord_dtm 범위
       -> OFFSET 앞의 상당수 행도 순서대로 처리·건너뜀

GOOD (x19의 검색 키와 비교 조건이 맞는 경우)
Limit
  -> Index Scan using tr_ord_big_x19
       Index Cond: (ord_dtm, mbr_id, ord_no) > 커서 키
                  AND ord_dtm < 2023-04-01
       -> 이전 페이지의 마지막 키 다음부터 검색 가능

Plain Text

복사

기억할 이유: OFFSET 200000은 앞의 20만 행을 반환하지 않을 뿐, 서버가 그 행들을 건너뛰기 위한 작업을 수행해야 한다. 키셋 페이징은 정렬 키 자체를 커서로 삼아 다음 검색 위치를 지정한다.

주의: 키셋 방식은 'n번째 페이지로 임의 이동' 기능과 동일하지 않다. 이전 페이지 마지막 행을 알아야 다음 페이지를 검색할 수 있다. 커서 값과 정렬 방향이 일치해야 하고, 위 튜플 비교에는 세 키의 NOT NULL 보장이 필요하다. 실제 ord_no 타입이 문자형이면 마지막 값도 그 타입으로 바꾼다. 데이터가 변경되는 동안 페이지가 어긋나는 문제는 요구되는 일관성 수준·스냅샷 정책에 따라 별도로 다뤄야 한다.

출처 구분: PDF p.293에는 LIMIT 30 OFFSET 60과 OFFSET 200000을 비교하며 뒤쪽 페이지의 부담을 설명한다. 키셋 대안 SQL은 교재 원문이 아니라 추가한 PostgreSQL 작성 패턴이다. 튜플 비교 의미는 PostgreSQL 17 공식 문서 Row Constructor Comparison, OFFSET 처리 특성은 LIMIT/OFFSET 참고.

REMINDER 10-10. 페이징 완료 후 부가 JOIN을 수행할 수 있는지 검토하자

CHECK · 상황: 2023년 3월 주문 목록의 6001~6030번째 주문에 매장명과 회원 닉네임을 함께 표시한다. 정렬 기준은 주문 컬럼으로만 결정되며, 회원·매장명으로 필터링하지 않는다.

BAD 후보 — 전체 주문을 부가 테이블과 결합한 뒤 페이징

SELECT o.ord_no, o.ord_dtm, o.shop_id, o.mbr_id,
       o.ord_amt, s.shop_nm, m.nick_nm
FROM tr_ord_big AS o
LEFT JOIN ms_shop_big AS s ON s.shop_id = o.shop_id
LEFT JOIN ms_mbr_big AS m ON m.mbr_id = o.mbr_id
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 OFFSET 6000;

SQL

복사

GOOD 후보 — 주문 30건을 먼저 확정한 뒤 부가정보 JOIN

SELECT p.ord_no, p.ord_dtm, p.shop_id, p.mbr_id,
       p.ord_amt, s.shop_nm, m.nick_nm
FROM (
  SELECT o.ord_no, o.ord_dtm, o.shop_id, 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 OFFSET 6000
) AS p
LEFT JOIN ms_shop_big AS s ON s.shop_id = p.shop_id
LEFT JOIN ms_mbr_big AS m ON m.mbr_id = p.mbr_id
ORDER BY p.ord_dtm, p.mbr_id, p.ord_no;

SQL

복사

예상 실행계획

BAD (JOIN이 페이지 제한 전에 발생하는 경우의 가능성)
Limit
  -> Sort
       -> Hash Left Join / Nested Loop Left Join
            -> 해당 기간의 주문 검색
            -> 회원·매장 조회
  → 페이지 밖 주문에도 부가정보 접근 가능

GOOD (가능한 형태)
Nested Loop Left Join
  -> Nested Loop Left Join
       -> Limit
            -> Index Scan using tr_ord_big_x19
                 Index Cond: ord_dtm 범위
       -> 매장 PK Index Scan (최대 30회)
  -> 회원 PK Index Scan (최대 30회)
  → 회원·매장 접근을 최종 주문 30건에 제한 가능

Plain Text

복사

기억할 이유: 페이징 결과가 주문 테이블 컬럼만으로 정해지면 필요한 주문 N건부터 확정한 뒤 회원·매장 정보를 붙이는 구조가 불필요한 반복 조인을 줄일 수 있다.

주의: 결과 동등성을 위해 회원·매장의 조인 키가 고유하다는 전제가 필요하다. 두 SQL 모두 LEFT JOIN을 사용해 관련 회원·매장이 없어도 주문을 남긴다. 반면 회원등급·매장명에 WHERE 조건이 있거나, 매장명 기준으로 정렬해야 한다면 JOIN 이후에 페이징을 해야 할 수 있다. PostgreSQL 옵티마이저가 BAD에서도 불필요한 JOIN 평가를 미루는 등 비슷한 최적화를 할 수 있으므로 실측 검증이 필요하다.

교재 근거: StartUP Tuning PG PDF p.294~295, SQL-PAGE-SUB1/SUB2, SQL-PAGE-JOIN1/JOIN2. 원본은 스칼라 서브쿼리와 LATERAL 집계를 페이징 앞뒤로 이동하는 실습이며, 위 예제는 일반 회원·매장 LEFT JOIN으로 재구성했다. 실제 사례 0910_24, 0910_30, 0922_57에도 페이징 선처리 유형이 분류돼 있다.

개발자가 기억할 페이징 처리 단계

1단계 — 결과 순서의 정의: ORDER BY가 없다면 첫 페이지·두 번째 페이지의 의미 자체가 없다. ord_dtm, mbr_id, ord_no처럼 최종적으로 유일한 순서를 구성한다.

2단계 — DB에서 결과 제한: LIMIT으로 필요한 행만 전송한다. 인덱스가 정렬을 지원하는지 확인한다.

3단계 — 후속 데이터 접근 최소화: 부가 조회가 결과 순서를 결정하지 않을 때만 페이징 이후에 JOIN 또는 LATERAL을 고려한다.

4단계 — 뒤쪽 페이지 처리 전략: 깊은 OFFSET 요구가 많으면 키셋 페이징을 검토하되, 임의 페이지 이동 및 데이터 변경 시 페이지 일관성 요구사항을 확인한다.

실제 실행계획 점검에 사용할 SQL

-- 1. 실제 생성된 인덱스 확인
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = current_schema()
  AND tablename = 'tr_ord_big'
ORDER BY indexname;

-- 2. 쿼리를 실행하지 않고 예상 실행계획 확인
EXPLAIN (COSTS OFF)
SELECT o.ord_no, o.ord_dtm, o.mbr_id, o.shop_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;

-- 3. 실습 환경에서 SELECT를 실제 실행해 측정하는 경우
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.ord_dtm, o.mbr_id, o.shop_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

복사

확인할 노드

•

HashAggregate, GroupAggregate, MixedAggregate: 어떤 키로 집계하고 입력 행 수가 얼마인지

•

HashAggregate, Sort, Unique: DISTINCT·UNION 중복 제거의 추가 처리가 있는지

•

Sort Method: external merge 및 temp read/written: 정렬이 디스크를 사용했는지

•

Incremental Sort: 인덱스로 이미 정렬된 앞부분(Presorted Key)을 활용하는지

•

Bitmap Heap Scan: 반환 순서가 인덱스 키 정렬과 다를 수 있음을 기억

•

Limit: 정확히 어느 단계의 앞뒤에 있는지, 실제로 몇 행을 스캔했는지

•

Index Cond, Filter, actual rows, loops, Buffers: 검색·반복 접근의 실제 부담

EXPLAIN ANALYZE는 실제로 SQL을 수행하므로 운영 환경에서 무작정 실행하지 않는다. 여기의 예상 계획을 실측 계획으로 인용하지 않는다.

개발 전 체크리스트

집계 단위와 GROUP BY 컬럼을 업무 요구사항에 맞게 지정했는가?

불필요한 DISTINCT를 사용하거나 조인 중복 문제를 DISTINCT로 가리지는 않았는가?

동일한 입력에서 여러 조건별 집계를 반복하지 않았는가?

UNION 대신 UNION ALL을 써도 중복 정책이 같은가?

매장 소계·총계를 위해 같은 데이터를 여러 번 읽고 있지 않은가?

ORDER BY 대상 행 수와 행 폭이 지나치게 크지 않은가?

기존 인덱스의 키와 ORDER BY의 컬럼·방향이 일치하는가?

Bitmap 방식에서도 정렬이 생길 수 있음을 알고 있는가?

WAS에 전체 결과를 보내지 않고 DB에서 LIMIT을 적용하는가?

페이징 SQL에 유일한 정렬 기준이 있는가?

OFFSET이 큰 화면이라면 키셋 페이징의 사용 가능성을 검토했는가?

부가 JOIN·서브쿼리 실행을 페이징 후로 옮겨도 결과가 같은가?

실제 계획의 Sort, Limit, Buffers, loops를 확인할 준비가 되어 있는가?

참고한 자료와 자료 밖 보강 내용

첨부 교재에서 확인한 직접 근거

•

StartUPTuning_PG_20250616.pdf p.156~160: 정렬 부하, external merge·quicksort, work_mem, 인덱스 정렬과 Bitmap의 차이.

•

같은 교재 p.161~166: B-tree 정방향·역방향 탐색, ORDER BY LIMIT, 정렬 컬럼과 인덱스 구성.

•

같은 교재 p.203: Incremental Sort와 GroupAggregate가 등장하는 JOIN 집계 실습 예제.

•

같은 교재 p.288~293: WAS 페이징 / DB 페이징 / DB-INDEX 페이징, ROW_NUMBER·LIMIT, 인덱스 tr_ord_big_x19(ord_dtm,mbr_id,ord_no), 깊은 OFFSET 부담.

•

같은 교재 p.294~296: 페이징 완료 후 스칼라·JOIN 처리, 정렬 컬럼·방향 변경 시 DB-INDEX 페이징이 깨질 수 있는 문제.

•

StartUPTuning_PG_SQL_Script.sql: SQL-ORDER-LOAD, SQL-ORDER-LOAD2, SQL-PAGE-NOPAGE, SQL-PAGE-RNUM, SQL-PAGE-LIMIT, SQL-CRIX-TR_ORD_BIG_X19, SQL-PAGE-SUB1/SUB2, SQL-PAGE-JOIN1/JOIN2.

•

SQL GUIDE for MySQL.pptx 슬라이드 14(GROUP BY), 18(UNION/ALL), 20(반복 UNION ALL과 ROLLUP) / 002_SQL GUIDE for ORACLE.pptx 슬라이드 15~16(UNION/ALL, ROLLUP).

•

: 0701_02 DISTINCT 제거, 0928_62 조건부 집계, 0910_24·0910_30·0922_57 페이징, 0325_11 정렬 인덱스와 검색 인덱스 간 선택 문제.

공식 문서·일반 PostgreSQL 기능으로 보강

•

PostgreSQL 17 — GROUPING SETS, ROLLUP: ROLLUP과 GROUPING의 동작.

•

PostgreSQL 17 — LIMIT and OFFSET: ORDER BY 필요성과 OFFSET 건너뛰기 비용.

•

PostgreSQL 17 — Row and Array Comparisons: 키셋 페이징의 행 생성자(튜플) 비교 조건.

•

PostgreSQL 17 — Indexes and ORDER BY: 인덱스 정렬과 LIMIT의 관계.

실행 여부: 본문 BAD/GOOD SQL 10개 규칙은 BIG 모델을 활용한 교육용 재구성 SQL이며 원본 자료에 없는 FILTER, GROUPING, 키셋 페이징 예제도 있다. 교재의 측정 수치를 이 예제의 실측 결과로 사용하지 않았다.

그림 제작 메모 — 실제 이미지는 삽입하지 않음

•

그림 A · GROUP BY 집계 단위: 매장별 집계와 매장·결제유형별 집계의 행 수·의미 비교

•

그림 B · UNION vs UNION ALL: 중복 제거를 위한 Sort/Hash 경로 유무 비교

•

그림 C · ROLLUP: 매장·결제유형별 상세행 → 매장 소계 → 전체 총계와 GROUPING 값 0/1/3을 함께 시각화

•

그림 D · 대량 SORT와 인덱스: Bitmap Heap Scan 후 external merge 정렬 대 x19 인덱스 순서 직접 읽기

•

그림 E · WAS/DB/DB-INDEX 페이징: 전체 DB 조회 후 WAS 잘라내기 vs DB 제한 vs 필요한 인덱스 행만 읽기 비교

•

그림 F · 깊은 OFFSET vs 키셋: 앞 200,000행을 건너뛰는 방식과 마지막 정렬 키 다음 위치에서 이어 읽는 방식 비교

•

그림 G · 페이징 후 JOIN: 대량 주문과 회원·매장을 먼저 조인한 뒤 30건 자르기 vs 주문 30건 확정 후 부가정보 접근

SECTION 10 한 줄 요약: 중복 집계·정렬·전송을 줄이고, 필요한 페이지의 데이터만 정확하게 읽도록 SQL을 설계하자.