입찰 마감 탭 목록 9초 → 1초: covering 인덱스와 bucket pagination lazy count

TL;DR

공급사 마감 탭 목록이 진입할 때마다 9초씩 걸렸고, 한 화면에서 탭 카운트 쿼리가 3발 병렬로 나가 최악엔 20초를 넘겼습니다.

EXPLAIN (ANALYZE, BUFFERS)로 범인을 둘로 좁혔습니다. (1) 참여 공고를 prefetch하는 쿼리가 인덱스에 buy_idx를 담지 않아 heap 3만 페이지를 cold IO로 읽었고(9,367ms), (2) 페이지 한 번에 bucket count가 3발씩 나갔습니다.

(join_id) INCLUDE (buy_idx) covering 인덱스로 Index Only Scan을 만들어 9,367ms → 15.95ms(586배), skipTotal lazy count로 count를 3발에서 1발로 줄였습니다.

스택은 NestJS · Prisma · PostgreSQL입니다.

문제: 마감 탭만 유독 9초였습니다#

공급사가 입찰 공고 목록에서 마감 탭(/supplier/bids?tab=closed)에 들어가면 첫 페이지가 뜨는 데 9초가 걸렸습니다. 진행중 탭은 멀쩡했습니다. 마감 탭만 느렸고, 참여 이력이 많은 공급사일수록 더 심했습니다.

마감 탭은 정렬 정책이 다릅니다. 사내에서 "odn 정렬"이라 부르는 레거시 규칙으로, 내가 응찰한 공고를 맨 위에 올리고(odn=1), 그다음 응찰자가 있는 공고(odn=2), 나머지(odn=3) 순으로 묶어 보여줍니다. 이걸 구현하려면 먼저 "이 공급사가 참여한 공고 buy_idx 집합"을 알아야 합니다. 그래서 목록을 그리기 전에 이 쿼리가 한 발 나갑니다.

sql
SELECT buy_idx FROM b2b_buy_join WHERE join_id = $1;

평범한 단일 조건 조회입니다. 인덱스도 이미 있었습니다(idx_b2b_buy_join_join_id (join_id)). 그런데 이게 9초를 먹고 있었습니다.

왜 느렸나: EXPLAIN으로 범인을 지목#

추측 대신 프로덕션에서 EXPLAIN (ANALYZE, BUFFERS)를 돌렸습니다. 참여가 특히 많은(약 39,667건) 한 대형 공급사 계정으로 실측한 결과입니다.

plain
Bitmap Heap Scan on b2b_buy_join  (rows=39667)
  Buffers: shared hit=1 read=31015      ← heap 31,000 페이지를 디스크에서 read
  Execution Time: 9367 ms

Buffers: ... read=31015가 핵심입니다. 인덱스로 조건에 맞는 행을 찾긴 했는데, join_id만 담긴 인덱스에는 정작 필요한 buy_idx가 없습니다. 그래서 매 행마다 실제 테이블(heap)로 되돌아가 값을 읽어옵니다. 참여 4만 건이면 그 heap 방문이 31,000 페이지 cold IO로 번지고, 그게 통째로 9초입니다. shared hit=1이라 캐시도 거의 못 탔습니다.

여기서 한 가지 더 확인했습니다. PK가 (buy_idx, buy_seq) 복합키라 join_id로 시작하지 않습니다. 즉 PK로는 이 역방향 조회를 가속할 수 없어서, 인덱스를 새로 만드는 게 맞았습니다.

범인은 하나가 아니었습니다. 목록 total을 구하는 경로도 문제였습니다. odn 버킷을 PostgreSQL에서 페이지네이션하면서 세 버킷의 count(*)를 병렬로 세 발 날립니다. 이 count는 노출 조건(지명입찰 + 2단계 적격 심사) OR가 얹혀 각각 5~9초입니다. 페이지를 한 번 넘길 때 count × 3 = 15~27초가 나올 수 있었습니다. 커밋 메시지에 "9s × 3"이라고 적어둔 게 이 지점입니다.

해결 1: covering 인덱스로 Index Only Scan#

첫 번째 범인은 인덱스 하나로 끝냈습니다. join_id로 찾은 뒤 buy_idx를 읽으러 heap까지 가는 게 문제니까, 인덱스 leaf에 buy_idx를 같이 저장하면 됩니다. PostgreSQL의 INCLUDE가 정확히 이 용도입니다.

sql
CREATE INDEX IF NOT EXISTS idx_b2b_buy_join_join_id_covering
  ON public.b2b_buy_join (join_id) INCLUDE (buy_idx);

join_id는 탐색 키로, buy_idx는 leaf에 얹는 payload로 넣었습니다. 이렇게 하면 쿼리에 필요한 컬럼이 전부 인덱스 안에 있어 heap을 건드릴 이유가 사라집니다. 플래너가 Index Only Scan을 선택하고, Heap Fetches: 0을 기대할 수 있습니다(visibility map이 최신이면 heap 재방문 0).

프로덕션에 적용한 뒤 같은 쿼리를 다시 재봤습니다.

plain
9,367 ms  →  15.95 ms   (약 586배)

Prisma 쪽 코드는 손댈 게 거의 없었습니다. prefetch는 원래 이렇게 나가고 있었고, 인덱스만 바뀌면 플랜이 알아서 바뀝니다.

typescript
const joinedRows = await this.prisma.b2bBuyJoin.findMany({
  where: { joinId: userId },
  select: { buyIdx: true },
  distinct: ['buyIdx'],
  orderBy: { buyIdx: 'desc' },
  take: MAX_JOINED_BID_IDS, // 30_000
});

take에 상한을 둔 건 성능이 아니라 사고 방지용입니다. 이 buy_idx 목록은 아래 버킷 WHERE에 IN (...) 리터럴로 그대로 박히는데, PostgreSQL prepared statement의 bind 변수 한도가 32,767(uint16)입니다. 수만 건 응찰한 메가 비더가 이 한도를 넘겨 GET /bids가 500으로 터진 적이 있어서, 최근 참여 순으로 30,000건만 가져오도록 헤드룸을 남겼습니다.

운영 함정 하나: CONCURRENTLY#

b2b_buy_join은 약 89만 행입니다. 이 규모에서 CREATE INDEX를 그냥 걸면 빌드가 끝날 때까지 테이블에 write lock이 잡힙니다. 원칙적으로는 CREATE INDEX CONCURRENTLY로 락 없이 만들어야 하는데, 여기엔 제약이 하나 있습니다. CONCURRENTLY는 트랜잭션 밖에서만 실행됩니다. 우리가 쓰는 마이그레이션 실행 환경은 스크립트를 트랜잭션으로 감싸서 CONCURRENTLY가 막힙니다. 그래서 마이그레이션 파일 자체는 일반 CREATE INDEX로 두고, 트래픽 낮은 시간에 적용하거나 psql로 직접 접속할 때 CONCURRENTLY를 붙이도록 주석에 남겨뒀습니다. 인덱스 크기는 참여 컬럼 하나가 늘어 16MB에서 25~30MB 정도로 커지는데, 단일 정수 컬럼이라 write 비용 증가는 미미했습니다.

해결 2: bucket pagination의 lazy count#

두 번째 범인인 count 3발은 접근이 달랐습니다. 정말 3발 다 필요한가를 먼저 물었습니다.

관찰: 이 목록의 total은 사실 목록 count 쿼리로 다시 셀 필요가 없었습니다. 화면 상단 탭 뱃지에 쓰는 statusCounts가 이미 같은 WHERE로 같은 숫자를 60초 캐시로 들고 있었으니까요. 목록과 뱃지가 같은 출처를 봐야 서로 어긋나지 않습니다. 그러면 목록 쪽 count는 그냥 스킵하고 뱃지 값으로 채우면 됩니다.

핸들러에서 탭이 지정된 요청은 skipTotal을 켜고, total은 statusCounts에서 가져옵니다.

typescript
// 탭이 지정되면 list count와 statusCounts[tab]가 같은 WHERE의 같은 숫자.
const skipTotal = tab !== undefined;

const [listResult, statusCounts] = await Promise.all([
  this.bidsRepository.findBySpecification(listSpec, { page, limit, sortPolicy, skipTotal }),
  statusCountsPromise,
]);

const total =
  tab !== undefined ? (statusCounts.get(tab) ?? listResult.total) : listResult.total;

이 skipTotal을 repository의 bucket 페이지네이션까지 흘려보냈습니다. bucket count는 "어느 버킷에서 몇 개를 take할지" 정하는 데 여전히 필요하지만, outer total을 위해 끝까지 셀 이유는 없어졌습니다. 그래서 로직을 이렇게 바꿨습니다. 원래는 세 버킷 count를 Promise.all로 한꺼번에 던졌는데, 순차로 돌면서 페이지가 다 채워지면 남은 버킷 count를 생략합니다.

typescript
for (let i = 0; i < buckets.length; i++) {
  // skipTotal이고 페이지가 이미 찼으면 남은 bucket count 생략 → break
  if (skipTotal && remainingTake <= 0) break;

  const bucketCount = await this.countBoardRows(boardType, buckets[i]);
  bucketCounts.push(bucketCount);
  if (bucketCount === 0) continue;
  if (remainingTake <= 0) continue; // skipTotal=false 경로만 끝까지 count 계속
  // ... remainingSkip / findMany로 페이지 채우기
}

const total = skipTotal ? 0 : bucketCounts.reduce((a, c) => a + c, 0);

공급사 평균 참여가 40건쯤이라, 1페이지(limit 20)는 첫 번째 버킷(내가 참여한 공고)에서 대부분 채워집니다. 그러면 bucket1 count 한 발로 페이지가 서고, bucket2·3 count는 아예 안 나갑니다. 평균 케이스에서 count가 3발에서 1발로 줄었습니다.

기존 호출자와의 호환도 지켰습니다. skipTotal=false로 부르는 쪽(grand total이 진짜 필요한 경로)은 예전처럼 세 버킷을 끝까지 세고 total을 합산합니다. 분기는 skipTotal 하나로 갈립니다.

total=0을 반환해도 괜찮은 이유#

skipTotal=true일 때 repository는 outer total을 0으로 돌려줍니다. 언뜻 버그처럼 보이는데, 계약(contract)이 정해져 있습니다. 핸들러가 반드시 statusCounts[activeTab]로 덮어씁니다. 이 0은 "값 없음" 신호지 화면에 나가는 숫자가 아닙니다.

statusCounts는 같은 필터의 탭 전환·페이지 이동에서 변하지 않으니 60초 TTL 캐시를 태웁니다. 다만 보고 있는 활성 탭만은 리스트와 어긋나지 않게 신경 썼습니다. 카운트가 작을 땐 fresh 단일 count로 갱신해 "0인데 4로 표시" 같은 시각적 불일치를 막고, 마감처럼 22만 건짜리 큰 카운트는 fresh 재스캔(≈9초)을 생략하고 60초 캐시를 신뢰합니다. 뱃지 정합성과 부하 사이의 타협점입니다.

회귀 테스트는 repository 스펙에 4건을 더했습니다. bucket1만으로 페이지가 차는 경우, bucket2까지 가서 멈추는 경우, 그리고 skipTotal=false에서 limit을 키워도 outer total이 끝까지 합산되는지(lazy와 양립하는지)를 각각 고정했습니다.

결과와 남은 것#

참여 공고 prefetch: 9,367ms → 15.95ms(covering 인덱스, 프로덕션 실측)
마감 탭 진입 시 count fan-out: 평균 케이스 3발 → 1발
목록 total: 별도 count 쿼리 없이 statusCounts 60초 캐시로 충당

정직하게 남겨두면, bucket count 자체가 노출 조건 OR 위에서 여전히 5~9초라는 건 이번 PR에서 완전히 풀지 않았습니다. skipTotal과 캐시로 사용자가 그 비용을 매 요청 치르지 않게 우회했을 뿐, 근본 해결은 아닙니다. 노출 조건 OR(지명입찰 + 2단계 적격)를 단일 IN으로 합쳐 count 플랜 자체를 바꾸는 건 별도 PR로 분리했습니다. 한 번에 다 고치려다 회귀 범위를 키우기보다, covering 인덱스처럼 확실히 검증되는 것부터 잘라 내보냈습니다.

독자 TIP#

EXPLAIN에는 항상 BUFFERS를 켜세요. Execution Time만 보면 "느리다"까지밖에 모릅니다. Buffers: ... read=31015 같은 heap read 수치가 있어야 "인덱스는 탔는데 heap 방문이 범인"이라는 진단이 나옵니다. covering 인덱스가 답인지 아닌지가 이 한 줄에서 갈립니다.
covering 인덱스는 SELECT 컬럼이 좁고 고정일 때 잘 듣습니다. WHERE 컬럼은 인덱스 키로, SELECT에서 딱 필요한 컬럼만 INCLUDE로. 여기선 buy_idx 하나였고, Index Only Scan의 이상적인 조건이었습니다. SELECT *처럼 컬럼이 많으면 인덱스만 비대해지니 득이 없습니다.
PK가 있다고 안심하지 마세요. 복합 PK (A, B)는 A 조회는 도와도 B나 역방향은 못 도웁니다. WHERE가 PK 선두 컬럼으로 시작하는지 꼭 확인하세요.
lazy count는 "total을 다른 데서 이미 안다"일 때만 유효합니다. 우리는 탭 뱃지 캐시가 같은 숫자를 들고 있어서 목록 count를 통째로 스킵할 수 있었습니다. total의 단일 출처(SSOT)를 먼저 정하는 게 순서입니다.
IN (...) 리터럴엔 상한을 두세요. 리스트가 파라미터로 박히면 bind 변수 한도(32,767)에 걸려 500이 납니다. 꼬리를 자르는 게 터지는 것보다 낫습니다.
대용량 테이블 인덱스는 CONCURRENTLY를 기억하세요. 다만 트랜잭션으로 감싸는 실행 환경에선 막히니, 마이그레이션 도구의 트랜잭션 처리 방식을 미리 확인해야 합니다.

TL;DR

공급사 마감 탭 목록이 진입할 때마다 9초씩 걸렸고, 한 화면에서 탭 카운트 쿼리가 3발 병렬로 나가 최악엔 20초를 넘겼습니다.

EXPLAIN (ANALYZE, BUFFERS)로 범인을 둘로 좁혔습니다. (1) 참여 공고를 prefetch하는 쿼리가 인덱스에 buy_idx를 담지 않아 heap 3만 페이지를 cold IO로 읽었고(9,367ms), (2) 페이지 한 번에 bucket count가 3발씩 나갔습니다.

(join_id) INCLUDE (buy_idx) covering 인덱스로 Index Only Scan을 만들어 9,367ms → 15.95ms(586배), skipTotal lazy count로 count를 3발에서 1발로 줄였습니다.

스택은 NestJS · Prisma · PostgreSQL입니다.

문제: 마감 탭만 유독 9초였습니다#

sql
SELECT buy_idx FROM b2b_buy_join WHERE join_id = $1;

평범한 단일 조건 조회입니다. 인덱스도 이미 있었습니다(idx_b2b_buy_join_join_id (join_id)). 그런데 이게 9초를 먹고 있었습니다.

왜 느렸나: EXPLAIN으로 범인을 지목#

추측 대신 프로덕션에서 EXPLAIN (ANALYZE, BUFFERS)를 돌렸습니다. 참여가 특히 많은(약 39,667건) 한 대형 공급사 계정으로 실측한 결과입니다.

plain
Bitmap Heap Scan on b2b_buy_join  (rows=39667)
  Buffers: shared hit=1 read=31015      ← heap 31,000 페이지를 디스크에서 read
  Execution Time: 9367 ms

해결 1: covering 인덱스로 Index Only Scan#

sql
CREATE INDEX IF NOT EXISTS idx_b2b_buy_join_join_id_covering
  ON public.b2b_buy_join (join_id) INCLUDE (buy_idx);

프로덕션에 적용한 뒤 같은 쿼리를 다시 재봤습니다.

plain
9,367 ms  →  15.95 ms   (약 586배)

Prisma 쪽 코드는 손댈 게 거의 없었습니다. prefetch는 원래 이렇게 나가고 있었고, 인덱스만 바뀌면 플랜이 알아서 바뀝니다.

typescript
const joinedRows = await this.prisma.b2bBuyJoin.findMany({
  where: { joinId: userId },
  select: { buyIdx: true },
  distinct: ['buyIdx'],
  orderBy: { buyIdx: 'desc' },
  take: MAX_JOINED_BID_IDS, // 30_000
});

운영 함정 하나: CONCURRENTLY#

해결 2: bucket pagination의 lazy count#

두 번째 범인인 count 3발은 접근이 달랐습니다. 정말 3발 다 필요한가를 먼저 물었습니다.

핸들러에서 탭이 지정된 요청은 skipTotal을 켜고, total은 statusCounts에서 가져옵니다.

typescript
// 탭이 지정되면 list count와 statusCounts[tab]가 같은 WHERE의 같은 숫자.
const skipTotal = tab !== undefined;

const [listResult, statusCounts] = await Promise.all([
  this.bidsRepository.findBySpecification(listSpec, { page, limit, sortPolicy, skipTotal }),
  statusCountsPromise,
]);

const total =
  tab !== undefined ? (statusCounts.get(tab) ?? listResult.total) : listResult.total;

typescript
for (let i = 0; i < buckets.length; i++) {
  // skipTotal이고 페이지가 이미 찼으면 남은 bucket count 생략 → break
  if (skipTotal && remainingTake <= 0) break;

  const bucketCount = await this.countBoardRows(boardType, buckets[i]);
  bucketCounts.push(bucketCount);
  if (bucketCount === 0) continue;
  if (remainingTake <= 0) continue; // skipTotal=false 경로만 끝까지 count 계속
  // ... remainingSkip / findMany로 페이지 채우기
}

const total = skipTotal ? 0 : bucketCounts.reduce((a, c) => a + c, 0);

total=0을 반환해도 괜찮은 이유#

결과와 남은 것#

참여 공고 prefetch: 9,367ms → 15.95ms(covering 인덱스, 프로덕션 실측)
마감 탭 진입 시 count fan-out: 평균 케이스 3발 → 1발
목록 total: 별도 count 쿼리 없이 statusCounts 60초 캐시로 충당

독자 TIP#

EXPLAIN에는 항상 BUFFERS를 켜세요. Execution Time만 보면 "느리다"까지밖에 모릅니다. Buffers: ... read=31015 같은 heap read 수치가 있어야 "인덱스는 탔는데 heap 방문이 범인"이라는 진단이 나옵니다. covering 인덱스가 답인지 아닌지가 이 한 줄에서 갈립니다.
covering 인덱스는 SELECT 컬럼이 좁고 고정일 때 잘 듣습니다. WHERE 컬럼은 인덱스 키로, SELECT에서 딱 필요한 컬럼만 INCLUDE로. 여기선 buy_idx 하나였고, Index Only Scan의 이상적인 조건이었습니다. SELECT *처럼 컬럼이 많으면 인덱스만 비대해지니 득이 없습니다.
PK가 있다고 안심하지 마세요. 복합 PK (A, B)는 A 조회는 도와도 B나 역방향은 못 도웁니다. WHERE가 PK 선두 컬럼으로 시작하는지 꼭 확인하세요.
lazy count는 "total을 다른 데서 이미 안다"일 때만 유효합니다. 우리는 탭 뱃지 캐시가 같은 숫자를 들고 있어서 목록 count를 통째로 스킵할 수 있었습니다. total의 단일 출처(SSOT)를 먼저 정하는 게 순서입니다.
IN (...) 리터럴엔 상한을 두세요. 리스트가 파라미터로 박히면 bind 변수 한도(32,767)에 걸려 500이 납니다. 꼬리를 자르는 게 터지는 것보다 낫습니다.
대용량 테이블 인덱스는 CONCURRENTLY를 기억하세요. 다만 트랜잭션으로 감싸는 실행 환경에선 막히니, 마이그레이션 도구의 트랜잭션 처리 방식을 미리 확인해야 합니다.

문제: 마감 탭만 유독 9초였습니다#

왜 느렸나: EXPLAIN으로 범인을 지목#

해결 1: covering 인덱스로 Index Only Scan#

운영 함정 하나: CONCURRENTLY#

해결 2: bucket pagination의 lazy count#

total=0을 반환해도 괜찮은 이유#

결과와 남은 것#

독자 TIP#

관련 글

문제: 마감 탭만 유독 9초였습니다#

왜 느렸나: EXPLAIN으로 범인을 지목#

해결 1: covering 인덱스로 Index Only Scan#

운영 함정 하나: CONCURRENTLY#

해결 2: bucket pagination의 lazy count#

total=0을 반환해도 괜찮은 이유#

결과와 남은 것#

독자 TIP#

관련 글