실행 계획(Explain Analyze)으로 보는 SQL 성능 튜닝: 병목 구간 제거하기

쿼리 실행 계획 (Query Plan) 이란?

쿼리 실행 계획은 데이터베이스 관리 시스템(DBMS)이 SQL 쿼리를 어떻게 실행할지에 대한 절차를 나타낸 설계도이다.

우리가 SQL을 실행하면 결과만 반환되지만, 그 내부에서는 어떤 인덱스를 사용할지, 어떤 방식으로 테이블을 읽을지, 어떤 순서로 조인을 수행할지 같은 복잡한 의사결정 과정이 일어난다.

실행 계획은 이 과정을 단계별로 보여주며, 성능 병목 구간을 찾고 쿼리를 최적화하는 데 핵심적인 도구가 된다. 그리고 이 실행 계획을 만드는 두뇌가 바로 옵티마이저(Optimizer) 이다.

 

옵티마이저(Optimizer)란?

옵티마이저는 SQL을 가장 효율적으로 수행하기 위한 최적의 처리 경로(Execution Path) 를 설계하는 DBMS의 핵심 엔진이다.
컴퓨터에서 CPU가 두뇌 역할을 하듯, DBMS의 두뇌는 옵티마이저라고 볼 수 있다.
개발자가 SQL을 실행하면 DBMS는 곧바로 처리하지 않고, 옵티마이저가 먼저 여러 가지 실행 계획을 세운다.

이후 시스템 통계 정보(테이블 크기, 인덱스 분포, 데이터 양 등) 를 기반으로 각 실행 계획의 예상 비용(Cost)을 산정하고, 그중 가장 효율적인 계획을 선택해 실제 쿼리를 수행한다.

---

실행 계획의 기본 개념

실행 계획에는 다양한 연산자가 등장하지만, 우선 네 가지 기본적인 개념만 이해해도 큰 도움이 된다.

 

Sequential Scan (Seq Scan)

테이블의 모든 데이터를 처음부터 끝까지 한 줄씩 읽는 방식이다.
- 예시: 책 제목은 알지만 도서번호를 모르고, 책장을 처음부터 끝까지 다 뒤지는 경우
- 장점: 작은 테이블에서는 단순해서 빠르다.
- 단점: 데이터가 커질수록 성능이 급격히 저하된다.

 

Index Scan

인덱스를 이용해 필요한 데이터만 직접 찾아가는 방식이다.
- 예시: 책에서 특정 단어를 색인(Index) 페이지로 바로 찾아 들어가는 경우
- 장점: 인덱스가 걸린 조건에서는 매우 빠르다.
- 단점: 인덱스가 없는 컬럼이라면 불가능하고, 결국 Seq Scan으로 돌아간다.

 

Hash Join

조인을 할 때 작은 테이블(또는 한쪽 테이블)을 해시 테이블로 만들어 두고, 다른 테이블의 값을 빠르게 매칭하는 방식이다.
- 예시: 두 명단을 비교할 때, 한쪽 명단을 사물함 번호판(해시셋)으로 바꿔두고 다른 명단을 훑으면서 바로 매칭하는 경우
- 장점: 대량 데이터 조인에서 효율적이다.
- 단점: 해시 테이블을 메모리에 생성하기 때문에 메모리 사용량이 증가할 수 있다.

 

Nested Loop

한쪽 테이블의 각 행을 기준으로 다른 테이블을 반복 조회하는 방식이다.
- 예시: 반 친구 명단을 하나씩 보면서 다른 명단에서 같은 이름을 일일이 찾아보는 경우
- 장점: 작은 데이터 조인에는 빠르다.
- 단점: 대량 데이터 조인에서는 반복이 너무 많아져 성능이 저하된다.

 

Merge Join

두 테이블이 정렬된 상태(주로 인덱스를 사용해 정렬)라면, 정렬된 컬럼을 기준으로 차례대로 비교해가며 조인을 수행하는 방식이다.
- 예시: 두 명단이 이름순으로 정렬돼 있다면, 앞에서부터 한 줄씩 나란히 비교하며 매칭하는 경우  
- 장점: 이미 정렬돼 있거나 인덱스를 이용해 정렬된 데이터를 다룰 때 매우 효율적이다.
- 단점: 조인 전에 정렬이 필요하다면, 정렬 과정 자체가 큰 비용이 될 수 있다.

---

직접 확인해보기

상품(products)(1만건)과 상품 이미지(product_images)(3만건)는 1 : N 관계로 구성되어 있다.
가장 먼저 단순히 상품만 조회해보자.

 

상품(products)단독 조회

EXPLAIN ANALYZE
SELECT * FROM products;

 

실행 결과에는 아래와 같은 계획이 출력된다.

 

이는 인덱스를 타지 않고, 테이블 전체를 처음부터 끝까지 순차적으로(Sequential) 읽은 것을 의미한다.
상품 테이블은 1만 건이라 빠르게 끝났지만, 데이터가 수십만 건 이상으로 늘어나면 성능 저하가 발생할 수 있다.

Planning Time 은 옵티마이저가 쿼리를 “어떻게 실행할지” 계획을 세우는 데 걸린 시간 예를 들어, 
어떤 인덱스를 쓸지, 어떤 조인 방식을 쓸지 (Hash Join / Nested Loop), 순서를 어떻게 할지, 
이런 실행 경로(Execution Path)를 설계하는 데 걸린 시간이다.

Execution TIme 은 옵티마이저가 만든 실행 계획에 따라 실제로 데이터를 읽고 결과를 반환하는 데 걸린 시간이다. 우리가 보통 체감하는 쿼리속도 가 사실은 Execution Time이다.

정리하면
Planning Time : “시험 문제 풀이 전략 세우는 시간”
Execution Time : “실제로 답안지에 풀어 쓰는 시간” 이다.

 

상품(products) + 상품이미지(product_images) 조인

실제 개인프로젝트에서 JPA를 통해 나가는 쿼리이다.

EXPLAIN (ANALYZE, VERBOSE)
select distinct p1_0.PRODUCT_ID,  
                p1_0.CREATED_AT,  
                p1_0.DESCRIPTION,  
                p1_0.PRODUCT_NAME,  
                pi1_0.PRODUCT_ID,  
                pi1_0.id,  
                pi1_0.position,  
                pi1_0.url,  
                p1_0.SALE_TYPE,  
                p1_0.SELLER_ID,  
                p1_0.STARTING_PRICE,  
                p1_0.UPDATED_AT  
from PRODUCTS p1_0  
         left join PRODUCT_IMAGES pi1_0 on p1_0.PRODUCT_ID = pi1_0.PRODUCT_ID;

실행 결과에는 아래와 같은 계획과 시간 그리고 어떻게 접근했는지 출력된다.

 

이번에는 옵티마이저가 Hash Join 방식을 선택했다.

• 예상 결과 행 수: 30,000
• 실제 결과 행 수도 30,000 (상품 1만 건 × 이미지 3만 건, 1:N 관계라서 데이터가 늘어남)
• Execution Time은 단일 테이블 조회(1.3ms)보다 훨씬 늘어나 59ms가 걸렸다.

그런데 실행계획을 자세히 보면 단순 HashJoin만 있는게 아니라 HashAggregate 단계가 추가된 것을 볼 수 있다.이는 JPA가 자동 생성한 쿼리에 포함된 DISTINCT 때문인데, 모든행(30,000)을 모아서 Group Key 기준으로 중복제거(HashAggregate) 이 과정에서 메모리 사용, 디스크 임시저장까지 발생 해 Execution Time이 늘어난 것이다.

 

---

개선 해보기

위 실행계획에서 확인했듯이, 단순한 Hash Join은 큰 문제가 아니였다. 
병목은  DISTINCT가 있으면 Postgres는 내부적으로 HashAggregate 연산을 추가해서, 
모든 결과 행을 모아 중복 제거를 수행한다. 이 과정에서 메모리와 디스크 I/O가 발생한다. 
Execution Time이 늘어난 원인이였다. 따라서 첫번째 개선은 DISTINCT 제거였다.

 

EXPLAIN (ANALYZE, VERBOSe)
select p1_0.PRODUCT_ID,  
                p1_0.CREATED_AT,  
                p1_0.DESCRIPTION,  
                p1_0.PRODUCT_NAME,  
                pi1_0.PRODUCT_ID,  
                pi1_0.id,  
                pi1_0.position,  
                pi1_0.url,  
                p1_0.SALE_TYPE,  
                p1_0.SELLER_ID,  
                p1_0.STARTING_PRICE,  
                p1_0.UPDATED_AT  
from PRODUCTS p1_0  
         left join PRODUCT_IMAGES pi1_0 on p1_0.PRODUCT_ID = pi1_0.PRODUCT_ID;

 

아까 JPA가 생성해준 쿼리에서 distinct 를 제거하고 실행해 보았다.

 

결과를 보면 HashAggregate 단계가 사라지고 속도가 59ms-> 21ms 약 3배 까지 향상 된 것을 알 수있다.
또 조회하는 테이블의 TEXT, BLOB, CLOB, JSON, 긴 문자열 같은 대용량 컬럼이 존재 한다면 SELECT를 최소화해 속도가 더 빨라질 수 있다.

인덱스 추가

CREATE INDEX idx_product_images_product_id ON product_images(product_id);

를 추가해 위와 같은 쿼리를 실행해 보았다.

 

해당 내역과 같이 아무 변화 없이 Seq Scan으로 가져오는것을 볼수있다.
여전히 PostgreSQL 옵티마이저가 여전히 Seq Scan + Hash Join을 선택했기 때문이다. 
PostgreSQL에서 해시 조인을 할 때는 보통 한쪽 테이블을 통째로 읽어서 해시 테이블을 만든 뒤 다른 테이블을 훑으며 매칭한다. product_images가 30,000건, products가 10,000건이라 테이블 크기가 작아 옵티마이저는 Seq Scan를 사용해도 비용이 낮다고 판단 한것이다.

EXPLAIN (ANALYZE, VERBOSE)  
select p1_0.PRODUCT_ID,  
                p1_0.CREATED_AT,  
                p1_0.DESCRIPTION,  
                p1_0.PRODUCT_NAME,  
                pi1_0.PRODUCT_ID,  
                pi1_0.id,  
                pi1_0.position,  
                pi1_0.url,  
                p1_0.SALE_TYPE,  
                p1_0.SELLER_ID,  
                p1_0.STARTING_PRICE,  
                p1_0.UPDATED_AT  
from PRODUCTS p1_0  
         left join PRODUCT_IMAGES pi1_0 on p1_0.PRODUCT_ID = pi1_0.PRODUCT_ID  
WHERE p1_0.product_id = 123;

 

WHERE 을 추가해 결과를 확인하면

 

옵티마이저가 Index Scan을 사용해 가져오는것을 알 수있다. 
"인덱스를 추가했다고 항상 속도가 빨라지는것은 아니다. 전체 데이터를 다 훑을때는 Seq Scan 이 더 효율적일수도 있다." 

하지만 특정 조건으로 조회하면 옵티마이저가 Index Scan을 선택하고, 실행 시간이 눈에 띄게 줄어든다. 

 

---

마무리

이처럼 실행 계획은 단순이 쿼리 속도를 보여주는 도구가 아니라,
쿼리가 왜 느린지 / 어떤 연산에서 병목이 발생하는지 / 게선이 효과 있었는지를 확인하게 해준다.

• JPA가 자동 생성한 DISTINCT → HashAggregate로 이어져 불필요한 오버헤드 발생 
• DISTINCT 제거 → Execution Time 약 3배 개선  
• Index 추가 → 전체 스캔에는 효과 없음, 하지만 조건 검색 시 Index Scan으로 최적 경로 선택

Cost (예상 비용) 과 Rows (예상 행 수 vs 실제 행 수) 그리고 Width (행 당 평균 바이트 수) 등 이 요소들을 주의 깊게 본다면 성능 병목을 빨리 찾을수 있다.