3일 동안 쿼리 튜닝만 한 스토리 (2)

요약

• 증상: 대시보드 진입 시 1.9분 무응답
• 원인: 267줄 단일 CTE 쿼리 + 상관 서브쿼리 수백만 회 + 5개 쿼리 순차 실행
• 해결: users 테이블 직접 조회 + 캐시 테이블 활용 + Promise.all 병렬화
• 결과: 1.9분 → 1초 (200만 회원 실측)

---

문제

가입자 통계를 고치고 나서 다른 화면들도 확인해봤다.

백오피스 대시보드 진입 시 Network 탭에서 summary API가 1.9분 동안 응답을 받지 못하고 있었다. 대시보드는 서비스의 첫 화면인데, 첫 화면이 2분 가까이 멈춰있는 상황이었다.

이번엔 조금 더 빠르게 원인을 찾을 수 있을 것 같았다. 지난번에 EXPLAIN ANALYZE와 코드 흐름 파악을 병행하는 방식을 배웠기 때문이다.

마찬가지로 개발서버에서 진행하였고, 운영서버와 최대한 비슷하게 데이터를 집어넣고 테스트를 진행하였다.

---

구조 파악

대상 파일 get-summary.js를 열었다. 흐름을 따라가보니 이런 구조였다.

GET /api/statistics/dashboard/summary
  └─ getDashboardSummaryData()   ← 쿼리 1 (267줄 단일 CTE)
  └─ getChannelData()            ← 쿼리 2
  └─ getMonthlyUserStats()       ← 쿼리 3
  └─ getTodayCsCount()           ← 쿼리 4
  └─ getMonthlyCsStats()         ← 쿼리 5

5개 쿼리가 전부 순차 await로 실행되고 있었다. 서로 의존성이 없는데도 직렬로 실행되니, 총 응답시간 = 각 쿼리 시간의 합이었다.

그런데 더 큰 문제는 첫 번째 쿼리였다.

---

EXPLAIN ANALYZE

getDashboardSummaryData의 핵심 쿼리를 추출해서 찍어봤다.

Execution Time: 1,053 ms  (getUserStats 부분)
Execution Time: 1,281 ms  (오늘 가입/해지 부분)

Buffers: shared hit=수천만
->  Parallel Seq Scan on users  (rows=676,531 loops=2)
->  Nested Loop  (actual time=4.122..1256.947 rows=12,040 loops=1)

이번엔 실행 시간 자체보다 실행 계획의 내용이 더 충격적이었다.

-- 기존 쿼리의 핵심 구조
user_daily_status AS (
  SELECT
    aui.user_id,
    (SELECT ush.after_status      -- 유저당 상관 서브쿼리 1
     FROM user_status_histories ush
     WHERE ush.user_id = aui.user_id ...
     ORDER BY ush.created_at DESC LIMIT 1) AS final_status,
    (SELECT ush.after_status      -- 유저당 상관 서브쿼리 2
     FROM user_status_histories ush
     WHERE ush.user_id = aui.user_id ...
     ORDER BY ush.created_at DESC LIMIT 1) AS prev_status
  FROM all_user_ids aui           -- 전체 유저 200만
  CROSS JOIN date_range dr
)

가입자 통계에서 봤던 것과 똑같은 패턴이었다. 200만 유저 전부에 대해 상관 서브쿼리를 수백만 회 돌리고 있었다.

근데 여기서 이상한 생각이 들었다.

---

답이 이미 있었다

user_status_histories를 전부 뒤져서 구하는 값이 정확히 뭔가를 확인했다.

• 누적 가입자 수: 현재 join_type = '10'인 유저 수
• 30일 유지 고객: 30일 이상 무료/유료 상태를 유지 중인 유저 수

users 테이블을 열어봤다. join_type 컬럼이 있었다.

join_type	의미
00	미가입
10	무료
20	유료

현재 가입 상태가 이미 저장되어 있었다.

Sequelize 설정도 확인했다. timestamps: true로 되어 있어서 join_type이 바뀔 때 updated_at이 자동으로 갱신되고 있었다.

즉, 기존 쿼리가 user_status_histories 전체 이력을 수백만 번 뒤져서 구하던 값을 이렇게 바꿀 수 있었다.

-- Before: user_status_histories 전체 이력 CROSS JOIN + 상관 서브쿼리 수백만 회
-- After: users 테이블 1회 스캔
SELECT
  COUNT(*) FILTER (WHERE join_type = '10') AS cumulative_free_users,
  COUNT(*) FILTER (WHERE join_type = '20') AS cumulative_paid_users,
  COUNT(*) FILTER (WHERE join_type = '10' AND updated_at <= NOW() - INTERVAL '30 days') AS active_free_users,
  COUNT(*) FILTER (WHERE join_type = '20' AND updated_at <= NOW() - INTERVAL '30 days') AS active_paid_users
FROM users;

이 변경 하나로 상관 서브쿼리 수백만 회 → 0회였다.

가장 큰 최적화는 불필요한 쿼리를 없애는 것이었다.

---

원인 1 — 이미 있는 값을 수백만 번 다시 계산

users.join_type에 현재 상태가 있는데 user_status_histories에서 전체 이력을 뒤지고 있었다.

30일 유지 고객 계산도 마찬가지였다.

-- Before: 유저마다 이력에서 마지막 상태 변경일을 상관 서브쿼리로 조회
active_users_count AS (
  SELECT COUNT(DISTINCT CASE
    WHEN uds.final_status = '10'
      AND (dr.date - (
        SELECT (created_at + INTERVAL '9 hours')::DATE
        FROM user_status_histories ush
        WHERE ush.user_id = uds.user_id AND ush.after_status = '10'
        ORDER BY ush.created_at DESC LIMIT 1
      )) >= 30
    THEN uds.user_id
  END) AS active_free_users_count
)

-- After: users.updated_at 활용
WHERE join_type = '10' AND updated_at <= NOW() - INTERVAL '30 days'

Sequelize가 상태 변경 시 updated_at을 자동 갱신하고 있었으므로 이걸 그대로 활용했다.

---

원인 2 — 신규 가입 판별을 위한 전체 이력 스캔

오늘 가입한 유저가 신규인지 기존 유저의 상태 변경인지 판별하는 로직이 있었다.

-- Before: 유저마다 이전 이력이 있는지 NOT EXISTS로 전체 스캔
NOT EXISTS (
  SELECT 1 FROM user_status_histories prev
  WHERE prev.user_id = ush.user_id
    AND prev.created_at < $오늘시작
) AS is_first_history

users.created_at을 보면 됐다. 오늘 생성된 유저면 신규 가입이다.

-- After: 오늘 생성된 유저 ID를 미리 뽑아서 LEFT JOIN (Hash Join으로 처리)
WITH new_user_ids AS (
  SELECT id FROM users
  WHERE created_at >= $오늘시작 AND created_at < $오늘끝
)
SELECT ...
FROM user_status_histories ush
LEFT JOIN new_user_ids nu ON ush.user_id = nu.id
WHERE ush.created_at >= $오늘시작 AND ush.created_at < $오늘끝

전체 이력 NOT EXISTS 스캔 → 오늘 범위 Hash Join으로 바꿨다.

---

원인 3 — 월별 통계를 매번 처음부터 계산

월별 누적 가입자 추이를 보여주는 차트가 있었다. 기존 코드는 이걸 전체 유저 × 5개월 CROSS JOIN으로 계산하고 있었다.

그런데 가입자 통계 기능에서 이미 join_daily라는 캐시 테이블에 날짜별 누적 데이터를 저장하고 있었다. 대시보드에서는 이걸 사용하지 않고 매번 처음부터 계산하고 있었던 것이다.

-- Before: 전체 유저 × 5개월 CROSS JOIN + 상관 서브쿼리
-- After: 이미 있는 캐시 테이블 조회
SELECT DISTINCT ON (TO_CHAR(jsd.stat_date, 'YYYY-MM'))
  TO_CHAR(jsd.stat_date, 'YYYY-MM') AS month_key,
  jsd.total_paid_users,
  jsd.total_free_users
FROM join_daily jsd
WHERE jsd.channel_id = -1 AND jsd.show_active_users_only = false
ORDER BY TO_CHAR(jsd.stat_date, 'YYYY-MM'), jsd.stat_date DESC;

이미 존재하는 캐시 테이블을 확인하지 않고 새로 계산하고 있었다.

---

원인 4 — 5개 쿼리 순차 실행

서로 의존성이 없는 5개 쿼리가 직렬로 실행되고 있었다.

// Before: 순차 실행 (총 시간 = 합)
const summaryData = await getDashboardSummaryData();
const channelData = await getChannelData();
const monthlyData = await getMonthlyUserStats();
const todayCsCount = await getTodayCsCount();
const monthlyCsData = await getMonthlyCsStats();

// After: 병렬 실행 (총 시간 = 최대값)
const [summaryData, channelData, monthlyData, todayCsCount, monthlyCsData] = await Promise.all([
  getDashboardSummaryData(),
  getChannelData(),
  getMonthlyUserStats(),
  getTodayCsCount(),
  getMonthlyCsStats()
]);

getDashboardSummaryData 내부에서도 두 쿼리를 Promise.all로 병렬화하고, 오늘 통계 쿼리도 테이블이 다른 4개로 분리해서 병렬 실행했다.

getDashboardSummaryData()
  └─ Promise.all([
       getUserStats()          ← users 1회 COUNT
       getTodayChangeStats()
         └─ Promise.all([
              쿼리 A: 가입/해지     ← user_status_histories + users (오늘 범위)
              쿼리 B: 당일해지       ← user_status_histories (오늘 범위)
              쿼리 C: 이용 통계      ← user_events + users
              쿼리 D: 문서 + 크레딧  ← 각 테이블 독립 COUNT
            ])
     ])

단, Promise.all을 쓸 때 커넥션 풀 크기를 확인했다. 병렬 쿼리 수가 풀 크기를 초과하면 대기가 생겨서 오히려 느려질 수 있다. 동시에 실행되는 최대 쿼리 수가 풀 크기 이내인지 확인하고 적용했다.

---

최적화 전후 비교

항목	Before	After
누적 가입자 계산	history 전체 CROSS JOIN + 상관 서브쿼리	users 테이블 1회 COUNT
30일 유지 고객	유저당 상관 서브쿼리	users.updated_at 조건
신규 가입 판별	history 전체 NOT EXISTS	users.created_at Hash Join
월별 누적 통계	전체 유저 × 5개월 CROSS JOIN	join_statistics_daily 캐시
상관 서브쿼리	수백만 회	0회
쿼리 실행 방식	5개 순차 await	Promise.all 병렬
API 응답시간	1.9분	1초

---

이번에 배운 것

1. 테이블 설계를 확인해라.

users.join_type에 이미 현재 상태가 있는데 user_status_histories에서 수백만 번 재계산하고 있었다.

2. Sequelize의 자동 갱신을 활용해라.

timestamps: true 설정으로 updated_at이 상태 변경 시 자동 갱신된다는 걸 확인하고 상관 서브쿼리를 완전히 없앴다. ORM 설정이 쿼리 최적화에 영향을 줄 수 있다.

3. 이미 존재하는 캐시 테이블을 확인해라.

join_daily에 월별 누적 데이터가 이미 있었는데 대시보드에서는 사용하지 않고 있었다. 새로 만들기 전에 기존 데이터 구조를 먼저 파악하는 게 중요하다.

4. Promise.all은 공짜 최적화지만, 커넥션 풀을 확인해라.

의존성 없는 쿼리를 순차 실행하는 건 순수 낭비다. 코드 한 줄 바꿔서 절반 이상 단축할 수 있다. 단, 동시 실행 쿼리 수가 풀 크기를 초과하지 않는지 반드시 확인해야 한다.