값이 하나뿐인 컬럼이 인덱스를 죽인다

동료가 조사 결과를 보내왔다. 특정 기관의 파일 용량 합계를 구하려는데 35초 제한에 계속 걸린다는 것이었다.

SELECT SUM(file_size) FROM files WHERE tenant_id = 'ACME_001';

결론도 함께였다. "tenant_id 선두 인덱스가 없어서로 보입니다. 실시간 집계는 포기하고 스냅샷 방식으로 가야 합니다."

납득이 가는 이야기였다. 2억 행짜리 테이블이고, 조건은 컬럼 하나다. 인덱스가 없으면 당연히 풀스캔이다. 그런데 확인이나 해보자는 마음으로 인덱스 목록을 찍어봤다.

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'files';

pk_files   (portal_id, channel_id, tenant_id, file_no)  UNIQUE
files_ix1  (portal_id, channel_id, tenant_id, owner_id)
files_ix2  (portal_id, channel_id, tenant_id, file_no, orig_name)
files_ix3  (portal_id, channel_id, tenant_id, file_no, created_at DESC)
files_ix4  (created_at DESC)
files_ix5  (file_no, rand_key, tenant_id)

인덱스는 있었다. tenant_id 가 들어간 것만 네 개다. 전부 세 번째 컬럼으로.

선두 컬럼이 없으면 인덱스는 없는 것과 같다

B-tree 복합 인덱스는 선두 컬럼부터 순서대로 정렬된 자료구조다. 전화번호부가 성으로 먼저 정렬돼 있는 것과 같다. 이름만 알고 성을 모르면 처음부터 끝까지 훑는 수밖에 없고, 그럴 바에는 테이블을 직접 읽는 게 빠르다. 그래서 플래너는 Seq Scan 을 고른다.

PostgreSQL 18 이전에는 이를 우회하는 인덱스 스킵 스캔이 없다. 선두 컬럼의 값이 몇 종류 안 되더라도, 그 값들을 하나씩 돌면서 인덱스 탐색을 반복하는 최적화를 엔진이 알아서 해주지 않는다.

여기까지는 아는 이야기다. 진짜 함정은 그다음이었다.

값이 하나뿐인 컬럼

portal_id 와 channel_id 가 뭔지 궁금해서 분포를 찍어봤다.

SELECT portal_id, channel_id, count(*)
FROM files TABLESAMPLE SYSTEM (0.01)
GROUP BY 1, 2;

 portal_id | channel_id | count
-----------+------------+-------
 PTL_3     | CHNL_1     | 18902

샘플 1만 9천 행이 전부 같은 값이었다. 멀티포털을 대비해 설계에 넣어둔 축인데, 실제 운영에서는 한 값만 쓰이고 있었다.

그러니까 이 컬럼들은 아무것도 구분하지 않는다. 조건에 넣어도 걸러지는 게 없다. 애플리케이션 코드를 짠 사람 입장에서 생략하는 게 너무 당연하다. 항상 같은 값인데 왜 쓰겠는가.

그런데 인덱스 입장에서는 그게 유일한 진입점이다. 의미가 없어서 생략했고, 생략했기 때문에 인덱스 네 개가 통째로 죽었다.

항상 참인 조건 두 개를 더 쓴다

고치는 방법은 허무하다. 상수를 그냥 적어준다.

SELECT SUM(file_size) FROM files
 WHERE portal_id = 'PTL_3'
   AND channel_id = 'CHNL_1'
   AND tenant_id = 'ACME_001';

실행 시간은 35초 타임아웃에서 525ms 가 됐다. 쿼리의 의미는 조금도 바뀌지 않았다. 항상 참인 조건 두 개를 적었을 뿐이다.

그래도 안 되는 구간이 있다

여기서 끝났으면 좋았겠지만, 가장 큰 기관으로 다시 해보니 인덱스를 타고도 35초를 넘겼다.

매칭되는 행이 478만 건이었고, files_ix1 에는 file_size 가 없다. 인덱스로 행을 찾은 다음 크기를 읽으려고 478만 번 힙에 다시 다녀와야 한다. 이건 인덱스를 탄다고 해결되는 문제가 아니다.

이 경우는 커버링 인덱스로 Index Only Scan 을 만들어야 한다.

CREATE INDEX CONCURRENTLY files_usage_ix
    ON files (portal_id, channel_id, tenant_id) INCLUDE (file_size);

INCLUDE 로 넣은 컬럼은 인덱스 키가 아니라 리프에 얹히는 값이라 정렬에는 영향을 주지 않으면서 힙 접근만 없애준다. 다만 225GB 테이블에 인덱스를 하나 더 만드는 비용은 별도로 판단할 문제라, 이건 제안까지만 하고 넘겼다.

그래서 동료의 원래 결론인 "스냅샷 방식으로 가야 한다"는 여전히 유효하다. 다만 이유가 다르고, 적용 범위도 다르다. 인덱스가 없어서가 아니라 대형 기관의 힙 접근 때문이고, 중소 기관은 0.5초면 되니까 온디맨드 재계산이 가능하다. 전 기관을 한 덩어리로 묶어서 포기할 필요는 없었다.

남는 생각

느린 쿼리를 만나면 "인덱스가 없구나" 하고 결론짓기 전에 pg_indexes 를 먼저 본다. 인덱스가 있는데 안 타는 경우가 아예 없는 경우보다 흔하고, 그중 상당수는 선두 컬럼 누락이다.

그리고 이 문제는 컬럼이 무의미할수록 잘 생긴다. 카디널리티가 1인 컬럼은 걸러내는 게 없으니 개발자가 자연스럽게 생략하는데, 하필 그게 인덱스의 입구다. 스키마에 미래를 대비해 넣어둔 축이 복합 PK 선두에 박혀 있다면, 그 축은 쿼리마다 빠짐없이 채워야 한다.

한 가지 더. 남이 내린 결론을 받았을 때 그 결론이 합리적일수록 검증을 건너뛰게 된다. "2억 행에 인덱스가 없으면 느리다"는 너무 말이 되는 문장이라 확인할 생각이 안 든다. 이번에 아낀 건 결국 pg_indexes 한 번 조회한 30초였다.

참고

• PostgreSQL: Multicolumn Indexes
• PostgreSQL: Index-Only Scans and Covering Indexes