Index Scan 인데 느린 이유 — EXPLAIN BUFFERS 로 원인 가르기

EXPLAIN 을 보니 인덱스 스캔이에요. 순차 스캔이 아니니 잘된 줄 알았는데 쿼리는 여전히 3초가 걸려요.

인덱스를 하나 더 만들어 봐도, 컬럼 순서를 바꿔 봐도 크게 안 줄어요. 계획은 계속 인덱스 스캔이라고 나오고요.

여기서 대부분 막혀요. 인덱스를 타는 게 목표였는데 목표를 달성했는데도 안 빨라지니, 다음에 뭘 봐야 할지 모르겠는 거죠.

인덱스를 탄다는 건 시작이지 끝이 아니에요. 인덱스가 하는 일은 "어디를 읽을지 알려 주는 것"까지고, 그 뒤에 실제로 읽는 비용은 따로 남아요.

계획 이름은 진단의 첫 줄일 뿐이고, 답은 그 아래 숫자들에 있습니다.

왜 그런가

인덱스 스캔은 두 단계로 이뤄져요. 인덱스에서 조건에 맞는 행의 위치를 찾고, 그 위치로 가서 테이블 본체를 읽어요.

첫 단계는 대개 빨라요. 정렬된 구조를 타고 내려가니까요. 문제는 두 번째예요.

찾은 위치들이 테이블 여기저기에 흩어져 있으면, 읽기가 전부 임의 접근이 돼요. 100만 행을 찾았다면 100만 번의 흩어진 읽기예요. 반면 순차 스캔은 처음부터 끝까지 죽 읽으니 한 번에 여러 페이지를 가져올 수 있어요.

그래서 결과 행이 많아지면 어느 지점부터 인덱스 스캔이 순차 스캔보다 느려져요. 인덱스를 타는 게 항상 이득이 아닌 이유예요.

여기서 상관관계가 중요해져요. 인덱스의 순서와 테이블에 실제로 저장된 순서가 비슷하면, 인덱스를 따라 읽어도 물리적으로 가까운 곳을 읽게 돼요. 시간순으로 쌓이는 데이터에 시간 인덱스를 걸면 이 조건이 좋아요. 반대로 무작위 키로 인덱스를 만들면 매번 멀리 뛰어야 해요.

그리고 인덱스를 탔지만 대부분을 버리는 경우도 있어요. 인덱스로 10만 행을 찾았는데 다른 조건으로 걸러서 50행만 남는다면, 나머지 99,950 행을 읽은 비용은 그대로 낸 거예요.

코드로 보면

어디에 시간이 갔는지부터 가른다:

코드

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

· 1) 본체 읽기가 비싼가
✕ Index Scan ... shared read=480000
✕ → 인덱스로 찾은 뒤 흩어진 곳을 48만 번 읽었다
✓ 커버링 인덱스로 본체 접근 자체를 없앤다

· 2) 읽어 놓고 버리는가
✕ Rows Removed by Filter: 99950
✓ 거른 조건을 인덱스에 넣어 처음부터 안 읽게 한다

· 3) 반복이 많은가
✕ Nested Loop ... loops=120000
✓ 안쪽 1회가 빨라도 12만 번이면 그게 전부다

· SSD 라면 임의 접근 비용 파라미터를 실제에 맞춘다
✓ SET random_page_cost = 1.1;   -- 기본 4.0 은 회전 디스크 전제

✕ 문제 지점 · ✓ 해법 · 그대로 돌려 보기 전에 자기 환경에 맞게 고쳐 주세요.

실전에서는

인덱스를 탔는데 느린 경우를 원인별로 나누면 이래요. 대응이 각각 다릅니다.

첫째, 결과 행이 너무 많은 경우. 인덱스로 좁혔는데도 수십만 행이면 본체 접근이 그만큼 일어나요. 이건 인덱스 문제가 아니라 쿼리가 가져오는 양의 문제예요. 조건을 더 좁히거나, LIMIT 을 걸거나, 애초에 그렇게 많은 행이 필요한지 다시 봐야 해요.

둘째, 임의 접근이 비싼 경우. 인덱스 순서와 저장 순서의 상관이 낮으면 매번 멀리 뛰어요. 커버링 인덱스로 본체 접근을 없애는 게 가장 확실한 해법이에요. 물리적 순서를 인덱스에 맞춰 재정렬하는 방법도 있는데, 한 번 하면 이후 삽입으로 다시 흐트러집니다.

셋째, 읽고 버리는 경우. 인덱스로 찾은 행 중 대부분이 다른 조건에서 걸러진다면, 그 조건을 인덱스에 포함시켜야 해요. 복합 인덱스로 만들면 처음부터 안 읽어요.

넷째, 반복이 많은 경우. 중첩 루프의 안쪽에서 인덱스를 타면 1회는 빨라요. 그런데 그게 12만 번 돌면 총합이 큽니다. 이건 조인 전략 문제고 다음 편의 주제예요.

다섯째, 비용 파라미터가 하드웨어와 안 맞는 경우. 임의 접근 비용의 기본값은 회전 디스크를 전제한 값이에요. SSD 에서는 임의 접근이 순차 접근과 크게 다르지 않은데, 옵티마이저는 여전히 비싸다고 계산해요. 그래서 인덱스 스캔을 실제보다 나쁘게 평가하거나, 반대로 계획 선택이 어긋납니다.

진단의 핵심은 EXPLAIN ANALYZE 에서 어느 항목이 큰지 보는 거예요. 읽은 블록 수가 크면 접근 비용, 버린 행 수가 크면 조건 배치, 반복 횟수가 크면 조인 구조입니다.

처방

읽은 블록 수를 보세요. 인덱스 스캔인데 이 값이 크면 본체 접근이 병목이에요. 커버링 인덱스를 검토하세요.
버린 행 수를 보세요. 크면 그 조건을 인덱스에 넣어 처음부터 안 읽게 하세요.
반복 횟수를 보세요. 안쪽 1회가 빨라도 반복이 많으면 그게 전부입니다.
결과 행 자체를 줄일 수 있는지 보세요. 정말 그 많은 행이 필요한지 물어보세요.
SSD 라면 임의 접근 비용 파라미터를 실제에 맞추세요. 기본값은 회전 디스크 전제입니다.
인덱스 순서와 저장 순서의 상관을 확인하세요. 낮으면 커버링 인덱스가 더 중요해집니다.
캐시 상태를 감안하세요. 두 번째 실행이 빠른 건 계획이 좋아서가 아니라 데이터가 올라와서일 수 있어요.
계획 이름만 보고 판단하지 마세요. 인덱스 스캔은 목표가 아니라 수단입니다.
두 번째 실행이 빠르다고 고쳐진 게 아니에요. 캐시에 올라와서일 수 있으니 읽은 블록 수로 확인하세요.

지금 확인해 보세요

어느 항목이 큰가:

코드

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

· 큰 항목이 원인을 가른다
✓ shared read 가 크다        → 본체 접근. 커버링 인덱스
✓ Rows Removed by Filter 큼  → 늦게 걸렀다. 조건을 인덱스로
✓ loops 가 크다              → 조인 구조 문제
✓ Heap Fetches 가 크다       → 가시성 맵. 청소 문제

· 인덱스 순서와 저장 순서의 상관 (1 에 가까울수록 좋다)
SELECT attname, correlation FROM pg_stats
 WHERE tablename = 'orders' AND attname = 'created_at';
✕ 0 에 가까우면 임의 접근이 많다

· 하드웨어와 비용 파라미터가 맞는가
SHOW random_page_cost;   -- SSD 면 1.1 근처가 현실적

핵심 한 줄인덱스를 타는 건 목표가 아니라 수단이다. 읽은 양을 봐라.

왜 그런가

코드로 보면

실전에서는

처방

지금 확인해 보세요

이어서 읽기