상황: 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 SQL — 제외할 상태만 부정 조건으로 지정
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 SQL — 실제 필요한 상태를 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은 제외한다. 코드 집합이 완전하다는 업무 규칙을 먼저 확인해야 한다. 부정 조건이 언제나 인덱스를 못 타는 것도 아니며, 실제 실행계획과 선택도에 따라 달라진다. 재측정 실행시간은 캐시·부하 등의 영향을 받으므로 고정된 성능 개선 배율로 일반화하지 않는다.
추가 주의 — 복합 인덱스가 있어도 부정 일자 조건은 Filter로 남을 수 있다
실습 인덱스: 기존 tr_ord_x55(shop_id, ord_dtm, ord_no) (B-tree)
매장 조건은 동등 비교, 주문일시는 부정 범위 조건으로 조회한다. 테이블 접근 여부를 드러내기 위해 인덱스에 없는 ord_st도 SELECT에 포함한다.
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no
,o.shop_id
,o.ord_dtm
,o.ord_st
FROM startdbpg.tr_ord o
WHERE o.shop_id = 'S092'
AND o.ord_dtm NOT BETWEEN TIMESTAMP '2020-04-01'
AND TIMESTAMP '2020-10-31 23:59:59'
ORDER BY o.ord_dtm, o.ord_no
LIMIT 30;
SQL
복사
Supabase PostgreSQL 17 실측 실행계획
Limit (actual time=0.109..0.568 rows=30 loops=1)
Buffers: shared hit=60
-> Index Scan using tr_ord_x55 on tr_ord o (actual time=0.108..0.561 rows=30 loops=1)
Index Cond: ((shop_id)::text = 'S092'::text)
Filter: ((ord_dtm < '2020-04-01 00:00:00'::timestamp without time zone) OR (ord_dtm > '2020-10-31 23:59:59'::timestamp without time zone))
Rows Removed by Filter: 37
Buffers: shared hit=60
Planning:
Buffers: shared hit=204
Planning Time: 1.263 ms
Execution Time: 0.673 ms
Plain Text
복사
•
Index Cond에는 매장 조건만 적용되었다. 복합 인덱스에 ord_dtm도 있지만, 이번 계획에서는 부정 범위 조건이 인덱스 탐색 범위를 줄이지 못했다.
•
Filter에서 주문일시 조건을 판단해 37건을 제거했다. Index Scan에서 인덱스에 없는 ord_st까지 조회하므로 테이블 행 접근도 발생한다.
•
LIMIT 30 때문에 결과 30건에서 검색을 종료했다. Rows Removed by Filter: 37은 이 실행에서 제거된 행 수이며, 매장의 전체 주문에 대한 제거 건수가 아니다.
PostgreSQL 주의: 인덱스 컬럼이라고 해서 모든 부정 조건이 Index Cond가 되는 것은 아니다.
•
매장 조건으로 인덱스를 찾더라도, 일자의 <>, NOT BETWEEN 등 부정 조건은 테이블 접근 후 Filter로 평가될 수 있다. 실제로 어떤 조건이 Index Cond인지, 어떤 조건이 Filter인지 반드시 실행계획에서 확인하자.
•
항상 Filter로 남는다는 뜻은 아니다. NOT BETWEEN이 두 범위로 풀려 BitmapOr 등 인덱스 경로가 선택될 수도 있다. ORDER BY, LIMIT, 선택도 및 통계 등에 따라 계획은 달라진다.
•
부정 조건을 긍정 범위 조건으로 바꿀 때는 NULL 처리와 검색 결과의 동등성을 먼저 검증하자.