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

요약

증상: 가입자 통계 화면 진입 시 6.6분 무응답
원인: CROSS JOIN + 상관 서브쿼리 3,700만 회 + 날짜별 순차 루프 40회
해결: 스냅샷 + LATERAL → O(전체유저) → O(변경유저) 로 스케일링 특성 변경
결과: 6.6분 → 4초 (200만 회원 실측)

문제

운영 백오피스 가입자 통계 화면에 진입했을 때 페이지가 뜨지 않는 문제가 있었다.

운영 페이지는 확인해볼 수 없고, 현상과 이미지 정도만 받을 수 있었다.

개발 서버에서는 운영 서버만큼 데이터가 없어서 성능적 이슈를 못 느꼈었다. 원활한 테스트를 위해 개발서버에도 운영서버와 최대한 비슷하게 데이터를 집어넣고 테스트를 진행하였다.

데이터를 API 이용해서 200만건 넣느라 오래 걸렸다. DB에 insert를 하면 오래 안걸리겠지만, 최대한 운영과 비슷한 환경을 만들어야했기에 API를 활용했다. 때문에 데이터를 집어 넣는 도중에 테스트 한 부분도 있어서 뒤죽박죽이다.

Network 탭을 보니 get-list API가 응답을 받지 못하고 있었다. 1분, 2분, 3분… 6분이 넘어도 응답이 없었다.

처음엔 서버가 다운됐나 싶었는데, 다른 API는 멀쩡했다. 이 화면만 특정적으로 느린 것이었다. join_daily라는 캐시 테이블이 비어있을 때 재계산이 트리거되는 구조였는데, 200만 회원 기준으로 이 재계산이 6.6분 이상 걸리고 있었다. 매일 한 번은 계산을 해야하니 매일 6분 이상 계산을 해야하는 상황이었다.

재계산이 느리다는 건 알겠는데, 어디서 시간을 잡아먹는지 알 수가 없었다. 1,768줄짜리 파일을 처음부터 읽을 수는 없으니, 일단 전체 호출 흐름부터 파악했다.

원활한 테스트를 위해 개발서버에서 진행하였고, 개발서버에도 운영서버와 최대한 비슷하게 데이터를 집어넣고 테스트를 진행하였다.

구조 파악

흐름을 쭉 따라가 보니 이런 구조였다.

GET /api/statistics/join/get-list
  └─ calculateAndSaveStatistics()
       └─ for (날짜 1~10) {            ← 순차 await 루프
            calculateStatisticsForDate()
              └─ getNewJoinStatisticsDataOriginal()  ← 핵심 쿼리
            if (전날 캐시 없음) {
              calculateStatisticsForDate(전날)       ← 재귀 호출
            }
            upsertDailyStatistics()                 ← 날짜별 개별 INSERT
          }

10일치를 재계산할 때 실제 DB 쿼리가 약 20회 발생하고 있었다.

그리고 이 화면은 로딩 시 API를 2번 호출한다는 것도 확인했다. (showActiveUsersOnly=false, showActiveUsersOnly=true 각각 한 번씩)

즉, 화면 진입 한 번 = 약 40회 DB 쿼리 순차 실행.

그런데 더 큰 문제는 따로 있었다. 핵심 쿼리인 getNewJoinStatisticsDataOriginal 자체가 문제였다. 이걸 EXPLAIN ANALYZE로 찍어봤다.

EXPLAIN ANALYZE

Execution Time: 23,952 ms
Buffers: shared hit=29,493,308  read=86,393
Sort Method: external merge  Disk: 18,464kB

43만 유저일 때, 2일 범위 기준으로 쿼리 1회가 24초였다.

실행계획을 보다가 이 줄에서 멈췄다.

->  Index Only Scan using idx_ush_user_date on user_status_histories
    (actual time=0.002 rows=1 loops=7,963,720)

인덱스도 있고, Index Only Scan도 맞다. 그런데 loops=7,963,720.

인덱스가 있어도 800만 번 실행하면 24초가 걸린다. 당연한 결과였다.

이때 처음으로 깨달은 게 있었다. EXPLAIN을 볼 때 Index Only Scan이라는 실행 방식만 보고 "인덱스 타고 있으니까 괜찮겠지"라고 생각했는데, 그게 아니었다. 각 노드의 actual time × loops가 그 노드의 실제 총 비용이다. 실행 방식보다 실행 횟수가 훨씬 중요했다.

그러면 왜 800만 번 실행하는 걸까 ?

원인 1 — CROSS JOIN + 상관 서브쿼리

핵심 CTE인 user_daily_status의 구조를 단순화하면 이렇다.

user_daily_status AS (
  SELECT
    aui.user_id,
    dr.date,
    -- 상관 서브쿼리 1: 이 날짜까지의 최종 상태
    (SELECT after_status FROM user_status_histories
     WHERE user_id = aui.user_id AND created_at < 날짜끝
     ORDER BY created_at DESC LIMIT 1) AS final_status,
    -- 상관 서브쿼리 2: 전날 상태
    (SELECT after_status FROM user_status_histories
     WHERE user_id = aui.user_id AND created_at < 날짜시작
     ORDER BY created_at DESC LIMIT 1) AS prev_status
  FROM all_user_ids aui    -- 전체 유저 200만
  CROSS JOIN date_range dr -- 2일
)

계산해보면 간단하다.

all_user_ids: 200만 행
date_range: 2일
CROSS JOIN: 200만 × 2 = 400만 행
각 행마다 상관 서브쿼리 2개 → 800만 회 실행

200만 유저면 약 3,700만 회.

여기서 나를 가장 당황하게 만든 부분이 있었다. 분명히 created_at 날짜 필터가 있는데, 왜 200만 유저를 전부 읽는 걸까? 처음엔 이해가 안 됐다.

답은 WHERE 절의 위치였다. 날짜 조건이 서브쿼리 안쪽에만 있고, 바깥의 CROSS JOIN은 날짜와 무관하게 전체를 순회한다. 필터는 이미 400만 행이 만들어진 뒤에 각 서브쿼리 안에서 작동한다. "하루치 계산이니까 빠르겠지"라는 직관이 틀렸다.

원인 2 — DATE() 함수로 인덱스 무력화

당일해지 계산 쿼리에서 이런 패턴을 발견했다.

WHERE DATE(ush.created_at + INTERVAL '9 hours') = dr.date

DATE(컬럼 + 상수) 형태는 컬럼에 함수를 씌우므로 인덱스를 사용할 수 없다. 46만 건 풀스캔이 4회 발생하고 있었다.

해결은 간단하다. 함수를 컬럼이 아니라 비교값에 적용하면 된다.

-- Before: 인덱스 사용 불가
WHERE DATE(created_at + INTERVAL '9 hours') = '2026-03-16'

-- After: 범위 조건으로 인덱스 Range Scan 가능
WHERE created_at >= '2026-03-15 15:00:00'
  AND created_at <  '2026-03-16 15:00:00'

원인 3 — 항상 빈 결과를 반환하는 CTE

코드를 읽다가 이런 CTE를 발견했다.

users_with_transitions AS (
  SELECT DISTINCT user_id FROM user_status_histories WHERE ...
),
users_without_transitions AS (
  SELECT user_id FROM initial_joins ij
  WHERE NOT EXISTS (SELECT 1 FROM users_with_transitions WHERE user_id = ij.user_id)
)

initial_joins도 user_status_histories에서, users_with_transitions도 user_status_histories에서 나온다. 이 테이블에 이력이 있으면 무조건 users_with_transitions에 포함된다. 따라서 users_without_transitions는 항상 빈 결과를 반환한다.

비용만 소모하고 아무것도 하지 않는 코드가 5개나 있었다.

1차 시도 — DISTINCT ON + LAG, 그리고 새로운 문제

상관 서브쿼리를 없애는 게 급선무였다. Window Function을 쓰면 된다고 생각했다.

-- 각 날짜의 유저별 최종 상태를 DISTINCT ON으로 한 번에
user_status_at_date AS (
  SELECT DISTINCT ON (dr.date, ush.user_id)
    dr.date, ush.user_id, ush.after_status AS final_status
  FROM date_range dr
  JOIN user_status_histories ush
    ON ush.created_at < (dr.date + 1일)
  ORDER BY dr.date, ush.user_id, ush.created_at DESC
),
-- 전날 상태는 LAG()로 — 상관 서브쿼리 제거
user_daily_with_prev AS (
  SELECT *, LAG(final_status) OVER (PARTITION BY user_id ORDER BY date) AS prev_status
  FROM user_status_at_date
)

상관 서브쿼리 800만 회 → 0회 방향은 맞았다.

그런데 코드 리뷰를 하면서 문제를 발견했다.

date_range × user_status_histories JOIN의 중간 결과를 계산해봤다.

10일 × 전체 이력 수백만 건 = 수천만 행
이걸 ORDER BY dr.date, ush.user_id, ush.created_at DESC로 정렬

상관 서브쿼리는 없앴지만 "전체 유저를 매번 읽는" 구조는 그대로였다. 여전히 O(전체유저 × 날짜)이었다..

변경이 있는 유저만 보면 된다

이 통계가 실제로 구하는 게 뭔가?

특정 날짜의 유료/무료 총이용자 수
각 날짜의 가입/해지 건수

1번은 기준일의 스냅샷을 한 번만 찍으면 된다. 매 날짜마다 새로 계산할 이유가 없다.

2번은 그날 상태가 바뀐 유저만 보면 된다. 200만 유저 중 하루에 상태가 바뀌는 유저는 수천~수만 명이다.

Before: O(전체유저 × 날짜)  — 매일 200만 명 전부 읽기
After:  O(변경유저 × 날짜)  — 매일 상태 바뀐 유저만 읽기

유저가 10배 늘어도 하루 변경량이 비슷하다면 속도가 유지된다. 단순히 "빠르게"가 아니라 스케일링 특성 자체를 바꾸는 것이 목표였다.

스냅샷 + LATERAL

구조를 세 단계로 나눴다.

Step 1. 기준일 스냅샷 — 1회

조회 시작 전날 기준으로 전체 유료/무료 총이용자 수를 한 번만 계산한다.

base_counts AS (
  SELECT
    COUNT(*) FILTER (WHERE end_status = '20') AS paid_total,
    COUNT(*) FILTER (WHERE end_status = '10') AS free_total
  FROM (
    SELECT DISTINCT ON (user_id) user_id, after_status AS end_status
    FROM user_status_histories
    WHERE created_at < $base_date
    ORDER BY user_id, created_at DESC
  ) base
)

Step 2. 변경 유저 추출 + LATERAL로 상태 조회

조회 기간 중 상태가 바뀐 유저만 뽑아서, 그 유저들에 대해서만 LATERAL 서브쿼리를 실행한다.

changed_users AS (
  SELECT DISTINCT user_id FROM user_status_histories
  WHERE created_at >= $start AND created_at < $end
),

changed_user_statuses AS (
  SELECT cu.user_id, dr.date,
    prev.after_status AS prev_status,
    curr.after_status AS end_status
  FROM changed_users cu
  CROSS JOIN date_range dr
  LEFT JOIN LATERAL (
    SELECT after_status FROM user_status_histories
    WHERE user_id = cu.user_id AND created_at < 날짜시작
    ORDER BY created_at DESC LIMIT 1
  ) prev ON true
  LEFT JOIN LATERAL (
    SELECT after_status FROM user_status_histories
    WHERE user_id = cu.user_id AND created_at < 날짜끝
    ORDER BY created_at DESC LIMIT 1
  ) curr ON true
)

LATERAL은 변경 유저에 대해서만 실행된다. 3,700만 회 → 수만 회로 줄어든다.

Step 3. Running Sum으로 총이용자 산출

스냅샷 + 날짜별 순증감을 누적합으로 계산한다.

bc.paid_total + SUM(COALESCE(dd.paid_net, 0)) OVER (ORDER BY dr.date) AS total_paid_users

기준일 스냅샷에 이후 날짜의 delta를 더해가는 방식이라 user_status_histories 전체 스캔이 필요 없다.

애플리케이션 레벨도 함께 개선

SQL만 고쳐서는 부족했다. 순차 루프도 문제였기 때문이다.

연속 날짜 그룹화

캐시가 없는 날짜들을 연속 구간으로 묶어서 SQL 호출을 최소화했다.

누락 날짜: [D3, D4, D7, D8, D9]

Before: D2~D9 전체 범위 1회 (D5, D6 불필요하게 재계산)
After:  D2~D4 그룹 1회 + D6~D9 그룹 1회

Batch INSERT

날짜별 개별 INSERT를 multi-row INSERT ... ON CONFLICT로 교체해서 DB 왕복을 1회로 줄였다.

최적화 전후 비교

항목	Before	After
상관 서브쿼리 (200만 유저)	~3,700만 회	0회
DB 왕복 (10일 기준)	~40회	2회
스케일링 특성	O(전체유저 × 날짜)	O(변경유저 × 날짜)
DATE() 풀스캔	4회	0회
죽은 CTE	5개	제거
API 응답시간	6.6분	4초

이번에 배운 것

1. EXPLAIN의 loops 값을 항상 확인해라.

Index Only Scan이라는 말에 안심했다가 크게 당했다. actual time × loops가 그 노드의 실제 총 비용이다. 아무리 효율적인 실행 방식이라도 3,700만 번 반복하면 의미가 없다.

2. WHERE 절의 위치가 중요하다.

날짜 조건이 서브쿼리 안에만 있으면 바깥 CROSS JOIN은 전체를 순회한다. "날짜 필터가 있으니까 괜찮겠지"라는 직관이 완전히 틀렸다. 필터가 어느 레벨에서 작동하는지가 핵심이다.

3. 1차 최적화가 최종 답이 아닐 수 있다.

DISTINCT ON + LAG로 상관 서브쿼리를 없앴을 때 "됐다"고 생각했는데, 구조적 문제는 그대로였다.

4. 스케일링 특성이 다른 방법을 찾아라.

"빠르게" 만드는 것보다 "유저가 늘어도 버티는 구조" 로 바꾸는 게 더 중요했다. O(전체유저)에서 O(변경유저)로 바꾸면, 서비스가 성장해도 통계 계산 속도는 큰 영향을 받지 않는다.