SECTION 03. WHERE 조건과 검색 효율

상세 페이지

검색 조건은 정확한 데이터를 필요한 범위만큼 읽도록 작성하자. 날짜·NULL·LIKE·부정 조건은 결과와 성능을 함께 확인한다.

권장 난이도: 초급 후반~중급 초반 · 진행 방식: BAD SQL → GOOD SQL → 핵심 한 문장.

예상 실행계획·고급 인덱스·조인 알고리즘은 강의 후반부로 옮겼다. 상세 예외와 원본 실습 근거는 SECTION 03 원문 백업에서 확인할 수 있다.

REMINDER 03-01. WHERE에서 인덱스 컬럼을 함수로 가공하지 말자

상황: 2023년 2월 1일에 주문된 데이터 건수를 조회한다. Supabase startdbpg.tr_ord에는 약 73만 건이 있으며, ord_dtm에 B-tree 인덱스 tr_ord_x51이 있다. 실제 조회 결과는 604건이다.

BAD — 인덱스 컬럼에 TO_CHAR 적용

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*) AS ord_cnt
FROM   startdbpg.tr_ord o
WHERE  TO_CHAR(o.ord_dtm, 'YYYYMMDD') = '20230201';

SQL

복사

실측 실행계획 (BAD)

Finalize Aggregate (actual time=325.510..334.002 rows=1 loops=1)
  Buffers: shared hit=1970
  ->  Gather (actual time=325.372..333.994 rows=2 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        Buffers: shared hit=1970
        ->  Partial Aggregate (actual time=320.191..320.192 rows=1 loops=2)
              Buffers: shared hit=1970
              ->  Parallel Index Only Scan using tr_ord_x51 on tr_ord o (actual time=139.968..320.152 rows=302 loops=2)
                    Filter: (to_char(ord_dtm, 'YYYYMMDD'::text) = '20230201'::text)
                    Rows Removed by Filter: 364862
                    Heap Fetches: 0
                    Buffers: shared hit=1970
Planning:
  Buffers: shared hit=128
Planning Time: 0.620 ms
Execution Time: 334.152 ms

Plain Text

복사

GOOD — 원본 TIMESTAMP 컬럼의 범위 조건

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*) AS ord_cnt
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-02-01 00:00:00'
  AND  o.ord_dtm <  TIMESTAMP '2023-02-02 00:00:00';

SQL

복사

실측 실행계획 (GOOD)

Aggregate (actual time=0.255..0.256 rows=1 loops=1)
  Buffers: shared hit=5
  ->  Index Only Scan using tr_ord_x51 on tr_ord o (actual time=0.081..0.203 rows=604 loops=1)
        Index Cond: ((ord_dtm >= '2023-02-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-02-02 00:00:00'::timestamp without time zone))
        Heap Fetches: 0
        Buffers: shared hit=5
Planning:
  Buffers: shared hit=127
Planning Time: 0.835 ms
Execution Time: 0.373 ms

Plain Text

복사

실측 비교: 두 SQL 모두 604건을 집계한다. BAD도 tr_ord_x51의 Parallel Index Only Scan을 사용했지만, TO_CHAR(ord_dtm) 조건이 Index Cond가 아닌 Filter로 남아 인덱스를 광범위하게 읽었다. Rows Removed by Filter: 364862는 병렬 작업 1회당 평균 값(loops=2)으로, 전체 제거 행 수를 364,862건으로 해석하면 안 된다. 실행 버퍼는 BAD shared hit=1970, GOOD shared hit=5였다. 실행시간은 BAD 334.152ms, GOOD 0.373ms로 측정됐으나 단일 실행 결과이며 환경에 따라 달라질 수 있다.

핵심: 인덱스 컬럼을 함수로 가공하지 말고 원본 컬럼으로 검색 범위를 표현하자. 노드 이름이 Index Only Scan이어도 효율적인 탐색인지 판단하려면 Index Cond와 Filter를 구분해야 한다.

예외·주의: BAD가 반드시 Seq Scan이 되는 것은 아니다. TO_CHAR와 같은 표현식을 지원하도록 별도로 설계한 검색 구조가 있다면 계획이 달라질 수 있다. 날짜 검색은 종료일 미만의 반열린 구간을 사용하며, 실제 ord_dtm이 timestamp without time zone인 점과 업무상 날짜·타임존 정의를 확인한다.

REMINDER 03-02. TIMESTAMP 일자 검색은 종료일 미만의 범위를 쓰자

상황: startdbpg.tr_ord에서 2024년 1월 한 달간 발생한 주문을 조회한다. ord_dtm은 timestamp without time zone이며 tr_ord_x51 인덱스가 있다. 실데이터로 검증하면 잘못된 종료 조건 때문에 603건이 누락된다.

BAD — BETWEEN의 종료값을 1월 31일 00:00:00으로 지정

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.ord_dtm
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm BETWEEN TIMESTAMP '2024-01-01 00:00:00'
                     AND TIMESTAMP '2024-01-31 00:00:00';

SQL

복사

실측 결과 (BAD): 16,849건

Index Scan using tr_ord_x51 on tr_ord o (actual time=0.761..124.674 rows=16849 loops=1)
  Index Cond: ((ord_dtm >= '2024-01-01 00:00:00'::timestamp without time zone) AND (ord_dtm <= '2024-01-31 00:00:00'::timestamp without time zone))
  Buffers: shared hit=252
Planning:
  Buffers: shared hit=127
Planning Time: 0.642 ms
Execution Time: 125.914 ms

Plain Text

복사

GOOD — 2월 1일 00:00:00 미만까지 포함하는 반열린 범위

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.ord_dtm
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2024-01-01 00:00:00'
  AND  o.ord_dtm <  TIMESTAMP '2024-02-01 00:00:00';

SQL

복사

실측 결과 (GOOD): 17,452건

Index Scan using tr_ord_x51 on tr_ord o (actual time=0.082..11.809 rows=17452 loops=1)
  Index Cond: ((ord_dtm >= '2024-01-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2024-02-01 00:00:00'::timestamp without time zone))
  Buffers: shared hit=260
Planning:
  Buffers: shared hit=124
Planning Time: 0.636 ms
Execution Time: 13.250 ms

Plain Text

복사

누락 건수 직접 검증

SELECT COUNT(*) AS missed_cnt
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm > TIMESTAMP '2024-01-31 00:00:00'
AND    o.ord_dtm < TIMESTAMP '2024-02-01 00:00:00';

SQL

복사

실행 결과: 603건. 예를 들어 ord_no=523831의 주문일시는 2024-01-31 06:32:00으로 GOOD에는 포함되지만 BAD에서는 제외된다.

핵심: BETWEEN은 시작과 종료값을 모두 포함한다. TIMESTAMP 조건에서 종료일을 00:00:00으로 쓰면 그날 자정 이후의 주문을 놓친다. >= 시작일 AND < 다음날(또는 다음달) 형태로 작성하자.

실행계획 해석: BAD/GOOD 모두 tr_ord_x51의 Index Scan + Index Cond를 사용한다. 이번 사례는 인덱스 사용 여부가 아니라 검색 경계와 결과 건수의 정확성을 보여준다. 실행시간 125.914ms와 13.250ms는 각각 한 번 측정한 값으로, 캐시·실행환경 등에 영향을 받으므로 GOOD의 보편적인 성능 우위를 증명하지 않는다.

예외·주의: 조회할 업무 기간과 시간대 기준을 먼저 확정한다. timestamp without time zone과 timestamptz의 의미는 다르며, DATE형 컬럼과도 구분해서 사용한다.

REMINDER 03-03. 문자형 날짜 컬럼을 숫자로 변환해 비교하지 말자

상황: startdbpg.tr_ord는 주문일시(ord_dtm)에서 날짜만 추출한 YYYYMMDD 8자리 문자형 반정규화 컬럼 ord_ymd varchar(8)를 사용한다. ord_ymd에는 B-tree 인덱스 tr_ord_x52가 있다. 실제 주문 730,327건 중 2023년 4월 1일 주문은 729건이다.

BAD — 문자형 날짜 컬럼을 숫자로 변환

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*) AS ord_cnt
FROM   startdbpg.tr_ord o
WHERE  o.ord_ymd::numeric = 20230401;

SQL

복사

실측 실행계획 (BAD)

Finalize Aggregate (actual time=496.329..512.182 rows=1 loops=1)
  Buffers: shared hit=7 read=648
  ->  Gather (actual time=495.923..512.173 rows=2 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        Buffers: shared hit=7 read=648
        ->  Partial Aggregate (actual time=485.842..485.844 rows=1 loops=2)
              Buffers: shared hit=7 read=648
              ->  Parallel Index Only Scan using tr_ord_x52 on tr_ord o (actual time=229.812..485.798 rows=364 loops=2)
                    Filter: ((ord_ymd)::numeric = '20230401'::numeric)
                    Rows Removed by Filter: 364799
                    Heap Fetches: 0
                    Buffers: shared hit=7 read=648
Planning:
  Buffers: shared hit=132
Planning Time: 4.199 ms
Execution Time: 512.870 ms

Plain Text

복사

GOOD — 날짜를 저장된 문자 형식 그대로 비교

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*) AS ord_cnt
FROM   startdbpg.tr_ord o
WHERE  o.ord_ymd = '20230401';

SQL

복사

실측 실행계획 (GOOD)

Aggregate (actual time=1.466..1.467 rows=1 loops=1)
  Buffers: shared hit=5
  ->  Index Only Scan using tr_ord_x52 on tr_ord o (actual time=1.315..1.399 rows=729 loops=1)
        Index Cond: (ord_ymd = '20230401'::text)
        Heap Fetches: 0
        Buffers: shared hit=5
Planning:
  Buffers: shared hit=128
Planning Time: 0.703 ms
Execution Time: 1.645 ms

Plain Text

복사

실측 비교: 두 SQL 모두 729건을 반환한다. BAD는 tr_ord_x52를 사용한 Parallel Index Only Scan이지만 조건이 Index Cond가 아닌 Filter에 남아 인덱스를 넓게 스캔한다. BAD의 Rows Removed by Filter: 364799는 loops=2인 병렬 작업당 평균값이다. GOOD은 Index Cond로 필요한 날짜의 인덱스 항목만 탐색한다. 실행 버퍼는 BAD 655블록(hit=7, read=648), GOOD 5블록(hit=5)이었다. 실행시간 512.870ms와 1.645ms는 각각 실측 1회 결과로 캐시·부하 등에 따라 달라질 수 있다.

자료형 불일치 오류도 검증

SELECT COUNT(*) AS ord_cnt
FROM   startdbpg.tr_ord o
WHERE  o.ord_ymd = 20230401;

SQL

복사

PostgreSQL 실행 결과: SQLSTATE 42883: operator does not exist: character varying = integer. 오류를 해결하려고 ord_ymd::numeric으로 컬럼을 바꾸는 대신, 날짜 값을 '20230401'처럼 컬럼과 같은 문자형으로 비교해야 한다.

핵심: YYYYMMDD 형태로 저장한 날짜 문자열도 인덱스 컬럼 자체를 변환하지 말자. 비교값을 컬럼 형식에 맞추면 인덱스가 날짜 문자열의 원본 값으로 검색할 수 있다. 8자리로 통일된 유효한 YYYYMMDD 문자열은 날짜 순서와 문자열 정렬 순서가 일치하므로, 날짜 범위도 문자값으로 표현할 수 있다.

예외·주의: ord_ymd는 실제 DATE/TIMESTAMP 자료형이 아니라 날짜를 담은 문자형 컬럼이다. 이 반정규화 방식 자체를 일반적으로 권장하는 것은 아니다. 잘못된 날짜 문자열을 저장하지 않도록 검증이 필요하며, ord_dtm이 바뀌면 ord_ymd도 동기화해야 한다. 현재 데이터는 두 컬럼이 일치하지만 변경 시 자동 동기화 기능은 설정하지 않았다. 표현식 인덱스를 따로 설계하면 BAD의 실행계획이 달라질 수 있다.

REMINDER 03-04. 정확한 값 검색에 습관적으로 LIKE를 사용하지 말자

상황: 매장 코드가 정확히 S033인 주문만 검색한다. 개발자가 습관적으로 LIKE 'S033%'처럼 와일드카드(%)를 붙이면 검색 의미가 달라지고, 일반 B-tree 인덱스를 효율적으로 사용하지 못할 수 있다. 실제 startdbpg.tr_ord의 shop_id에는 tr_ord_fk02 인덱스가 있다.

BAD — 정확한 일치가 필요한데 LIKE 접두사 검색 사용

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.shop_id
FROM   startdbpg.tr_ord o
WHERE  o.shop_id LIKE 'S033%';

SQL

복사

실측 실행계획 (BAD)

Gather (actual time=0.571..411.922 rows=4182 loops=1)
  Workers Planned: 1
  Workers Launched: 1
  Buffers: shared hit=16174 read=1142
  ->  Parallel Seq Scan on tr_ord o (actual time=0.029..390.108 rows=2091 loops=2)
        Filter: ((shop_id)::text ~~ 'S033%'::text)
        Rows Removed by Filter: 363072
        Buffers: shared hit=16174 read=1142
Planning:
  Buffers: shared hit=119
Planning Time: 0.623 ms
Execution Time: 412.502 ms

Plain Text

복사

GOOD — 정확한 매장 코드는 = 로 비교

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.shop_id
FROM   startdbpg.tr_ord o
WHERE  o.shop_id = 'S033';

SQL

복사

실측 실행계획 (GOOD)

Index Scan using tr_ord_fk02 on tr_ord o (actual time=0.080..14.185 rows=4182 loops=1)
  Index Cond: ((shop_id)::text = 'S033'::text)
  Buffers: shared hit=3177
Planning:
  Buffers: shared hit=125
Planning Time: 0.624 ms
Execution Time: 14.578 ms

Plain Text

복사

실측 비교: 두 SQL 모두 4,182건을 조회했지만 접근 경로가 달랐다. BAD는 Parallel Seq Scan + Filter로 테이블을 광범위하게 읽었고, GOOD은 tr_ord_fk02의 Index Cond를 사용했다. 버퍼 사용량은 BAD 17,316블록(hit=16,174 / read=1,142), GOOD 3,177블록(hit=3,177)이었다. 실행시간 412.502ms 대 14.578ms는 단일 측정으로, 캐시·시스템 부하 등에 따라 변한다.

검색 결과가 현재 같은 이유: 지금 데이터에서는 shop_id = 'S033'인 행도 4,182건이고, shop_id LIKE 'S033%'인 행도 4,182건이며, S033으로 시작하지만 S033이 아닌 코드의 주문은 0건이었다. 그러나 일반적으로 LIKE 'S033%'는 S0331, S033A 등 추가 문자열로 이어지는 코드까지 포함할 수 있다. 지금 결과가 같다고 두 조건이 동등한 것은 아니다.

핵심: 정확한 값은 =, 접두사 검색은 LIKE '값%'를 사용하자. 습관적인 와일드카드는 조회 의미와 인덱스 접근 방식 모두에 영향을 줄 수 있다.

예외·주의: 와일드카드 없는 LIKE 'S033'은 이 실습 환경에서 Index Cond: (shop_id = 'S033')로 최적화되어 인덱스를 사용했으며 LIKE Filter도 남았다. 따라서 모든 LIKE가 인덱스를 못 탄다는 의미는 아니다. 접두사 LIKE 'S033%' 역시 Collation, text_pattern_ops/varchar_pattern_ops 같은 인덱스 연산자 클래스, 통계 등에 따라 다른 계획을 선택할 수 있다. 실제로 접두사 검색이 필요한 업무라면 LIKE를 =로 바꾸지 말고 적절한 인덱스 설계를 검토해야 한다.

REMINDER 03-05. 접두사 검색은 LEFT 대신 LIKE로 표현하고, Index Cond를 확인하자

상황: 닉네임이 Mo로 시작하는 회원을 검색한다. 실제 startdbpg.ms_mbr 테이블은 9,999건이고, nick_nm에 B-tree 인덱스 ms_mbr_x51이 있다(mbr_id는 INCLUDE). 해당 조건에 맞는 회원은 400건이다.

BAD — 컬럼 앞부분을 LEFT 함수로 잘라서 비교

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.mbr_id, m.nick_nm
FROM   startdbpg.ms_mbr m
WHERE  LEFT(m.nick_nm, 2) = 'Mo';

SQL

복사

실측 실행계획 (BAD)

Index Only Scan using ms_mbr_x51 on ms_mbr m (actual time=1.302..2.600 rows=400 loops=1)
  Filter: ("left"((nick_nm)::text, 2) = 'Mo'::text)
  Rows Removed by Filter: 9599
  Heap Fetches: 0
  Buffers: shared hit=42
Planning:
  Buffers: shared hit=82
Planning Time: 2.449 ms
Execution Time: 2.722 ms

Plain Text

복사

GOOD — 시작 문자열을 LIKE 접두사 조건으로 표현

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.mbr_id, m.nick_nm
FROM   startdbpg.ms_mbr m
WHERE  m.nick_nm LIKE 'Mo%';

SQL

복사

실측 실행계획 (GOOD)

Index Only Scan using ms_mbr_x51 on ms_mbr m (actual time=0.938..1.859 rows=400 loops=1)
  Filter: ((nick_nm)::text ~~ 'Mo%'::text)
  Rows Removed by Filter: 9599
  Heap Fetches: 0
  Buffers: shared hit=42
Planning:
  Buffers: shared hit=80
Planning Time: 4.775 ms
Execution Time: 1.990 ms

Plain Text

복사

실측 비교: 두 SQL 모두 400건을 반환했고, ms_mbr_x51의 Index Only Scan을 사용했다. 하지만 둘 다 Index Cond가 없고 조건이 Filter로 남았다. 즉, 인덱스 9,999개 항목을 모두 검사하여 9,599건을 제거했다. 버퍼 사용량도 똑같이 shared hit=42였다. 실행시간은 각각 2.722ms, 1.990ms였지만 단일 측정 결과로, LIKE가 인덱스 검색 범위를 좁혔거나 일반적으로 더 빠르다는 증거는 아니다.

핵심: 접두사 검색은 LIKE 'Mo%'로 명확하게 표현하자. 그러나 SQL에서 컬럼 함수를 제거했다고 해서 반드시 인덱스 탐색이 최적화되는 것은 아니다. 실행계획에서 Index Cond가 생성됐는지, 또는 인덱스를 전체 스캔한 후 Filter로 처리하는지 확인해야 한다.

예외·주의: LIKE 'Mo%'는 문자열이 Mo로 시작하는 경우이고, LIKE '%Mo%'는 문자열 어디든 포함되는 경우이므로 의미가 다르다. PostgreSQL에서 일반 B-tree 인덱스가 접두사 LIKE를 범위 탐색으로 처리할지는 Collation과 인덱스 연산자 클래스에 좌우된다. 실제 접두사 검색의 인덱스 범위 탐색이 필요하면 데이터형에 맞는 text_pattern_ops/varchar_pattern_ops 또는 적절한 Collation·인덱스 설계를 별도로 검토해야 한다. 이번 실습에서는 인덱스를 추가하거나 변경하지 않았다.

REMINDER 03-06. NOT IN 서브쿼리의 NULL 함정을 기억하자

상황: 기준일 달력 startdbpg.cm_base_dt에서 2025년 1월 23일~30일 중 픽업이 한 번도 발생하지 않은 날짜를 찾는다. 주문 테이블 startdbpg.tr_ord의 pkup_dtm은 TIMESTAMP이며, 실제 730,327건 중 NULL이 20건 있다. 테이블 데이터를 변경하거나 NULL을 인위적으로 추가하지 않고 확인한 사례다.

BAD — NULL이 포함된 서브쿼리를 NOT IN으로 비교

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT c.base_dt
FROM   startdbpg.cm_base_dt c
WHERE  c.base_dt BETWEEN DATE '2025-01-23'
                     AND DATE '2025-01-30'
AND    c.base_dt NOT IN (
         SELECT o.pkup_dtm::date
         FROM   startdbpg.tr_ord o
       )
ORDER BY c.base_dt;

SQL

복사

실측 결과: 0건 — 픽업이 없는 날짜까지 모두 누락

Index Only Scan using cm_base_dt_pk on cm_base_dt c (actual time=1834.868..1834.869 rows=0 loops=1)
  Index Cond: ((base_dt >= '2025-01-23'::date) AND (base_dt <= '2025-01-30'::date))
  Filter: (NOT (ANY (base_dt = (SubPlan 1).col1)))
  Rows Removed by Filter: 8
  Heap Fetches: 0
  Buffers: shared hit=16657 read=662, temp read=8397 written=1250
  SubPlan 1
    ->  Materialize (actual time=0.024..157.147 rows=705066 loops=8)
          Buffers: shared hit=16654 read=662, temp read=8397 written=1250
          ->  Seq Scan on tr_ord o (actual time=0.036..359.799 rows=730327 loops=1)
                Buffers: shared hit=16654 read=662
Planning:
  Buffers: shared hit=151
Planning Time: 0.748 ms
Execution Time: 1837.847 ms

Plain Text

복사

GOOD — 일치하는 픽업 이력이 없는 날짜를 NOT EXISTS로 검색

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT c.base_dt
FROM   startdbpg.cm_base_dt c
WHERE  c.base_dt BETWEEN DATE '2025-01-23'
                     AND DATE '2025-01-30'
AND    NOT EXISTS (
         SELECT 1
         FROM   startdbpg.tr_ord o
         WHERE  o.pkup_dtm::date = c.base_dt
       )
ORDER BY c.base_dt;

SQL

복사

실측 결과: 5건

Plain Text

복사

실측 실행계획 (GOOD)

Sort (actual time=441.916..441.920 rows=5 loops=1)
  Sort Key: c.base_dt
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=16692 read=630
  ->  Hash Right Anti Join (actual time=441.831..441.839 rows=5 loops=1)
        Hash Cond: ((o.pkup_dtm)::date = c.base_dt)
        Buffers: shared hit=16689 read=630
        ->  Seq Scan on tr_ord o (actual time=0.039..283.270 rows=730327 loops=1)
              Buffers: shared hit=16686 read=630
        ->  Hash (actual time=0.055..0.057 rows=8 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              Buffers: shared hit=3
              ->  Index Only Scan using cm_base_dt_pk on cm_base_dt c (actual time=0.044..0.046 rows=8 loops=1)
                    Index Cond: ((base_dt >= '2025-01-23'::date) AND (base_dt <= '2025-01-30'::date))
                    Heap Fetches: 0
                    Buffers: shared hit=3
Planning:
  Buffers: shared hit=204
Planning Time: 1.381 ms
Execution Time: 442.090 ms

Plain Text

복사

NOT IN도 NULL을 제외하면 정상 동작하는지 검증

SELECT c.base_dt
FROM   startdbpg.cm_base_dt c
WHERE  c.base_dt BETWEEN DATE '2025-01-23'
                     AND DATE '2025-01-30'
AND    c.base_dt NOT IN (
         SELECT o.pkup_dtm::date
         FROM   startdbpg.tr_ord o
         WHERE  o.pkup_dtm IS NOT NULL
       )
ORDER BY c.base_dt;

SQL

복사

실측 결과: GOOD과 같은 5건. 서브쿼리에서 NULL을 제거하자 의도한 결과가 반환됐다.

왜 0건이 되는가? NOT IN (값1, 값2, NULL)은 목록에 없는 날짜도 <> NULL 비교 때문에 UNKNOWN이 된다. WHERE는 TRUE인 행만 반환하므로 해당 날짜가 제외된다. 반면 NOT EXISTS는 해당 날짜와 일치하는 픽업 행이 존재하는지만 판단해 NULL의 영향을 받지 않는다.

핵심: NOT IN의 서브쿼리가 NULL을 반환할 수 있다면 결과가 예상과 달라진다. 먼저 NULL 가능성을 확인하고, 존재 여부를 배제하는 요구에는 NOT EXISTS를 우선 검토하자.

실행계획·성능 주의: 이번 실측에서는 BAD가 SubPlan + Materialize로 1,837.847ms, GOOD이 Hash Right Anti Join으로 442.090ms였다. 하지만 이 차이는 단일 실행 및 각 실행계획에 따른 것으로 NOT EXISTS가 항상 더 빠르다는 뜻은 아니다. 또한 pkup_dtm::date는 날짜 단위 비교를 설명하기 위한 표현이다. pkup_dtm 인덱스를 이용한 범위 탐색이 중요한 환경에서는 o.pkup_dtm >= c.base_dt AND o.pkup_dtm < c.base_dt + INTERVAL '1 day' 형태도 검토하자.

REMINDER 03-07. 부정 조건을 긍정 조건으로 바꿀 수 있는지 검토하자

상황: startdbpg.tr_ord에서 픽업 완료(PKUP)가 아닌 주문을 검색한다. 실제 ord_st 상태값과 건수는 PKUP 730,307건, PREP 10건, MFGC 10건이다(총 730,327건, NULL 0건). 이 데이터에서는 PKUP이 아닌 상태를 PREP, MFGC 두 값으로 정확하게 표현할 수 있다.

실습용 인덱스: 기존 ord_st 인덱스가 없는 것을 확인한 후, B-tree 인덱스 tr_ord_x53을 실제로 추가했다. 크기는 측정 당시 약 4.9MB였다.

CREATE INDEX tr_ord_x53 ON startdbpg.tr_ord (ord_st);

SQL

복사

BAD — 제외할 상태만 부정 조건으로 지정

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.ord_st
FROM   startdbpg.tr_ord o
WHERE  o.ord_st <> 'PKUP';

SQL

복사

인덱스 추가 후 실측 실행계획 (BAD, 재측정)

Gather (actual time=362.116..456.276 rows=20 loops=1)
  Workers Planned: 1
  Workers Launched: 1
  Buffers: shared hit=17134 read=182
  ->  Parallel Seq Scan on tr_ord o (actual time=275.065..346.264 rows=10 loops=2)
        Filter: ((ord_st)::text <> 'PKUP'::text)
        Rows Removed by Filter: 365154
        Buffers: shared hit=17134 read=182
Planning:
  Buffers: shared hit=130
Planning Time: 1.037 ms
Execution Time: 457.107 ms

Plain Text

복사

GOOD — 실제 필요한 상태를 IN으로 지정

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.ord_st
FROM   startdbpg.tr_ord o
WHERE  o.ord_st IN ('PREP', 'MFGC');

SQL

복사

인덱스 추가 후 실측 실행계획 (GOOD, 재측정)

Index Scan using tr_ord_x53 on tr_ord o (actual time=0.838..0.899 rows=20 loops=1)
  Index Cond: ((ord_st)::text = ANY ('{PREP,MFGC}'::text[]))
  Buffers: shared hit=7
Planning:
  Buffers: shared hit=189
Planning Time: 0.989 ms
Execution Time: 0.968 ms

Plain Text

복사

인덱스 추가 전후 비교 (실측):

비교	BAD: <> 'PKUP'	GOOD: IN ('PREP','MFGC')
조회 결과	20건	20건
인덱스 추가 전 실행계획	Parallel Seq Scan / Filter	Parallel Seq Scan / Filter
인덱스 추가 전 실행시간	406.163ms	288.039ms
인덱스 추가 후 실행계획	Parallel Seq Scan / Filter	Index Scan / Index Cond
추가 후 재측정 버퍼	17,316블록(hit 17,134 + read 182)	7블록(hit 7)
추가 후 재측정 실행시간	457.107ms	0.968ms

결과 동일성 검증 SQL: 두 조건으로 구한 주문번호 집합의 차집합을 각각 비교했다.

WITH neg AS MATERIALIZED (
    SELECT ord_no FROM startdbpg.tr_ord WHERE ord_st <> 'PKUP'
),
pos AS MATERIALIZED (
    SELECT ord_no FROM startdbpg.tr_ord WHERE ord_st IN ('PREP', 'MFGC')
)
SELECT (SELECT COUNT(*) FROM neg) AS neg_rows,
       (SELECT COUNT(*) FROM pos) AS pos_rows,
       (SELECT COUNT(*) FROM (SELECT ord_no FROM neg EXCEPT SELECT ord_no FROM pos) d) AS neg_only,
       (SELECT COUNT(*) FROM (SELECT ord_no FROM pos EXCEPT SELECT ord_no FROM neg) d) AS pos_only;

SQL

복사

실측 결과: neg_rows=20, pos_rows=20, neg_only=0, pos_only=0. 두 SQL이 현재 데이터에서 정확히 같은 20건을 반환한다.

핵심: 조회할 값이 명확하고 적다면 부정 조건 대신 긍정 조건을 검토하자. 이번에는 73만 건 중 20건이 필요했으며, IN은 tr_ord_x53의 Index Cond를 사용했지만 <>는 인덱스가 있어도 테이블 전체를 병렬 스캔했다. Rows Removed by Filter: 365154는 병렬 스캔의 loops=2 기준 작업당 평균 수치다.

예외·주의: ord_st <> 'PKUP'와 ord_st IN ('PREP','MFGC')는 모든 데이터에서 동치가 아니다. 새로운 주문 상태가 추가되면 결과가 달라질 수 있고, 두 조건 모두 NULL은 제외한다. 코드 집합이 완전하다는 업무 규칙을 먼저 확인해야 한다. 부정 조건이 언제나 인덱스를 못 타는 것도 아니며, 실제 실행계획과 선택도에 따라 달라진다. 재측정 실행시간은 캐시·부하 등의 영향을 받으므로 고정된 성능 개선 배율로 일반화하지 않는다.

REMINDER 03-08. 업무상 필수인 조회 조건을 빠뜨리지 말자

상황: 회원 M0019의 2023년 3월 주문을 조회하는 화면을 구현한다. 원래 예제의 M00019는 실제 startdbpg.tr_ord에 존재하지 않아, 실데이터에 존재하는 M0019로 변경했다. tr_ord에는 tr_ord_fk01(mbr_id), tr_ord_x51(ord_dtm) 인덱스가 이미 있다.

BAD — 회원 조건을 빠뜨리고 3월 주문 전체 검색

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.mbr_id, o.ord_dtm
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01';

SQL

복사

실측 실행계획 (BAD, 17,582건)

Index Scan using tr_ord_x51 on tr_ord o (actual time=0.052..5.920 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=139
Planning Time: 0.750 ms
Execution Time: 7.129 ms

Plain Text

복사

GOOD — 반드시 필요한 회원 조건을 함께 지정

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.mbr_id, o.ord_dtm
FROM   startdbpg.tr_ord o
WHERE  o.mbr_id = 'M0019'
  AND  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01';

SQL

복사

실측 실행계획 (GOOD, 31건)

Bitmap Heap Scan on tr_ord o (actual time=5.902..6.044 rows=31 loops=1)
  Recheck Cond: (((mbr_id)::text = 'M0019'::text) AND (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))
  Heap Blocks: exact=31
  Buffers: shared hit=80
  ->  BitmapAnd (actual time=5.876..5.877 rows=0 loops=1)
        Buffers: shared hit=49
        ->  Bitmap Index Scan on tr_ord_fk01 (actual time=4.857..4.857 rows=2130 loops=1)
              Index Cond: ((mbr_id)::text = 'M0019'::text)
              Buffers: shared hit=5
        ->  Bitmap Index Scan on tr_ord_x51 (actual time=0.888..0.888 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=44
Planning:
  Buffers: shared hit=150
Planning Time: 0.728 ms
Execution Time: 6.857 ms

Plain Text

복사

실측 비교

항목	BAD: 기간만 조회	GOOD: 회원 + 기간
결과 행 수	17,582건	31건
주요 접근	tr_ord_x51 Index Scan	BitmapAnd • Bitmap Heap Scan
인덱스 조건	ord_dtm	mbr_id • ord_dtm
Buffers hit	262	80
실행시간(1회)	7.129ms	6.857ms

전체 3월 주문은 17,582건이며 이 중 M0019의 주문은 31건이다. BAD는 화면 요구와 관계없는 다른 회원의 주문 17,551건까지 반환한다. GOOD은 회원 인덱스에서 2,130건, 날짜 인덱스에서 17,582건의 후보를 각각 찾고, BitmapAnd로 두 조건을 결합한 후 실제 테이블에서 31건을 확인했다(Heap Blocks: exact=31). 즉, GOOD이 처음부터 인덱스 항목 31개만 읽은 것은 아니다.

핵심: 기간·회원·매장·조직처럼 업무상 반드시 필요한 검색 조건은 WHERE에서 빠뜨리지 말자. 이번에는 인덱스를 추가하지 않았어도 필수 회원 조건을 지정하는 것만으로 조회 범위와 접근 방식이 달라졌다. 조회 건수의 큰 차이가 핵심이며, 단일 실행시간 차이 0.272ms만으로 의미 있는 성능 향상을 단정하지 않는다. 반환되는 결과 건수에 따라 애플리케이션 데이터 전송·처리량에도 차이가 생길 수 있다.

예외·주의: BAD와 GOOD은 동일한 결과를 반환하는 동치 SQL이 아니다. 업무 요구가 특정 회원의 주문 조회일 때만 GOOD이 정확하다. 관리자용 전체 회원 주문 조회에는 회원 조건을 임의로 추가하지 않는다. 실제 서비스에서는 회원 ID를 바인드 변수로 전달하고 권한도 별도로 검증해야 한다.

개발자가 기억할 체크리스트

날짜 컬럼을 함수로 변환하지 않았는가?

기간 조건은 종료값 미만의 반열린 범위인가?

LIKE와 NULL·NOT IN의 의미를 정확히 적용했는가?

업무에서 요구하는 필수 WHERE 조건이 빠지지 않았는가?

이 섹션의 한 줄 요약: 검색 조건은 정확한 데이터를 필요한 범위만큼 읽도록 작성하자. 날짜·NULL·LIKE·부정 조건은 결과와 성능을 함께 확인한다.