줄였는데 왜 더 느려지는 거야?

어드민 vs 셀러 어드민

내부 직원을 위한 백오피스(어드민)를 개발하다, 셀러 어드민도 개발해달라는 요구가 (오래 전에) 있었다.

상품/주문 관리 등등 기능은 백오피스와 다를 게 하나도 없어서, 셀러 어드민용 API를 별도로 만들지 않고, 기존 어드민 API에 특정 셀러로 필터를 하기로 결정했다. 그리고 그를 위해 모든 뷰셋에 다음과 같은 믹스인을 상속시켰다.

class SellerAdminMixIn(viewsets.ModelViewSet):
    def get_queryset(self):
        queryset = super().get_queryset()
        if self.seller_lookup_path is not None:
            if seller_code := self.request.GET.get('seller_code'):
                queryset = queryset.filter(**{f'{self.seller_lookup_path}__code': seller_code})
        return queryset

특정 셀러란 필터를 추가하니까 탐색할 데이터는 훨씬 더 적을테고, 그러니 셀러 어드민은 백오피스보다 빠르면 빨랐지 느려지면 안 된다.

그리고 주문, 상품, 정산 등 어지간한 기능의 성능은 예상대로 잘 동작했다.

발주...

하지만 발주 기능이 추가되었고, 이게 어드민 쪽에선 문제가 없는데 셀러 어드민 쪽에선 504가 뜨는 상황이 발생했다.

문제를 파보기 전에 어드민 쪽 쿼리와 쿼리 플랜을 보자. 참고로 단순화했고, 데이터는 로컬이라 프로덕션보다 더 적은 상황.

// 쿼리
SELECT DISTINCT pv.id, T3.id
FROM shopping_productvariant pv
INNER JOIN shopping_supplyorderitemunit soi
  ON pv.id = soi.productvariant_id
INNER JOIN shopping_supplyorderitemunit T3
  ON pv.id = T3.productvariant_id
WHERE T3.created BETWEEN '2025-04-14' AND '2025-07-14'

// 쿼리 플랜
Unique (4,098 rows) [96ms]
└─ Sort (149,421 rows)
   └─ Hash Join (149,421 rows)
      ├─ Seq Scan on supplyorderitemunit (12,784 rows)
      └─ Hash
         └─ Nested Loop
            ├─ Seq Scan on supplyorderitemunit T3 (4,098 rows 날짜 필터)
            └─ Memoize
               └─ Index Scan on productvariant

이제 동일한 api 요청에 seller 파라미터 하나만 더 붙였을 때의 쿼리와 쿼리 플랜을 보자.

// 쿼리
SELECT DISTINCT pv.id, T6.id
FROM shopping_productvariant pv
INNER JOIN shopping_supplyorderitemunit soi 
  ON pv.id = soi.productvariant_id
INNER JOIN shopping_product p 
  ON pv.product_id = p.id
INNER JOIN shopping_seller s 
  ON p.seller_id = s.id
INNER JOIN shopping_supplyorderitemunit T6 
  ON pv.id = T6.productvariant_id
WHERE s.code = 'ZXCVZXCV'
  AND T6.created BETWEEN '2025-04-14' AND '2025-07-14'

// 쿼리 플랜
Unique (346 rows) [2,070ms] ❌
└─ Sort (81,348 rows → 346 rows)
   └─ Nested Loop Anti Join (NOT EXISTS)
      └─ Nested Loop (supplyorderitemunit T6)
         └─ Nested Loop (supplyorderitemunit)
            └─ Nested Loop (productvariant)
               └─ Nested Loop (product)
                  └─ Nested Loop (seller)
                     └─ Index Scan on seller (1 row)
                  └─ Index Scan on product (2,320 rows)
               └─ Index Scan on productvariant (6,013 rows)
            └─ Index Scan on supplyorderitemunit (rows=235 loops=346 → 81,310 rows)
         └─ Index Scan on supplyorderitemunit T6 (날짜 필터)

필터 추가했는데 왜 느려졌을까?

일단 seller 추가 쿼리를 보면, join 이 (당연히) 늘어났다. 기존엔 supplyorderitemunit 하나만 하면 되는데, seller를 추가하니 pv에 연결된 product와 product에 연결된 seller 까지. 하지만 전부 inner join 이고 인덱스도 타는데 테이블 조금 늘어났다고 문제가 되는 걸까?

대답은 yes이고, 이유는 join 방식 때문.

옵티마이저의 잘못된 조인 전략

항목	셀러 어드민	어드민
조인 테이블 수	5개 테이블	3개 테이블
조인 순서	seller → product → variant → supplyorderitemunit → supplyorderitemunit(T6)	supplyorderitemunit(T3) → variant → supplyorderitemunit
조인 방식	Nested Loop only	Hash Join + Nested Loop + Memoize
시작 데이터	1개 (seller)	4,098개 (T3 필터링)
중간 결과	81,348 rows	149,421 rows
최종 결과	346 rows	4,098 rows
인덱스 조회	6,013번 (각 variant별)	2,069번 (Memoize 캐시)
캐시 활용	없음	Memoize 49.5% 히트율
I/O 패턴	Random I/O (인덱스)	Sequential I/O (Hash Join)
메모리 사용	최소	Hash 테이블 289KB
실행 시간	2,070ms ❌	96ms ✅
성능 차이	기준	95.4% 빠름

셀러 전체인 어드민 쪽이 시작 데이터, 중간 결과, 최종 결과 모두 데이터가 많지만, 그럼에도 불구하고 Hash Join 으로 효율적으로 쿼리가 되었다.

셀러 어드민 쪽은 옵티마이저가 적은 수(seller 하나)부터 점진적으로 Nested Loop로 조인하는 방식을 택했는데, seller(1) → product(2,320) → productvariant(6,013 - unique 346) → SupplyOrderItemUnit(346x235=81,348) 순으로 증폭이 되었고 이걸 루프를 돌며 랜덤 엑세스(인덱스)를 타다보니 성능이 나쁠 수 밖에 없는 상황.

그럼 셀러 어드민 쿼리의 옵티마이저는 왜 Nested Loop 를 택했는지가 궁금해지는데, 그 전에 둘의 쿼리 플랜을 조금 더 분석하며 Hash Join 과 Nested Loop Join의 차이를 살펴보자.

Hash Join vs Nested Loop Join

위의 쿼리 플랜을 코드로 변경해보면 다음과 같다. (쿼리 플랜은 nested 된 것부터 읽어나가야해서 바로바로 읽는 게 좀 헷갈린다. 개인적으론 아래처럼 코드화해서 읽는 게 낫더라.)

어드민 쿼리의 구조 (Hash Join 사용)

# 1단계: Build Phase 준비
for t3 in supplyorderitemunit:  # 루프 1: 전체 스캔
    if date_filter(t3):  # 날짜 필터로 4,098개만 선택
        # Memoize를 통한 productvariant 조회
        if t3.variant_id in cache:
            variant = cache[t3.variant_id]  # 캐시 히트
        else:
            variant = index_lookup(t3.variant_id)  # 캐시 미스
            cache[t3.variant_id] = variant
        
        # 해시 테이블에 추가
        hash_table[variant.id].append((variant, t3))

# 2단계: Hash Join Probe Phase
for soi in supplyorderitemunit:  # 루프 2: 전체 스캔 (12,784개)
    if soi.variant_id in hash_table:  # O(1) 해시 조회
        for matched in hash_table[soi.variant_id]:
            process(matched, soi)

Hash Join은 hash table을 만드는 Build Phase와 해당 테이블을 이용해 Join을 하는 Probe Phase 로 나뉘는데, 일반적으로 데이터가 적은 걸로 hash table을 만들고, Probe Phase 에선 큰 테이블을 Sequential(순차) Scan을 한다.

이에 따라 Hash Join는 각 테이블을 한 번씩만 읽기 때문에 시간복잡도는 O(n+m) 이고 O(16,882) (4098 + 12784) (물론 n, m으로 단순하게 시간복잡도를 계산하는 걸로는 부족하다. hash table을 만드는 오버헤드 비용도 있기 때문에.)

셀러 어드민 쿼리의 구조 (Nested Loop Join)

# 5중 중첩 루프
for seller in sellers:           # 루프 1: 전체 중 1개
    for product in products:     # 루프 2: 2,320번
        for variant in unique_variants: # 루프 3: 346
            supplyorderitemunits = index_lookup('soi_by_variant', variant.id) 평균 235개
            for sp_unit in supplyorderitemunits:  # 루프 4
                for t6 in index_lookup(variant.id):     # 루프 5: 또 조회
                    if date_filter(t6):
                        process(...)        # 81,348개 행 생성

seller(1) → product(2,320) → productvariant(6,013)로 가면서 각 단계마다 인덱스를 통해 다음 테이블을 조회한다. 특히 문제가 되는 부분은 supplyorderitemunits = index_lookup('soi_by_variant', variant.id) 평균 235개 단계다.

옵티마이저는 왜 Nested Loop 를 택했나?

일반적으로 Nested Loop 는 처리할 데이터가 극소수이고 조인 컬럼에 인덱스가 있을 때 어울리고, Hash Join은 많은 데이터를 처리할 때 어울린다고 한다.

그런데 셀러 어드민에선 데이터가 많음에도 불구하고 왜 Nested Loop 로 동작을 했나 궁금한데, 아래는 클로드를 닥달해서 얻은 대답이다. (신뢰도가 100%은 아니겠지만, 그럴듯하다고 생각한다.)

PostgreSQL 옵티마이저는 통계 정보를 기반으로 실행 계획을 결정한다. 셀러 어드민 쿼리에서:

• 시작점의 함정: seller 1개로 시작 → "적은 데이터니까 Nested Loop"
• 통계 정보 오류: productvariant당 supplyorderitemunit이 3개로 예상했지만 실제로는 235개
• 중간 증폭 미예측: 1 → 2,320 → 6,013 → 81,348로 증폭되는 것을 예측 못함

결과적으로 "적은 데이터"라고 판단했지만, 실제로는 대량의 Random I/O가 발생하는 최악의 시나리오가 되었다.

셀러 필터를 추가하면 탐색의 범위가 줄어서 빨라질 거라 생각했는데, 오히려 JOIN의 시작점이 1개를 만든 게 트리거가 되고 이것이 잘못된 통계 정보와 맞물려 최종적으로 Nested Loop라는 실행 계획을 선택한 것으로 보인다.

그렇다면 통계 정보가 정확했다면 옵티마이저는 다른 선택을 했을까 궁금해서 질문해보니, (역시 신뢰도가 100%는 아니겠지만) 클로드가 그럴듯한 답을 해준다.

# 현재 (잘못된 통계)
nested_loop_cost = 6,013 × 3 × random_page_cost
                = 6,013 × 3 × 4.0
                = 72,156

# 정확한 통계라면
nested_loop_cost = 6,013 × 235 × random_page_cost
                = 6,013 × 235 × 4.0
                = 5,652,220  # 78배 증가!

# Hash Join 비용
hash_join_cost = seq_scan_cost + build_cost
               = 12,784 × 1.0 + 6,013 × 1.0
               = 18,797

# 결과: Hash Join이 압도적으로 유리!

// 예상되는 쿼리 플랜 
옵션 1: 부분적 Hash Join
Unique (346 rows) [예상 300-400ms] ✅
└─ Sort
   └─ Hash Join  ← 여기가 변경!
      ├─ Seq Scan on supplyorderitemunit T6
      │  Filter: created BETWEEN dates
      └─ Hash (6,013 rows)
         └─ Hash Join  ← 또는 여기도!
            ├─ Nested Loop (여전히 작은 부분은 유지)
            │  └─ Nested Loop
            │     └─ Nested Loop
            │        └─ Index Scan on seller (1 row)
            │     └─ Index Scan on product (2,320 rows)
            └─ Hash
               └─ Seq Scan on productvariant
옵션 2: 전면적 재구성
Unique (346 rows) [예상 200ms] ✅
└─ Sort
   └─ Hash Join
      ├─ Seq Scan on supplyorderitemunit (12,784 rows)
      └─ Hash
         └─ Nested Loop
            ├─ HashAggregate  ← variant ID만 먼저 구함
            │  └─ Nested Loop (seller → product → variant)
            └─ Index Scan on supplyorderitemunit T6
               Filter: created BETWEEN dates

해결책

해결책은 간단했다. seller 필터를 추가하는 게 아니라 seller 조건이 들어간 걸 미리 평가하고 id만 뽑아서 재이용하는 방식.

class SellerAdminMixIn(viewsets.ModelViewSet):
    def get_queryset(self):
        queryset = super().get_queryset()
        if self.seller_lookup_path is not None:
            if seller_code := self.request.GET.get('seller_code'):
                seller_filtered = list(queryset.filter(**{f'{self.seller_lookup_path}__code': seller_code}).values_list('id', flat=True))
                queryset = queryset.filter(id__in=seller_filtered)
        return queryset

쿼리와 쿼리 플랜은 다음과 같다. 알아서 hash join 으로 짜줘서 동일한 결과인데 성능이 92.2%나 개선이 되었다.

// 쿼리
SELECT DISTINCT pv.id, T3.id
FROM shopping_productvariant pv
INNER JOIN shopping_supplyorderitemunit soi
  ON pv.id = soi.productvariant_id
INNER JOIN shopping_supplyorderitemunit T3
  ON pv.id = T3.productvariant_id
WHERE pv.id IN (912534, 912535, ... 67개)  -- seller의 variant ID들
  AND T3.created BETWEEN '2025-04-14' AND '2025-07-14'

// 쿼리 플랜
Unique (346 rows) [161ms] ✅
└─ Sort (81,348 rows)
   └─ Hash Join (81,348 rows)
      ├─ Seq Scan on supplyorderitemunit (12,784 rows)
      └─ Hash (840 rows, 11KB)
         └─ Index Scan on productvariant 
            Index Cond: id IN (912534, 912535, ... 67개)

그리고 쿼리 플랜의 코드화

# 1단계: Build Phase - IN 절의 variant들을 해시 테이블로
variant_ids = [912534, 912535, ...]  # 67개 (seller의 variants)
hash_table = {}

for variant in productvariant:
    if variant.id in variant_ids:  # IN 절 조건
        hash_table[variant.id] = variant

# 2단계: Hash Join Probe Phase  
for soi in supplyorderitemunit:  # 전체 스캔 (12,784개)
    if soi.variant_id in hash_table:  # O(1) 해시 조회
        # T3와 추가 조인 필요 (날짜 필터)
        for t3 in supplyorderitemunit:
            if t3.variant_id == soi.variant_id and date_filter(t3):
                process(hash_table[soi.variant_id], soi, t3)