PostgreSQL 인덱스 안타는 이유 9가지와 해결

서버가 느려졌을 때 가장 흔히 보는 장면이 EXPLAIN 결과의 Seq Scan 입니다. 인덱스를 만들었는데도 플래너가 인덱스를 타지 않는다면, 대개는 “인덱스가 쓸모없다”가 아니라 “현재 쿼리 형태와 데이터 분포에서 인덱스가 이득이 아니다” 혹은 “통계/설정/타입 때문에 인덱스를 못 쓴다” 쪽입니다.

이 글은 PostgreSQL에서 인덱스가 안 타는 대표적인 이유 9가지를 케이스별로 분해하고, 각 케이스의 해결책을 EXPLAIN (ANALYZE, BUFFERS) 중심으로 정리합니다. 문제를 재현하는 간단한 예시 SQL도 함께 제공합니다.

참고로, 장애나 성능 이슈는 “원인 후보를 빠르게 좁히는 체크리스트”가 중요합니다. 비슷한 진단 접근은 gRPC 마이크로서비스 503·데드라인 초과 디버깅 글의 트러블슈팅 흐름도 도움이 됩니다.

0) 먼저 확인: 정말 인덱스가 ‘안 타는’가?

인덱스가 전혀 사용되지 않는지, 혹은 사용되지만 느린지부터 구분해야 합니다.

• Seq Scan이면 테이블 전체(또는 파티션 전체)를 읽고 조건을 필터링합니다.
• Index Scan은 인덱스를 통해 필요한 튜플을 찾아 테이블로 접근합니다.
• Index Only Scan은 테이블 접근을 생략할 수 있습니다(가시성 맵 조건 충족 시).
• Bitmap Index Scan + Bitmap Heap Scan은 “많이 걸리지만 전부는 아닌” 범위에서 자주 나옵니다.

진단 기본 쿼리:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42
  AND created_at >= now() - interval '7 days';

여기서 봐야 할 포인트:

• actual rows vs rows(추정치) 차이: 통계/상관관계 문제 신호
• BUFFERS: shared hit/read 비율(디스크 읽기 여부)
• Filter:에 조건이 남아 있는지(인덱스 조건으로 내려갔는지)

1) 선택도(Selectivity)가 낮아 인덱스가 손해인 경우

조건이 너무 많은 행을 반환하면 인덱스를 타는 것보다 그냥 순차 스캔이 빠를 수 있습니다. 특히 반환 비율이 수십 %에 가까워지면 인덱스는 랜덤 I/O 비용이 커집니다.

예:

-- status가 대부분 'PAID'라면
SELECT *
FROM orders
WHERE status = 'PAID';

해결:

• 더 선택적인 조건을 함께 묶어 복합 인덱스를 고려
• 결과가 “대부분”이라면 인덱스가 아니라 파티셔닝/요약 테이블/쿼리 구조 변경이 정답일 수 있음

복합 인덱스 예:

CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC);

status만으로는 선택도가 낮지만, 최근 기간 조건이 함께 들어오면 (status, created_at)이 의미가 생깁니다.

2) 함수/연산으로 컬럼을 감싸서 인덱스 사용이 막히는 경우

가장 흔한 실수입니다. 컬럼에 함수가 적용되면 일반 B-tree 인덱스는 조건을 그대로 매칭할 수 없습니다.

나쁜 예:

-- created_at에 함수 적용
SELECT *
FROM orders
WHERE date(created_at) = current_date;

해결 A: 조건을 “컬럼이 그대로 남는 형태”로 변환

SELECT *
FROM orders
WHERE created_at >= current_date
  AND created_at < current_date + interval '1 day';

해결 B: 표현식 인덱스(Functional/Expression index)

CREATE INDEX CONCURRENTLY idx_orders_created_date
ON orders ((date(created_at)));

주의: 표현식 인덱스는 쿼리의 표현식이 정확히 동일해야 잘 탑니다.

3) 타입 불일치/암묵적 캐스팅으로 인덱스가 무력화되는 경우

컬럼 타입과 비교 값 타입이 다르면 플래너가 암묵적 캐스팅을 삽입하고, 그 결과 인덱스를 못 타는 경우가 있습니다.

예: user_id가 uuid인데 문자열로 비교

SELECT *
FROM sessions
WHERE user_id = '9b2f...';

해결: 명시 캐스팅으로 타입을 맞추기

SELECT *
FROM sessions
WHERE user_id = '9b2f...'::uuid;

또는 애플리케이션 레이어에서 바인딩 타입을 정확히 전달(Prepared Statement)하는 것이 장기적으로 안전합니다.

4) LIKE 패턴이 앞에 와일드카드라 B-tree가 못 쓰는 경우

B-tree는 접두(prefix) 매칭에 강하고, 접미/중간 검색(%keyword%)에는 약합니다.

나쁜 예:

SELECT *
FROM users
WHERE email LIKE '%@gmail.com';

해결 A: 접두 검색으로 바꿀 수 있으면 바꾸기

SELECT *
FROM users
WHERE email LIKE 'dev%';

해결 B: pg_trgm + GIN 인덱스

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY idx_users_email_trgm
ON users USING gin (email gin_trgm_ops);

그리고 쿼리는 그대로:

SELECT *
FROM users
WHERE email LIKE '%@gmail.com';

트라이그램은 저장공간과 쓰기 비용이 증가하므로, 검색 패턴과 트래픽을 보고 적용해야 합니다.

5) OR 조건 때문에 플랜이 꼬이거나 인덱스를 못 타는 경우

OR는 플래너가 인덱스를 조합할 수도 있지만, 비용 추정이 불리해 Seq Scan으로 떨어지기도 합니다.

예:

SELECT *
FROM orders
WHERE user_id = 42
   OR guest_email = 'a@b.com';

해결 A: UNION ALL로 분리(중복 제거 필요 시 UNION)

SELECT *
FROM orders
WHERE user_id = 42
UNION ALL
SELECT *
FROM orders
WHERE guest_email = 'a@b.com'
  AND user_id IS DISTINCT FROM 42;

해결 B: 각 조건에 맞는 인덱스를 준비

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);
CREATE INDEX CONCURRENTLY idx_orders_guest_email ON orders (guest_email);

OR를 유지해야 한다면, 실제로 BitmapOr가 만들어지는지 EXPLAIN으로 확인하세요.

6) 복합 인덱스의 컬럼 순서가 쿼리와 맞지 않는 경우

복합 인덱스는 “왼쪽부터(prefix)” 규칙이 중요합니다.

예: 인덱스가 (created_at, user_id)인데, 쿼리는 user_id만 필터링

-- 인덱스
-- (created_at, user_id)
SELECT *
FROM orders
WHERE user_id = 42;

이 경우 (created_at, user_id) 인덱스는 user_id 단독 조건에 잘 쓰이지 않습니다.

해결:

• 쿼리 패턴에 맞춰 인덱스 순서를 재설계
• 또는 필요한 단독 인덱스를 추가

CREATE INDEX CONCURRENTLY idx_orders_user_id_created_at
ON orders (user_id, created_at DESC);

팁: ORDER BY created_at DESC LIMIT 50 같은 패턴이 많다면, 정렬 방향까지 고려한 인덱스가 체감 성능을 크게 바꿉니다.

7) 통계가 오래됐거나 분포가 치우쳐 추정이 틀린 경우

PostgreSQL 플래너는 통계에 기반해 비용을 계산합니다. 통계가 오래되었거나, 특정 값에 데이터가 몰린 “스큐(skew)”가 심하면 추정이 빗나가 인덱스를 포기할 수 있습니다.

체크:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42;

rows=(추정)와 actual rows=(실제)가 크게 다르면 통계 문제 가능성이 큽니다.

해결 A: ANALYZE

ANALYZE orders;

해결 B: 통계 타깃 상향(특정 컬럼만)

ALTER TABLE orders
ALTER COLUMN user_id SET STATISTICS 1000;

ANALYZE orders;

해결 C: 확장 통계(컬럼 상관관계)

서로 연관된 조건이 함께 쓰일 때(예: country와 city) 독립 가정이 깨져 추정이 틀어집니다.

CREATE STATISTICS st_orders_country_city (dependencies)
ON country, city
FROM orders;

ANALYZE orders;

8) MVCC로 인한 Index Only Scan 불가(가시성 맵 미충족)

인덱스는 타지만 기대만큼 빠르지 않은 경우, Index Only Scan이 안 나와서 “테이블 힙 접근”이 많이 발생할 수 있습니다. 이는 업데이트/삭제가 잦아 가시성 맵이 덜 채워졌거나, VACUUM이 충분히 돌지 않았을 때 흔합니다.

체크: EXPLAIN에서 Index Only Scan인지, Heap Fetches가 많은지 확인

해결:

• autovacuum 설정/튜닝
• 대량 변경 작업 후 수동 VACUUM (ANALYZE) 고려

VACUUM (ANALYZE) orders;

운영에서는 “VACUUM을 무조건 돌려라”가 아니라, 테이블별 변경량에 맞춰 autovacuum을 조정하는 게 핵심입니다.

9) 플래너 비용 파라미터/메모리 설정 때문에 인덱스가 불리해지는 경우

특히 클라우드/컨테이너 환경에서 스토리지 특성이 다르거나, 기본 비용 파라미터가 실제 환경과 맞지 않으면 플래너가 잘못된 선택을 할 수 있습니다.

대표 파라미터:

• random_page_cost: 랜덤 I/O 비용(SSD에서는 낮추는 경우가 많음)
• seq_page_cost: 순차 I/O 비용
• effective_cache_size: OS 캐시 포함 “캐시로 기대하는 메모리” 추정
• work_mem: 정렬/해시 등에 쓰는 메모리(너무 낮으면 디스크 스필)

현재 값 확인:

SHOW random_page_cost;
SHOW effective_cache_size;
SHOW work_mem;

해결 방향(예시): SSD 환경에서 random_page_cost를 낮춰 인덱스가 더 경쟁력 있게 만들기

ALTER SYSTEM SET random_page_cost = '1.1';
ALTER SYSTEM SET effective_cache_size = '8GB';
SELECT pg_reload_conf();

주의:

• 값은 “정답”이 아니라 워크로드와 하드웨어에 따라 달라집니다.
• 변경 전후로 반드시 EXPLAIN (ANALYZE, BUFFERS)와 실제 p95/p99 지표를 비교하세요.

실전 점검 순서: 빠르게 원인을 좁히는 체크리스트

1. EXPLAIN (ANALYZE, BUFFERS)로 실제 실행 계획 확인
2. 조건이 반환하는 행 비율이 큰지(선택도) 확인
3. 컬럼이 함수/캐스팅/연산으로 감싸졌는지 확인
4. LIKE 패턴이 %로 시작하는지 확인
5. OR를 UNION ALL로 분리 가능한지 검토
6. 복합 인덱스의 컬럼 순서가 쿼리 패턴과 맞는지 확인
7. 추정치가 틀리면 ANALYZE, 통계 타깃, 확장 통계 검토
8. “인덱스는 타는데 느리다”면 Index Only Scan/Heap Fetches와 VACUUM 상태 확인
9. 환경에 맞는 비용 파라미터/메모리 설정 점검

이런 식의 “원인 후보를 단계적으로 제거”하는 방식은 다른 장애 진단에도 그대로 적용됩니다. 예를 들어, 외부 요인과 내부 요인을 분리해 좁혀가는 접근은 OpenAI Responses API 429·rate_limit 해결 10가지 같은 글의 구조와도 유사합니다.

자주 쓰는 보조 테크닉: 인덱스 설계 패턴 3가지

부분 인덱스(Partial Index): 특정 조건에서만 자주 조회될 때

예: “활성 사용자만” 자주 조회

CREATE INDEX CONCURRENTLY idx_users_active_email
ON users (email)
WHERE deleted_at IS NULL;

부분 인덱스는 크기가 작고 유지 비용이 낮아, 조건이 명확할 때 매우 효과적입니다.

커버링 인덱스(INCLUDE): 테이블 접근을 줄이고 싶을 때

PostgreSQL의 INCLUDE는 정렬/검색 키가 아닌 컬럼을 인덱스에 실어 Index Only Scan 가능성을 높입니다.

CREATE INDEX CONCURRENTLY idx_orders_user_id_include
ON orders (user_id)
INCLUDE (status, created_at, total_amount);

조회 컬럼이 자주 고정되어 있을 때 유리합니다.

인덱스 힌트 대신 플랜 검증: 강제로 “인덱스 타게” 만들지 말 것

PostgreSQL은 전통적인 의미의 인덱스 힌트가 없습니다(확장으로 우회는 가능). 이는 오히려 장점일 때가 많습니다. 특정 시점에 인덱스가 이득이더라도, 데이터가 변하면 독이 될 수 있기 때문입니다.

대신:

• 쿼리 형태를 인덱스 친화적으로 바꾸고
• 통계를 최신화하고
• 인덱스를 워크로드에 맞게 설계하고
• 비용 파라미터를 현실화

하는 쪽이 장기적으로 안정적입니다.

마무리

인덱스가 안 타는 문제는 “인덱스가 없어서”보다 “인덱스를 못 쓰는 쿼리 형태”, “데이터 분포/통계 문제”, “환경과 비용 모델 불일치”에서 더 자주 발생합니다.

가장 먼저 EXPLAIN (ANALYZE, BUFFERS)로 실제 실행과 추정을 비교하고, 이 글의 9가지 원인을 체크리스트처럼 하나씩 제거해 보세요. 대개는 1) 함수/캐스팅, 2) 선택도, 3) 복합 인덱스 순서, 4) 통계 이 네 가지에서 빠르게 결론이 납니다.