[권장:성능]WITH 조건값과 CROSS JOIN의 인덱스 활용 주의

날짜 검색 조건을 WITH 절에서 한 번 정의하고 CROSS JOIN으로 가져오는 방식 자체가 문제는 아닙니다. 그러나 CTE가 물질화된 상태에서 선택 조건용 OR·COALESCE 등을 결합하면 날짜 범위 조건이 인덱스 검색 조건으로 구성되지 않아 전체 주문을 읽을 수 있습니다.

BAD: 470.919ms, hit=15698, read=1618

물질화된 조건값에 OR 조건 적용

WITH p AS MATERIALIZED (
    SELECT  TIMESTAMP '2025-01-25'
            AS start_dt
           ,TIMESTAMP '2025-01-26'
            AS end_dt
)
SELECT  o.ord_no
        ,o.ord_dtm
        ,o.ord_amt
FROM    startdbpg.tr_ord o
        CROSS JOIN p
WHERE   (p.start_dt IS NULL
         OR o.ord_dtm >= p.start_dt)
AND     (p.end_dt IS NULL
         OR o.ord_dtm < p.end_dt)
;

SQL

복사

실행계획 보기

•

MATERIALIZED CTE에서 가져온 날짜값은 외부 SQL 계획 단계에서 단순 상수로 펼쳐지지 않습니다. 여기에 p.start_dt IS NULL OR ... 같은 선택 조건이 더해지면, PostgreSQL이 날짜 범위를 Index Cond로 구성하지 못하고 Join Filter로 처리할 수 있습니다. 이번 실측에서는 주문 730,327건을 모두 읽었습니다.

GOOD: 0.449ms, hit=10

날짜 범위를 직접 비교

WITH p AS MATERIALIZED (
    SELECT  TIMESTAMP '2025-01-25'
            AS start_dt
           ,TIMESTAMP '2025-01-26'
            AS end_dt
)
SELECT  o.ord_no
       ,o.ord_dtm
       ,o.ord_amt
FROM    startdbpg.tr_ord o
        CROSS JOIN p
WHERE   o.ord_dtm >= p.start_dt
AND     o.ord_dtm < p.end_dt
;

SQL

복사

실행계획 보기

•

날짜 검색이 필요한 경우 컬럼을 변형하지 않고 직접 범위 조건으로 비교합니다. CROSS JOIN 및 MATERIALIZED를 그대로 유지해도 ord_dtm >= p.start_dt AND ord_dtm < p.end_dt는 실제로 인덱스 범위 검색에 사용됐습니다.

실제 실행 결과 비교

비교 항목	BAD	GOOD
실행계획	Seq Scan • Join Filter	Index Scan • Index Cond
주문 테이블 접근 행 수	730,327건	445건
최종 반환 행 수	445건	445건
실행시간(1회 측정)	470.919ms	0.449ms
공유 버퍼	hit 15,698 / read 1,618	hit 10

실습 테이블은 startdbpg.tr_ord, 날짜 컬럼은 ord_dtm, 사용 가능한 날짜 인덱스는 tr_ord_x51입니다. 위 수치는 Supabase에서 EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)로 실제 확인한 결과이며, 실행시간은 캐시 및 측정 시점의 부하에 따라 달라집니다.

TIP

•

① 시작일·종료일이 선택사항이라면 입력된 조건만 WHERE 절에 포함하도록 SQL을 생성하는 방법을 검토합니다. 무조건 OR, CASE, COALESCE로 모든 조건을 한 SQL에 처리하려 하지 않는 것이 좋습니다.

•

② CTE를 여러 곳에서 참조하면 PostgreSQL이 기본적으로 CTE를 물질화할 수 있습니다. 단순한 날짜값 정의라도 실제 계획에서 CTE Scan이 발생하는지, 검색 조건이 Index Cond인지 Join Filter인지 확인해야 합니다.

•

③ o.ord_dtm::DATE = p.base_dt처럼 검색 컬럼을 변형해도 일반적인 ord_dtm 인덱스 범위 검색이 어려워집니다. 하루 검색이라면 시작 시각 이상, 다음 날 시작 시각 미만으로 비교합니다.

KEY POINT

•

중요: CROSS JOIN 자체가 인덱스 사용을 막는 것은 아닙니다. 이번 사례에서의 차이는 물질화된 조건값과 선택 조건용 OR의 조합입니다. WITH p AS (...)처럼 일반적인 단일 참조 CTE는 인라인화되면서 같은 OR 조건도 단순화해 인덱스를 사용할 수 있었습니다.

정리: WITH 절로 조건값을 공통화하더라도, 그 조건이 원본 테이블의 인덱스 검색 조건으로 전달되는지를 실행계획에서 확인해야 합니다. 단순한 구문 형태보다 Index Cond, Join Filter, 실제 처리 행 수가 판단 기준입니다.

기존 Supabase 스키마를 대상으로 읽기 전용 조회 및 실행계획만 확인했으며, 테이블 구조와 데이터는 변경하지 않았습니다.