SECTION 08. 정렬과 페이징 최적화

상세 페이지

필요한 순서를 명확하게 지정하고, 필요한 페이지의 행만 DB에서 조회하자. 마지막 정렬 키에 유일한 기준을 포함하고 뒤쪽 페이지의 접근량을 확인한다.

난이도: 초급 후반~중급 초반 · 설명 방식: BAD SQL → GOOD SQL → 핵심 규칙. 실행계획 전문 용어는 후반 SECTION 11에서 다룬다.

키셋 페이징은 심화 대안이다. 화면에 임의의 N번째 페이지로 바로 이동하는 요구에는 같은 방식으로 적용할 수 없다. 예외와 상세 예상 계획은 SECTION 10 원문 백업에 보존돼 있다.

REMINDER 08-02. ORDER BY와 인덱스의 정렬 순서를 확인하자

상황: 2023년 3월 주문을 ord_dtm, mbr_id, ord_no 순서로 정렬해 처음 30건을 가져온다. 실제 일반 테이블 startdbpg.tr_ord로 실행계획을 검증했다.

기존 인덱스: tr_ord_x51(ord_dtm)가 존재한다. (ord_dtm, mbr_id, ord_no) 복합 인덱스는 현재 없다. 이 항목에서 DB 인덱스는 새로 만들거나 변경하지 않았다.

사례 A — 날짜 인덱스가 정렬 키 일부만 지원하는 경우

SELECT o.ord_no, o.ord_dtm, o.mbr_id,
       o.shop_id, o.ord_amt
FROM   startdbpg.tr_ord 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

복사

날짜 인덱스의 출력 순서는 ord_dtm까지 보장하므로, PostgreSQL은 Incremental Sort로 mbr_id, ord_no 부분을 정렬했다. 실제로 2023년 3월 주문은 17,582건, 서로 다른 주문일시는 2,617개이며, 첫 시각인 2023-03-01 06:32:00에도 주문이 39건 있어서 동률 해소가 필요하다.

실제 실행계획 A — EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)

Limit (actual time=0.171..0.197 rows=30 loops=1)
  Buffers: shared hit=17
  ->  Incremental Sort (actual time=0.170..0.173 rows=30 loops=1)
        Sort Key: ord_dtm, mbr_id, ord_no
        Presorted Key: ord_dtm
        Full-sort Groups: 1  Sort Method: quicksort
        Average Memory: 27kB  Peak Memory: 27kB
        Buffers: shared hit=17
        ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.043..0.061 rows=40 loops=1)
              Index Cond: ((ord_dtm >= '2023-03-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-04-01 00:00:00'::timestamp without time zone))
              Buffers: shared hit=5
Planning:
  Buffers: shared hit=170
Planning Time: 3.311 ms
Execution Time: 0.326 ms

Plain Text

복사

해석: 기존 인덱스에서 40행만 읽고 정렬해 30행을 반환했다. 이미 0.326ms로 매우 빠르므로, Sort가 존재한다는 이유만으로 새 인덱스가 반드시 필요한 것은 아니다.

사례 B — 요구한 정렬 기준이 기존 인덱스 하나와 일치하는 경우

SELECT o.ord_no, o.ord_dtm, o.mbr_id,
       o.shop_id, o.ord_amt
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
ORDER BY o.ord_dtm
LIMIT 30;

SQL

복사

tr_ord_x51(ord_dtm)를 순서대로 읽어 별도 Sort 없이 30건에서 멈춘다.

실제 실행계획 B

Limit (actual time=0.045..0.062 rows=30 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.044..0.058 rows=30 loops=1)
        Index Cond: ((ord_dtm >= '2023-03-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-04-01 00:00:00'::timestamp without time zone))
        Buffers: shared hit=5
Planning:
  Buffers: shared hit=167
Planning Time: 0.778 ms
Execution Time: 0.176 ms

Plain Text

복사

중요: B는 A의 결과 동등한 GOOD SQL이 아니다. ord_dtm 동률일 때 mbr_id, ord_no 순서를 보장하지 않으므로, 페이지 정렬이 확정적이어야 한다면 정렬 키를 임의로 줄이면 안 된다.

설계 후보 — 동일한 SQL과 정렬 요구를 유지하면서 인덱스를 개선한다면

-- 현재 DB에는 생성하지 않은 예시 DDL
-- CREATE INDEX tr_ord_x_sort
--     ON startdbpg.tr_ord (ord_dtm, mbr_id, ord_no);

SQL

복사

이런 복합 B-tree 인덱스는 사례 A의 정렬을 인덱스 순서로 충족할 가능성이 있다. 다만 생성 후 옵티마이저가 실제로 선택할지는 미확인이다. 이 DDL을 실행한 성능 수치를 제시하지 않는다.

추가 실습 — 검색 조건과 ORDER BY, LIMIT을 함께 고려하자

매장 S100의 2024년 1~3월 준비완료 주문 중 최신 10건을 조회한다.

SELECT o.ord_no, o.prep_cmp_dtm, o.shop_id
FROM   startdbpg.tr_ord 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

복사

실제 조건 일치 주문은 4,344건이다. 이 SQL의 실행계획에서는 tr_ord_fk02(shop_id)로 매장 주문 84,885행을 후보로 잡은 뒤 80,541행을 날짜 Filter로 제거하고, 남은 4,344건을 top-N heapsort로 처리하여 10건을 반환했다.

추가 실습 실제 실행계획

Limit (actual time=71.769..71.774 rows=10 loops=1)
  Buffers: shared hit=7236
  ->  Sort (actual time=71.767..71.770 rows=10 loops=1)
        Sort Key: prep_cmp_dtm DESC, ord_no DESC
        Sort Method: top-N heapsort  Memory: 26kB
        Buffers: shared hit=7236
        ->  Bitmap Heap Scan on tr_ord o (actual time=62.120..69.826 rows=4344 loops=1)
              Recheck Cond: ((shop_id)::text = 'S100'::text)
              Filter: ((prep_cmp_dtm >= '2024-01-01 00:00:00'::timestamp without time zone) AND (prep_cmp_dtm < '2024-04-01 00:00:00'::timestamp without time zone))
              Rows Removed by Filter: 80541
              Heap Blocks: exact=7163
              Buffers: shared hit=7230
              ->  Bitmap Index Scan on tr_ord_fk02 (actual time=9.036..9.036 rows=84885 loops=1)
                    Index Cond: ((shop_id)::text = 'S100'::text)
                    Buffers: shared hit=67
Planning:
  Buffers: shared hit=169
Planning Time: 0.810 ms
Execution Time: 72.794 ms

Plain Text

복사

복합 인덱스 설계 후보는 (shop_id, prep_cmp_dtm, ord_no)다. 동등 조건인 shop_id 이후 날짜·정렬 키를 배치하여 후보 검색과 정렬을 함께 지원할 수 있지만, 아직 생성하거나 성능 측정한 인덱스는 아니다.

핵심: ORDER BY 순서를 지원하는 B-tree 인덱스라면 Sort 없이 상위 N건만 가져올 수 있다. 인덱스가 정렬 키 일부만 충족하면 Incremental Sort가 사용될 수도 있다. 단순히 인덱스가 있다는 사실보다 실제 읽은 행 수, 정렬 대상 행 수, 실행계획을 확인하자.

주의: Bitmap Heap Scan은 인덱스의 출력 순서를 보존하지 않는다. 정렬 방향과 동률 처리 기준도 검토해야 한다. SQL의 ORDER BY가 업무 요구를 정의하므로, 성능을 위해 정렬 의미를 바꿔서는 안 된다. 표기한 실행시간은 각각 1회 측정값이다.

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

상황: 사용자는 2023년 3월 주문 목록의 첫 30건만 확인한다. 실제 일반 테이블 startdbpg.tr_ord에서 주문일시 → 회원ID → 주문번호 순으로 조회한다. ord_no는 PK이므로 마지막 정렬 키로 두면 같은 주문일시·회원ID의 행까지 일관된 순서로 정렬할 수 있다.

BAD — 전체 조회 후 애플리케이션(WAS)에서 앞 30건만 사용

SELECT o.ord_no, o.ord_dtm, o.shop_id,
       o.mbr_id, o.ord_amt
FROM   startdbpg.tr_ord 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

복사

WAS가 결과를 전부 받아서 앞 30건만 보여준다면, 사용하지 않을 17,552건까지 DB에서 생성하고 전송·수신·처리해야 한다. 이 SQL은 전체 17,582건을 조회한다.

GOOD — DB에서 필요한 페이지의 30건만 반환

SELECT o.ord_no, o.ord_dtm, o.shop_id,
       o.mbr_id, o.ord_amt
FROM   startdbpg.tr_ord 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와 완전히 동일하고, DB가 첫 30건만 반환한다. 현재 tr_ord_x51(ord_dtm) 인덱스를 사용한 실제 실행계획에서는 전체 17,582건이 아니라 40행만 읽고 Incremental Sort를 수행한 뒤 30건을 반환했다.

실제 데이터·결과 검증

•

해당 기간 전체 주문: 17,582건

•

BAD SQL 최종 반환 행: 17,582건

•

GOOD SQL 최종 반환 행: 30건

•

BAD 전체 결과를 동일한 ORDER BY로 순위 매긴 첫 30건과 GOOD 30건을 행별 순위까지 포함해 EXCEPT ALL 양방향 비교: 차이 0건

실제 실행계획 비교 (EXPLAIN (ANALYZE, BUFFERS, COSTS OFF))

항목	BAD: 전체 조회	GOOD: LIMIT 30
DB 반환 행 수	17,582	30
주문 Index Scan 읽은 행 수	17,582	40
Buffers: shared hit	274	17
실행시간(각 1회)	31.988ms	0.299ms
주요 노드	Index Scan → Incremental Sort	Index Scan → Incremental Sort → Limit

BAD 실제 실행계획

Incremental Sort (actual time=0.250..30.650 rows=17582 loops=1)
  Sort Key: ord_dtm, mbr_id, ord_no
  Presorted Key: ord_dtm
  Full-sort Groups: 334  Sort Method: quicksort
  Average Memory: 26kB  Peak Memory: 26kB
  Pre-sorted Groups: 465  Sort Method: quicksort
  Average Memory: 25kB  Peak Memory: 25kB
  Buffers: shared hit=274
  ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.053..7.405 rows=17582 loops=1)
        Index Cond: ((ord_dtm >= '2023-03-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-04-01 00:00:00'::timestamp without time zone))
        Buffers: shared hit=262
Planning:
  Buffers: shared hit=170
Planning Time: 0.909 ms
Execution Time: 31.988 ms

Plain Text

복사

GOOD 실제 실행계획

Limit (actual time=0.186..0.193 rows=30 loops=1)
  Buffers: shared hit=17
  ->  Incremental Sort (actual time=0.185..0.188 rows=30 loops=1)
        Sort Key: ord_dtm, mbr_id, ord_no
        Presorted Key: ord_dtm
        Full-sort Groups: 1  Sort Method: quicksort
        Average Memory: 27kB  Peak Memory: 27kB
        Buffers: shared hit=17
        ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.051..0.069 rows=40 loops=1)
              Index Cond: ((ord_dtm >= '2023-03-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-04-01 00:00:00'::timestamp without time zone))
              Buffers: shared hit=5
Planning:
  Buffers: shared hit=170
Planning Time: 0.861 ms
Execution Time: 0.299 ms

Plain Text

복사

실행계획 읽기: LIMIT이 있다는 사실 자체가 무조건 적은 데이터 검색을 뜻하지는 않는다. 이번 사례에서는 실제로 Index Scan 40행 → Incremental Sort → Limit 30행이므로 필요한 페이지를 얻기 전에 많은 행을 읽지 않았음을 확인할 수 있다. 08-02처럼 인덱스와 ORDER BY의 관계가 중요하다.

핵심: 화면에 30건만 필요하다면 데이터베이스에서 ORDER BY와 LIMIT으로 30건만 반환하자. 전체 결과를 WAS로 전송한 뒤 잘라내는 방식은 불필요한 DB 작업·네트워크 전송·애플리케이션 메모리 사용을 유발한다.

주의·범위: 여기의 실행시간은 EXPLAIN ANALYZE가 기록한 DB 서버 내부의 각 1회 측정값이며, 실제 네트워크 전송시간·WAS 객체 생성시간은 측정하지 않았다. LIMIT 30은 첫 페이지만 가져오는 예제다. 깊은 페이지의 OFFSET 비용은 08-04에서 별도로 다룬다. 사용자 화면에 일관된 순서가 필요하므로 ORDER BY와 유일한 동률 해소 키를 빠뜨리지 않는다.

REMINDER 08-05. 페이징 후 필요한 테이블만 JOIN하자

상황: 2023년 3월 주문의 6,001~6,030번째 행을 주문일시·회원ID·주문번호 순으로 표시하면서 매장명과 회원 닉네임도 함께 출력한다. 정렬·페이징 기준은 주문 컬럼으로만 결정되고, 매장명·회원명으로 필터링하지 않는다.

실습 일반 테이블

•

startdbpg.tr_ord: 주문(PK ord_no)

•

startdbpg.ms_shop: 매장(PK shop_id)

•

startdbpg.ms_mbr: 회원(PK mbr_id)

•

tr_ord.shop_id, tr_ord.mbr_id는 각각 매장·회원 테이블에 대한 FK다. 각 JOIN 대상의 PK는 유일하므로 주문 1건이 JOIN 때문에 여러 행으로 늘지 않는다.

BAD — 매장·회원을 먼저 JOIN하고 마지막에 페이징

SELECT o.ord_no, o.ord_dtm, o.shop_id, o.mbr_id,
       o.ord_amt, s.shop_nm, m.nick_nm
FROM   startdbpg.tr_ord o
LEFT JOIN startdbpg.ms_shop s
  ON   s.shop_id = o.shop_id
LEFT JOIN startdbpg.ms_mbr 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

복사

실제 실행계획에서 30건만 반환하는데도, Nested Loop Left Join이 6,050행을 처리한 다음 Incremental Sort와 LIMIT/OFFSET을 적용했다. ms_mbr 쪽 Memoize는 6,050번 접근하되 캐시 히트 2,538회·미스 3,512회였고, 실제 회원 PK Index Scan은 3,512회 실행됐다.

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   startdbpg.tr_ord 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
) p
LEFT JOIN startdbpg.ms_shop s
  ON   s.shop_id = p.shop_id
LEFT JOIN startdbpg.ms_mbr m
  ON   m.mbr_id = p.mbr_id
ORDER BY p.ord_dtm, p.mbr_id, p.ord_no;

SQL

복사

인라인뷰에서 주문 30건을 먼저 확정하므로, ms_shop과 ms_mbr의 PK Index Scan은 각각 30회만 실행된다. 바깥쪽 ORDER BY는 최종 출력의 정렬 순서를 명시하기 위한 것이다.

실제 실행 결과 (EXPLAIN (ANALYZE, BUFFERS, COSTS OFF))

항목	BAD: JOIN 후 페이징	GOOD: 페이징 후 JOIN
최종 반환 행	30	30
tr_ord_x51 Index Scan 행 수	6,050	6,050
매장 PK Index Scan 실행	289회 (Memoize 경유)	30회
회원 PK Index Scan 실행	3,512회 (Memoize 경유)	30회
Buffers: shared hit	11,219	255
실행시간 (각 1회)	40.652ms	11.830ms

결과 동등성: BAD와 GOOD 모두 30건이며, 출력한 전체 컬럼에 대해 EXCEPT ALL 양방향 비교를 수행한 결과 차이 0건이다.

BAD 실제 실행계획

Limit (actual time=40.158..40.271 rows=30 loops=1)
  Buffers: shared hit=11219
  ->  Incremental Sort (actual time=1.082..39.945 rows=6030 loops=1)
        Sort Key: o.ord_dtm, o.mbr_id, o.ord_no
        Presorted Key: o.ord_dtm
        Buffers: shared hit=11219
        ->  Nested Loop Left Join (actual time=0.364..30.246 rows=6050 loops=1)
              Buffers: shared hit=11207
              ->  Nested Loop Left Join (actual time=0.067..8.589 rows=6050 loops=1)
                    Buffers: shared hit=671
                    ->  Index Scan using tr_ord_x51 on tr_ord o
                          (actual time=0.038..2.137 rows=6050 loops=1)
                          Buffers: shared hit=93
                    ->  Memoize (actual time=0.001..0.001 rows=1 loops=6050)
                          Cache Key: o.shop_id
                          Hits: 5761  Misses: 289
                          ->  Index Scan using ms_shop_pk on ms_shop s
                                (actual time=0.003..0.003 rows=1 loops=289)
              ->  Memoize (actual time=0.003..0.003 rows=1 loops=6050)
                    Cache Key: o.mbr_id
                    Hits: 2538  Misses: 3512
                    Buffers: shared hit=10536
                    ->  Index Scan using ms_mbr_pk on ms_mbr m
                          (actual time=0.004..0.004 rows=1 loops=3512)
Planning Time: 2.174 ms
Execution Time: 40.652 ms

Plain Text

복사

GOOD 실제 실행계획

Nested Loop Left Join (actual time=11.193..11.627 rows=30 loops=1)
  Buffers: shared hit=255
  ->  Nested Loop Left Join (actual time=11.155..11.313 rows=30 loops=1)
        Buffers: shared hit=165
        ->  Limit (actual time=11.118..11.176 rows=30 loops=1)
              Buffers: shared hit=105
              ->  Incremental Sort (actual time=0.190..10.853 rows=6030 loops=1)
                    Sort Key: o.ord_dtm, o.mbr_id, o.ord_no
                    Presorted Key: o.ord_dtm
                    Buffers: shared hit=105
                    ->  Index Scan using tr_ord_x51 on tr_ord o
                          (actual time=0.040..2.207 rows=6050 loops=1)
                          Buffers: shared hit=93
        ->  Index Scan using ms_shop_pk on ms_shop s
              (actual time=0.004..0.004 rows=1 loops=30)
              Buffers: shared hit=60
  ->  Index Scan using ms_mbr_pk on ms_mbr m
        (actual time=0.010..0.010 rows=1 loops=30)
        Buffers: shared hit=90
Planning Time: 1.769 ms
Execution Time: 11.830 ms

Plain Text

복사

위 실행계획은 핵심 연산을 발췌했다. 인덱스 조건과 일부 중간 노드는 생략했으므로 SQL 실행 시 EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)로 전문을 확인할 수 있다.

실행계획 해석: 두 SQL 모두 OFFSET 6000 때문에 주문 인덱스에서 6,050행을 읽어야 했다. GOOD은 OFFSET 비용을 없앤 것이 아니라, 30건만 확정한 이후 부가 JOIN을 수행해 수천 번의 반복 조회를 줄인 것이다.

핵심: 정렬·페이징 기준이 기준 테이블의 컬럼으로만 결정된다면 필요한 N건을 먼저 확정하고, 화면에 필요한 부가 정보를 그 N건에 대해서만 JOIN하자.

예외·주의:

•

매장명 또는 회원등급 등의 JOIN 테이블 컬럼으로 필터링하거나 정렬한다면 JOIN 이후에 페이지를 결정해야 하므로 이 방식으로 무작정 변환하면 결과가 달라질 수 있다.

•

부가 JOIN 대상이 PK·유니크 키가 아닌 1:N 관계라면 중복이 생기므로 두 SQL의 결과가 같다고 보장할 수 없다.

•

LEFT JOIN은 매장·회원 정보가 누락돼도 주문을 유지한다. 외래키·NULL 여부와 INNER JOIN 전환 가능성은 별도 판단한다.

•

PostgreSQL 옵티마이저가 원래 SQL의 JOIN을 효율적으로 처리하는 경우도 있으므로 실제 실행계획을 검증해야 한다. 실행시간은 각 1회 실측 결과로 일반화하지 않는다.

REMINDER 08-06. 스칼라 서브쿼리는 페이징 후에 실행하자

08-05와의 연계: 08-05는 JOIN 전에 페이징, 08-06은 스칼라 서브쿼리 실행 전에 페이징한다. 둘 다 주문 목록의 필요한 30건을 먼저 확정하고, 부가정보 조회를 그 30건으로 제한하는 작성 원칙이다. 스칼라 서브쿼리를 무조건 JOIN으로 바꾸라는 뜻은 아니다.

상황: 2023년 3월 주문의 6,001~6,030번째 행을 주문일시·회원ID·주문번호 순으로 조회하면서 매장명과 회원 닉네임도 출력한다. 실제 일반 테이블 startdbpg.tr_ord, startdbpg.ms_shop, startdbpg.ms_mbr를 사용한다. shop_id, mbr_id는 매장·회원 테이블 각각의 PK를 참조한다.

BAD — 스칼라 서브쿼리가 페이징 이전 행에 반복 실행될 수 있는 구조

SELECT o.ord_no, o.ord_dtm, o.shop_id, o.mbr_id, o.ord_amt,
       (SELECT s.shop_nm
        FROM   startdbpg.ms_shop s
        WHERE  s.shop_id = o.shop_id) shop_nm,
       (SELECT m.nick_nm
        FROM   startdbpg.ms_mbr m
        WHERE  m.mbr_id = o.mbr_id) nick_nm
FROM   startdbpg.tr_ord 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;

SQL

복사

GOOD — 주문 30건을 확정한 다음 스칼라 서브쿼리 실행

SELECT p.ord_no, p.ord_dtm, p.shop_id, p.mbr_id, p.ord_amt,
       (SELECT s.shop_nm
        FROM   startdbpg.ms_shop s
        WHERE  s.shop_id = p.shop_id) shop_nm,
       (SELECT m.nick_nm
        FROM   startdbpg.ms_mbr m
        WHERE  m.mbr_id = p.mbr_id) nick_nm
FROM (
    SELECT o.ord_no, o.ord_dtm, o.shop_id, o.mbr_id, o.ord_amt
    FROM   startdbpg.tr_ord 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
) p
ORDER BY p.ord_dtm, p.mbr_id, p.ord_no;

SQL

복사

실측 결과 — EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)

비교 항목	BAD	GOOD
최종 반환 행 수	30	30
주문 인덱스가 읽은 행 수	6,050	6,050
매장명 스칼라 SubPlan 1 실행	6,030회	30회
회원명 스칼라 SubPlan 2 실행	6,030회	30회
Buffers: shared hit	30,255	255
실행시간 (각 1회)	62.277ms	12.999ms

BAD 실제 실행계획 (핵심 노드)

Limit (actual time=61.756..62.087 rows=30 loops=1)
  Buffers: shared hit=30255
  ->  Result (actual time=0.294..61.625 rows=6030 loops=1)
        ->  Incremental Sort (actual time=0.240..11.403 rows=6030 loops=1)
              Sort Key: o.ord_dtm, o.mbr_id, o.ord_no
              Presorted Key: o.ord_dtm
              ->  Index Scan using tr_ord_x51 on tr_ord o
                    (actual time=0.051..2.238 rows=6050 loops=1)
        SubPlan 1
          ->  Index Scan using ms_shop_pk on ms_shop s
                (actual time=0.003..0.003 rows=1 loops=6030)
                Buffers: shared hit=12060
        SubPlan 2
          ->  Index Scan using ms_mbr_pk on ms_mbr m
                (actual time=0.004..0.004 rows=1 loops=6030)
                Buffers: shared hit=18090
Execution Time: 62.277 ms

Plain Text

복사

GOOD 실제 실행계획 (핵심 노드)

Subquery Scan on p (actual time=12.310..12.861 rows=30 loops=1)
  Buffers: shared hit=255
  ->  Limit (actual time=12.196..12.296 rows=30 loops=1)
        ->  Incremental Sort (actual time=0.208..11.852 rows=6030 loops=1)
              Sort Key: o.ord_dtm, o.mbr_id, o.ord_no
              Presorted Key: o.ord_dtm
              ->  Index Scan using tr_ord_x51 on tr_ord o
                    (actual time=0.042..2.523 rows=6050 loops=1)
  SubPlan 1
    ->  Index Scan using ms_shop_pk on ms_shop s
          (actual time=0.005..0.005 rows=1 loops=30)
          Buffers: shared hit=60
  SubPlan 2
    ->  Index Scan using ms_mbr_pk on ms_mbr m
          (actual time=0.011..0.011 rows=1 loops=30)
          Buffers: shared hit=90
Execution Time: 12.999 ms

Plain Text

복사

실행계획은 핵심 노드 발췌본이다. 각 SQL을 직접 EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)로 실행하면 인덱스 조건 등 전체 출력을 확인할 수 있다.

결과 동등성: BAD와 GOOD 모두 30건이며, 결과 전체 컬럼에 대한 EXCEPT ALL 양방향 비교 차이 0건. 최종 정렬 순서에 행 번호를 부여한 비교도 차이 0건이다.

실행계획 해석: BAD에서는 OFFSET 6000을 통과시키기 위한 상위 Result가 6,030행에 대해 두 스칼라 서브쿼리를 반복 평가했다. GOOD에서는 주문을 먼저 30행으로 제한한 후 스칼라 서브쿼리 두 개를 각 30회 평가했다. 두 방식 모두 주문 후보 6,050행은 읽었으므로, 이 개선은 OFFSET 비용 제거가 아니라 부가 서브쿼리의 반복 실행 횟수 감소다.

주의할 예외

•

PostgreSQL은 SQL 형태·버전·최적화 방식에 따라 스칼라 표현식을 이미 LIMIT 이후로 평가할 수도 있다. 단순히 SELECT 절에 스칼라 서브쿼리가 있다는 이유만으로 반드시 많은 횟수 실행된다고 단정하지 말고 실제 SubPlan loops를 확인한다.

•

서브쿼리 결과를 WHERE나 ORDER BY에서 사용한다면 페이징 이전 평가가 필요할 수 있으므로 임의로 후처리하면 결과가 달라진다.

•

상관 스칼라 서브쿼리의 반환값은 최대 한 행이어야 한다. 매장·회원 PK처럼 단일 행이 보장되는 관계에서 사용한다.

•

측정값은 각각 1회 실측이다. 일반적인 성능 배수로 단정하지 않는다.

핵심: 스칼라 서브쿼리가 페이징 결과의 출력용 부가정보만 조회한다면, 필요한 N건을 먼저 확정하고 그 N건에 대해서만 스칼라 서브쿼리가 실행되도록 SQL 구조와 실제 실행계획을 확인하자.

SQL 작성 후 체크리스트

ORDER BY에 명확한 동률 기준이 있는가?

필요하지 않은 컬럼까지 모두 전송하는가?

DB에서 LIMIT을 적용하고 있는가?

OFFSET이 깊으면 키셋 방식도 검토했는가?

페이징 기준이 JOIN 이후 달라지지는 않는가?

한 줄 요약: 필요한 순서를 명확하게 지정하고, 필요한 페이지의 행만 DB에서 조회하자. 마지막 정렬 키에 유일한 기준을 포함하고 뒤쪽 페이지의 접근량을 확인한다.