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

요약

• 증상: 가입자 조회 화면 진입 시 11초 소요
• 원인: 전체 집계 후 LIMIT + COUNT 쿼리 중복 실행 + 인덱스 부재
• 해결: 복합 인덱스 추가 + Promise.all 병렬화 + LATERAL JOIN 전환
• 결과: 11초 → 264ms (200만 회원 실측)

---

문제

대시보드까지 고치고 나서 여러 페이지를 확인하다 가입자 관리 화면을 열었다.

Network 탭에서 list API가 11초 걸리고 있었다. 앞의 두 케이스에 비하면 짧아 보일 수 있지만, 이건 페이지당 10건만 표시하는 목록 화면이었다. 10건을 보여주는 데 11초가 걸린다는 건 구조적으로 뭔가 잘못됐다는 것이다.

이번엔 원인을 더 빠르게 찾을 수 있었다. 앞에서 두 번 해봤으니까.

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

---

구조 파악

list.js의 핵심 실행 흐름을 따라가 봤다.

// 페이지네이션 쿼리
const paginatedQuery = `
  SELECT * FROM (
    ${query}   // ← UNION ALL 전체 결과를 먼저 계산
  ) as combined_results
  LIMIT ${limit} OFFSET ${offset}
`;

// 전체 건수 쿼리
const countQuery = `
  SELECT COUNT(*) as total FROM (
    ${countQuery}  // ← 동일한 UNION ALL을 한 번 더 실행
  ) as combined_results
`;

// 순차 실행
users = await sequelize.query(paginatedQuery, ...);
const countResult = await sequelize.query(countQueryFinal, ...);

문제가 두 가지였다.

첫째, UNION ALL 전체를 서브쿼리로 감싸고 바깥에서 LIMIT을 적용하고 있었다. PostgreSQL은 이 구조에서 LIMIT을 안쪽으로 push-down하지 못한다. 10건만 필요한데 전체를 집계한 뒤 자르고 있었다.

둘째, 동일한 무거운 쿼리를 전체 건수를 구하기 위해 한 번 더 순차 실행하고 있었다. 11초 중 절반씩 두 쿼리가 나눠 차지하는 구조였다.

---

원인 1 — UNION ALL 전체 집계 후 LIMIT

내부 쿼리 구조를 보면 이렇다.

-- 활성 회원
SELECT u.id, ...,
  MAX(CASE WHEN ush.after_status = '10' THEN ush.created_at END) AS free_joined_at,
  MAX(CASE WHEN ush.after_status = '20' THEN ush.created_at END) AS paid_joined_at,
  ...
FROM users u
LEFT JOIN user_status_histories ush ON u.id = ush.user_id
WHERE u.join_type != '00'
GROUP BY u.id, ...

UNION ALL

-- 탈퇴 회원
SELECT ud.id, ...,
  MAX(CASE WHEN ush.after_status = '10' THEN ush.created_at END) AS free_joined_at,
  ...
FROM users_deleted ud
LEFT JOIN user_status_histories_deleted ush ON ud.original_user_id = ush.user_id
GROUP BY ud.id, ...

ORDER BY id DESC

LEFT JOIN user_status_histories에서 유저당 여러 건의 이력이 조인되면서 행이 팽창한다. 예를 들어 유저 10만 명 × 평균 이력 5건 = 50만 행을 GROUP BY로 줄이는 구조다. 이걸 전체 계산한 뒤 LIMIT 10을 적용하고 있었다.

---

원인 2 — 인덱스 부재

user_status_histories 테이블의 인덱스 현황을 확인했다.

인덱스	컬럼
user_status_histories_pkey	id
idx_user_status_histories_user_id	user_id
idx_user_status_histories_created_at_desc	created_at

(user_id, after_status, created_at) 복합 인덱스가 없었다.

LEFT JOIN + MAX(CASE WHEN after_status = '10') 패턴은 user_id로 조인한 뒤 after_status로 필터링하는 구조인데, 이 조합에 맞는 인덱스가 없으니 매번 해당 유저의 이력 전체를 스캔하고 있었다.

blacklists 테이블도 마찬가지였다.

-- 가입자제한 필터 (ownerType=4)
EXISTS (SELECT 1 FROM blacklists b WHERE u.phone_no = b.phone AND b.del_yn = 'N')

blacklists에는 PK 인덱스만 있고 (phone, del_yn) 복합 인덱스가 없었다. 필터를 걸 때마다 풀스캔이 발생하고 있었다.

---

해결 — 단계별 적용

Phase 1: 인덱스 추가

코드 변경 없이 DDL만으로 즉시 적용할 수 있는 것부터 했다.

-- 가장 중요: LEFT JOIN + MAX(CASE WHEN) + EXISTS 모두 활용
CREATE INDEX CONCURRENTLY idx_ush_user_after_status_created
ON user_status_histories (user_id, after_status, created_at DESC);

-- 해지일 조회용 (before_status 포함)
CREATE INDEX CONCURRENTLY idx_ush_user_before_after_created
ON user_status_histories (user_id, before_status, after_status, created_at DESC);

-- 가입자제한 필터
CREATE INDEX CONCURRENTLY idx_blacklists_phone_del_yn
ON blacklists (phone, del_yn);

-- 탈퇴 회원 테이블도 동일하게 추가
CREATE INDEX CONCURRENTLY idx_ush_deleted_user_after_status_created
ON user_status_histories_deleted (user_id, after_status, created_at DESC);

CREATE INDEX CONCURRENTLY를 쓴 이유는 운영 중 테이블 락 없이 인덱스를 생성할 수 있기 때문이다.

Phase 2: Promise.all 병렬화

데이터 쿼리와 COUNT 쿼리는 서로 의존성이 없다. 순차 실행할 이유가 없었다.

// Before: 순차 실행 (11초 = 데이터 쿼리 + COUNT 쿼리)
users = await sequelize.query(paginatedQuery, ...);
const countResult = await sequelize.query(countQueryFinal, ...);

// After: 병렬 실행
const [users, countResult] = await Promise.all([
  sequelize.query(paginatedQuery, { type: sequelize.QueryTypes.SELECT }),
  sequelize.query(countQueryFinal, { type: sequelize.QueryTypes.SELECT })
]);

커넥션 풀이 max: 5였는데, 2개 병렬 실행은 문제없었다.

Phase 3: LATERAL JOIN 전환

LEFT JOIN + GROUP BY + MAX(CASE WHEN) 패턴을 LATERAL 서브쿼리로 대체했다.

-- Before: 모든 이력을 JOIN 후 GROUP BY로 축소
SELECT u.id, ...,
  MAX(CASE WHEN ush.after_status = '10' THEN ush.created_at END) AS free_joined_at,
  MAX(CASE WHEN ush.after_status = '20' THEN ush.created_at END) AS paid_joined_at
FROM users u
LEFT JOIN user_status_histories ush ON u.id = ush.user_id
GROUP BY u.id, ...

-- After: 유저당 1번씩 LATERAL로 직접 조회
SELECT u.id, ...,
  free_join.at AS free_joined_at,
  paid_join.at AS paid_joined_at
FROM users u
LEFT JOIN LATERAL (
  SELECT MAX(created_at) AS at
  FROM user_status_histories
  WHERE user_id = u.id AND after_status = '10'
) free_join ON true
LEFT JOIN LATERAL (
  SELECT MAX(created_at) AS at
  FROM user_status_histories
  WHERE user_id = u.id AND after_status = '20'
) paid_join ON true

LEFT JOIN + GROUP BY는 이력 전체를 JOIN한 뒤 축소하는 방식이라 중간 결과가 팽창한다. LATERAL은 유저마다 독립적으로 딱 필요한 값만 가져오므로 낭비가 없다. Phase 1에서 추가한 (user_id, after_status, created_at DESC) 인덱스와 함께 쓰면 각 LATERAL이 Index Scan 1회로 완료된다.

---

최적화 전후 비교

항목	Before	After
(user_id, after_status, created_at) 인덱스	없음	추가
(phone, del_yn) 인덱스	없음	추가
COUNT 쿼리 실행 방식	순차 await	Promise.all 병렬
집계 방식	LEFT JOIN + GROUP BY (전체 팽창)	LATERAL (유저당 1회)
API 응답시간	11초	264ms

---

3일 동안 배운 것

세 케이스를 마무리하면서 공통적으로 느낀 것들을 정리했다.

1. EXPLAIN의 loops 값이 핵심이다.

Index Only Scan이라도 800만 번 반복하면 24초다. 실행 방식보다 실행 횟수가 중요하다. actual time × loops가 그 노드의 실제 총 비용이다.

2. 인덱스는 컬럼 단위가 아니라 쿼리 패턴 단위로 설계해야 한다.

user_id 단일 인덱스가 있어도 WHERE user_id = ? AND after_status = ? 패턴에선 전체 스캔에 가깝다. 실제 쿼리가 어떤 조건 조합으로 필터링하는지 보고 복합 인덱스를 설계해야 한다.

3. 가장 빠른 최적화는 불필요한 연산을 없애는 것이다.

세 케이스 모두 공통적이었다. 이미 users.join_type에 있는 값을 이력에서 재계산하고, 이미 캐시 테이블에 있는 데이터를 매번 새로 집계하고, 의존성 없는 쿼리를 순차 실행하고 있었다. 복잡한 알고리즘 최적화보다 "이게 진짜 필요한 연산인가"를 먼저 생각하는게 더 효과적이었다.