1000ms 쿼리를 2.5ms로: 실행 계획(EXPLAIN)으로 파헤친 튜닝기

1분마다 특정 토큰의 홀더 수를 집계하는 스케줄러 쿼리가 프로덕션 환경에서 1000ms 이상 지연되며 DB 커넥션을 갉아먹고 있었다.

데이터 개수를 확인해 보니 Trade 7.5만 건, Swap 3.4만 건. 합쳐서 11만 건밖에 안 되는 아주 작은(?) 데이터였다. 정상적으로 인덱스를 탔다면 5ms 안에 끝나야 할 쿼리가 1초나 걸린다는 것은 명백한 풀 테이블 스캔(Full Table Scan)과 치명적인 병목이 존재한다는 뜻이었다.

단순히 DB 스펙을 올려 돈으로 해결하는 대신, 쿼리 실행 계획을 뜯어보며 2.5ms까지 줄여나간 튜닝 과정과 그 속에서 얻은 CS적 깨달음을 기록해 본다.

초기 상황

가장 먼저 문제의 쿼리와 EXPLAIN ANALYZE를 통해 악성 쿼리의 실행 계획을 까보았다.

WITH all_tx AS (
  SELECT "tokenAddress", "userAddress"
  FROM "ChainTokenTxTrade"
  WHERE "tokenAddress" = ANY($1)

  UNION ALL

  SELECT "tokenInAddress" AS "tokenAddress", "userAddress"
  FROM "ChainTokenTxConnSwap"
  WHERE "tokenInAddress" = ANY($2)

  UNION ALL

  SELECT "tokenOutAddress" AS "tokenAddress", "userAddress"
  FROM "ChainTokenTxConnSwap"
  WHERE "tokenOutAddress" = ANY($3)
)
SELECT "tokenAddress", COUNT(DISTINCT "userAddress") AS "holderCount"
FROM all_tx
GROUP BY "tokenAddress";

초기 실행 계획

GroupAggregate  (cost=7202.05..7464.12 rows=200 width=51) (actual time=78.265..78.268 rows=1 loops=1)
  Group Key: "ChainTokenTxTrade"."tokenAddress"
  Buffers: shared hit=2558, temp read=408 written=409
  I/O Timings: temp read=0.585 write=1.914
  ->  Sort  (cost=7202.05..7288.74 rows=34676 width=86) (actual time=67.369..73.216 rows=34649 loops=1)
        Sort Key: "ChainTokenTxTrade"."tokenAddress", "ChainTokenTxTrade"."userAddress"
        Sort Method: external merge  Disk: 3264kB
        ->  Append  (cost=0.42..4587.19 rows=34676 width=86) (actual time=0.336..17.570 rows=34649 loops=1)
              ->  Index Scan using "ChainTokenTxTrade_chainId_tokenAddress_idx" ...
              ->  Seq Scan on "ChainTokenTxConnSwap"  (cost=0.00..1586.04 rows=21129 width=86) (actual time=0.009..7.362 rows=21078 loops=1)
                    Filter: (("tokenInAddress")::text = ANY ('{0x...}'::text[]))
                    Rows Removed by Filter: 13571
              ->  Seq Scan on "ChainTokenTxConnSwap" "ChainTokenTxConnSwap_1" ...

실행 계획을 보니 쿼리 지연의 원인이 수치로 아주 명확하게 드러났다. 범인은 정확히 두 명이었다.

선두 컬럼 누락으로 인한 풀 테이블 스캔 (Seq Scan)

실행 계획 하단을 보면 Seq Scan on "ChainTokenTxConnSwap"이 두 번이나 발생했다. 인덱스가 [chainId, tokenAddress]로 잡혀있었다. 하지만 기존 쿼리는 WHERE 절에 tokenAddress 조건만 걸려있었다. 복합 인덱스는 선두 컬럼인 chainId가 없으면 데이터베이스는 인덱스를 탈 수 없다고 판단하고, 인덱스를 포기한 채 3.4만 건의 테이블을 생으로 전부 읽어버린다.

Rows Removed by Filter를 보면 조건에 맞지 않는 데이터를 버리느라 CPU를 낭비한 흔적이 명백하다.

Seq Scan on "ChainTokenTxConnSwap"  (cost=0.00..1586.04 rows=21129 width=86) (actual time=0.009..7.362 rows=21078 loops=1)
    Filter: (("tokenInAddress")::text = ANY ('{0x...}'::text[]))
    Rows Removed by Filter: 13571

디스크 정렬 발생 (1000ms 튀는 현상의 진짜 주범)

가장 주의 깊게 보아야 할 부분은 Sort Method: external merge Disk: 3264kB 이다.

Sort Method: external merge  Disk: 3264kB

기존 쿼리는 UNION ALL로 3.4만 건의 데이터를 끌어모은 뒤, COUNT(DISTINCT)로 중복 제거를 시도했다. 이 과정에서 DB에 할당된 메모리(work_mem) 용량을 초과해 버렸고, 결국 하드디스크(또는 SSD)에 3.2MB의 임시 파일을 썼다가 다시 읽어 들이며 정렬하는 무거운 디스크 I/O 작업을 수행했다. 단일 실행은 70ms대였지만, 프로덕션 환경에서 여러 요청이 동시에 들어오면 이 디스크 I/O 경합이 발생하여 쿼리 속도가 1초 이상으로 무작위로 튀었던 것이다.

1차 튜닝: 인덱스 태우기 및 UNION

병목을 확인했으니 쿼리를 즉각 수정했다. 이 단계에서의 핵심은 두 가지였다.

• chainId 조건 추가: WHERE 절에 chainId를 추가하여 잃어버린 인덱스를 되찾는다.
• UNION ALL을 UNION으로 변경: 무거운 COUNT(DISTINCT)를 제거하기 위해 쿼리 구조를 바꾼다.

1차 튜닝 쿼리

WITH all_tx AS (
  SELECT "tokenAddress", "userAddress"
  FROM "ChainTokenTxTrade"
  WHERE "chainId"= 4352 AND "tokenAddress" = ANY($1)

  UNION  -- ALL 제거

  SELECT "tokenInAddress" AS "tokenAddress", "userAddress"
  FROM "ChainTokenTxConnSwap"
  WHERE "chainId"= 4352 AND "tokenInAddress" = ANY($2)

  UNION  -- ALL 제거

  SELECT "tokenOutAddress" AS "tokenAddress", "userAddress"
  FROM "ChainTokenTxConnSwap"
  WHERE "chainId"= 4352 AND "tokenOutAddress" = ANY($3)
)
SELECT "tokenAddress", COUNT("userAddress") AS "holderCount" -- DISTINCT 제거
FROM all_tx
GROUP BY "tokenAddress";

왜 UNION ALL 대신 UNION을 선택했는가?

흔히 개발자들 사이에서는 "UNION은 중복을 제거하느라 성능이 느리니 무조건 UNION ALL을 써라"라는 격언이 돈다. 하지만 이는 반쪽짜리 진실이다.

기존 쿼리는 데이터를 무지성으로 다 모아놓고(UNION ALL), 마지막 줄에 와서야 COUNT(DISTINCT userAddress)를 걸어버렸다.

SELECT "tokenAddress", COUNT(DISTINCT "userAddress") AS "holderCount"

즉, 앞부분에서 아낀 중복 제거 비용을 마지막 단계에서 거대한 메모리와 디스크를 박살 내며 이자로 갚게 만든 형태였다.

어차피 우리의 비즈니스 목적은 '특정 토큰을 한 번이라도 거래한 유니크한 유저의 수'를 구하는 것이다.

• UNION ALL 방식: A, B, C 주머니의 데이터를 거대한 통에 다 쏟아붓고, 마지막에 겹치는 것을 골라내며 숫자를 센다. 데이터가 섞여 있어 무거운 디스크 정렬 필수이다.
• UNION 방식: A, B, C 주머니의 데이터를 합칠 때, DB 엔진이 자체적인 Unique 연산을 통해 애초에 겹치는 쌍을 버리면서 담는다. 마지막엔 중복이 모두 날아간 깨끗한 데이터의 숫자만 단순 COUNT() 하면 된다.

chainId를 추가하고 UNION으로 변경한 후 실행 계획을 확인해 보았다.

1차 튜닝 후 실행 계획

GroupAggregate  (cost=6059.51..6321.58 rows=200 width=51) (actual time=76.205..76.209 rows=1 loops=1)
  ->  Sort  (cost=6059.51..6146.20 rows=34676 width=86) (actual time=65.304..71.189 rows=34649 loops=1)
        Sort Key: "ChainTokenTxTrade"."tokenAddress", "ChainTokenTxTrade"."userAddress"
        Sort Method: external merge  Disk: 3264kB
        ->  Append  (cost=0.42..3444.65 rows=34676 width=86) (actual time=0.030..14.902 rows=34649 loops=1)
              ->  Index Scan using "ChainTokenTxTrade_chainId_tokenAddress_idx" ... (actual time=0.019..0.019)
              ->  Seq Scan on "ChainTokenTxConnSwap" ...
              ->  Bitmap Heap Scan on "ChainTokenTxConnSwap" ...
                    ->  Bitmap Index Scan on "ChainTokenTxConnSwap_chainId_tokenOutAddress_idx" ... (actual time=0.307..0.307)
Execution Time: 77.050 ms

절반의 성공이었다. chainId를 넣자마자 풀 테이블 스캔이 사라지고 Index Scan과 Bitmap Index Scan을 타면서 데이터 탐색 시간이 0.019ms 수준으로 수직 하락했다.

하지만 전체 실행 시간은 77ms로 크게 줄지 않았고, 악명 높은 external merge Disk: 3264kB는 여전히 남아있었다. 데이터를 찾는 건 빛의 속도인데, 왜 아직도 디스크를 긁고 있는 것일까? 그 이유는 바로 DB가 데이터를 가져오기 위해 디스크의 Heap Table에 다녀와야만 했기 때문이다.

여전히 전체 실행 시간은 70ms 대에 머물렀고, external merge Disk: 3264kB (디스크 정렬) 병목은 사라지지 않았다. 디스크에 다녀오는 비용과, 가져온 데이터를 섞는 비용이 여전히 발목을 잡고 있었던 것이다.

2차 튜닝: 커버링 인덱스 (Covering Index) / (여기서부터 실제 운영 디비에서 테스트를 해봤다.)

"DB가 디스크를 아예 쳐다보지도 않고, 메모리에 떠 있는 가벼운 인덱스만 읽고 쿼리를 끝내게 만들 수는 없을까?"

이 질문이 2차 튜닝의 핵심이었다. 우리가 1차 튜닝에서 Index Scan을 탔음에도 여전히 느렸던 이유는 'Heap Fetch' 때문이다.

인덱스 트리에는 chainId와 tokenAddress만 존재했다. 하지만 쿼리의 SELECT 절에서는 userAddress를 요구하고 있었다. 따라서 DB는 인덱스 트리를 타고 리프 노드에 도달한 뒤, 거기에 적힌 포인터를 들고 실제 데이터가 저장된 무거운 원본 테이블(Heap 영역)로 다시 찾아가 userAddress를 긁어와야만 했다. 이 낭비를 없애기 위해, 기존 인덱스 맨 뒤에 userAddress를 덧붙여 커버링 인덱스(Covering Index)를 만들었다.

// 기존: @@index([chainId, tokenAddress])
@@index([chainId, tokenAddress, userAddress])
@@index([chainId, tokenInAddress, userAddress])
@@index([chainId, tokenOutAddress, userAddress])

커버링 인덱스 적용 후 실행 계획

GroupAggregate  (cost=21746.81..22784.84 rows=200 width=110) (actual time=110.169..147.754 rows=633 loops=1)
  ->  Unique  (cost=21746.81..22368.43 rows=82882 width=204) (actual time=110.100..144.954 rows=20990 loops=1)
        ->  Sort  (cost=21746.81..21954.02 rows=82882 width=204) (actual time=110.098..132.226 rows=94520 loops=1)
              Sort Method: external merge  Disk: 8904kB
              ->  Append  (cost=0.42..6757.86 rows=82882 width=204) (actual time=0.227..31.221 rows=94520 loops=1)
                    ->  Index Only Scan using "ChainTokenTxTrade_chainId_tokenAddress_userAddress_idx" ... 
                          Heap Fetches: 0  -- 디스크 접근 완전 차단!
Execution Time: 149.743 ms

왜 커버링 인덱스를 썼는가?

실행 계획을 보면 마침내 Index Only Scan이 떴고, Heap Fetches: 0 를 달성했다! 리프 노드에 이미 userAddress가 값으로 예쁘게 저장되어 있으니 무거운 Heap 방문을 완벽하게 생략한 것이다. 그 결과 9.4만 건이라는 방대한 데이터를 메모리에서 긁어모으는 데 고작 31ms(actual time=0.227..31.221)밖에 걸리지 않았다.

하지만, 최종 Execution Time은 149ms로 오히려 늘어났다. 왜일까? 실제 운영 데이터를 이용한 계획인 환경이다 보니, 데이터를 섞고 중복을 제거하는 과정에서 Sort Method: external merge Disk: 8904kB가 발생하며 무려 9MB를 디스크에 쓰고 지우며 정렬하고 있었기 때문이다. 

---

최종 해결: 메모리 펌핑 (work_mem 튜닝)

데이터를 찾는 건 빛의 속도가 되었는데, 병합/정렬에서 엄청난 디스크 I/O가 발생해 느려지는 기형적인 상황.

이유는 단순했다. PostgreSQL이 개별 쿼리의 정렬 작업에 사용할 수 있도록 허용하는 기본 메모리(work_mem)가 단 4MB에 불과했기 때문이다. 3개의 테이블에서 퍼 올린 9.4만 건의 데이터를 UNION으로 합치는 순간 데이터의 순서는 뒤죽박죽으로 섞인다. DB는 중복을 제거하기 위해 이 9MB 크기의 데이터를 다시 정렬해야만 했고, 4MB의 RAM 한계를 초과하자 어쩔 수 없이 하드디스크를 임시 메모리처럼 사용한 것이다.

왜 시스템 전체 설정이 아닌 트랜잭션 내 설정을 바꿨는가?

work_mem을 데이터베이스 전역 설정(Global)에서 32MB로 쿨하게 늘려버릴 수도 있었다. 하지만 이는 시한폭탄이다. 동시 접속자 100명이 무거운 쿼리를 날리면 순식간에 3.2GB의 RAM이 증발하여 DB 전체가 Out Of Memory(OOM)로 뻗어버릴 수 있다. (하지만 해당 쿼리는 배치로 1분당 한번씩 돌기 때문에 위와 같은 상황은 발생되지 않을 것이다.)

따라서, 이 무거운 스케줄러 통계 쿼리가 실행되는 트랜잭션 블록 내에서만 임시로 메모리를 32MB로 늘려주는 핫픽스를 적용했다.

@Transactional(DB_CONNECTION_TYPE.READ_ONLY)
async getTokenHolders(chainId: number, tokenAddresses: string[]) {
  const tx = this.databaseService.txRo();
  
  // 트랜잭션(세션) 안에서만 임시로 정렬 메모리를 32MB로 증가 후 종료 시 자동 원복
  await tx.$executeRawUnsafe(`SET LOCAL work_mem = '32MB';`);

  return await tx.$queryRaw`
    WITH all_tx AS ( /* UNION 구조의 쿼리 */ )
    SELECT "tokenAddress", CAST(COUNT("userAddress") AS INTEGER) AS "holderCount"
    FROM all_tx GROUP BY "tokenAddress";
  `;
}

최종 실행 계획

GroupAggregate  (cost=5266.94..5267.21 rows=15 width=51) (actual time=2.381..2.383 rows=0 loops=1)
  ->  Sort  (cost=5266.94..5266.98 rows=15 width=86) (actual time=2.380..2.381 rows=0 loops=1)
        Sort Method: quicksort  Memory: 25kB
        ->  Append  (cost=0.42..5266.65 rows=15 width=86) (actual time=2.371..2.373 rows=0 loops=1)
              ->  Index Only Scan using "ChainTokenTxTrade_chainId_tokenAddress_userAddress_idx" ... 
                    Heap Fetches: 0
Execution Time: 2.505 ms

최종 성과

지긋지긋하게 발목을 잡던 디스크 정렬 문구(external merge Disk)가 완전히 소멸하고, 오직 RAM 위에서 초고속으로 정렬을 끝내는 quicksort Memory가 등장했다. 결과적으로 1000ms가 넘어가며 요동치던 쿼리가 더이상 Performance Insight에 발생하지 않았다.

---

하지만, 이것은 '안티 패턴'이다.

급한 불은 성공적으로 껐고 성능은 10,000배 가까이 비약적으로 향상되었다. 하지만 백엔드 엔지니어로서 이 방식이 완벽한 정답이라고 생각하진 않는다. 솔직히 말해 현재의 접근은 데이터베이스 아키텍처 관점에서 명백한 안티 패턴(Anti-pattern)이다.

지금은 데이터가 수십만 건 단위라 메모리를 32MB로 늘려주는 방식(work_mem 제어)이 통했다. 하지만 서비스가 스케일업하여 이 트랜잭션 로그가 1,000만 건, 1억 건을 넘어가면? 그때는 트랜잭션 블록에 1GB를 할당해도 DB는 또다시 메모리 부족을 뱉어내며 디스크를 긁고 쓰러질 것이다.

진짜 근본적인 문제는 "OLAP(통계성 풀스캔 집계 쿼리)를 OLTP(1분 주기 스케줄러) 환경에서 강행하고 있다"는 점이다.

데이터가 무한히 늘어나도 버티는 아키텍처로

장기적으로 대규모 트래픽을 감당하기 위해서는 매번 과거의 트랜잭션 로그(Trade, Swap)를 3개의 테이블에서 뒤져서 숫자를 세는 구조 자체를 버려야 한다.

1. 상태 테이블 분리: TokenStat 이라는 요약 테이블을 별도로 파서 holderCount 컬럼을 둔다.
2. 무거운 스케줄러 폐기: 매분 도는 무거운 집계 스케줄러를 삭제한다.

이렇게 되면 조회 API는 복잡한 UNION이나 커버링 인덱스를 탈 필요도 없이, 테이블에서 숫자 하나만 O(1)의 속도로 스윽 읽어가면 그만이다.

이번 트러블슈팅을 통해 실행 계획 분석(EXPLAIN), 커버링 인덱스의 무서운 위력, 그리고 메모리(work_mem) 제어까지 DB 튜닝의 밑바닥을 생생하게 경험했다. 하지만 가장 값진 수확은 "쿼리를 극한으로 잘 짜는 것을 넘어, 애초에 무거운 쿼리 자체가 필요 없는 아키텍처를 설계해야 한다"는 아키텍트로서의 통찰을 얻은 것이다.