동호의 블로그

인덱스 조회 최적화 대상 쿼리문 1. 인덱스가 없을 경우 2. FK 단일 인덱스 3. 커버링 인덱스를 사용할 경우 실험 결과

인덱스 조회


SELECT * FROM pg_indexes WHERE tablename = 'cafeteria_diet';

실행 결과

indexname	indexdef
cafeteria-diet_pkey	CREATE UNIQUE INDEX "cafeteria-diet_pkey" ON public.cafeteria_diet USING btree (diet_id)

인사이트

PostgreSQL은 MySQL과 다르게 FK 제약조건 생성 시 자동으로 인덱스를 만들어주지 않는다.

최적화 대상 쿼리문


SELECT 
    dish_category, 
    dish_type, 
    dish_name 
FROM cafeteria_diet 
WHERE cafeteria_id = 1 
  AND date = '2025-06-30' 
  AND time = '아침';

실행 결과

dish_category	dish_type	dish_name
한식	주식	기장밥
한식	국류	해물보탕국
한식	찬류	치즈불닭

1. 인덱스가 없을 경우


EXPLAIN ANALYZE
SELECT
    dish_category,
    dish_type,
    dish_name
FROM cafeteria_diet
WHERE cafeteria_id = 4
  AND date = '2025-05-13'
  AND time = '점심';

실행 결과

QUERY PLAN

Seq Scan on cafeteria_diet (cost=0.00..1007.70 rows=3 width=39) (actual time=1.263..3.327 rows=9 loops=1)

Filter: ((cafeteria_id = 4) AND (date = '2025-05-13'::date) AND (("time")::text = '점심'::text))

Rows Removed by Filter: 32831

Planning Time: 0.068 ms

Execution Time: 3.350 ms

2. FK 단일 인덱스

FK 단일 인덱스 추가


CREATE INDEX idx_cafeteria_diet_cafeteria_id
ON cafeteria_diet (cafeteria_id);

실행 결과

QUERY PLAN

Bitmap Heap Scan on cafeteria_diet (cost=20.86..494.14 rows=3 width=39) (actual time=0.260..0.425 rows=9 loops=1)

Recheck Cond: (cafeteria_id = 4)

Filter: ((date = '2025-05-13'::date) AND (("time")::text = '점심'::text))

Rows Removed by Filter: 2279

Heap Blocks: exact=84

-> Bitmap Index Scan on idx_cafeteria_diet_cafeteria_id (cost=0.00..20.86 rows=2302 width=0) (actual time=0.089..0.089 rows=2288 loops=1)

Index Cond: (cafeteria_id = 4)

Planning Time: 0.083 ms

Execution Time: 0.447 ms

3. 커버링 인덱스를 사용할 경우

커버링 인덱스 추가


CREATE INDEX idx_cover
ON cafeteria_diet (date, cafeteria_id, time)
INCLUDE (dish_category, dish_type, dish_name);

실행 결과

QUERY PLAN

Index Only Scan using idx_cafeteria_diet_cover on cafeteria_diet (cost=0.41..2.68 rows=3 width=39) (actual time=0.019..0.021 rows=9 loops=1)

Index Cond: ((date = '2025-05-13'::date) AND (cafeteria_id = 4) AND ("time" = '점심'::text))

Heap Fetches: 0

Planning Time: 0.083 ms

Execution Time: 0.041 ms

실험 결과

인덱스 구성	실행 계획	실행 시간	인덱스 크기 (PK 제외)	특징
1️⃣ 인덱스 없음	Seq Scan	3.350 ms	-	테이블 풀 스캔
2️⃣ FK 단일 인덱스	Bitmap Heap Scan	0.447 ms	240 kB	FK 기준 필터링
3️⃣ 커버링 인덱스	Index Only Scan	0.041 ms	2656 kB	인덱스만으로 처리

얻을 수 있는 인사이트

FK 인덱스 추가만으로도 약 7.5배 성능 개선됐다. (3.350 ms → 0.447 ms)

커버링 인덱스로 Heap 접근을 제거하면 추가로 약 11배 개선된다. (0.447 ms → 0.041 ms)

결과적으로 디스크 I/O를 거의 제거하여 전체 쿼리 성능이 약 82배 향상된다.

단, 인덱스 크기는 성능 개선에 비례해 증가한다.

따라서 커버링 인덱스는 조회가 많고, 쿼리 패턴이 거의 고정된 경우에만 사용하는 것이 적절하다.

조회 컬럼이 자주 바뀌거나, 쓰기가 많은 테이블에서는 오히려 인덱스 유지 비용이 커질 수 있으므로 신중하게 선택해야 한다.