[PostgreSQL] 인덱스가 있는데 안 쓰는 진짜 이유

시리즈 마지막이다. 앞의 두 편에서 행 수 추정을 맞추는 이야기를 했다. 이번엔 추정이 맞아도 인덱스를 안 쓰는 경우다.

인덱스를 만들었는데 EXPLAIN에 여전히 Seq Scan이 나온다. 이때 먼저 가를 것이 있다. 플래너가 인덱스를 못 쓰는 건지, 안 쓰는 건지.

못 쓰는 경우

조건의 모양이 인덱스와 안 맞으면 플래너는 선택지 자체가 없다.

컬럼에 함수를 씌웠다.

WHERE lower(email) = 'a@b.com'      -- email 인덱스 못 씀
WHERE date(created_at) = '2026-09-01'  -- created_at 인덱스 못 씀
WHERE created_at + interval '1 day' > now()

인덱스는 email의 값으로 정렬돼 있지 lower(email)의 값으로 정렬돼 있지 않다. 표현식 인덱스를 만들거나, 조건을 컬럼이 그대로 남는 형태로 바꾼다.

WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02'

타입이 다르다. varchar 컬럼에 정수를 비교하면 컬럼이 캐스팅되고, 그러면 위와 같은 상황이 된다. bigint 컬럼에 int 파라미터는 괜찮다. 파라미터가 승격되니까. 반대로 int 컬럼에 bigint 파라미터도 대개 괜찮다. 문제는 숫자와 문자열 사이다.

LIKE 앞에 와일드카드. LIKE '%abc'는 B-tree로 못 찾는다. LIKE 'abc%'는 될 수 있는데, 조건이 있다. 데이터베이스 로케일이 C가 아니면 일반 인덱스로는 안 되고 text_pattern_ops 인덱스가 필요하다.

CREATE INDEX idx_name_pattern ON items (name text_pattern_ops);

OR로 다른 컬럼을 묶었다. WHERE a = 1 OR b = 2는 각각 인덱스가 있어도 하나로는 못 푼다. 비트맵 OR로 두 인덱스를 합칠 수는 있는데 플래너가 항상 그러진 않는다. UNION으로 쪼개면 확실하다.

NOT IN, <>, IS NOT NULL. 대부분의 행이 해당되므로 인덱스가 의미 없다. 이건 못 쓴다기보다 써도 이득이 없는 쪽에 가깝다.

여기까지가 못 쓰는 경우다. 조건을 고치면 해결된다.

안 쓰는 경우

쓸 수 있는데 안 쓴다면 플래너가 비용을 계산해서 순차 스캔이 싸다고 판단한 것이다. 이건 세 가지 중 하나다.

실제로 순차 스캔이 맞다

결과가 테이블의 상당 부분이면 인덱스가 손해다. 인덱스로 행 위치를 찾고, 그 위치로 가서 읽고, 다음 위치로 가서 읽고. 임의 접근이 반복된다. 테이블을 처음부터 끝까지 한 번 읽는 게 빠르다.

경계는 대략 5~10%다. 정확한 값은 테이블 크기, 인덱스 상관도, 하드웨어에 따라 다르다. 그 사이에 비트맵 스캔이라는 중간 단계가 있다. 인덱스로 위치를 모아서 정렬한 뒤 순서대로 읽는 방식이다.

작은 테이블도 마찬가지다. 몇 페이지짜리 테이블은 그냥 읽는 게 빠르다. 개발 환경에서 데이터가 적을 때 인덱스가 안 쓰인다고 걱정할 필요 없는 이유다.

행 수 추정이 틀렸다

1편, 2편 이야기다. 실제로는 0.1%인데 30%로 추정하면 순차 스캔을 고른다. EXPLAIN ANALYZE의 추정과 실제를 먼저 본다.

비용 파라미터가 하드웨어와 안 맞는다

추정 행 수가 맞는데도 순차 스캔이면 여기다.

플래너의 비용 단위는 이렇다.

random_page_cost = 4.0이 핵심이다. 임의 접근이 순차 접근의 4배 비싸다는 가정인데, 이건 회전 디스크 기준이다. 헤드가 움직여야 하니까. SSD에서는 그 차이가 거의 없다.

기본값 그대로면 플래너는 인덱스 스캔(임의 접근 많음)을 실제보다 비싸게 본다. 그래서 순차 스캔 쪽으로 기운다.

ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

SSD면 1.1~1.5가 보통이다. 매니지드 서비스도 기본값을 4.0으로 두는 경우가 있으니 확인해볼 만하다.

effective_cache_size도 자주 방치된다. 이건 메모리를 할당하는 값이 아니다. 플래너에게 “인덱스와 데이터가 이 정도는 캐시에 있을 거다"라고 알려주는 힌트다. 크면 인덱스 스캔이 디스크를 덜 읽을 거라 보고 비용을 낮게 잡는다.

기본값 4GB는 64GB 서버에서 너무 작다. shared_buffers와 OS 캐시를 합쳐 전체 메모리의 50~75%로 잡는 게 보통이다.

ALTER SYSTEM SET effective_cache_size = '48GB';

진단하는 법

파라미터 문제인지 확인하는 방법이 있다.

SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;

순차 스캔을 강제로 비싸게 만들어서 인덱스 계획을 뽑아본다. 두 계획의 actual time을 비교한다.

• 인덱스 계획이 실제로 빠르면: 비용 파라미터가 틀렸다. random_page_cost부터 본다.
• 인덱스 계획이 실제로 느리면: 플래너가 맞았다. 순차 스캔이 정답이다.

enable_seqscan은 진단용이다. 운영에 켜두면 안 된다. 세션이 끝나면 돌아온다.

한 가지 더. 인덱스 전용 스캔(Index Only Scan)이 안 나오는 건 또 다른 이유다. 가시성 맵이 갱신돼 있어야 하는데, 그건 VACUUM이 한다. VACUUM 시리즈에서 다룬 이야기다. 갱신이 잦은 테이블은 Index Only Scan이 나와도 Heap Fetches가 많아서 이득이 적다.

시리즈를 마치며

세 편의 순서가 진단 순서다.

먼저 조건의 모양을 본다. 인덱스를 못 쓰는 형태면 조건을 고친다. 다음으로 추정 행 수를 본다. 실제와 다르면 통계(1편)나 독립 가정(2편)이다. 마지막으로 추정이 맞는데도 계획이 이상하면 비용 파라미터(3편)다.

대부분의 느린 쿼리는 이 셋 중 하나에서 잡힌다. 그리고 이 중 하드웨어에 맞는 random_page_cost와 effective_cache_size는 서버 세팅 때 한 번만 맞추면 되는데, 그 한 번을 안 해서 계속 순차 스캔이 나오는 경우가 생각보다 많다.

자주 묻는 질문

Q. 인덱스를 만들었는데 순차 스캔을 하는 이유는 무엇인가요?

두 경우로 나뉩니다. 조건이 인덱스와 맞지 않아 아예 쓸 수 없는 경우(컬럼에 함수 적용, 타입 불일치, 앞쪽 와일드카드 LIKE 등)와, 쓸 수는 있지만 플래너가 비용을 계산해 순차 스캔이 더 싸다고 판단한 경우입니다. 후자는 결과 행이 테이블의 상당 비율이거나, 비용 파라미터가 실제 하드웨어와 맞지 않을 때 생깁니다.

Q. random_page_cost는 왜 바꿔야 하나요?

기본값 4.0은 임의 접근이 순차 접근보다 4배 비싸다는 뜻으로, 회전 디스크 기준입니다. SSD에서는 그 차이가 거의 없어 1.1 정도가 실제에 가깝습니다. 기본값을 그대로 두면 플래너가 인덱스 스캔의 비용을 실제보다 높게 계산해 순차 스캔을 과하게 선호합니다.

Q. effective_cache_size는 메모리를 할당하나요?

아닙니다. 메모리를 잡지 않고 플래너에게 힌트만 줍니다. 운영체제 캐시와 shared_buffers를 합쳐 인덱스와 데이터가 얼마나 메모리에 있을지 알려주는 값이며, 기본값 4GB는 대부분의 서버에서 너무 작습니다. 전체 메모리의 50~75%로 두면 인덱스 스캔 비용이 현실적으로 계산됩니다.