상황: 기준일 달력 startdbpg.cm_base_dt에서 2025년 1월 23일~30일 중 픽업이 한 번도 발생하지 않은 날짜를 찾는다. 주문 테이블 startdbpg.tr_ord의 pkup_dtm은 TIMESTAMP이며, 실제 730,327건 중 NULL이 20건 있다. 테이블 데이터를 변경하거나 NULL을 인위적으로 추가하지 않고 확인한 사례다.
BAD SQL — 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 SQL — 일치하는 픽업 이력이 없는 날짜를 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건
2025-01-26
2025-01-27
2025-01-28
2025-01-29
2025-01-30
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' 형태도 검토하자.