CockroachDB 인덱스 — 걸었는데 20만 행을 다 읽은 이유

내부 테스트에서 클러스터 리소스 목록 조회가 느려서 인덱스를 걸었다. 실행시간이 233ms에서 104ms로 줄어서 처음엔 해결된 줄 알았다. 그런데 EXPLAIN ANALYZE에서 읽은 행 수를 보니 205,000에서 200,025로 거의 그대로였다. 숫자가 이상했다.

증상: 줄일 데가 안 보였다

내부 테스트를 돌리면서 클러스터 리소스 목록 API의 응답 시간을 보고 있었다. 목표는 단순했다. 여기서 더 줄일 방법이 있나.

먼저 본 건 애플리케이션 쪽이었다. 불필요한 직렬화, 반복 호출, 락 경합을 전부 한 번씩 의심해 봤고 딱히 나오는 게 없었다. 코드는 이미 여러 번 다듬은 상태였다. 그래서 남은 DB 쪽으로 갔다.

첫 가설: dev에서는 안 느렸다

dev 환경에서 재 보면 쿼리가 안 느렸다. 수십 밀리초 안에 끝났다.

dev에서 수십 밀리초면 DB는 문제가 아니지 않을까.

안 느린데요로 끝낼 수도 있었다. 그런데 테이블 행 수를 세어 보니 답이 나왔다.

instances  : 112 rows
volumes    :  87 rows
networks   :  23 rows
projects   :  11 rows

테이블당 0~150건. 이 크기에서는 풀스캔이든 인덱스 스캔이든 차이가 안 난다. 100건을 다 읽는 데 걸리는 시간은 어차피 0에 수렴한다.

지금 안 느린 건 쿼리가 좋아서가 아니라 데이터가 없어서다. 실제 환경의 클라우드 클러스터는 인스턴스와 볼륨이 수천에서 수만 건이니, 여기서 잰 수십 밀리초로는 거기서 어떨지 알 수 없다.

단서: EXPLAIN의 FULL SCAN

DB 파드에 직접 붙어서 실제로 나가는 쿼리를 EXPLAIN으로 찍었다.

> EXPLAIN SELECT * FROM volumes WHERE uuid = 'x';

                  info
------------------------------------------
  • scan
    table: volumes@volumes_pkey
    spans: FULL SCAN                        ← 여기

  index recommendations: 1
    1. CREATE INDEX ON volumes (uuid) STORING (...);

볼 줄은 spans: FULL SCAN이다. uuid 하나로 한 건을 찾는데 테이블 전체를 훑고 있었다. 아래 index recommendations가 uuid 인덱스를 권하는 것도 같은 이야기다.

조인이 걸린 쪽도 찍어 봤다.

EXPLAIN SELECT i.* FROM instances i
  JOIN projects p ON i.project_id = p.id
 WHERE p.cluster_id = 1;

  → instances : FULL SCAN + lookup join

여기서도 instances는 전체를 읽고, 프로젝트와 잇는 데에 lookup join이 붙어 있었다.

전수 확인: 보조 인덱스가 하나도 없었다

한 군데만 그런 건지 확인하려고 information_schema.statistics로 전수 조회를 돌렸다. 결과는 예상보다 나빴다. 리소스 테이블 17개에 PK 외 보조 인덱스가 하나도 없었다.

uuid, project_id처럼 가장 자주 필터링되는 컬럼도 전부 PK의 storing 컬럼일 뿐이었다. storing 컬럼은 인덱스 항목에 값을 같이 얹어 둘 뿐이라, 그 값으로는 위치를 찾아갈 수 없다. 그 컬럼으로 찾으려면 결국 전체를 읽어야 한다.

id로는 정렬된 순서를 따라 찾고, uuid로는 테이블 전체를 읽는다

재현: 30만 행 스크래치 테이블

문제는 알았는데, 이걸 어떻게 증명하느냐가 남았다. dev 데이터로는 아무리 재도 차이가 안 난다.

운영 테이블에 데이터를 넣어 볼 수는 없었다. 그래서 같은 구조의 스크래치 테이블을 만들고 30만 행을 적재했다. 컬럼 구성(id, FK 컬럼, uuid)만 같게 두고 실제 테이블은 건드리지 않았다. 측정이 끝난 뒤 DROP했고, 실제 테이블에 영향이 없다는 것도 확인했다.

30만 행	인덱스 없음	인덱스 있음
`project_id`	401ms 300,000행	13ms 120행
`uuid`	418ms 300,000행	3ms 2행

인덱스 하나로 각각 약 30배, 140배 차이가 났다. 이 표가 안 느린데요에 대한 답이었다. 행이 30만 개가 되면 같은 쿼리가 400ms를 넘긴다.

직접 해 보기

운영 테이블을 건드리지 않고 스케일 영향을 재는 방법은 간단하다. 같은 컬럼 구성의 임시 테이블에 generate_series로 행을 채우고, 인덱스 유무만 바꿔 가며 EXPLAIN ANALYZE를 찍는다. 절대값은 환경마다 다르지만 배율은 남는다.

CREATE TABLE scratch_volumes (id INT PRIMARY KEY, project_id INT, uuid UUID);
INSERT INTO scratch_volumes
  SELECT i, i % 2500, gen_random_uuid() FROM generate_series(1, 300000) AS g(i);

EXPLAIN ANALYZE SELECT * FROM scratch_volumes WHERE project_id = 42; -- 인덱스 없음
CREATE INDEX ON scratch_volumes (project_id);
EXPLAIN ANALYZE SELECT * FROM scratch_volumes WHERE project_id = 42; -- 인덱스 있음

DROP TABLE scratch_volumes;

인덱스를 걸었는데 왜 20만 행을 읽었나

인덱스가 줄인 건 5천 행짜리 프로젝트 쪽 스캔이었다. 20만 행짜리 인스턴스 테이블은 인덱스를 건 뒤의 실행계획에서도 처음부터 끝까지 읽혔고, 쿼리를 IN 서브쿼리 형태로 바꾸고 나서야 읽은 행이 1,049로 줄었다.

재현 표까지 만들었을 때는 인덱스 목록을 정리해 DDL에 반영하면 끝날 줄 알았다. 가장 무거운 쿼리부터 다시 쟀다. 인스턴스 목록 조회는 5개 테이블을 엮는 조인이다.

SELECT i.*, p.*, h.*, az.*, s.*
  FROM instances i
  JOIN projects            p  ON i.project_id     = p.id
  JOIN hypervisors         h  ON i.hypervisor_id  = h.id
  JOIN availability_zones  az ON i.az_id          = az.id
  JOIN specs               s  ON i.spec_id        = s.id
 WHERE p.cluster_id = ?;

인스턴스 20만 행, 프로젝트 5천 행 규모로 재현해서 인덱스 전후를 비교했다. 실행시간만 보면 2.2배 개선이다. 나쁘지 않아 보였다. 그런데 읽은 행 수가 이상했다. 205,000에서 200,025. 거의 안 줄었다.

EXPLAIN ANALYZE의 읽은 행 수 (인스턴스 20만 · 프로젝트 5천)

인덱스 없음 · 233ms205,000
인덱스만 추가 · 104ms200,025
인덱스 + 쿼리 재작성 · 89ms1,049

인덱스만 추가했을 때 projects는 5,000 → 25로 줄었지만, instances 200,000행은 그대로 읽었다

두 숫자를 나눠 보고 나서야 무슨 일이 일어난 건지 알았다. 205,000은 인스턴스 200,000행과 프로젝트 5,000행이고, 200,025는 인스턴스 200,000행과 프로젝트 25행이다. 인덱스가 먹은 건 프로젝트 쪽뿐이었다.

실행시간이 절반이 된 것도 5,000행 스캔이 사라지고 조인 방식이 바뀌면서 붙은 효과였다. 20만 행 스캔은 그대로 남아 있었다.

처음 세운 설명과 그 빈 곳

처음엔 cluster_id 조건이 조인 뒤에 걸려 있어서 인스턴스를 다 읽는 줄 알았다.

cluster_id는 projects의 컬럼이니, 조인하기 전에는 어떤 인스턴스가 대상인지 알 수 없다고 본 것이다.

그런데 이 설명에는 빈 곳이 있었다. SQL에서 WHERE를 어디에 적었는지가 실제 실행 순서를 정하지는 않는다. CockroachDB 옵티마이저는 통계와 비용 추정으로 조인 순서, 필터를 적용할 위치, 인덱스 사용 여부를 고른다. 같은 뜻의 쿼리가 환경에 따라 다른 계획이 되기도 하고, 모양이 다른 쿼리가 같은 계획으로 정리되기도 한다(CockroachDB 비용 기반 옵티마이저 설명).

그래서 여기서는 관측한 것만 적는다. 당시 계획에서는 인스턴스 전체 스캔이 남았고, 아래 IN 서브쿼리 형태로 바꾸자 스캔 범위가 줄었다. 옵티마이저가 왜 처음 계획을 골랐는지는 당시 통계와 DB 버전까지 봐야 설명할 수 있는데, 그건 확인하지 못했다.

쿼리 형태를 바꿨다

인덱스를 더 얹는 대신 쿼리 형태를 바꿨다. 대상 클러스터의 project id를 먼저 좁히고, 그 결과로 인스턴스 스캔 범위 자체를 제한한다.

-- ✘ 인덱스만 추가: 이때 계획에서는 instances가 여전히 FULL SCAN
 WHERE p.cluster_id = ?;

-- ✓ 스캔 범위 자체를 제한한다
 WHERE i.project_id IN (
         SELECT id FROM projects WHERE cluster_id = ?
       );

이 형태가 조인보다 늘 빠르다는 보장은 없다. 비슷한 상황이면 같은 데이터에서 두 형태의 EXPLAIN ANALYZE를 나란히 떠 보는 게 먼저다.

방식	실행시간	읽은 행
인덱스 없음 (조인 그대로)	233ms	205,000
인덱스만 추가 (조인 그대로)	104ms	200,025
인덱스 + 쿼리 재작성	89ms	1,049

실행시간은 104ms에서 89ms로 15% 정도만 더 줄었다. 하지만 읽은 행은 200,025에서 1,049로 190배 줄었다.

당시 테스트 데이터(인스턴스 20만 행, 프로젝트 5천 행)에서 잰 값이다. 데이터 분포나 DB 버전이 다르면 옵티마이저가 다른 계획을 고를 수 있어서, 개선 폭도 달라질 수 있다.

이 20만 행 규모에서는 데이터가 전부 캐시에 올라와 있어서 실행시간 차이가 작게 나온다. 데이터가 늘어나면 갈라지는 건 실행시간 쪽이다. 1,049는 클러스터 하나의 크기를 따라가고, 200,025는 시스템 전체 크기를 따라간다. 앞의 것은 상수에 가깝고 뒤의 것은 계속 자란다.

인덱스를 전부 걸지 않은 이유

목록을 만들면서 필터링에 쓰이는 컬럼 전부로 갈 수도 있었다. 그렇게 하지 않았다. 인덱스는 읽기 비용을 쓰기 비용으로 바꾸는 거래이기 때문이다.

읽기 비용 ↓

스캔 범위가 줄어든다
조인할 때 lookup이 가능해진다
정렬 비용을 피할 수 있다

쓰기 비용 ↑

쓸 때마다 인덱스도 함께 갱신한다
저장 공간이 복제본 수만큼 늘어난다
옵티마이저가 따질 후보가 늘어난다

리소스 테이블은 클러스터 동기화 때 대량 쓰기가 일어나는 쪽이라 공짜가 아니었다. 그래서 기준을 두 개로 좁혀 16개를 골랐다.

실제 쿼리에서 단독 필터 조건으로 쓰이는 컬럼 (uuid 등)
조인의 FK 쪽 컬럼. 조인마다 lookup이 걸리는 자리다

하나 더. 새 인덱스 추가는 기존 컬럼과 제약 조건을 건드리지 않고, CockroachDB의 온라인 스키마 변경으로 서비스 중단 없이 적용된다. 이건 확인하고 넘어갔다.

정리

실행시간

89ms

233ms에서

인스턴스 20만 · 프로젝트 5천 행 테스트 데이터

읽은 행

1,049

205,000에서

같은 데이터, EXPLAIN ANALYZE

실행시간이 절반으로 줄었을 때 거기서 멈출 뻔했다. 전체 스캔이 남아 있다는 건 읽은 행 수를 보고서야 알았다. 다음부터는 최적화 전후로 실행시간과 읽은 행 수, 데이터 크기, 실행계획을 같이 적어 두려고 한다.

비슷한 조회가 느려지면 이 순서로 본다.

테이블 행 수. dev에서 안 느린 이유가 데이터가 없어서인지부터 가른다.
EXPLAIN의 spans. FULL SCAN이 보이면 그 필터 컬럼이 인덱스 키인지 확인한다.
인덱스를 건 뒤의 EXPLAIN ANALYZE. 실행시간만 보지 않고 테이블별 읽은 행 수가 줄었는지 본다.

읽은 행 수도 CPU나 네트워크, 캐시 비용까지 보여 주지는 않아서, 그쪽이 의심되면 따로 재 봐야 할 것 같다.

참고 자료

EXPLAIN, EXPLAIN ANALYZE: CockroachDB 문서. spans, index recommendations, 읽은 행 수를 읽는 법
Secondary Indexes: 보조 인덱스와 STORING 컬럼
Cost-Based Optimizer: 통계와 비용으로 계획을 고르는 방식
Online Schema Changes: 서비스 중단 없이 인덱스를 추가하는 방식
information_schema: 전수 확인에 쓴 statistics 테이블