PostgreSQL이 인덱스를 사용하지 않는 이유 7가지: 직접 재현할 수 있는 EXPLAIN 출력과 함께

CREATE INDEX를 실행하고 쿼리를 돌렸는데도 EXPLAIN에는 여전히 Seq Scan이 나옵니다. 플래너를 탓하기 전에 알아 둘 점이 있습니다. 대부분의 경우 플래너는 여러분이 작성한 쿼리에 대해서는 옳고, 여러분이 의도한 바에 대해서만 틀립니다. 이 글은 PostgreSQL이 인덱스를 사용하지 않는 가장 흔한 일곱 가지 원인을 다룹니다. 컬럼에 적용한 함수, 타입 불일치, 앞쪽 와일드카드, 테이블 대부분과 일치하는 필터, 오래된 통계, 순서가 잘못된 멀티컬럼 인덱스, 그리고 단순히 너무 작은 테이블입니다. 각 사례에는 PostgreSQL 17.11에서의 재현 방법, 실제로 나온 정확한 실행 계획, 해결 방법이 함께 있습니다. 설정 코드를 psql에 붙여 넣으면 기본 설정에서 같은 실행 계획을 얻을 수 있습니다.

쿼리 앞에 EXPLAIN을 붙입니다. Index Scan, Index Only Scan, Bitmap Index Scan이라는 계획 노드가 보이면 인덱스가 사용되고 있다는 뜻입니다. Seq Scan(또는 Parallel Seq Scan)은 플래너가 테이블 전체를 읽는다는 뜻입니다. EXPLAIN은 쿼리의 계획만 세우고, EXPLAIN ANALYZE는 쿼리를 실제로 실행하여 실제 행 수까지 보여 줍니다. 아래의 오래된 통계 사례에서는 이 실제 행 수가 필요합니다.

실행 계획을 읽고 비교하기 쉽게 만드는 습관이 두 가지 있습니다.

• EXPLAIN (COSTS OFF)는 테이블 크기와 설정에 따라 달라지는 비용 수치를 제거합니다.
• EXPLAIN (ANALYZE, BUFFERS)는 플래너가 예상한 페이지 수가 아니라 쿼리가 실제로 접근한 페이지 수를 보여 줍니다.

일곱 가지 사례 모두 사용자 200,000명이 들어 있는 테이블 하나를 사용합니다. 데이터는 생성해서 만들기 때문에 내려받을 것이 없습니다.

SET TimeZone = 'UTC';
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE users (
  id         bigint PRIMARY KEY,
  email      text NOT NULL,
  status     text NOT NULL,
  org_id     int  NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO users
SELECT g,
       'User' || g || '@Example.com',
       CASE WHEN g % 100 = 0 THEN 'banned' ELSE 'active' END,
       (g % 500) + 1,
       timestamptz '2026-01-01' + (g || ' seconds')::interval
FROM generate_series(1, 200000) g;

CREATE INDEX users_email_idx ON users (email);
ANALYZE users;

기준선으로, 인덱스가 걸린 컬럼에 대한 정확히 일치하는 조건은 인덱스를 사용합니다.

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email = 'User42@Example.com';

 Index Scan using users_email_idx on users
   Index Cond: (email = 'User42@Example.com'::text)

아래는 모두 이 쿼리를 조금 바꿔서 인덱스를 잃는 방법입니다.

email 인덱스는 email의 값을 저장합니다. lower(email)에 대한 조건은 다른 값에 대한 질문이므로 인덱스가 답할 수 없습니다.

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: (lower(email) = 'user42@example.com'::text)

실행 계획은 병렬이지만 여전히 순차 스캔입니다. 해결책은 표현식 인덱스, 즉 쿼리가 사용하는 것과 같은 표현식에 만든 인덱스입니다.

CREATE INDEX users_lower_email_idx ON users (lower(email));
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE lower(email) = 'user42@example.com';

 Bitmap Heap Scan on users
   Recheck Cond: (lower(email) = 'user42@example.com'::text)
   ->  Bitmap Index Scan on users_lower_email_idx
         Index Cond: (lower(email) = 'user42@example.com'::text)

쿼리의 표현식은 인덱스의 표현식과 일치해야 합니다. lower(email)과 lower(trim(email))은 서로 다른 표현식입니다. 같은 규칙이 coalesce(col, 0), price * 100 같은 산술식, 날짜 함수에도 적용됩니다. 날짜 함수에는 한 가지 함정이 더 있습니다. timestamptz 컬럼에서 date_trunc('day', created_at)과 created_at::date는 세션 시간대에 따라 결과가 달라지므로 Postgres는 이를 직접 인덱싱하지 못하게 합니다(functions in index expression must be marked IMMUTABLE). 대신 ((created_at AT TIME ZONE 'UTC')::date)처럼 시간대를 고정하는 표현식을 인덱싱하고, 쿼리에서도 같은 표현식을 사용하세요.

가능하다면 인덱스를 만드는 대신 조건을 고쳐 쓰세요. created_at >= '2026-01-02' AND created_at < '2026-01-03'은 created_at의 일반 인덱스를 사용할 수 있지만, created_at::date = '2026-01-02'는 사용할 수 없습니다.

비교하는 양쪽의 타입이 다르면 Postgres는 한쪽을 변환해야 합니다. 컬럼을 변환하면 그 컬럼의 일반 인덱스는 더 이상 적용되지 않습니다. 여기서 id는 bigint이고 리터럴 42.0은 numeric입니다.

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id = 42;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id = 42.0;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE id::text = '42';

 Index Scan using users_pkey on users
   Index Cond: (id = 42)

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((id)::numeric = 42.0)

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((id)::text = '42'::text)

Filter 줄을 보세요. (id)::numeric은 컬럼에 캐스트가 적용되었다는 뜻입니다. 이 캐스트가 바로 단서이며, 인덱스가 있는 컬럼인데 실행 계획에 Seq Scan이 나올 때마다 찾아봐야 할 대상입니다.

해결 방법은 리터럴이나 파라미터를 컬럼 타입에 맞추는 것입니다. 애플리케이션 코드에서 흔한 원인은 숫자를 numeric으로 보내는 드라이버이거나 문자열로 들어오는 값입니다. 쿼리에서 파라미터를 캐스트하거나(WHERE id = $1::bigint) 원천에서 타입을 바로잡으세요. 컬럼 자체의 타입이 잘못되어 있다면, 예를 들어 식별자를 text로 저장해 놓고 숫자와 비교한다면 컬럼 타입을 바꾸는 것이 진짜 해결책입니다.

B-tree 인덱스는 정렬되어 있으므로 User4로 시작하는 모든 값은 빠르게 찾을 수 있습니다. 하지만 1234@Example.com으로 끝나는 모든 값은 찾을 수 없습니다. 값이 끝부분 기준으로 정렬되어 있지 않기 때문입니다.

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email LIKE '%1234@Example.com';

 Seq Scan on users
   Filter: (email ~~ '%1234@Example.com'::text)

pg_trgm 확장은 세 글자 조각을 인덱싱하므로, Postgres가 앞쪽 와일드카드가 있는 패턴, ILIKE, % 유사도 검색에도 인덱스를 사용할 수 있게 해 줍니다. pg_trgm은 Relyt One에서 사용할 수 있는 확장 중 하나입니다. 이 인덱스는 gin_trgm_ops 연산자 클래스를 쓰는 GIN 인덱스입니다.

CREATE INDEX users_email_trgm_idx ON users USING gin (email gin_trgm_ops);
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email LIKE '%1234@Example.com';

 Bitmap Heap Scan on users
   Recheck Cond: (email ~~ '%1234@Example.com'::text)
   ->  Bitmap Index Scan on users_email_trgm_idx
         Index Cond: (email ~~ '%1234@Example.com'::text)

트라이그램 인덱스는 B-tree 인덱스보다 크고 갱신이 느리며, 검색 문자열이 완전한 트라이그램을 하나 이상 포함할 만큼 길 때 가장 효과적입니다. 세 글자보다 짧은 패턴은 활용할 거리가 거의 없습니다. 실제로 필요한 것이 텍스트 안의 단어 검색이라면 LIKE 대신 전문 검색(tsvector)을 사용하세요.

인덱스는 데이터베이스가 테이블의 대부분을 건너뛸 수 있게 해 줄 때 효과가 있습니다. 조건이 행의 99%와 일치한다면 인덱스를 198,000번 오가는 것보다 테이블 전체를 순서대로 읽는 편이 더 저렴하며, 플래너도 이를 알고 있습니다.

CREATE INDEX users_status_idx ON users (status);
ANALYZE users;

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE status = 'active';
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE status = 'banned';

 Seq Scan on users
   Filter: (status = 'active'::text)

 Index Scan using users_status_idx on users
   Index Cond: (status = 'banned'::text)

이는 인덱스가 설계대로 동작하는 것입니다. 같은 인덱스를 1%의 행에는 사용하고 99%의 행에는 건너뜁니다. 플래너 쪽에서 고칠 것은 없습니다. 흔한 값에 대한 쿼리가 중요하다면, 애초에 그렇게 많은 행을 반환해야 하는지가 질문입니다(LIMIT, 추가 필터 또는 집계를 더하세요).

개선할 수 있는 쪽은 드문 값입니다. 부분 인덱스는 조회하는 행만 포함하므로 작고 유지 비용이 낮습니다.

DROP INDEX users_status_idx;
CREATE INDEX users_banned_idx ON users (id) WHERE status = 'banned';
EXPLAIN (COSTS OFF) SELECT id FROM users WHERE status = 'banned';

 Index Only Scan using users_banned_idx on users

쿼리의 WHERE 절이 인덱스의 WHERE 절을 함의해야 합니다. status = 'banned'를 요청하는 쿼리는 이 인덱스를 사용할 수 있습니다. 다른 status를 요청하는 쿼리는 사용할 수 없습니다.

플래너는 쿼리를 계획할 때 데이터를 직접 들여다보지 않습니다. ANALYZE가 수집한 통계를 보고 거기서 추정합니다. 마지막 ANALYZE 이후 테이블이 크게 바뀌면 추정치가 현실과 맞지 않게 되고, 계획은 어느 방향으로든 틀릴 수 있습니다. 인덱스를 쓰면 안 되는 상황에서 인덱스를 고르는 경우도 포함됩니다.

autovacuum은 보통 테이블의 약 10%가 변경되면 통계를 갱신합니다(autovacuum_analyze_scale_factor = 0.1에 기본 50행이 더해집니다). 효과를 결정적으로 보여 주기 위해 한 테이블에서 autovacuum을 끄고, 행이 다섯 개일 때 분석한 다음 300,000행을 일괄 적재합니다.

CREATE TABLE events (id bigint, kind text, created_at timestamptz);
ALTER TABLE events SET (autovacuum_enabled = false);
CREATE INDEX events_kind_idx ON events (kind);

INSERT INTO events SELECT g, 'click', now() FROM generate_series(1, 5) g;
ANALYZE events;
INSERT INTO events SELECT g, 'view', now() FROM generate_series(1, 300000) g;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE kind = 'view';

 Aggregate  (cost=4.31..4.32 rows=1 width=8) (actual rows=1 loops=1)
   ->  Index Only Scan using events_kind_idx on events  (cost=0.29..4.30 rows=1 width=0) (actual rows=300000 loops=1)
         Index Cond: (kind = 'view'::text)
         Heap Fetches: 300000

플래너는 한 행(rows=1)을 예상했지만 실제로는 300,000행(actual rows=300000)이 나왔습니다. 테이블 전체를 읽는 조회에 인덱스를 선택한 것이며, Heap Fetches: 300000은 모든 행에서 테이블 방문까지 필요했다는 뜻입니다. ANALYZE 이후에는 같은 쿼리의 계획이 바뀝니다.

ANALYZE events;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events WHERE kind = 'view';

 Finalize Aggregate  (cost=5558.22..5558.23 rows=1 width=8) (actual rows=1 loops=1)
   ->  Gather  (cost=5558.11..5558.22 rows=1 width=8) (actual rows=2 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=4558.10..4558.11 rows=1 width=8) (actual rows=1 loops=2)
               ->  Parallel Seq Scan on events  (cost=0.00..4116.92 rows=176474 width=0) (actual rows=150000 loops=2)
                     Filter: (kind = 'view'::text)
                     Rows Removed by Filter: 2

정확한 비용 수치는 실행할 때마다 조금씩 달라지므로 숫자를 한 자리씩 비교하지 마세요. 교훈은 실행 계획을 읽는 방법에 있습니다. 모든 노드에서 rows=와 actual rows=를 비교하세요. 둘이 자릿수 단위로 차이 난다면 문제는 인덱스가 아니라 추정치입니다. 일괄 적재나 대규모 백필 뒤에는 ANALYZE를 실행하고, autovacuum이 밀리고 있다고 의심되면 pg_stat_user_tables의 last_autoanalyze를 확인하세요. 값의 분포가 치우친 컬럼이라면 통계 목표(ALTER TABLE … ALTER COLUMN … SET STATISTICS 500)를 높여 기본값 100보다 더 세밀한 정보를 플래너에 줄 수 있습니다.

멀티컬럼 B-tree 인덱스는 첫 번째 컬럼으로 정렬되고, 첫 번째 컬럼의 각 값 안에서 두 번째 컬럼으로 정렬되며, 이후도 마찬가지입니다. 선행 컬럼으로 필터링하는 쿼리는 이 인덱스를 잘 사용할 수 있습니다. 선행 컬럼을 건너뛰는 쿼리는 사용할 수 없습니다.

CREATE INDEX users_org_created_idx ON users (org_id, created_at);
ANALYZE users;

-- 선행 컬럼에 조건이 있으면 인덱스를 사용합니다
EXPLAIN (COSTS OFF)
SELECT * FROM users WHERE org_id = 7 AND created_at >= '2026-01-02';

-- 선행 컬럼이 없으면 인덱스를 사용하지 않습니다
EXPLAIN (COSTS OFF)
SELECT * FROM users
WHERE created_at >= '2026-01-02 00:00:00' AND created_at < '2026-01-02 00:05:00';

 Bitmap Heap Scan on users
   Recheck Cond: ((org_id = 7) AND (created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone))
   ->  Bitmap Index Scan on users_org_created_idx

 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on users
         Filter: ((created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone) AND (created_at < ...

created_at만으로 필터링해야 한다면 created_at을 앞에 둔 인덱스가 필요합니다. 두 종류의 쿼리를 모두 처리하는 인덱스에서는 동등 조건이 있는 컬럼을 앞에, 범위 컬럼을 뒤에 두는 것이 일반적인 규칙입니다.

CREATE INDEX users_created_org_idx ON users (created_at, org_id);
EXPLAIN (COSTS OFF)
SELECT * FROM users
WHERE created_at >= '2026-01-02 00:00:00' AND created_at < '2026-01-02 00:05:00';

 Index Scan using users_created_org_idx on users
   Index Cond: ((created_at >= '2026-01-02 00:00:00+00'::timestamp with time zone) AND (created_at < ...

이 사례는 버전에 따라 달라집니다. PostgreSQL 18에서는 B-tree 인덱스에 스킵 스캔이 추가되어, 선행 컬럼을 생략한 쿼리도 경우에 따라 인덱스를 사용할 수 있습니다. 같은 테이블과 같은 (org_id, created_at) 인덱스로 PostgreSQL 18.6에서 실행해 보니, created_at만 사용한 범위 쿼리가 (org_id, created_at) 인덱스를 사용했습니다. Relyt One이 실행하는 PostgreSQL 17에서는 그렇지 않습니다. 스킵 스캔은 플래너가 선행 컬럼의 서로 다른 값을 거의 건너뛰지 않아도 된다고 예상하거나 뒤쪽 조건의 선택도가 높을 때, 여기서처럼, 에만 이득이므로 쿼리에 맞게 설계한 인덱스가 여전히 더 확실한 선택입니다.

인덱스는 모두 쓰기마다 비용을 발생시킵니다. 인덱스를 추가하기 전에 기존 인덱스가 이미 그 쿼리를 처리하지 못하는지 확인하세요.

행이 스무 개인 테이블은 한 페이지에 들어갑니다. 그 한 페이지를 읽는 편이 인덱스 페이지를 읽고 다시 테이블 페이지를 읽는 것보다 저렴하므로 플래너는 테이블을 스캔합니다.

CREATE TABLE plans (id int PRIMARY KEY, name text);
INSERT INTO plans SELECT g, 'plan' || g FROM generate_series(1, 20) g;
ANALYZE plans;

EXPLAIN (COSTS OFF) SELECT * FROM plans WHERE id = 7;

 Seq Scan on plans
   Filter: (id = 7)

이는 올바른 동작이며, 작은 개발 데이터베이스에서 테스트하면서 운영 환경에서 얻을 계획을 기대하거나 그 반대로 기대할 때는 함정이 됩니다. 인덱스 관련 결정은 크기와 분포가 운영 환경과 비슷한 데이터에서 테스트하세요.

인덱스를 사용할 수 없는 것인지 단지 선택되지 않은 것인지 구분이 안 될 때 쓰는 진단용 스위치도 있습니다. 한 트랜잭션에서만 순차 스캔을 끄고 실행 계획을 확인합니다.

BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT * FROM plans WHERE id = 7;
ROLLBACK;

 Index Scan using plans_pkey on plans
   Index Cond: (id = 7)

인덱스가 나타나면 사용할 수 있었는데 플래너가 테이블 스캔이 더 저렴하다고 판단한 것이므로 통계, 선택도, 테이블 크기를 살펴보세요. 여전히 나타나지 않는다면 작성한 그대로의 쿼리에는 그 인덱스를 쓸 수 없는 것이므로 사례 1, 2, 3, 6으로 돌아가세요. 이 스위치는 진단용으로만 사용하세요. 켜 둔 채로 두지 말고, 애플리케이션 코드에도 넣지 마세요.

반대의 문제는 아무도 사용하지 않는 인덱스입니다. 이런 인덱스는 모든 insert와 update를 느리게 만들고 공간을 차지합니다. pg_stat_user_indexes는 통계가 마지막으로 초기화된 이후의 인덱스 스캔 횟수를 집계합니다.

SELECT count(*) FROM users WHERE email = 'User42@Example.com';
SELECT count(*) FROM users WHERE email = 'User43@Example.com';

SELECT pg_sleep(1.5);  -- 통계는 약간의 지연 후에 보고됩니다

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'users'
ORDER BY idx_scan DESC, indexrelname;

테스트 테이블에서 email에 대한 정확히 일치하는 쿼리를 두 번 실행하고 통계가 보고될 때까지 잠시 기다린 뒤의 출력은 다음과 같았습니다.

     indexrelname      | idx_scan
-----------------------+----------
 users_email_idx       |        2
 users_banned_idx      |        0
 users_created_org_idx |        0
 users_email_trgm_idx  |        0
 users_lower_email_idx |        0
 users_org_created_idx |        0
 users_pkey            |        0

주의할 점이 두 가지 있습니다. EXPLAIN만으로는 스캔으로 집계되지 않으므로 실제로 실행한 것만 나타납니다. 그리고 스캔 횟수가 0인 인덱스는 삭제 후보일 뿐 삭제해도 된다는 증거는 아닙니다. 유니크 제약을 강제하기 위해 존재할 수도 있고, 통계가 초기화된 뒤 아직 실행되지 않은 월간 리포트에 쓰일 수도 있습니다.

1. EXPLAIN (ANALYZE, BUFFERS)를 실행하고 시간을 가장 많이 차지하는 노드를 찾습니다.
2. 그 노드의 Filter 줄에서 컬럼에 적용된 함수나 캐스트를 찾습니다(사례 1과 2).
3. 패턴이 %로 시작하면 트라이그램 인덱스를 고려합니다(사례 3).
4. rows=와 actual rows=를 비교합니다. 차이가 크면 ANALYZE가 필요합니다(사례 5).
5. 조건이 몇 개의 행과 일치하는지 확인합니다. 테이블 대부분이라면 스캔이 올바른 선택입니다(사례 4).
6. 멀티컬럼 인덱스라면 쿼리가 선행 컬럼에 조건을 거는지 확인합니다(사례 6).
7. 현실적인 데이터에서 테스트하고, SET LOCAL enable_seqscan = off로 "사용 불가"와 "선택되지 않음"을 구분합니다(사례 7).

큰 테이블에서는 인덱스를 만드는 일이 가장 비용이 큰 부분입니다. 일반 CREATE INDEX는 실행되는 동안 쓰기를 막고, CREATE INDEX CONCURRENTLY는 이를 피하는 대신 빌드가 더 느립니다. Relyt One에서는 모든 브랜치가 자체 컴퓨트와 copy-on-write 스토리지를 가지므로, 데이터베이스의 브랜치를 만들고 거기서 인덱스를 빌드한 뒤 실제 데이터에 EXPLAIN (ANALYZE)를 실행해 보고, 계획이 개선되지 않으면 브랜치를 버리면 됩니다. 메인 브랜치는 인덱스를 빌드하지 않고 실험도 보지 못합니다. 브랜치와 운영 환경 사이에서는 소요 시간이 아니라 실행 계획과 행 수 추정치를 비교하세요. 브랜치의 컴퓨트는 크기가 다를 수 있고 캐시가 비어 있는 상태로 시작합니다.

무료로 시작하기