실행계획(EXPLAIN) 한 번에 읽기

실행계획(EXPLAIN) 읽는 법 — MySQL·PostgreSQL·Oracle·SQL Server 한 번에. 추가한 인덱스가 실제로 타는지 확인하려면 실행계획을 봐야 한다. DB마다 명령어도 출력도 다르지만, 결국 보는 것은 똑같다. 네 가지 주요 DB의 실행계획 확인법을, 실제 출력과 함께 정리한다.

---

들어가며

데이터베이스 인덱스를 추가하기 전에 보는 것들 글에서 인덱스가 왜 빠른지, 언제 추가해야 하는지를 다뤘다. 그런데 한 가지 질문이 남는다. 추가한 인덱스가 실제로 쓰이고 있을까?

인덱스를 만들어 놓고도 쿼리가 여전히 느린 경우가 많다. 인덱스가 있는데 옵티마이저가 안 쓰기로 했거나, 쓸 수 없게 쿼리가 작성됐거나, 통계가 어긋나 옵티마이저가 풀스캔이 더 싸다고 오판한 경우다. 이걸 눈으로 확인하는 도구가 실행계획(execution plan)이다.

인덱스를 추가하는 건 시작일 뿐이다. 옵티마이저가 그 인덱스를 실제로 골랐는지까지 실행계획으로 확인해야 비로소 검증이 끝난다.

문제는 DB마다 실행계획을 보는 방법이 제각각이라는 점이다. MySQL의 EXPLAIN, PostgreSQL의 EXPLAIN ANALYZE, Oracle의 DBMS_XPLAN, SQL Server의 실행 계획은 명령어도 출력 형식도 다르다. 그래서 한 DB에 익숙해도 다른 DB로 넘어가면 다시 막힌다.

이 글에서는 네 가지 주요 DB(MySQL/MariaDB, PostgreSQL, Oracle, SQL Server)에서 실행계획을 어떻게 뽑고 어떻게 읽는지를 실제 출력과 함께 정리한다. 그리고 마지막에 모든 DB에 공통으로 적용되는 "인덱스가 안 타는 이유"를 짚는다.

---

1. 실행계획이란 — 옵티마이저의 작업 지시서

SQL은 "무엇을 원하는지"만 적는 선언형 언어다. "어떻게 가져올지"는 옵티마이저(optimizer)가 정한다. 같은 SELECT라도 테이블을 풀스캔할지, 인덱스를 탈지, 어떤 테이블부터 조인할지 수많은 경로가 있고, 옵티마이저는 그중 비용(cost)이 가장 낮다고 추정한 경로를 고른다. 이렇게 선택된 경로가 바로 실행계획이다.

여기서 반드시 구분해야 할 두 가지가 있다.

• 예상(estimated) 계획: 쿼리를 실행하지 않고, 옵티마이저가 "이렇게 돌릴 것 같다"고 내놓는 계획. 빠르고 안전하지만 어디까지나 추정이다.
• 실제(actual) 계획: 쿼리를 실제로 돌린 뒤, 각 단계가 행을 몇 개 처리했고 시간이 얼마 걸렸는지 측정한 계획. 정확하지만 쿼리를 실행해야 한다.

튜닝의 핵심은 이 둘의 차이다. 옵티마이저가 "이 단계에서 10행 나올 것"이라 예상했는데 실제로 100만 행이 나왔다면, 통계가 틀렸거나 선택도 판단이 빗나간 것이고, 그 지점이 대개 성능 문제의 진원지다. 흥미로운 건, DB마다 부르는 이름만 다를 뿐 전부 이 "예상 vs 실제"를 보여준다는 점이다.

---

2. 어느 DB든 똑같이 봐야 할 3가지

명령어가 달라도, 실행계획에서 읽어야 할 핵심은 어느 DB나 같다.

1. 스캔 방식 — 테이블을 어떻게 읽는가. 인덱스로 필요한 행만 찾아가는가(seek / index scan), 아니면 전체를 훑는가(full scan / seq scan). 큰 테이블에서 풀스캔이 보이면 일단 의심한다.
2. 조인 방식 — 여러 테이블을 어떻게 합치는가. Nested Loop(작은 집합에 유리), Hash Join(큰 집합·비정렬에 유리), Merge Join(정렬된 입력에 유리). 데이터 크기에 안 맞는 조인이 선택되면 느려진다.
3. 예상 vs 실제 행 수 — 옵티마이저의 추정이 현실과 얼마나 맞는가. 차이가 크면 통계부터 의심한다.

실행계획은 대부분 트리 구조다. 가장 깊이 들여 쓴(또는 가장 안쪽) 노드가 먼저 실행되고, 그 결과가 위로 흘러 조인을 거쳐 최종 결과가 된다. 읽을 때는 안쪽(아래)부터 바깥(위)으로 따라가면 된다.

이 세 가지를 기준으로, 이제 DB별로 어떻게 뽑고 읽는지 본다. 모든 예제는 orders 테이블에 customer_id 인덱스(idx_cust)가 있다고 가정한다.

---

3. MySQL / MariaDB

가장 헷갈리는 부분부터 짚자. 예상 계획은 둘 다 EXPLAIN으로 같지만, 실제 계획을 보는 명령은 MySQL과 MariaDB가 다르다.

예상 계획 — EXPLAIN (공통)

EXPLAIN
SELECT * FROM orders WHERE customer_id = 12345;

전통적인 표 형태로 나온다.

+----+-------------+--------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | ref  | idx_cust      | idx_cust | 4       | const |    4 |   100.00 | NULL  |
+----+-------------+--------+------+---------------+----------+---------+-------+------+----------+-------+

봐야 할 핵심 컬럼은 다음과 같다.

• type — 접근 방식. 좋은 순서로 const > eq_ref > ref > range > index > ALL. 맨 끝의 ALL이 풀 테이블 스캔으로, 큰 테이블에서 보이면 위험 신호다. 위 예시는 ref(인덱스로 찾음)라 양호하다.
• key — 실제로 사용된 인덱스. NULL이면 인덱스를 전혀 안 탔다는 뜻이다.
• rows — 이 단계에서 읽을 것으로 예상한 행 수.
• Extra — Using index(커버링 인덱스, 좋음), Using where, Using filesort·Using temporary(정렬·임시 테이블, 무거움)를 주의해서 본다.

만약 인덱스가 없었다면 같은 쿼리가 이렇게 나온다. type=ALL, key=NULL, rows가 폭증하는 전형적인 "인덱스 안 탐" 신호다.

+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 1048576 |    10.00 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+

실제 계획 — 여기서 갈린다

MySQL 8.0.18 이상은 EXPLAIN ANALYZE를 쓴다. 쿼리를 실제로 돌리고, 트리 형태로 예상과 실제를 같이 보여준다.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345\G

-> Index lookup on orders using idx_cust (customer_id=12345)
   (cost=1.65 rows=4) (actual time=0.028..0.041 rows=4 loops=1)

(cost=... rows=...)가 예상, (actual time=... rows=... loops=...)가 실제다. 여기선 예상 4행 / 실제 4행으로 잘 맞는다.

MariaDB는 EXPLAIN ANALYZE(트리)도 있지만, 전통적으로는 ANALYZE 문을 쓴다. 익숙한 EXPLAIN 표에 실측 컬럼이 덧붙는 형태라 비교가 직관적이다.

ANALYZE
SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+---------------+----------+---------+-------+------+--------+----------+------------+-------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra |
+----+-------------+--------+------+---------------+----------+---------+-------+------+--------+----------+------------+-------+
|  1 | SIMPLE      | orders | ref  | idx_cust      | idx_cust | 4       | const |    5 |   4.00 |   100.00 |     100.00 | NULL  |
+----+-------------+--------+------+---------------+----------+---------+-------+------+--------+----------+------------+-------+

EXPLAIN에 없던 두 실측 컬럼이 추가된다.

• r_rows — rows(예상)에 대응하는 실제 행 수. 위에선 예상 5 / 실제 4로 근접.
• r_filtered — filtered(예상 선택도)에 대응하는 실제 선택도(%).

실무 감각으로, 풀스캔(type=ALL)인데 r_filtered가 15%보다 낮다면 읽은 행의 대부분을 버리고 있다는 뜻이므로 적절한 인덱스를 추가할 때다. 더 자세히 보려면 ANALYZE FORMAT=JSON을 쓴다.

---

4. PostgreSQL

PostgreSQL은 명령 체계가 가장 깔끔하다.

예상 계획 — EXPLAIN

EXPLAIN
SELECT * FROM orders WHERE customer_id = 12345;

                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using idx_cust on orders  (cost=0.29..8.31 rows=5 width=64)
   Index Cond: (customer_id = 12345)

• cost=0.29..8.31 — 시작 비용..총 비용(디스크 페이지 페치 단위의 추정값).
• rows=5 — 예상 행 수, width=64 — 행 평균 바이트.
• Index Scan — 인덱스를 탔다는 뜻. 이 자리에 Seq Scan이 보이면 인덱스를 못/안 탄 것이다.

실제 계획 — EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 12345;

                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using idx_cust on orders  (cost=0.29..8.31 rows=5 width=64)
                                       (actual time=0.018..0.024 rows=4 loops=1)
   Index Cond: (customer_id = 12345)
   Buffers: shared hit=3
 Planning Time: 0.072 ms
 Execution Time: 0.041 ms

• actual time=0.018..0.024 — 실제 시작..종료 시간(ms), rows=4 — 실제 행 수, loops=1 — 이 노드가 반복된 횟수.
• Buffers: shared hit=3 — 캐시에서 읽은 블록 수(hit은 캐시 적중, read는 디스크).

주의할 함정이 loops다. Nested Loop 안쪽 노드는 여러 번 반복되는데, 표시되는 rows는 반복당 평균이다. 실제 총 처리 행수는 rows × loops로 계산해야 한다.

참고로 PostgreSQL 18부터는 EXPLAIN ANALYZE에 BUFFERS가 기본 포함된다. 17 이하에서는 위처럼 BUFFERS를 직접 붙여야 I/O 정보가 나온다. 출력은 FORMAT JSON/XML/YAML로도 받을 수 있다.

---

5. Oracle

Oracle은 계획을 만드는 명령과 그걸 보기 좋게 출력하는 명령(DBMS_XPLAN)이 따로다.

예상 계획 — EXPLAIN PLAN

EXPLAIN PLAN FOR
SELECT * FROM orders WHERE customer_id = 12345;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

---------------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |    4 |   240 |     3   (0)|      |
|  1 |  TABLE ACCESS BY INDEX ROWID | ORDERS   |    4 |   240 |     3   (0)|      |
|* 2 |   INDEX RANGE SCAN           | IDX_CUST |    4 |       |     1   (0)|      |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUSTOMER_ID"=12345)

Operation(TABLE ACCESS FULL=풀스캔, INDEX RANGE SCAN, TABLE ACCESS BY INDEX ROWID 등), Rows(예상 행 수, E-Rows), Cost 컬럼이 나온다. 단, 이건 바인드 변수 값을 무시한 추정이다.

실제 계획 — GATHER_PLAN_STATISTICS

실측을 보려면 쿼리에 GATHER_PLAN_STATISTICS 힌트를 넣어 실행하고, DISPLAY_CURSOR를 ALLSTATS LAST 포맷으로 출력한다.

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM orders WHERE customer_id = 12345;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));

-------------------------------------------------------------------------------------------------
| Id | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |      1 |        |      4  |00:00:00.01 |       5 |
|  1 |  TABLE ACCESS BY INDEX ROWID | ORDERS   |      1 |      4 |      4  |00:00:00.01 |       5 |
|* 2 |   INDEX RANGE SCAN           | IDX_CUST |      1 |      4 |      4  |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

• E-Rows — 옵티마이저 예상 행 수, A-Rows — 실제 행 수.
• Starts — 해당 단계가 실행된 횟수(PostgreSQL의 loops에 해당).
• Buffers — 읽은 블록 수.

E-Rows와 A-Rows의 차이가 크게 벌어지는 첫 단계가 대개 문제의 출발점이다. SQL*Plus나 SQLcl에서는 SET AUTOTRACE ON으로 간단히 계획과 통계를 같이 볼 수도 있다.

또 하나, 출력 하단 Predicate Information이 인덱스 활용 판단의 결정적 단서다. 조건이 access로 잡히면 인덱스 탐색을 직접 구동한다는 뜻(좋음)이고, filter로 잡히면 행을 다 가져온 뒤 걸러낸다는 뜻(인덱스를 기대했다면 나쁨)이다.

---

6. SQL Server

SQL Server는 SSMS(Management Studio)의 그래픽 실행 계획이 강력하지만, 스크립트로도 모두 뽑을 수 있다.

예상 계획 — SHOWPLAN

SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM Orders WHERE CustomerID = 12345;
GO
SET SHOWPLAN_TEXT OFF;
GO

StmtText
------------------------------------------------------------------------------
  |--Index Seek(OBJECT:([Sales].[Orders].[idx_cust]),
                SEEK:([Orders].[CustomerID]=(12345)) ORDERED FORWARD)

Index Seek이 보이면 인덱스를 정확히 찾아간 것이다. 이 자리에 Index Scan이나 Clustered Index Scan/Table Scan이 큰 테이블에서 보이면 인덱스를 못 타는 형태다. SSMS에서는 Ctrl+L로 그래픽 예상 계획을 바로 볼 수 있다.

실제 리소스 — STATISTICS IO / TIME

실측에서 가장 자주 보는 건 논리적 읽기(logical reads)다. 같은 쿼리를 튜닝하며 이 숫자가 줄면 더 적은 페이지를 읽는다는 뜻이라 신뢰할 수 있는 지표다.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM Orders WHERE CustomerID = 12345;

Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

인덱스가 없으면 같은 쿼리가 Clustered Index Scan으로 떨어지면서 logical reads가 수천으로 뛴다. 그 숫자 자체가 "이 인덱스가 효과 있다"의 증거가 된다. 실제 그래픽 계획은 Ctrl+M 후 실행(또는 SET STATISTICS XML ON)으로 본다.

그래픽 계획을 빠르게 훑을 때의 요령 몇 가지.

• 화살표 두께 = 흐르는 행 수. 갑자기 굵어지는 지점이 보통 범인이다.
• Key Lookup이 대량 반복되면 비싸다 → 필요한 컬럼을 INCLUDE한 커버링 인덱스로 해결한다.
• 연산자 비용 %는 추정이라 그대로 믿지 말고, 실제 행 수·논리적 읽기를 함께 본다.
• 그래픽 계획의 노란 경고 아이콘과 녹색 누락 인덱스(missing index) 힌트도 좋은 출발점이다.

---

7. 인덱스가 안 타는 이유 찾기

여기까지가 도구라면, 이제 본론이다. 실행계획에서 풀스캔(또는 filter, Index Scan)이 보일 때, 인덱스가 있는데도 안 타는 이유는 DB가 달라도 대개 같은 패턴이다.

1. WHERE 컬럼에 함수나 연산을 씌움. WHERE YEAR(created_at) = 2024나 WHERE price + 1000 > 5000처럼 컬럼을 가공하면 인덱스를 못 쓴다(SARGable하지 않음). 컬럼은 그대로 두고 상수 쪽을 가공해야 한다 → WHERE created_at >= '2024-01-01'.
2. 타입 불일치로 인한 암묵적 형변환. 문자열 컬럼에 숫자를 비교하거나 그 반대면, DB가 한쪽을 변환하면서 인덱스가 무력화된다. Oracle의 Predicate가 filter로 바뀌는 전형적 원인이다.
3. 선택도가 낮음. 조건에 맞는 행이 테이블의 상당 비율이면, 옵티마이저는 인덱스 탐색보다 풀스캔이 더 싸다고 판단한다. 이건 오판이 아니라 정상적인 선택일 때가 많다.
4. 통계가 오래됐거나 부정확함. 옵티마이저는 통계로 행 수를 추정한다. 통계가 어긋나면 예상과 실제(rows vs r_rows, E-Rows vs A-Rows)가 크게 벌어지고 잘못된 계획을 고른다 → 통계를 갱신한다(ANALYZE TABLE, DBMS_STATS, UPDATE STATISTICS).
5. 선행 와일드카드 LIKE. LIKE '%검색어'는 인덱스를 못 탄다. 뒤쪽 와일드카드('검색어%')는 탈 수 있다.
6. 복합 인덱스의 선두 컬럼 누락. (a, b, c) 인덱스는 a부터 조건이 있어야 효율적으로 탄다. b만으로 검색하면 인덱스를 제대로 못 쓴다.

각 DB에서 "인덱스 안 탐"이 어떻게 보이는지 신호를 요약하면 이렇다.

• MySQL / MariaDB: key가 NULL, type이 ALL.
• PostgreSQL: Index Scan이어야 할 자리에 Seq Scan.
• Oracle: Predicate가 access가 아니라 filter, 그리고 TABLE ACCESS FULL.
• SQL Server: Index Scan·Table Scan, 노란 경고나 누락 인덱스 힌트.

---

8. 네 DB 비교

DB	예상 계획 (실행 안 함)	실제 계획 (실행함)	실제 행 수 컬럼	인덱스 미사용 신호
MySQL	EXPLAIN	EXPLAIN ANALYZE	(트리) actual rows	key=NULL, type=ALL
MariaDB	EXPLAIN	ANALYZE 문	r_rows	key=NULL, type=ALL
PostgreSQL	EXPLAIN	EXPLAIN ANALYZE	actual rows	Seq Scan
Oracle	EXPLAIN PLAN + DISPLAY	힌트 + DISPLAY_CURSOR	A-Rows	Predicate가 filter
SQL Server	SHOWPLAN_XML / Ctrl+L	STATISTICS XML / Ctrl+M	Actual Number of Rows	Index/Table Scan, 노란 경고

---

마치며

데이터베이스 인덱스를 추가하기 전에 보는 것들에서 "느리다고 무작정 인덱스를 추가하지 말라"고 했다. 이 글은 그 짝이 되는 글이다. 인덱스를 추가했다면, 실행계획으로 그게 실제로 쓰이는지 반드시 확인해야 한다. 만들었다고 끝이 아니라, 옵티마이저가 그 인덱스를 골랐는지까지 봐야 검증이 끝난다.

DB마다 명령어는 EXPLAIN, ANALYZE, DBMS_XPLAN, SET STATISTICS로 제각각이고 출력 형태도 표와 트리로 갈린다. 하지만 읽는 눈은 하나다. 스캔 방식, 조인 방식, 그리고 예상과 실제 행 수의 차이. 특히 예상과 실제가 크게 벌어지는 첫 지점을 찾는 습관만 들이면, 어느 DB로 옮겨가도 같은 방식으로 문제의 진원지를 짚을 수 있다.

---

참고 출처

• MySQL 8.0 Reference Manual — EXPLAIN Output Format — https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
• MariaDB Documentation — ANALYZE Statement — https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/analyze-statement
• PostgreSQL Documentation — EXPLAIN — https://www.postgresql.org/docs/current/sql-explain.html
• Oracle Database SQL Tuning Guide — Generating and Displaying Execution Plans — https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/generating-and-displaying-execution-plans.html
• Microsoft Learn — Showplan Logical and Physical Operators Reference — https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference
• Microsoft Learn — SET STATISTICS TIME (Transact-SQL) — https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql