동호의 블로그

인덱스 조회 최적화 대상 쿼리 3. 실험 결과 3.1. 인덱스 없이 실행했을 때 2. FK 단일 인덱스 적용 3. 복합 인덱스 적용 4. 커버링 인덱스 적용 실험 결과 비교 결론

인덱스 조회


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 = 4
  AND date = '2025-05-13'
  AND time = '점심';

실행 결과

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

3. 실험 결과

3.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 단일 인덱스 적용

인덱스 생성


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_cafeteria_diet_date_cafeteria_time
ON cafeteria_diet (date, cafeteria_id, time);

실행 결과

QUERY PLAN

Index Scan using idx_cafeteria_diet_date_cafeteria_time on cafeteria_diet (cost=0.29..4.74 rows=5 width=39) (actual time=0.015..0.019 rows=9 loops=1)

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

Planning Time: 0.080 ms

Execution Time: 0.043 ms

4. 커버링 인덱스 적용

커버링 인덱스 추가


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

실행 결과

QUERY PLAN

Index Only Scan using idx_cafeteria_diet_date_cafeteria_time_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

실험 결과 비교

인덱스 구성	실행 계획	실행 시간	인덱스 크기
1️⃣ 인덱스 없음	Seq Scan	3.350 ms	-
2️⃣ FK 단일 인덱스	Bitmap Heap Scan	0.447 ms	240 kB
3️⃣ 복합 인덱스	Index Scan	0.043 ms	432 kB
4️⃣ 커버링 인덱스	Index Only Scan	0.041 ms	2656 kB

결론

실험 결과, 본 쿼리 패턴에서는 복합 인덱스가 성능과 저장 공간 측면에서 가장 합리적인 선택임을 확인하였다.