PostgreSQL 인덱스 타는지 확인 — EXPLAIN으로 보고 안 탈 때 고치기
PostgreSQL에서 인덱스를 타는지 확인하려면 쿼리 앞에 EXPLAIN ANALYZE를 붙여 실행계획 첫 줄을 보면 됩니다. Index Scan / Index Only Scan / Bitmap Index Scan이면 인덱스를 탄 것이고, Seq Scan이면 테이블 전체를 읽은 것입니다. 인덱스를 만들어 뒀는데 Seq Scan이 나온다면 원인은 대개 넷입니다 — 컬럼에 함수를 씌웠거나, LIKE '앞%'인데 로케일이 C가 아니거나, 복합 인덱스의 선행 컬럼을 안 썼거나, 조건에 걸리는 행이 너무 많아 플래너가 일부러 안 탄 경우입니다.
아래 실행계획은 전부 PostgreSQL 18.3에서 20만 행짜리 테이블로 직접 돌려 얻은 출력입니다.
1. 인덱스 타는지 확인하는 법 — EXPLAIN ANALYZE
먼저 실습용 테이블입니다. 20만 행을 넣고 email에 인덱스를 만든 뒤 ANALYZE로 통계를 갱신합니다. (통계가 없으면 플래너가 엉뚱한 선택을 합니다)
CREATE TABLE members(
id bigserial primary key,
email text not null,
name text not null,
created_at timestamp not null
);
INSERT INTO members(email, name, created_at)
SELECT 'user'||g||'@example.com', 'name'||g,
timestamp '2024-01-01' + (g||' minutes')::interval
FROM generate_series(1, 200000) g;
CREATE INDEX idx_members_email ON members(email);
ANALYZE members;이제 확인합니다.
EXPLAIN ANALYZE
SELECT * FROM members WHERE email = 'user1234@example.com';Index Scan using idx_members_email on members
(cost=0.42..8.44 rows=1 width=48) (actual time=0.134..0.136 rows=1.00 loops=1)
Index Cond: (email = 'user1234@example.com'::text)
Execution Time: 20.869 ms첫 줄이 Index Scan using idx_members_email, 그 아래 Index Cond:가 있으면 인덱스를 탄 것입니다. 반대로 Seq Scan on members + Filter: 조합이면 인덱스를 안 탄 것입니다.
| 실행계획 노드 | 뜻 |
|---|---|
Index Scan |
인덱스로 위치를 찾아 테이블 행을 읽음 (소량 조회에 유리) |
Index Only Scan |
필요한 컬럼이 전부 인덱스에 있어 테이블을 안 읽음 |
Bitmap Index Scan |
인덱스로 블록 목록을 모은 뒤 한 번에 읽음 (중간 규모 조회) |
Seq Scan |
테이블 전체 순차 읽기 — 인덱스를 안 탄 것 |
ANALYZE 옵션은 쿼리를 실제로 실행합니다. 공식 문서도 "EXPLAIN ANALYZE는 쿼리를 실제로 실행하므로 결과가 버려지더라도 부수효과는 그대로 일어난다"고 못 박습니다. UPDATE·DELETE에 붙일 때는 트랜잭션으로 감싸고 ROLLBACK 하세요. 계획만 보고 싶으면 ANALYZE 없이 EXPLAIN만 씁니다.
2. 인덱스가 있는지, 실제로 쓰이는지 조회하기
실행계획을 보기 전에 인덱스 자체가 있는지부터 확인합니다.
-- 테이블에 걸린 인덱스 목록
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';
-- 그 인덱스가 지금까지 몇 번 쓰였나 (0이면 죽은 인덱스 후보)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE relname = 'orders'; relname | indexrelname | idx_scan
---------+------------------------+----------
orders | orders_pkey | 0
orders | idx_orders_shop_status | 1idx_scan은 그 인덱스가 스캔에 쓰인 누적 횟수입니다. 운영 중인데 계속 0이면 아무도 안 쓰는 인덱스이니 삭제 후보입니다(쓰기 성능만 갉아먹습니다). 참고로 pg_stat_user_indexes는 통계 수집이 비동기라 방금 실행한 쿼리가 몇 초 늦게 반영될 수 있습니다.
3. 인덱스가 있는데 안 탈 때 — 원인 4가지
원인 ① 컬럼에 함수를 씌웠다
가장 흔합니다. WHERE lower(email) = ...처럼 인덱스 컬럼을 함수로 감싸면 일반 인덱스는 못 씁니다. 인덱스에는 email 원본 값이 저장돼 있지 lower(email) 값이 없기 때문입니다.
EXPLAIN ANALYZE
SELECT * FROM members WHERE lower(email) = 'user1234@example.com';
Seq Scan on members (cost=0.00..4966.00 rows=1000 width=48)
(actual time=23.339..74.488 rows=1.00 loops=1)
Filter: (lower(email) = 'user1234@example.com'::text)
Rows Removed by Filter: 199999
Execution Time: 74.508 ms해결은 표현식 인덱스입니다. 조건에 쓴 표현식 그대로 인덱스를 만듭니다.
CREATE INDEX idx_members_email_lower ON members(lower(email));
ANALYZE members;
-- 같은 쿼리 재실행
Index Scan using idx_members_email_lower on members
(cost=0.42..8.44 rows=1 width=48) (actual time=0.023..0.023 rows=1.00 loops=1)
Index Cond: (lower(email) = 'user1234@example.com'::text)
Execution Time: 0.030 ms74.5ms → 0.03ms입니다. 실행계획 노드가 Seq Scan에서 Index Scan으로 바뀐 것을 그대로 확인할 수 있습니다.
원인 ② LIKE '앞%' 인데 로케일이 C가 아니다
이건 모르면 한참 헤맵니다. LIKE '%뒤'처럼 앞에 와일드카드가 오면 인덱스를 못 타는 건 널리 알려져 있는데, LIKE '앞%'도 DB 로케일이 C가 아니면 기본 인덱스로는 못 탑니다. 테스트 DB의 datcollate는 en_US.UTF-8이었고, 결과는 이렇습니다.
EXPLAIN ANALYZE
SELECT * FROM members WHERE email LIKE 'user1234@%';
Seq Scan on members (cost=0.00..4466.00 rows=20 width=48)
(actual time=0.511..40.087 rows=1.00 loops=1)
Filter: (email ~~ 'user1234@%'::text)
Rows Removed by Filter: 199999
Execution Time: 40.535 ms공식 문서(Operator Classes and Operator Families)는 이렇게 설명합니다. "text_pattern_ops, varchar_pattern_ops, bpchar_pattern_ops 연산자 클래스는 기본 연산자 클래스와 달리 로케일 규칙이 아니라 문자 단위로 엄격하게 비교한다. 그래서 데이터베이스가 표준 C 로케일을 쓰지 않을 때 LIKE나 POSIX 정규식 같은 패턴 매칭 쿼리에 적합하다."
CREATE INDEX idx_members_email_pat ON members(email text_pattern_ops);
ANALYZE members;
-- 같은 LIKE 쿼리 재실행
Index Scan using idx_members_email_pat on members
(cost=0.42..8.44 rows=20 width=48) (actual time=0.019..0.019 rows=1.00 loops=1)
Index Cond: ((email ~>=~ 'user1234@'::text) AND (email ~<~ 'user1234A'::text))
Filter: (email ~~ 'user1234@%'::text)
Execution Time: 0.028 msIndex Cond가 ~>=~ 'user1234@' ~ ~<~ 'user1234A' 범위로 바뀐 게 보입니다. 접두어 LIKE를 범위 검색으로 바꿔 인덱스를 탄 것입니다. 반대로 LIKE '%1234@example.com'처럼 앞이 와일드카드면 이 인덱스로도 못 탑니다 — 그때는 pg_trgm 확장의 GIN 인덱스나 전문 검색을 써야 합니다.
SELECT datname, datcollate FROM pg_database WHERE datname = current_database(); 결과가 C가 아니면 접두어 LIKE용으로 text_pattern_ops 인덱스가 따로 필요합니다. C 로케일이면 기본 인덱스로 충분합니다.
원인 ③ 복합 인덱스의 선행 컬럼을 조건에 안 썼다
(shop_id, status) 순서로 만든 인덱스는 앞 컬럼인 shop_id가 조건에 있어야 제값을 합니다.
CREATE INDEX idx_orders_shop_status ON orders(shop_id, status);
-- (O) 선행 컬럼 포함
EXPLAIN ANALYZE SELECT * FROM orders WHERE shop_id = 7 AND status = 'PAID';
Bitmap Heap Scan on orders (cost=5.77..392.88 rows=132 width=20)
(actual time=0.046..0.184 rows=133.00 loops=1)
Recheck Cond: ((shop_id = 7) AND (status = 'PAID'::text))
-> Bitmap Index Scan on idx_orders_shop_status
Index Cond: ((shop_id = 7) AND (status = 'PAID'::text))
Execution Time: 1.148 ms
-- (X) 후행 컬럼만 사용
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'PAID';
Seq Scan on orders (cost=0.00..3774.00 rows=66413 width=20)
(actual time=0.014..12.184 rows=66667.00 loops=1)
Filter: (status = 'PAID'::text)
Execution Time: 13.636 ms참고로 첫 번째는 Index Scan이 아니라 Bitmap Index Scan + Bitmap Heap Scan입니다. 이것도 인덱스를 탄 것입니다. 결과 행이 133건으로 좀 많아, 인덱스로 블록 목록을 모은 뒤 한 번에 읽는 쪽을 고른 겁니다.
원인 ④ 조건에 걸리는 행이 너무 많다 (일부러 안 탄다)
인덱스가 멀쩡히 있어도, 테이블의 대부분을 읽어야 하는 조건이면 플래너는 일부러 Seq Scan을 고릅니다. 인덱스를 거쳐 테이블로 가는 게 더 비싸기 때문입니다.
CREATE INDEX idx_members_created ON members(created_at);
-- 20만 행 전부 걸리는 조건 → Seq Scan
EXPLAIN ANALYZE SELECT * FROM members WHERE created_at > timestamp '2024-01-01';
Seq Scan on members (cost=0.00..4466.00 rows=200000) Execution Time: 20.675 ms
-- 31행만 걸리는 조건 → Index Scan
EXPLAIN ANALYZE SELECT * FROM members
WHERE created_at BETWEEN timestamp '2024-01-01 10:00' AND timestamp '2024-01-01 10:30';
Index Scan using idx_members_created (rows=31) Execution Time: 0.019 ms같은 컬럼, 같은 인덱스인데 걸리는 행 수에 따라 계획이 갈립니다. 이건 버그가 아니라 정상 동작입니다. 공식 문서도 "한 디스크 페이지밖에 안 되는 테이블에서는 인덱스가 있든 없든 거의 항상 순차 스캔 계획이 나온다"고 설명합니다.
4. 정말 인덱스가 나은지 비교하는 법 — enable_seqscan
"플래너가 잘못 고른 것 아닌가?" 싶을 때는 enable_seqscan을 잠깐 끄고 인덱스 쪽 비용·시간을 실측해 비교합니다. 세션 한정 설정이라 운영에 영향이 없습니다.
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM members WHERE created_at > timestamp '2024-01-01';
Index Scan using idx_members_created on members
(cost=0.42..7673.42 rows=200000 width=48)
(actual time=0.005..15.254 rows=200000.00 loops=1)
Buffers: local hit=6 read=2509
Execution Time: 19.381 ms
RESET enable_seqscan;비용은 7673 vs 4466으로 인덱스 쪽이 훨씬 비싸고, 읽은 블록도 2509 vs 1966으로 더 많습니다(위 원인 ④의 Seq Scan 계획과 비교). 플래너 판단이 맞았다는 뜻입니다. 이 설정은 진단용이며, 애플리케이션에서 켜 두는 용도가 아닙니다.
실행계획을 읽는 흐름이 익숙해지면 조회 성능 튜닝 전반이 쉬워집니다. 그룹별 최신 1건 뽑기 같은 무거운 조회는 PostgreSQL ROW_NUMBER PARTITION BY로 그룹별 순위 조회하기 글과 함께 보면 좋고, 데이터를 옮긴 뒤 중복키가 터진다면 PostgreSQL 시퀀스 값 변경(setval) 쪽을 먼저 확인하세요.
자주 묻는 질문 (FAQ)
Q. EXPLAIN과 EXPLAIN ANALYZE 중 뭘 써야 하나요?
계획만 보려면 EXPLAIN입니다. 추정 행 수(rows=)와 실제 행 수(actual ... rows=)가 얼마나 벌어지는지 보려면 EXPLAIN ANALYZE를 씁니다. 다만 쿼리가 실제로 실행되니 쓰기 쿼리에는 조심하세요.
Q. 인덱스를 만들었는데도 계획이 그대로예요.
ANALYZE 테이블명;으로 통계를 갱신했는지 확인하세요. 방금 대량 INSERT를 했다면 통계가 옛날 것이라 플래너가 엉뚱하게 판단합니다. 그리고 같은 세션이라도 준비된 구문(prepared statement)은 계획이 캐시될 수 있으니 새 세션에서 다시 보세요.
Q. Bitmap Index Scan은 인덱스를 탄 건가요?
탄 겁니다. 결과 행이 적당히 많을 때 인덱스로 블록 목록을 모아 한 번에 읽는 방식입니다. 인덱스를 아예 안 탄 건 Seq Scan + Filter만 나올 때입니다.
Q. 한글 컬럼에도 text_pattern_ops가 필요한가요?
로케일이 C가 아니라면 동일하게 필요합니다. 판단 기준은 데이터 내용이 아니라 datcollate 값입니다.
마무리
정리하면 이렇습니다. 확인은 EXPLAIN ANALYZE 첫 줄(Index/Bitmap = 탄 것, Seq Scan = 안 탄 것). 안 탈 때는 ① 컬럼에 함수를 씌웠는지 → 표현식 인덱스, ② 접두어 LIKE인데 로케일이 C가 아닌지 → text_pattern_ops, ③ 복합 인덱스 선행 컬럼을 썼는지, ④ 걸리는 행이 너무 많은 건 아닌지(그건 정상)를 차례로 봅니다. 마지막 확신이 필요하면 SET enable_seqscan = off로 실측해 비교하세요.
버전마다 실행계획 표기가 조금씩 달라지니, 쓰는 버전의 공식 문서를 함께 확인하는 걸 권합니다.
📚 참고 출처 (2026년 7월 21일 확인 · 실행계획은 PostgreSQL 18.3에서 직접 실행)
· PostgreSQL 18 Documentation — Using EXPLAIN
· PostgreSQL 18 Documentation — Operator Classes and Operator Families
· PostgreSQL 18 Documentation — EXPLAIN
COMMENTS