실행계획 읽기 공통 패턴: scan, seek, join, sort, aggregate

실행계획은 “DB가 일을 어떻게 줄이려 했는지”를 보여준다

SQL을 튜닝할 때 실행계획을 보는 이유는 단순히 Seq Scan이나 Using filesort 같은 단어를 찾기 위해서가 아니다. 실행계획은 옵티마이저가 쿼리를 어떤 작업 단위로 쪼갰고, 각 단계에서 몇 row가 흘러갈 것으로 예상했으며, 어떤 비용을 더 싸다고 판단했는지를 보여준다.

처음에는 DBMS마다 출력 모양이 달라 보인다. PostgreSQL은 plan node tree를 텍스트로 보여주고, MySQL의 전통적 EXPLAIN은 테이블별 row와 type, key, rows, filtered, Extra를 보여준다. SQL Server는 graphical plan에서 operator와 화살표를 보여준다. 하지만 읽는 순서는 크게 다르지 않다.

1. 어디서 row를 읽는가? scan, seek, index scan, table scan
2. 읽은 row를 어떻게 줄이는가? index condition, filter, residual predicate
3. 테이블을 어떻게 합치는가? nested loop, hash join, merge join
4. 정렬이나 그룹화가 별도 작업을 만드는가? sort, aggregate, temporary, hash
5. 예상과 실제가 어디서 갈라지는가? estimated rows vs actual rows, loops, buffers, reads

이번 장은 특정 DBMS 명령어 암기가 아니라, 실행계획을 볼 때 공통으로 반복되는 다섯 패턴인 scan, seek, join, sort, aggregate를 운영자 관점에서 읽는 방법을 정리한다.

실행계획은 트리다: 아래에서 읽고, 위에서 의미를 확인한다

PostgreSQL 문서는 query plan을 plan node의 tree라고 설명한다. 아래쪽 leaf node는 보통 테이블이나 인덱스에서 row를 가져오는 scan node이고, 그 위에 join, sort, aggregate, filter 같은 node가 올라간다. SQL Server의 Showplan도 물리 operator의 tree로 설명한다. MySQL의 전통적 표 형식은 트리처럼 보이지 않지만, 테이블이 읽히는 순서와 access type, chosen key, estimated rows를 통해 같은 질문을 던질 수 있다.

PostgreSQL의 cost=startup..total rows=... width=...에서 cost는 milliseconds가 아니라 planner의 추정 단위다. rows도 “읽은 row 수”가 아니라 해당 node가 내보낼 것으로 예상한 row 수다. 예를 들어 Seq Scan에 Filter가 붙어 rows=7000으로 보인다고 해서 7000 row만 읽었다는 뜻은 아니다. 테이블 전체를 읽고 그중 7000 row를 내보낸 계획일 수 있다.

그래서 실행계획을 볼 때는 아래처럼 구분해야 한다.

Scan과 seek: “많이 읽는가, 좁게 찾아가는가”

가장 먼저 볼 것은 row를 어떻게 가져오는지다. DBMS마다 이름은 조금씩 다르지만 의미는 비슷하다.

scan은 나쁜 단어가 아니다. 테이블의 대부분을 읽어야 하거나, 결과를 특정 순서로 넓게 읽어야 하거나, 작은 테이블을 조인할 때는 scan이 가장 싸다. 반대로 seek도 항상 좋은 것은 아니다. 매우 많은 row를 seek한 뒤 base table을 수십만 번 lookup하면 넓은 scan보다 느릴 수 있다.

예를 들어 고객별 최근 주문 몇 건만 찾는 쿼리는 (customer_id, created_at) 인덱스로 좁게 seek하는 편이 자연스럽다.

SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

반대로 지난 1년 전체 매출을 모두 집계하는 배치라면 넓은 scan이 정상일 수 있다.

SELECT date_trunc('month', created_at), sum(total_amount)
FROM orders
WHERE created_at >= date '2025-01-01'
GROUP BY 1;

실행계획에서 scan/seek를 볼 때 중요한 질문은 다음이다.

• 조건이 index access 단계에서 처리되는가, 아니면 row를 가져온 뒤 filter되는가?
• MySQL의 possible_keys에는 index가 있는데 key가 NULL인 이유는 무엇인가?
• PostgreSQL의 Index Cond와 Filter가 분리되어 있다면, filter에서 버리는 row가 너무 많지는 않은가?
• covering index로 해결할 문제인가, 아니면 write overhead 때문에 index 추가가 더 위험한가?
• random lookup이 너무 많아져 buffer cache와 disk I/O를 압박하지 않는가?

Filter 위치: “읽기 전에 줄였는가, 읽은 뒤 버렸는가”

같은 조건절이라도 어디에서 적용되느냐에 따라 비용이 달라진다. 인덱스가 조건을 직접 사용할 수 있으면 읽을 범위를 줄인다. 반대로 인덱스로 row를 가져온 뒤 filter로 버리면 이미 I/O와 CPU를 쓴 뒤다.

PostgreSQL에서는 보통 Index Cond와 Filter를 구분해서 본다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42
  AND lower(status) = 'paid';

만약 plan에 Index Cond: (customer_id = 42)만 있고 Filter: (lower(status) = 'paid')가 따로 있다면, customer_id로 가져온 row 중 상당수를 나중에 버리는 구조다. status 조건의 선택도가 높고 자주 쓰인다면 expression index나 조건 rewrite를 검토할 수 있다. 하지만 바로 index를 만들기보다 실제 row 수와 쓰기 비용을 먼저 봐야 한다.

MySQL에서는 type, key, rows, filtered, Extra를 함께 본다. rows는 검사할 row의 추정치이고, filtered는 table condition으로 남을 비율의 추정치다. Extra의 Using where, Using index, Using temporary, Using filesort 같은 표시도 힌트가 된다. Using filesort가 “디스크 파일을 반드시 만들었다”는 뜻은 아니지만, index order로 해결하지 못한 별도 정렬 작업이 있음을 의심하게 해준다.

필터 위치를 읽는 실전 기준은 단순하다.

1. 많이 읽고 많이 버리는가?
2. 버리는 조건이 자주 쓰이고 선택도가 높은가?
3. 그 조건을 SARGable하게 바꿀 수 있는가?
4. index를 추가하면 update, insert, storage, replication lag 비용이 감당 가능한가?

Join: nested loop, hash, merge는 입력 크기와 정렬 상태의 선택이다

조인은 실행계획에서 성능 차이를 크게 만드는 부분이다. 조인 operator를 볼 때는 이름보다 입력 row 수, 한쪽 입력을 몇 번 반복해서 읽는지, memory를 얼마나 쓰는지를 먼저 본다.

Nested loop는 작은 입력에서는 강력하다. 예를 들어 최근 20개 주문의 고객 정보를 찾는 쿼리는 주문 20개를 먼저 찾고 고객 PK를 20번 seek하면 된다.

SELECT o.id, o.created_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id = 42
ORDER BY o.created_at DESC
LIMIT 20;

하지만 같은 계획이 “최근 90일 전체 주문 수백만 건”으로 커지면 문제가 된다. 고객 테이블을 수백만 번 lookup하는 구조가 되기 때문이다. 이때 옵티마이저가 hash join을 고르는 것은 이상한 일이 아니다. 큰 입력을 한 번씩 읽고 hash table로 매칭하는 편이 전체 비용이 낮을 수 있다.

조인 계획을 볼 때 특히 조심할 것은 cardinality 오차다. 옵티마이저가 바깥 입력을 100 row로 예상했는데 실제로는 1,000,000 row라면 nested loop 선택은 치명적일 수 있다. 반대로 실제로는 20 row인데 큰 hash join과 sort를 준비하면 startup cost와 memory 사용이 과해질 수 있다.

운영자가 확인할 질문은 다음이다.

• 조인 순서에서 먼저 줄어드는 테이블이 앞에 오는가?
• estimated rows와 actual rows가 처음 크게 갈라지는 지점은 어디인가?
• nested loop의 inner side가 몇 번 실행되는가?
• hash join이 spill하지 않았는가?
• merge join을 위해 sort가 추가되었는가, 아니면 기존 index order를 활용했는가?
• FK, unique key, 통계, extended statistics가 부족해 조인 cardinality를 잘못 예측하지 않았는가?

Sort: 정렬은 latency와 memory를 동시에 압박한다

ORDER BY, GROUP BY, DISTINCT, window function, merge join은 정렬을 만들 수 있다. 정렬은 결과를 예쁘게 만드는 부가 작업처럼 보이지만, 운영에서는 tail latency와 memory 사용량을 크게 흔드는 operator다.

정렬의 특성은 두 가지다.

첫째, 많은 경우 blocking operator처럼 동작한다. 입력을 어느 정도 모으기 전에는 첫 row를 내보내기 어렵다. 그래서 startup cost가 커지고, 사용자 요청에서 p95/p99 latency가 튈 수 있다.

둘째, memory가 부족하면 디스크 임시 공간을 사용한다. PostgreSQL에서는 EXPLAIN (ANALYZE, BUFFERS)와 sort method, disk 사용량을 함께 봐야 하고, MySQL에서는 Using filesort, Using temporary가 단서가 된다. SQL Server에서는 sort spill, memory grant, tempdb 사용량을 함께 본다.

예를 들어 아래 쿼리는 조건은 좁아 보여도 정렬이 병목일 수 있다.

SELECT id, customer_id, created_at, total_amount
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC
LIMIT 100;

status='PAID'가 전체의 80%라면 status 인덱스만으로는 row를 많이 읽고 정렬해야 할 수 있다. (status, created_at DESC) 형태의 인덱스가 있으면 조건과 정렬을 동시에 만족할 수 있지만, 이 인덱스가 모든 workload에 좋은 것은 아니다. write overhead와 storage, 다른 쿼리와의 중복 index 여부를 같이 봐야 한다.

Sort를 볼 때는 다음 순서로 판단한다.

1. 정렬 입력 row 수가 얼마나 큰가?
2. LIMIT이 있어도 sort 전에 너무 많은 row가 들어오지는 않는가?
3. index order로 정렬을 피할 수 있는가?
4. 정렬이 memory 안에서 끝났는가, disk/temp 영역으로 spill했는가?
5. 정렬을 제거하기 위해 만든 index가 쓰기 성능을 해치지는 않는가?

Aggregate: group 수와 중간 row 수가 비용을 결정한다

GROUP BY, COUNT, SUM, DISTINCT는 aggregate operator를 만든다. aggregate의 비용은 최종 결과 row 수만으로 판단하면 안 된다. 중요한 것은 aggregate 전에 들어오는 row 수와 group cardinality다.

예를 들어 아래 두 쿼리는 모두 월별 집계를 만들지만 성격이 다르다.

-- 전체 주문을 월별로 집계한다. 입력 row가 크다.
SELECT date_trunc('month', created_at) AS month, count(*)
FROM orders
GROUP BY 1;

-- 한 고객의 주문만 월별로 집계한다. 입력 row가 작을 수 있다.
SELECT date_trunc('month', created_at) AS month, count(*)
FROM orders
WHERE customer_id = 42
GROUP BY 1;

DBMS는 상황에 따라 정렬 기반 aggregate나 hash aggregate를 선택한다. 정렬 기반은 입력이 이미 group key 순서로 정렬되어 있으면 효율적일 수 있다. Hash aggregate는 정렬 없이 group hash table을 만들 수 있지만 group 수가 많거나 memory 추정이 틀리면 spill이 발생할 수 있다.

Aggregate에서 확인할 것은 다음이다.

• aggregate 전 입력 row 수가 왜 그렇게 큰가?
• group key의 distinct count가 어느 정도인가?
• WHERE 조건을 aggregate 전에 더 강하게 pushdown할 수 있는가?
• partial/pre-aggregation, materialized view, summary table이 필요한 반복 workload인가?
• COUNT(DISTINCT ...)처럼 memory를 많이 쓰는 집계가 포함되어 있는가?
• group key 순서의 index가 sort와 aggregate를 동시에 줄일 수 있는가?

집계 튜닝은 “집계 함수를 빠르게”보다 “집계에 들어가기 전에 row를 줄이는 것”이 대부분이다.

실제 plan을 읽는 순서: 빨간 단어보다 숫자 흐름

실행계획을 처음 보면 Seq Scan, Using filesort, Hash Join 같은 단어가 먼저 눈에 들어온다. 하지만 운영에서는 단어보다 숫자의 흐름이 더 중요하다.

다음 순서로 읽으면 실수를 줄일 수 있다.

1. 최상위 node의 목표를 확인한다. 전체 결과를 다 만들어야 하는 쿼리인지, LIMIT이나 EXISTS처럼 일찍 멈출 수 있는 쿼리인지 본다.
2. leaf access를 본다. 각 테이블이 scan인지 seek인지, 어떤 index를 썼는지, partition pruning이 되었는지 확인한다.
3. 필터가 어디서 적용되는지 본다. index condition으로 줄였는지, table fetch 후 filter했는지 본다.
4. row 수가 급증하거나 급감하는 지점을 찾는다. 예상과 실제가 크게 갈라지는 첫 node가 원인 후보인 경우가 많다.
5. join 반복 횟수를 본다. nested loop의 inner side가 과도하게 반복되지 않는지 확인한다.
6. sort/aggregate의 입력 row와 memory를 본다. spill, temporary, filesort, tempdb 사용을 확인한다.
7. 실제 자원 지표와 연결한다. buffers, logical reads, disk I/O, CPU, wait, lock, network 전송량을 함께 본다.

예를 들어 plan에서 가장 높은 비용 비율이 sort에 찍혀 있어도, 실제 문제는 그 앞 join의 cardinality 오차일 수 있다. join이 1,000 row를 만들 것으로 예상했는데 실제로 10,000,000 row를 만들었고, 그 결과 sort가 커졌다면 sort를 없애기보다 join 입력을 줄이는 것이 먼저다.

DBMS별로 같은 질문을 다르게 묻는다

실무에서는 하나의 DBMS만 다루지 않을 때가 많다. 공통 질문을 DBMS별 출력에 연결해 두면 plan을 더 빨리 읽을 수 있다.

주의할 점은 plan 출력만으로 모든 것을 알 수 없다는 것이다. plan은 “어떤 일을 하려 했는지”를 보여주지만, 운영 장애에서는 lock wait, network, connection pool 대기, checkpoint, replication lag, storage throttling이 함께 섞인다. 그래서 실행계획은 slow log, pg_stat_statements, Performance Schema, Query Store, APM trace, OS metrics와 같이 읽어야 한다.

운영 체크리스트: 실행계획을 보고 바로 인덱스를 만들지 않는다

실행계획을 본 뒤 바로 index를 만드는 것은 위험하다. index는 읽기를 줄일 수 있지만 쓰기 비용, storage, cache pressure, backup size, replication lag를 늘린다. 특히 장애 중에는 빠른 완화와 장기 개선을 분리해야 한다.

실행계획 리뷰 체크리스트를 아래처럼 두면 좋다.

• 이 plan은 production과 비슷한 데이터 분포에서 나온 것인가?
• bind parameter, tenant, 날짜 범위가 실제 느린 요청과 같은가?
• EXPLAIN ANALYZE를 실행해도 안전한 쿼리인가?
• estimated rows와 actual rows가 처음 갈라지는 node는 어디인가?
• scan이 문제인가, 아니면 scan 뒤 filter/selectivity가 문제인가?
• nested loop 반복 횟수나 key lookup 횟수가 과도한가?
• sort/aggregate가 memory를 넘어서 spill했는가?
• index 추가 없이 query rewrite, 통계 갱신, predicate 정리, batch 범위 축소로 해결 가능한가?
• index를 추가한다면 기존 index와 중복되지 않는가?
• 튜닝 후 p95/p99 latency, total CPU, logical reads, write latency, replication lag를 모두 확인했는가?

실행계획은 정답지가 아니라 디버깅 지도다. 지도를 잘 읽는다는 것은 scan을 무조건 없애거나 hash join을 피하는 것이 아니다. DB가 어느 단계에서 많이 읽고, 많이 반복하고, 많이 정렬하고, 많이 잘못 예측했는지를 찾아서 가장 작은 변경으로 일을 줄이는 것이다.

References

• PostgreSQL Documentation, “14.1. Using EXPLAIN” — https://www.postgresql.org/docs/current/using-explain.html
• PostgreSQL Documentation, “EXPLAIN” — https://www.postgresql.org/docs/current/sql-explain.html
• MySQL 8.4 Reference Manual, “10.8.2 EXPLAIN Output Format” — https://dev.mysql.com/doc/refman/8.4/en/explain-output.html
• Microsoft Learn, “Logical and Physical Showplan Operator Reference” — https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference
• Markus Winand, “SQL Server execution plan operations — Use The Index, Luke” — https://use-the-index-luke.com/sql/explain-plan/sql-server/operations