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회 측정값이다.