백엔드 개발자라면 누구나 한 번쯤 "API 응답이 너무 느리다"는 사용자나 프론트엔드 팀의 피드백을 받게 됩니다. 병목의 원인을 추적하다 보면 십중팔구 복잡한 데이터베이스 조회 로직, 즉 '느린 쿼리(Slow Query)'가 원인인 경우가 많습니다. 이때 많은 개발자들이 직감에 의존하여 아무 컬럼에나 인덱스(Index)를 추가하거나 쿼리 문법을 이리저리 수정해 보는 식의 '찍어 맞추기'식 튜닝을 시도하곤 합니다.

하지만 데이터베이스 시스템은 사용자가 작성한 SQL을 그대로 실행하지 않습니다. 내부적으로 쿼리 옵티마이저(Query Optimizer)가 다양한 실행 전략을 평가하고 가장 비용이 낮을 것으로 예상되는 접근 계획(Access-plan)을 선택하여 실행합니다[1]. 옵티마이저가 데이터를 어떻게 읽고, 어떻게 조인(Join)하는지 정확히 알지 못하면 성능 최적화는 불가능에 가깝습니다.

이러한 블랙박스를 열어볼 수 있는 가장 강력한 도구가 바로 PostgreSQL의 EXPLAIN ANALYZE 명령어입니다. 본 포스트에서는 데이터베이스가 쿼리를 처리하는 수학적, 구조적 원리를 기반으로 EXPLAIN ANALYZE의 출력 결과를 읽고, 실전에서 쿼리 성능을 어떻게 튜닝해야 하는지 깊이 있게 파헤쳐 보겠습니다.

---

1. EXPLAIN과 EXPLAIN ANALYZE의 본질적 차이

단순히 결론부터 말하자면 쿼리 앞에 EXPLAIN ANALYZE를 붙이면 됩니다. 하지만 왜 그래야 할까요?

EXPLAIN 명령어는 데이터베이스 옵티마이저가 통계 정보를 바탕으로 생성한 예상 실행 계획(Estimated Execution Plan)만을 보여줍니다. 테이블에 데이터가 몇 건 있는지, 인덱스 트리의 깊이가 어느 정도인지에 대한 메타데이터를 기반으로 "아마 이 방식이 제일 빠를 것"이라고 추측한 결과입니다.

반면, EXPLAIN ANALYZE는 이 예상 계획을 실제로 데이터베이스 엔진에서 실행(Execute)한 뒤, 실제 소요된 시간(Actual time)과 처리된 행(Row)의 수를 예상치와 함께 비교하여 보여줍니다. 통계 정보가 오래되어 데이터베이스가 잘못된 실행 계획을 수립하는 상황을 잡아내려면, 반드시 쿼리를 실제 실행해 보는 ANALYZE 옵션이 필수적입니다.

---

2. 실행 계획을 읽는 절대 원칙: 안에서 밖으로, 아래에서 위로

EXPLAIN ANALYZE를 실행하면 들여쓰기(Indentation)가 포함된 트리(Tree) 구조의 텍스트가 출력됩니다. 처음 보는 개발자에게는 암호문 같겠지만, 딱 두 가지 원칙만 기억하면 됩니다. 가장 안쪽으로 들여쓰기 된 노드부터 실행되며, 동일한 깊이에서는 아래에서 위로 실행된다는 것입니다.

실제 출력 예시를 통해 핵심 지표들을 해석해 봅시다.

Hash Join  (cost=45.00..105.50 rows=100 width=40) (actual time=2.100..4.500 rows=95 loops=1)
  Hash Cond: (users.id = orders.user_id)
  ->  Seq Scan on users  (cost=0.00..20.00 rows=1000 width=20) (actual time=0.010..0.500 rows=1000 loops=1)
  ->  Hash  (cost=30.00..30.00 rows=500 width=20) (actual time=1.500..1.500 rows=500 loops=1)
        ->  Seq Scan on orders  (cost=0.00..30.00 rows=500 width=20) (actual time=0.015..1.000 rows=500 loops=1)
Plain Text
복사

이 실행 계획은 다음 순서로 해석해야 합니다.

orders 테이블을 먼저 스캔합니다 (가장 깊은 들여쓰기).

스캔한 orders 데이터를 메모리에 올려 해시(Hash) 테이블을 만듭니다.

users 테이블을 스캔합니다.

users의 각 행을 읽을 때마다 해시 테이블을 탐색하여 해시 조인(Hash Join)을 수행합니다.

괄호 안의 핵심 지표 해석

각 노드(Node)의 괄호 안에는 성능 분석의 핵심이 되는 숫자들이 있습니다.

•

cost (비용): 초기 준비 비용 .. 전체 완료 비용의 형태를 띱니다. 단위는 밀리초(ms)가 아니라, 디스크 페이지(Block)를 한 번 읽는 데 드는 임의의 단위입니다.

•

actual time (실제 시간): 밀리초(ms) 단위입니다. 0.015..1.000이라면, 첫 번째 행을 찾는 데 0.015ms가 걸렸고, 500개의 행을 모두 스캔하여 처리하는 데 총 1.000ms가 걸렸다는 의미입니다.

•

rows (행 수): 옵티마이저가 예상한 행 수와 actual ... rows=... 부분에 출력된 실제 반환된 행 수입니다. 예상 rows와 실제 rows의 차이가 크다면 통계 정보가 낡았다는 뜻이므로 ANALYZE 테이블명; 명령어를 통해 통계 정보를 갱신해야 합니다.

---

3. 디스크 I/O를 지배하는 데이터 접근 방식(Scan Methods)

데이터베이스 성능 병목의 90% 이상은 디스크 I/O에서 발생합니다. EXPLAIN ANALYZE 출력에서 데이터를 어떻게 읽어오는지 나타내는 스캔 방식은 튜닝의 가장 중요한 단서가 됩니다.

Seq Scan (Full Table Scan)

테이블의 처음부터 끝까지 모든 디스크 블록을 순차적으로 읽어 들입니다. 개발자들이 가장 두려워하는 단어이지만, 항상 나쁜 것은 아닙니다. 전체 데이터의 20~30% 이상을 읽어야 하거나 테이블 크기가 매우 작은 경우, 무작위 I/O(Random I/O)를 발생시키는 인덱스 스캔보다 한 번에 디스크를 긁어오는 Seq Scan이 운영체제의 미리 읽기(Read-ahead) 버퍼 메커니즘 덕분에 더 빠를 수 있습니다. 하지만 대용량 테이블에서 조건 필터링에 Seq Scan이 발생한다면 반드시 인덱스 추가를 고려해야 합니다.

Index Scan 과 B-Tree의 원리

Index Scan은 B+-tree 구조의 인덱스 확장 기능을 사용하여 조건을 만족하는 데이터를 찾습니다[2]. 인덱스 트리를 루트(Root)부터 리프(Leaf) 노드까지 탐색하여 데이터가 저장된 물리적 포인터를 찾은 뒤, 실제 테이블(Heap)에 접근하여 나머지 컬럼 데이터를 가져옵니다.

Index Only Scan (커버링 인덱스)

쿼리의 SELECT 절과 WHERE 절에 포함된 모든 컬럼이 이미 하나의 인덱스에 포함되어 있을 때 발생하는 가장 이상적인 스캔 방식입니다. 테이블에 접근하지 않고 인덱스 자체만으로 쿼리 결과를 반환할 수 있으므로 극단적인 성능 최적화를 달성할 수 있습니다[3]. 작업 세트(Working Set)가 RAM에 완전히 적재되어 있다면 물리적 디스크 I/O가 아예 발생하지 않을 수도 있습니다[3].

---

4. 조인(Join) 알고리즘의 원리와 병목 지점

복잡한 서비스 로직에서는 여러 테이블을 결합하는 조인 연산이 필수적입니다. 데이터베이스 엔진이 선택하는 조인 알고리즘에 따라 쿼리의 성능은 수십 배씩 차이 날 수 있습니다.

Nested Loop Join (중첩 루프 조인)

가장 기본적이고 직관적인 조인입니다. 외부 테이블(Outer Table)에서 행을 하나 읽을 때마다 내부 테이블(Inner Table)을 전체 스캔하며 일치하는 조건을 찾습니다.

문제 상황: 외부 테이블에 1만 건이 있고, 내부 테이블에 10만 건이 있는데 조인 키에 인덱스가 없다면

10,000 \times 100,000 = 10억

번의 비교 연산이 발생합니다. 실행 계획에 Nested Loop가 보이고 하위 노드에 내부 테이블에 대한 Seq Scan이 보인다면, 내부 테이블의 조인 조건 컬럼에 즉시 인덱스를 생성해야 합니다.

Hash Join (해시 조인)

대용량 데이터 집합을 정렬되지 않은 상태에서 조인할 때 탁월한 성능을 발휘합니다[1]. 해시 파일 구성(Hash file organization) 원리를 응용하여[2], 조인할 두 테이블 중 작은 테이블을 메모리(Work_mem)에 올려 해시 테이블 구조(버킷과 오버플로우 체이닝)로 구축한 뒤, 큰 테이블을 한 번만 스캔하며 해시 함수로 매칭되는 데이터를 찾습니다.

만약 실행 계획에서 해시를 만드는 데 시간이 너무 오래 걸리거나 디스크(Temp)를 사용한다는 경고가 보인다면, PostgreSQL 설정에서 work_mem 크기를 늘려주는 것을 고려해야 합니다.

Merge Join (정렬-병합 조인)

조인에 참여하는 양쪽 데이터가 이미 조인 키를 기준으로 정렬되어 있을 때 사용됩니다. 데이터 집합을 각각 스캔하면서 병합(Merge)만 하면 되기 때문에[4] 매우 효율적입니다. 만약 조인 키 컬럼에 B-Tree 인덱스가 걸려 있다면 데이터가 이미 유용한 정렬 순서(Interesting sort order)를 가지고 있으므로[1] 옵티마이저가 추가적인 비용 없이 즉시 Merge Join을 수행할 수 있습니다.

---

5. 실전 시나리오: EXPLAIN ANALYZE로 쿼리 구조 개선하기

실제 온라인 쇼핑몰의 '배송 준비 중' 상태인 주문 목록을 조회하는 쿼리가 3초나 걸리는 상황을 가정해 보겠습니다.

EXPLAIN ANALYZE 
SELECT o.order_id, o.order_date, u.email 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.status = 'PREPARING';
Plain Text
복사

튜닝 전 실행 계획 출력:

Hash Join  (cost=150.00..5500.00 rows=5000 width=35) (actual time=500.100..3100.500 rows=15 loops=1)
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o  (cost=0.00..4500.00 rows=5000 width=20) (actual time=0.050..2900.000 rows=15 loops=1)
        Filter: ((status)::text = 'PREPARING'::text)
        Rows Removed by Filter: 999985
  ->  Hash  (cost=100.00..100.00 rows=10000 width=23) (actual time=100.000..100.000 rows=10000 loops=1)
        ->  Seq Scan on users u  (cost=0.00..100.00 rows=10000 width=23) (actual time=0.010..50.000 rows=10000 loops=1)
Plain Text
복사

실행 계획 분석 및 진단

가장 치명적인 병목 발견: orders 테이블에 대한 Seq Scan 부분을 보면 actual time이 무려 2900ms입니다. 전체 3100ms의 실행 시간 중 대부분을 차지합니다.

Filter 성능 저하: Filter: status = 'PREPARING' 조건을 검사하기 위해 100만 건의 데이터를 읽었고, 그중 999,985건을 버렸습니다(Rows Removed by Filter). 고작 15건을 찾기 위해 전체 테이블을 디스크에서 읽어오는 막대한 I/O 낭비가 발생한 것입니다.

예상과 실제의 괴리: 옵티마이저는 PREPARING 상태의 주문이 5000건(예상 rows=5000)일 것이라 예상하고 큰 데이터를 처리하기 좋은 Hash Join을 선택했지만, 실제로는 15건밖에 없었습니다.

최적화 전략 적용 및 결과 검증

원인이 명확해졌습니다. orders 테이블의 status 컬럼에 대한 접근을 고속화해야 합니다. B-Tree 인덱스를 추가합니다.

CREATE INDEX idx_orders_status ON orders(status);
Plain Text
복사

인덱스 생성 후 EXPLAIN ANALYZE 출력:

Nested Loop  (cost=4.50..55.00 rows=15 width=35) (actual time=0.025..0.150 rows=15 loops=1)
  ->  Index Scan using idx_orders_status on orders o  (cost=0.20..15.00 rows=15 width=20) (actual time=0.015..0.050 rows=15 loops=1)
        Index Cond: ((status)::text = 'PREPARING'::text)
  ->  Index Scan using users_pkey on users u  (cost=0.15..8.00 rows=1 width=23) (actual time=0.005..0.005 rows=1 loops=15)
        Index Cond: (id = o.user_id)
Plain Text
복사

단순히 Seq Scan이 Index Scan으로 바뀌었을 뿐만 아니라 쿼리의 실행 시간(actual time)이 3초에서 0.15ms로 비약적으로 단축되었습니다!

또한, 찾아야 할 데이터가 15건뿐이라는 것을 인덱스를 통해 알게 된 옵티마이저가, 무거운 Hash Join 대신 적은 데이터 집합에 유리한 Nested Loop 조인 방식으로 실행 계획을 스마트하게 변경한 것을 확인할 수 있습니다. 기본키(users_pkey)를 이용해 내부 테이블을 빠르게 15번 찔러보는 방식으로 바뀐 것입니다.

---

결론 및 요약

데이터베이스의 성능 최적화는 '인덱스를 걸면 빨라진다'는 단순한 결론으로 귀결되지 않습니다. B-Tree 구조의 특성과 Hash 기반 알고리즘 등 컴퓨터 과학의 전공 지식이 집약된 결정체가 바로 DBMS의 쿼리 옵티마이저입니다.

EXPLAIN ANALYZE는 단순히 쿼리가 느리다는 현상을 넘어, 왜(Why) 느린지에 대한 명확한 근거를 제시합니다.

트리의 가장 깊숙한 곳부터 실행 흐름을 파악하십시오.

Seq Scan 단계에서 Rows Removed by Filter가 비정상적으로 높다면 필터링 효율을 높일 인덱스를 고민하십시오.

조인 단계에서 Nested Loop에 내부 테이블 인덱스가 누락되었는지 확인하고, 데이터 양에 따라 Hash Join 메모리(work_mem) 튜닝을 고려하십시오.

Index Only Scan을 유도하여 디스크 I/O를 원천 차단하는 커버링 인덱스를 적절히 활용하십시오.

실행 계획을 읽는 눈을 기르는 것은 단순한 감을 넘어, 데이터베이스의 내부 동작 원리와 수학적 비용 계산 모델을 이해하는 아키텍트 수준의 역량을 의미합니다. 지속해서 쿼리의 실행 비용을 모니터링하고 튜닝하는 습관이야말로 가장 신뢰할 수 있고 빠른(People-first) 백엔드 시스템을 설계하는 척도가 될 것입니다.

참고문헌

[1] Database-System-Concepts-7th-Edition — ° Dynamic-programming algorithm ° Left-deep join order ° Interesting sort order Heuristic optimization Plan caching Access-plan selection Practice Exercises 789 Correlated evaluation Decorrelation Semijoin Anti-semijoin Materialized views Materialized view maintenance ° Recomputation ° Incremental m…

[2] Database-System-Concepts-7th-Edition — Practice Exercises 679 ° Nonleaf nodes ° Internal nodes ° Range queries ° Node split ° Node coalesce ° Redistribute of pointers ° Uniquifier B+-tree extensions ° Prefix compression ° Bulk loading ° Bottom-up B+-tree construction B-tree indices Hash file organization ° Hash function ° Bucket ° Overfl…

[3] fastquerying-indexingforperformance4 — Ensure indexes fit in RAM Index Types & Properties Indexing Basics // Create index on author (ascending) >db.articles.ensureIndex( { author : 1 } ) // Create index on author (descending) >db.articles.ensureIndex( { author : -1 } ) // Create index on arrays of values on the "tags" field – multi key i…

[4] designing-data-intensive-applications — synchronized clocks for global snapshots, 294 snowflake schemas, 95 SOAP, 133 (see also services) evolvability, 136 software bugs, 8 maintaining integrity, 529 solid state drives (SSDs) access patterns, 84 detecting corruption, 519, 530 faults in, 227 sequential write throughput, 75 Solr (search ser…