MOMSOFT / 기술 블로그

Database

PostgreSQL 인덱스가 안 타는 흔한 이유

플랜을 먼저 읽고, 못 쓰는 것인지 안 쓰는 것인지 가른 뒤에 손을 대야 합니다. 진단 없이 추가한 인덱스는 쓰기 비용만 남깁니다.

작성 · 감수 윤수현 · 맘소프트 대표·발행 2026. 08. 09.·최종 확인 2026. 08. 19.·발행처 소개

결론부터 적겠습니다. 운영 중인 서비스에서 "인덱스를 만들었는데 안 탄다"는 상황의 대부분은 PostgreSQL 플래너의 결함이 아닙니다. 원인은 셋 중 하나입니다. 인덱스를 쓸 수 없게 쿼리가 쓰여 있거나, 인덱스의 컬럼 순서가 조건과 맞지 않거나, 플래너가 계산해 보니 안 쓰는 편이 실제로 더 싸다고 판단한 것입니다. 마지막 경우는 대개 플래너가 옳습니다. 그리고 옳지 않다면 그건 통계나 비용 파라미터가 현실과 어긋났다는 뜻이지, 플래너가 고집을 부린다는 뜻이 아닙니다.

그래서 이 글의 순서는 원인별 처방 나열이 아니라 판단 순서입니다. 먼저 EXPLAIN을 읽어 "안 쓰는 것"인지 "못 쓰는 것"인지를 가르고, 못 쓰는 것이면 표현식과 인덱스 정의를 보고, 안 쓰는 것이면 통계와 비용 모델을 봅니다. 이 순서를 건너뛰고 인덱스부터 추가하면, 안 쓰이는 인덱스만 늘어나고 쓰기 성능은 조용히 나빠집니다.

1. EXPLAIN ANALYZE를 읽는 최소한의 방법

진단은 항상 여기서 시작합니다. EXPLAIN만 쓰면 추정값만 나오므로, 실행을 감수할 수 있는 환경이라면 ANALYZE와 BUFFERS를 함께 켭니다.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, status, created_at
FROM orders
WHERE customer_id = 1042
  AND created_at >= '2026-07-01'
ORDER BY created_at DESC
LIMIT 20;

참고로 ANALYZE 옵션은 쿼리를 실제로 실행합니다. UPDATE나 DELETE를 이렇게 확인하려면 트랜잭션을 열고 마지막에 롤백해야 합니다. 통계를 수집하는 같은 이름의 ANALYZE 명령과는 다른 것이니 헷갈리지 마십시오.

봐야 할 숫자는 네 가지입니다

추정 rows와 actual rows의 괴리. rows=120인데 actual rows=48000이라면 그 노드 위쪽의 조인 방식 선택은 전부 잘못된 전제 위에 있습니다. 인덱스 문제가 아니라 통계 문제일 가능성이 큽니다. 실무적으로 한 자릿수 배수 차이는 넘길 만하고, 열 배 이상 벌어지면 원인을 찾습니다.
loops. actual time과 actual rows는 모두 루프 1회 기준 평균입니다. Nested Loop 안쪽 노드가 loops=5000이면 표시된 값에 5000을 곱해야 실제 총량입니다. 안쪽이 인덱스 스캔이라 안심했다가, 곱해 보고 나서야 병목을 찾는 경우가 흔합니다.
Rows Removed by Filter. 이 값이 크면 인덱스로 범위를 좁히지 못하고 읽은 뒤 버린 겁니다. 인덱스를 "탔다"고 안심할 수 없는 대표적 신호입니다.
Buffers의 shared read. 캐시에 없어 스토리지에서 읽은 블록 수입니다. 같은 쿼리를 두 번째 실행하면 시간이 확 줄어드는 이유가 여기 있으니, 첫 실행 결과와 두 번째 결과를 구분해서 봐야 합니다. 튜닝 전후를 비교할 때는 실행 시간보다 shared hit + read 블록 수가 더 안정적인 지표입니다.

Index Cond와 Filter의 차이가 핵심입니다

이 글에서 하나만 기억해야 한다면 이것입니다.

표시	의미	영향
`Index Cond`	인덱스 스캔 단계에서 인덱스 엔트리에 적용되는 조건	힙 접근을 줄임. 단 탐색 범위까지 좁히는지는 별개
`Filter`	힙에서 행을 가져온 뒤 걸러내는 조건	읽는 양은 그대로, CPU와 I/O만 소모
`Recheck Cond`	Bitmap Heap Scan에서 힙 접근 후 재확인	정상. 단 `lossy` 블록이 많으면 work_mem 부족 신호
`Heap Fetches`	Index Only Scan이 가시성 확인 때문에 힙을 본 횟수	0에 가까워야 의미가 있음. 크면 VACUUM 부족

"Index Scan이 찍혔으니 인덱스를 탔다"는 판단이 자주 틀리는 이유가 여기 있습니다. 조건 다섯 개 중 하나만 Index Cond에 들어가고 나머지가 Filter로 밀려나 있으면, 사실상 그 인덱스는 절반만 일하고 있는 겁니다. 반대로 Index Cond에 들어갔다고 해서 그 조건이 전부 탐색 범위를 좁히는 것도 아닙니다. 이 구분은 3장에서 다시 다룹니다.

운영 환경에서 느린 쿼리의 실제 플랜을 모으려면 auto_explain 모듈을 최소 실행시간 임계값과 함께 걸어 두는 편이 좋습니다. 재현이 안 되는 문제는 사후에 EXPLAIN을 돌려도 그때의 플랜이 아닐 수 있습니다. 다만 플랜을 출력하는 비용이 있으므로 임계값을 충분히 높게 잡고 시작하십시오.

2. 못 쓰는 경우: 표현식이 인덱스를 무력화한다

B-tree 인덱스는 컬럼 값 자체로 정렬된 자료구조입니다. 컬럼에 무언가를 씌우는 순간 그 정렬은 쓸 수 없게 됩니다. 규칙은 단순합니다. 연산은 항상 상수 쪽에, 컬럼은 맨몸으로.

함수 적용

-- 인덱스를 못 씀
WHERE date_trunc('day', created_at) = '2026-08-09'
WHERE lower(email) = '[email protected]'
WHERE substring(phone, 1, 3) = '010'

-- 재작성
WHERE created_at >= '2026-08-09' AND created_at < '2026-08-10'
WHERE email = '[email protected]'            -- citext 또는 저장 시 정규화
WHERE phone LIKE '010%'                  -- 연산자 클래스 주의(아래 참조)

재작성이 불가능하면 표현식 인덱스를 만듭니다. 다만 조건이 있습니다. 쿼리에 쓴 표현식과 인덱스에 쓴 표현식이 대응해야 하고, 그 함수가 IMMUTABLE이어야 합니다.

CREATE INDEX idx_users_email_lower ON users (lower(email));

여기서 자주 걸리는 함정이 시간대입니다. timestamptz 값을 날짜 단위로 잘라내는 연산은 세션 시간대에 따라 결과가 달라지므로 IMMUTABLE이 아니고, 그래서 그대로는 인덱스로 만들 수 없습니다. 날짜 단위 조회는 표현식 인덱스로 우회하려 하지 말고 반열린 구간(>= ~ <) 범위 조건으로 바꾸는 것이 정석입니다. 이 형태는 BETWEEN의 상한 포함 때문에 하루의 마지막 순간을 이중으로 세거나 반대로 놓치는 문제도 같이 없애 줍니다.

대신 경계값을 만들 때 어느 시간대 기준의 하루인지는 애플리케이션이 명확히 정해야 합니다. timestamptz 컬럼에 문자열 리터럴을 비교하면 세션 시간대로 해석되므로, 서버와 배치와 개발자 로컬의 설정이 다르면 같은 쿼리가 다른 구간을 보게 됩니다. 시간대를 고정해 계산한 경계값을 파라미터로 넘기는 편이 안전합니다.

암묵적 형변환

같은 문제가 눈에 안 보이는 형태로 나타나는 경우가 형변환입니다. 플랜에 ((id)::text = '1042'::text)처럼 컬럼 쪽에 캐스팅이 붙어 있으면 그 인덱스는 못 씁니다. 반대로 (id = 1042)처럼 상수 쪽이 변환된 형태는 정상입니다. 플랜을 볼 때 캐스팅이 등호의 어느 쪽에 붙었는지를 확인하십시오.

애플리케이션에서는 ORM이 원인일 때가 많습니다. 엔티티 필드 타입과 컬럼 타입이 어긋나거나(예: 문자열 필드에 uuid 컬럼, enum 매핑 방식 불일치), 바인딩 파라미터 타입이 드라이버에서 다른 타입으로 넘어가면 캐스팅이 컬럼 쪽에 생깁니다. 의심되면 ORM이 만든 SQL을 그대로 꺼내 psql에서 EXPLAIN을 돌려 비교하는 것이 가장 빠릅니다. 애플리케이션에서만 느리고 psql에서는 빠르다면 십중팔구 타입 또는 플랜 캐시 문제입니다.

조인에서도 같은 일이 벌어집니다. 한쪽은 bigint, 다른 쪽은 varchar인 키로 조인하면 인덱스 기반 조인 경로가 사라집니다. 문자열 키라면 콜레이션도 봐야 합니다. 양쪽 컬럼의 콜레이션이 다르면 비교 규칙이 어긋나 인덱스를 쓰지 못하거나 예상과 다른 경로를 고릅니다. 테이블을 서로 다른 시기에, 서로 다른 기준으로 만들었다면 한 번쯤 확인해 볼 항목입니다.

LIKE와 콜레이션

LIKE는 별도의 규칙이 붙습니다.

'abc%' 같은 접두 패턴만 B-tree로 처리할 수 있습니다. '%abc', '%abc%'는 원리상 불가능합니다.
접두 패턴이라도, 데이터베이스가 C 콜레이션을 쓰지 않는 한 기본 연산자 클래스로 만든 인덱스는 LIKE에 쓰이지 않습니다. 이때 필요한 것이 text_pattern_ops(varchar는 varchar_pattern_ops)입니다. 한국어 환경에서 대부분 C 콜레이션이 아니므로, "접두 검색인데 왜 안 타지"의 상당수가 이 케이스입니다. 앞에서 substring을 LIKE '010%'로 바꾼 예도 이 조건을 만족해야 실제로 인덱스를 씁니다.

CREATE INDEX idx_products_name_prefix
  ON products (name text_pattern_ops);

주의할 점은 이 인덱스가 패턴 매칭 전용이라는 것입니다. 일반 부등호 비교나 ORDER BY name에는 쓰이지 않으므로, 두 용도가 모두 필요하면 인덱스를 두 개 두어야 합니다. 그만한 값어치가 있는지는 따져 보십시오.

중간 일치나 대소문자 무시 검색이 진짜 요구사항이면 pg_trgm 확장의 GIN 인덱스로 갑니다. gin_trgm_ops는 LIKE '%abc%'와 ILIKE를 모두 지원합니다. 대신 인덱스 크기가 크고 쓰기 비용이 올라가며, 검색어가 세 글자에 못 미치면 트라이그램을 만들 수 없어 인덱스가 후보를 전혀 좁히지 못합니다. 자동완성처럼 한두 글자부터 반응해야 하는 화면이라면 이 방식만으로는 안 됩니다. 문서 단위 전문 검색이 목적이라면 애초에 tsvector와 GIN 조합이 맞습니다.

OR, NOT, 그리고 NULL

OR은 각 항에 모두 인덱스가 있으면 BitmapOr로 묶여 처리될 수 있지만, 한쪽이라도 인덱스가 없으면 전체가 순차 스캔으로 떨어집니다. 항끼리 성격이 다르면 UNION ALL로 쪼개는 편이 나을 때가 많습니다(양쪽에 걸리는 행의 중복 제거가 필요하면 UNION이지만 정렬 비용이 붙습니다). <>나 NOT IN은 선택도가 본질적으로 나빠서 인덱스가 도움이 되기 어렵습니다.

반면 NULL에 대한 오해는 정리해 둘 필요가 있습니다. PostgreSQL의 B-tree는 NULL도 저장하므로 IS NULL과 IS NOT NULL 모두 인덱스를 쓸 수 있습니다. 다만 NULL이 압도적으로 많은 컬럼에서 소수의 비-NULL만 찾는다면, 전체 인덱스보다 부분 인덱스가 훨씬 작고 빠릅니다.

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'PENDING';

부분 인덱스는 "전체의 극히 일부만 조회 대상"인 상태 컬럼에서 특히 효율이 좋습니다. 조건이 쿼리의 WHERE 절에 그대로, 또는 플래너가 함의를 증명할 수 있는 형태로 들어가야 쓰인다는 점만 지키면 됩니다. 여기서 자주 놓치는 것이, 그 조건 컬럼을 바인딩 파라미터로 넘기면 함의 증명이 안 될 수 있다는 점입니다. 값이 확정되지 않은 채 플랜을 만들면 status = $1이 status = 'PENDING'을 함의한다고 판단할 근거가 없기 때문입니다. 부분 인덱스를 확실히 노린다면 그 조건만큼은 리터럴로 두는 편이 안전합니다.

3. 복합 인덱스의 컬럼 순서

단일 컬럼 인덱스를 조건 개수만큼 만들어 두고 왜 하나만 타느냐고 묻는 경우가 많습니다. PostgreSQL은 여러 인덱스를 비트맵으로 결합할 수 있지만, 그건 차선책입니다. 조건이 함께 오는 컬럼은 하나의 복합 인덱스로 묶는 것이 원칙입니다.

순서 규칙

널리 통용되는 순서는 등치 조건 → 범위 조건 → 정렬 컬럼입니다. B-tree는 선두 컬럼부터 값이 정해져야 탐색 시작점과 끝점이 좁아지고, 범위 조건이 하나 등장한 순간 그 뒤 컬럼들은 탐색 범위를 좁히는 데 기여하지 못합니다.

WHERE tenant_id = ? AND status = ? AND created_at >= ?
ORDER BY created_at DESC

-- 적합
CREATE INDEX ON orders (tenant_id, status, created_at DESC);

-- 부적합: created_at 범위 뒤의 status는 탐색 범위를 좁히지 못함
CREATE INDEX ON orders (tenant_id, created_at, status);

여기서 오해하기 쉬운 부분을 짚겠습니다. 부적합한 쪽에서도 status 조건은 플랜의 Filter가 아니라 Index Cond에 남아 있을 수 있습니다. PostgreSQL은 후행 컬럼 조건을 인덱스 엔트리 단계에서 검사해 힙 접근을 줄이기 때문입니다. 그래서 "Index Cond에 다 들어가 있으니 괜찮다"고 넘어가면 안 됩니다. 범위 시작점부터 끝점까지의 인덱스 엔트리를 전부 훑는다는 사실은 그대로이고, 이 차이는 Buffers의 인덱스 블록 수와 실제 시간에서 드러납니다.

"카디널리티가 높은 컬럼을 앞에 두라"는 조언이 돌아다니지만, 그건 조건 형태가 같을 때의 부차적 기준입니다. 쿼리에서 어떤 형태로 쓰이는지가 항상 우선합니다.

선두 컬럼이 빠지면

선두 컬럼 조건이 없어도 복합 인덱스를 쓸 수는 있습니다. 다만 그 경우 인덱스 전체를 훑게 되므로 플래너가 잘 고르지 않고, 골라도 이득이 작습니다. 실무적으로는 "선두 컬럼이 없으면 못 쓴다"고 보수적으로 가정하고 설계하는 편이 안전합니다. 선두 컬럼의 값 종류가 적을 때 이를 건너뛰며 탐색하는 최적화가 메이저 버전에 따라 들어가 있기도 하니 운영 중인 버전의 릴리스 노트를 확인해 보시되, 그런 최적화가 있다고 해서 설계의 기본 전제를 바꿀 정도는 아닙니다.

정렬과 LIMIT

ORDER BY ... LIMIT이 붙은 쿼리에서 인덱스의 진짜 가치는 정렬을 생략하는 것입니다. 인덱스 순서가 ORDER BY와 일치하면 앞에서 필요한 만큼만 읽고 멈출 수 있습니다. 방향이 섞인 정렬(a ASC, b DESC)은 인덱스 정의에서도 방향을 맞춰 주어야 하고, NULL 위치(NULLS FIRST/LAST)까지 어긋나면 정렬을 생략하지 못합니다. 플랜에 Sort 노드가 남아 있고 그 아래 노드가 대량의 행을 뱉고 있다면, 컬럼 순서와 방향과 NULL 위치 세 가지를 함께 대조해 보십시오.

LIMIT이 붙자 갑자기 느려지는 경우

정렬 인덱스가 오히려 함정이 되는 패턴도 있습니다. WHERE 조건의 선택도가 매우 낮은데 ORDER BY id DESC LIMIT 1 같은 형태가 붙으면, 플래너는 "정렬 인덱스를 역순으로 조금만 훑으면 금방 한 건 나온다"고 추정하고 그쪽을 고를 수 있습니다. 실제로 조건에 맞는 행이 아주 오래된 데이터에만 있으면 인덱스를 끝까지 훑고 나서야 한 건을 찾습니다. 추정 비용은 낮은데 실제 시간이 폭발하는 전형적인 형태이고, LIMIT을 떼면 오히려 빨라지는 이상한 증상으로 나타납니다. 처방은 조건 컬럼과 정렬 컬럼을 함께 담은 복합 인덱스를 만들어 주는 것입니다.

중복 인덱스 정리와 커버링

(a, b, c) 인덱스는 (a)와 (a, b)의 역할을 대체합니다. 이런 접두 중복 인덱스는 지우는 것이 낫습니다. 반대로 조회 컬럼 한두 개 때문에 힙 접근이 발생한다면 INCLUDE로 페이로드만 얹어 Index Only Scan을 노릴 수 있습니다.

CREATE INDEX ON orders (tenant_id, created_at DESC) INCLUDE (status, amount);

INCLUDE 컬럼은 인덱스 키가 아니므로 탐색이나 정렬에는 쓰이지 않습니다. 오직 힙을 다시 읽지 않기 위한 페이로드입니다. 그리고 Index Only Scan이 실효를 보려면 가시성 맵이 최신이어야 합니다. 플랜의 Heap Fetches가 크다면 VACUUM이 따라오지 못하는 상태이니, 인덱스를 더 만들 게 아니라 해당 테이블의 autovacuum 설정을 손봐야 합니다.

실제로 쓰이지 않는 인덱스를 찾는 것도 정리의 일부입니다. pg_stat_user_indexes의 idx_scan이 오랜 기간 0에 가깝다면 후보입니다. 다만 통계는 리셋될 수 있고, 마감 배치처럼 드물게만 쓰이는 인덱스도 있으며, 통계는 노드별로 따로 쌓이므로 읽기 전용 복제본에서만 쓰이는 인덱스는 프라이머리에서 0으로 보입니다. 관측 기간과 관측 대상 노드를 모두 확인한 뒤에 지우십시오. 제약조건을 뒷받침하는 유니크 인덱스는 idx_scan이 0이어도 지우면 안 됩니다.

4. 안 쓰는 경우: 선택도와 비용 모델

표현식도 멀쩡하고 컬럼 순서도 맞는데 순차 스캔이 나온다면, 플래너가 일부러 고른 것입니다. 이유는 단순합니다. 인덱스 경로는 인덱스를 읽고 그다음 테이블(힙)의 흩어진 블록을 랜덤하게 읽습니다. 결과 행이 전체의 상당 비율이면, 랜덤 접근을 수만 번 하는 것보다 테이블을 처음부터 순서대로 읽는 편이 실제로 빠릅니다.

플래너는 이 판단을 비용 파라미터로 합니다. 기본값은 seq_page_cost 1.0, random_page_cost 4.0입니다. 이 4배 차이는 회전 디스크 시절의 가정입니다. SSD나 NVMe에서는 실제 격차가 훨씬 작으므로, 인덱스 경로가 부당하게 비싸게 평가됩니다. 하드웨어 특성을 반영해 random_page_cost를 낮추면 인덱스 선택이 정상화되는 경우가 상당히 많습니다. 함께 볼 것이 effective_cache_size입니다. 이 값은 실제로 메모리를 예약하지 않고 플래너에게 캐시 가용량을 알려 주는 추정치인데, 실제 사용 가능한 메모리보다 훨씬 작게 잡혀 있으면 플래너는 매번 스토리지를 때린다고 가정하고 인덱스를 기피합니다.

진단 요령이 하나 있습니다. 세션에서 순차 스캔을 억제해 두 플랜의 비용과 실제 시간을 비교하는 것입니다.

SET enable_seqscan = off;   -- 진단용. 세션 한정
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET enable_seqscan;

이 설정은 순차 스캔을 금지하는 것이 아니라 아주 큰 비용 패널티를 매기는 것입니다. 그래서 대안 경로가 없으면 여전히 순차 스캔이 나옵니다. 플랜이 그대로라면 "그 조건으로 쓸 수 있는 인덱스가 아예 없다"는 결론이 나오니, 그것대로 유용한 정보입니다.

플랜이 바뀌었다면 두 갈래로 나뉩니다. 인덱스 플랜의 추정 비용은 높은데 실제 시간이 더 짧다면 비용 파라미터나 통계가 현실과 어긋난 것입니다. 반대로 실제 시간까지 더 길다면 플래너가 옳았고, 문제는 다른 데 있습니다. 이 확인 없이 곧장 파라미터를 바꾸면, 한 쿼리를 고치고 열 쿼리를 망가뜨릴 수 있습니다.

덧붙여 enable_seqscan = off는 진단 도구지 해결책이 아닙니다. 이 설정을 애플리케이션 코드나 커넥션 초기화 스크립트에 박아 두는 것은 나중에 원인 파악을 어렵게 만드는 부채입니다.

5. 통계와 플랜 캐시

통계가 낡으면 전제가 무너진다

플래너의 모든 판단은 ANALYZE가 수집한 표본 통계 위에 있습니다. autovacuum이 통계를 갱신하는 기준은 변경된 행 수가 상수 임계치에 테이블 행 수의 일정 비율을 더한 값을 넘을 때입니다. 이 비율 방식은 작은 테이블에서는 잘 동작하지만, 행 수가 커질수록 갱신이 늦게 옵니다. 수천만 행 규모라면 비율 하나 때문에 갱신 주기가 크게 벌어집니다. 대량 적재 직후, 파티션을 새로 붙인 직후, 마이그레이션 직후는 통계가 비어 있거나 낡아 있다고 가정하고 수동으로 갱신하는 것이 안전합니다.

ANALYZE orders;

-- 큰 테이블은 갱신 임계치를 개별로 낮춘다
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

-- 분포가 치우친 컬럼은 표본 정밀도를 올린다
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders (status);

표본 정밀도를 올리면 최빈값 목록과 히스토그램이 촘촘해져 추정이 정확해지지만, ANALYZE 자체와 플래닝 시간이 늘어납니다. 전역 기본값을 올리기보다 문제가 확인된 컬럼에만 적용하십시오. 마지막 마이그레이션 시각과 pg_stat_user_tables의 last_analyze, last_autoanalyze를 함께 확인하는 습관을 들이면 이 부류의 사고는 대부분 예방됩니다.

컬럼 간 상관관계

플래너는 기본적으로 컬럼들이 서로 독립이라고 가정하고 선택도를 곱합니다. 그래서 city = '시흥'과 region = '경기'처럼 사실상 종속인 조건이 함께 오면 추정 행 수를 크게 과소평가하고, 그 결과 조인 방식을 잘못 고릅니다. 이때 쓰는 것이 확장 통계입니다.

CREATE STATISTICS stat_customers_region (dependencies, ndistinct)
  ON city, region FROM customers;
ANALYZE customers;

유형별로 쓰임이 다릅니다. dependencies는 등치 조건이 여러 개 겹칠 때, ndistinct는 여러 컬럼을 함께 GROUP BY할 때의 그룹 수 추정에 씁니다. 등치 이외의 조건까지 개선하려면 최빈값 조합을 담는 유형이 필요한데 지원 여부가 메이저 버전에 따라 다르니 확인하고 쓰십시오. 그리고 확장 통계는 추정을 개선할 뿐 인덱스를 대신하지 않습니다. 추정 rows와 actual rows가 크게 벌어지는데 인덱스 정의에는 문제가 없다면, 이쪽을 의심할 차례입니다.

준비된 구문의 일반 플랜

이건 psql에서는 재현되지 않고 애플리케이션에서만 느린 대표적인 원인입니다. PostgreSQL은 같은 준비된 구문이 몇 차례 반복 실행되면, 매번 파라미터 값에 맞춰 플랜을 만드는 대신 파라미터와 무관한 일반 플랜으로 전환할 수 있습니다. 값에 따라 선택도가 극단적으로 달라지는 조건(예: 대부분의 행이 한 상태값에 몰린 status 컬럼)에서는 이 일반 플랜이 특정 값에 대해 매우 나쁠 수 있습니다. JDBC 드라이버도 같은 구문이 일정 횟수 반복되면 서버 측 준비된 구문으로 전환하므로, 배포 직후가 아니라 트래픽이 좀 쌓인 뒤부터 느려지는 특유의 증상이 나옵니다. 전환 횟수는 드라이버 설정으로 조정하거나 끌 수 있습니다.

대응은 두 가지입니다. 해당 세션에서 plan_cache_mode를 커스텀 플랜 강제로 두거나, 그 쿼리만 서버 측 준비된 구문을 쓰지 않도록 바꾸는 것입니다. 어느 쪽이든 범위를 최소한으로 좁혀서 적용해야 합니다. 커스텀 플랜은 매 실행마다 플래닝 비용을 내므로, 초당 호출 수가 많은 단순 쿼리에까지 전역으로 걸면 다른 문제가 생깁니다.

인덱스 자체의 상태

대량 갱신과 삭제가 반복된 테이블의 인덱스는 부풀어 오릅니다. 인덱스가 커지면 읽을 페이지가 늘고, 플래너의 비용 계산에서도 불리해집니다. 재구축이 필요하면 REINDEX INDEX CONCURRENTLY로 잠금 시간을 줄이되, 실패하면 무효 상태의 인덱스가 남을 수 있습니다. 작업 후 pg_index.indisvalid를 확인하고 잔여물을 정리하는 절차까지 포함해서 계획하십시오. 재구축 동안에는 원본과 새 인덱스가 동시에 존재하므로 여유 공간과 I/O도 미리 확인해야 합니다.

6. 실제 점검 순서

EXPLAIN (ANALYZE, BUFFERS)로 플랜을 뜬다. 두 번 실행해 캐시 효과를 분리한다.
조건들이 Index Cond에 있는지 Filter에 있는지 확인한다. Filter로 밀렸다면 원인은 표현식이나 인덱스 정의다.
플랜에 컬럼 쪽 캐스팅이나 함수 호출이 보이는지 본다. 보이면 쿼리를 재작성하거나 표현식 인덱스를 만든다.
LIKE 접두 검색이면 연산자 클래스를 확인한다. 중간 일치라면 B-tree로는 안 된다는 사실을 먼저 받아들인다.
복합 인덱스라면 등치 → 범위 → 정렬 순서와 실제 조건이 맞는지 대조한다. Index Cond에 다 들어 있어도 탐색 범위를 좁히는지는 따로 본다.
추정 rows와 actual rows의 괴리를 본다. 크면 ANALYZE를 돌리고 다시 뜬다. 그래도 크면 확장 통계를 검토한다.
여전히 순차 스캔이면 enable_seqscan을 세션에서 꺼서 두 플랜의 비용과 실제 시간을 모두 비교한다.
인덱스 플랜이 실제로 빠른데 안 골랐다면 random_page_cost와 effective_cache_size를 하드웨어에 맞게 검토한다.
psql에서는 빠른데 애플리케이션에서만 느리면 파라미터 타입과 준비된 구문의 플랜 캐시를 의심한다.
고친 뒤 다른 쿼리들의 플랜이 바뀌지 않았는지 확인한다. 특히 전역 파라미터를 건드렸다면 필수다.

인덱스를 새로 만들 때, 운영 중인 테이블이라면 CREATE INDEX CONCURRENTLY를 씁니다. 일반 CREATE INDEX는 그 테이블의 쓰기를 막습니다. 대신 CONCURRENTLY는 더 오래 걸리고 실패 시 무효 인덱스를 남기므로, 실패 후 정리까지 절차에 넣어 두십시오.

7. 언제는 하지 말아야 하는가

인덱스는 공짜가 아닙니다. 모든 INSERT는 인덱스도 함께 갱신해야 하고, 인덱스가 걸린 컬럼을 UPDATE하면 같은 페이지 안에서 저렴하게 끝나는 갱신 경로(HOT)를 쓸 수 없어 테이블이 더 빨리 부풀며, VACUUM이 할 일도 늘고, 백업 용량도 커집니다. 다음 경우는 인덱스를 만들지 않는 쪽이 낫습니다.

테이블이 작을 때. 몇 페이지짜리 테이블은 통째로 읽는 것이 언제나 빠릅니다. 코드 테이블, 설정 테이블에 인덱스를 붙이고 왜 안 타느냐고 묻는 것은 질문 자체가 틀렸습니다.
선택도가 나쁜 단독 컬럼. 값이 두세 종류뿐인 컬럼에 단독 인덱스는 거의 쓰이지 않습니다. 조회 대상이 소수 값에 한정된다면 인덱스가 아니라 부분 인덱스가 답입니다.
쓰기가 압도적인 테이블. 이벤트 로그나 수집 테이블에 조회 편의를 위해 인덱스를 여러 개 얹으면 적재 처리량이 눈에 띄게 떨어집니다. 조회가 목적이라면 분석용 저장소를 따로 두거나, 파티셔닝으로 스캔 범위 자체를 줄이는 편이 낫습니다. 다만 파티션 프루닝이 걸리려면 파티션 키가 조건에 들어와야 하니, 키 설계를 조회 패턴에 맞춰야 합니다.
아직 느리지 않을 때. "언젠가 필요할 것 같아서" 만든 인덱스는 대부분 쓰이지 않고 비용만 냅니다. 느린 쿼리 로그나 pg_stat_statements로 문제를 확인한 뒤에 만드십시오.
병목이 인덱스가 아닐 때. ORM의 N+1, 큰 OFFSET 페이징, 필요 없는 컬럼까지 다 끌어오는 SELECT, 커넥션 풀 고갈은 인덱스로 해결되지 않습니다. 특히 깊은 페이지의 OFFSET 페이징은 인덱스를 아무리 잘 만들어도 앞의 행을 세면서 건너뛰는 구조라 느립니다. 정렬 키를 커서로 쓰는 키셋 페이징으로 바꾸는 것이 정답입니다.
전역 파라미터를 한 쿼리 때문에 바꾸려 할 때. 비용 파라미터 변경은 인스턴스의 모든 플랜에 영향을 줍니다. 하드웨어 특성을 반영하는 조정이라면 타당하지만, 특정 쿼리 하나를 살리려는 목적이라면 범위를 세션이나 해당 쿼리로 좁히십시오.
중간 일치 검색을 B-tree로 어떻게든 해보려 할 때. 원리상 불가능한 것을 우회하려는 시도는 대개 더 나쁜 구조를 남깁니다. 트라이그램이나 전문 검색으로 도구를 바꾸거나, 검색 요구사항 자체를 접두 검색으로 협상하는 편이 낫습니다.
이미 있는 인덱스의 접두사일 때. (a, b)가 있는데 (a)를 또 만드는 것은 쓰기 비용만 늘립니다. 새 인덱스를 만들기 전에 기존 인덱스 목록부터 확인하십시오.

정리

인덱스가 안 타는 문제를 다룰 때 가장 비용이 큰 실수는 진단 없이 인덱스를 추가하는 것입니다. 그렇게 쌓인 인덱스는 문제를 해결하지 못한 채 쓰기 성능과 운영 부담만 남깁니다.

순서는 늘 같습니다. 플랜을 뜨고, Index Cond와 Filter를 가르고, 못 쓰는 것이면 표현식과 컬럼 순서를 고치고, 안 쓰는 것이면 통계와 비용 모델을 봅니다. 그리고 고친 뒤에는 반드시 실제 시간으로 검증합니다. 추정 비용이 낮아진 것과 쿼리가 빨라진 것은 다른 이야기입니다.

대규모 정보계 시스템이든 사내 업무 솔루션이든, 이 판단 순서 자체는 달라지지 않습니다. 달라지는 것은 데이터 양과 허용 가능한 응답 시간, 그리고 잘못 짚었을 때 감당해야 하는 비용의 크기뿐입니다.

같은 주제의 다른 글

맘소프트는 19년 넘게 금융 · 엔터프라이즈 · 모바일 시스템을 만들어 왔습니다. 여기 적는 글은 그 과정에서 실제로 부딪히고 해결한 것들입니다.

기술 판단이 필요하신가요

맘소프트는 소프트웨어 개발과 CTO 대행 · 기술 자문을 제공합니다. 편하게 문의해 주세요.

[email protected]