실행 계획으로 쿼리 성능 읽기

2026-06-05Views 21약 7분생성형 AI 활용

Summary

실행 계획을 읽는 이유

느린 쿼리를 만났을 때 가장 위험한 대응은 "인덱스를 하나 더 만들면 빨라지겠지"라고 추측하는 것이다. 어떤 쿼리는 인덱스가 없어서 느리지만, 어떤 쿼리는 너무 많은 행을 정렬해서 느리고, 어떤 쿼리는 조인 순서가 틀어져서 느리다. 원인을 구분하려면 PostgreSQL이 쿼리를 어떤 순서와 방법으로 실행하려 했는지 확인해야 한다.

이때 사용하는 도구가 EXPLAIN이다.

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 10;

EXPLAIN은 실제 데이터를 바꾸거나 결과를 모두 반환하지 않고, PostgreSQL이 선택한 실행 계획을 보여준다. 여기에 ANALYZE를 붙이면 쿼리를 실제로 실행하고 각 단계의 실제 시간과 실제 행 수를 함께 보여준다. 즉, EXPLAIN은 예상이고 EXPLAIN ANALYZE는 실행 후 측정이다.¹

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

BUFFERS는 공유 버퍼에서 읽었는지, 디스크에서 읽었는지 같은 입출력 단서를 보여준다. 느린 쿼리를 볼 때는 보통 EXPLAIN (ANALYZE, BUFFERS)를 사용한다.

주의할 점이 있다. EXPLAIN ANALYZE는 쿼리를 실제로 실행한다. SELECT는 괜찮지만 UPDATE, DELETE, INSERT에 붙이면 실제 변경이 일어난다. 변경 쿼리를 분석할 때는 트랜잭션으로 감싼 뒤 되돌린다.

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE products
SET price = price * 1.1
WHERE category_id = 3;

ROLLBACK;

실행 계획으로 쿼리 성능 읽기 AI 생성 이미지

실행 계획의 기본 읽기

다음은 고객별 주문을 찾는 간단한 쿼리의 실행 계획 예다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, customer_id, ordered_at, total_amount
FROM orders
WHERE customer_id = 10
ORDER BY ordered_at DESC
LIMIT 20;

예상 출력은 환경마다 다르지만 형태는 대략 다음과 비슷하다.

Limit  (cost=0.42..18.80 rows=20 width=32)
  ->  Index Scan using idx_orders_customer_ordered_at on orders
        (cost=0.42..920.15 rows=1001 width=32)
        Index Cond: (customer_id = 10)

계획은 들여쓰기로 된 트리다. 안쪽에 있는 노드가 먼저 실행되고, 그 결과가 바깥 노드로 전달된다. 위 계획에서는 Index Scan으로 orders에서 고객의 주문을 찾고, 바깥의 Limit이 20건만 반환한다.

cost=0.42..18.80에서 앞 숫자는 첫 행을 반환하기까지의 예상 비용이고, 뒤 숫자는 모든 행을 반환할 때의 예상 비용이다. 비용은 밀리초가 아니라 PostgreSQL 내부의 상대적인 단위다. rows=20은 예상 행 수, width=32는 한 행의 평균 크기 추정치다.

EXPLAIN ANALYZE를 사용하면 다음 정보가 추가된다.

(actual time=0.040..0.210 rows=20 loops=1)

actual time은 실제 걸린 시간, rows는 실제 반환 행 수, loops는 해당 노드가 반복 실행된 횟수다. 특히 loops가 크면 작은 작업처럼 보이는 노드도 전체 비용이 커질 수 있다.

자주 만나는 실행 노드

실행 계획을 처음 볼 때는 모든 항목을 외우려 하지 말고, 자주 만나는 노드부터 구분하면 된다.

Seq Scan은 테이블을 처음부터 끝까지 읽는 방식이다. 작은 테이블이거나 대부분의 행을 읽어야 한다면 정상적인 선택이다. 하지만 수백만 행 테이블에서 결과가 몇 건뿐인데 Seq Scan이 나온다면 인덱스 부재, 조건식 문제, 통계 오류를 의심한다.

Seq Scan on orders
  Filter: (status = 'paid')

Index Scan은 인덱스를 따라 조건에 맞는 행을 찾고, 필요한 열을 테이블에서 읽는 방식이다. 조건에 맞는 행이 적을수록 유리하다.

Index Scan using idx_orders_customer_id on orders
  Index Cond: (customer_id = 10)

Bitmap Heap Scan은 조건에 맞는 위치를 인덱스로 먼저 모은 뒤, 테이블 페이지를 묶어서 읽는 방식이다. 한두 건이 아니라 어느 정도 많은 행을 읽을 때 자주 나온다. 보통 Bitmap Index Scan과 함께 나타난다.

Bitmap Heap Scan on orders
  Recheck Cond: (status = 'paid')
  ->  Bitmap Index Scan on idx_orders_status
        Index Cond: (status = 'paid')

조인에서는 세 가지 이름을 자주 본다.

Nested Loop는 바깥쪽 결과 한 행마다 안쪽 테이블을 반복해서 찾는 방식이다. 바깥쪽 행이 적고 안쪽을 인덱스로 빠르게 찾을 수 있으면 좋다. 반대로 바깥쪽 행이 많은데 안쪽도 반복 스캔하면 매우 느려진다.

Hash Join은 한쪽 입력으로 해시 테이블을 만들고 다른 쪽 입력을 대조하는 방식이다. 많은 행을 동등 조건으로 조인할 때 자주 선택된다. 메모리가 부족하면 임시 파일을 사용할 수 있어 느려질 수 있다.

Merge Join은 양쪽 입력이 조인 키 순서로 정렬되어 있을 때 순서대로 병합하는 방식이다. 이미 인덱스 순서로 읽을 수 있거나 정렬 비용이 감당 가능할 때 사용된다.

정렬이 보이면 Sort 노드를 확인한다.

Sort
  Sort Key: ordered_at DESC

정렬 대상 행이 많으면 비용이 크다. 특히 ORDER BY ordered_at DESC LIMIT 20처럼 상위 몇 건만 필요하다면, 정렬을 피할 수 있는 인덱스가 있는지 확인한다.

CREATE INDEX idx_orders_status_ordered_at
ON orders (status, ordered_at DESC);

이 인덱스가 있으면 status = 'paid' 조건을 만족하는 행을 이미 최신순에 가까운 순서로 읽을 수 있어, 전체 정렬 없이 LIMIT 20에서 멈출 가능성이 커진다.

예상 행 수와 실제 행 수가 어긋날 때

실행 계획에서 가장 먼저 비교할 값은 예상 행 수와 실제 행 수다.

Nested Loop  (cost=0.71..120.50 rows=10 width=80)
             (actual time=0.050..820.300 rows=120000 loops=1)

PostgreSQL은 통계 정보를 바탕으로 "이 조건이면 몇 행 정도 나오겠다"고 추정한다. 이 추정이 크게 틀리면 잘못된 조인 방식이나 잘못된 인덱스 선택으로 이어진다. 위 예처럼 예상은 10행인데 실제는 120,000행이면, PostgreSQL은 작은 결과라고 판단해 Nested Loop를 골랐을 수 있다.

통계가 낡았을 때는 ANALYZE로 갱신한다.

ANALYZE orders;
ANALYZE order_items;

대량 입력, 대량 삭제, 상태값 분포 변화가 있었는데 아직 자동 통계 갱신이 일어나지 않았다면 수동 ANALYZE가 도움이 된다. PostgreSQL 문서도 신뢰할 수 있는 계획 추정을 위해 테이블 통계가 최신이어야 한다고 설명한다.¹

다만 통계 갱신만으로 해결되지 않는 경우도 있다. 예를 들어 status와 ordered_at이 강하게 연결되어 있을 수 있다. 최근 주문은 대부분 paid이고 오래된 주문은 cancelled가 많다면, 각 열을 따로 본 통계만으로는 조합 조건의 선택도를 정확히 맞히기 어렵다. 이런 경우에는 쿼리 패턴에 맞는 복합 인덱스나 확장 통계, 또는 데이터 모델 조정까지 검토한다.

LIMIT, 정렬, 조인 순서가 계획을 바꾸는 방식

LIMIT은 단순히 마지막에 결과를 자르는 기능처럼 보이지만 실행 계획에는 큰 영향을 준다.

SELECT order_id, ordered_at
FROM orders
WHERE status = 'paid'
ORDER BY ordered_at DESC
LIMIT 20;

적절한 인덱스가 없으면 PostgreSQL은 조건에 맞는 많은 주문을 읽고 정렬한 뒤 20건만 반환한다.

Limit
  ->  Sort
        Sort Key: ordered_at DESC
        ->  Seq Scan on orders
              Filter: (status = 'paid')

반대로 (status, ordered_at DESC) 인덱스가 있으면 필요한 순서대로 읽다가 20건에서 멈출 수 있다.

Limit
  ->  Index Scan using idx_orders_status_ordered_at on orders
        Index Cond: (status = 'paid')

조인 쿼리에서도 시작점이 중요하다.

SELECT o.order_id, c.email, o.total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.email = '[email protected]'
ORDER BY o.ordered_at DESC
LIMIT 20;

customers.email이 유니크하고 인덱스가 있다면 먼저 고객 1명을 찾고, 그 고객의 주문을 최신순으로 읽는 계획이 자연스럽다.

CREATE UNIQUE INDEX idx_customers_email ON customers (email);
CREATE INDEX idx_orders_customer_ordered_at
ON orders (customer_id, ordered_at DESC);

이 구조에서는 Nested Loop가 좋은 선택일 수 있다. 바깥쪽 고객이 1행이고, 안쪽 주문을 인덱스로 찾기 때문이다. 같은 Nested Loop라도 바깥쪽 행이 1행인지 100만 행인지에 따라 의미가 완전히 달라진다.

서브쿼리와 CTE는 항상 같은 비용이 아니다

서브쿼리나 CTE를 쓰면 쿼리를 읽기 쉽게 나눌 수 있다. 하지만 실행 계획이 달라질 수 있으므로 결과만 보지 말고 계획도 확인해야 한다.

WITH paid_orders AS (
  SELECT *
  FROM orders
  WHERE status = 'paid'
)
SELECT *
FROM paid_orders
WHERE ordered_at >= timestamp '2026-05-01';

PostgreSQL 12 이후의 일반적인 비재귀 CTE는 한 번만 참조되고 부작용이 없으면 바깥 쿼리와 합쳐져 최적화될 수 있다. 하지만 여러 번 참조되는 CTE는 물질화되어 임시 결과처럼 다뤄질 수 있다. 이 경우 바깥 조건이 안쪽 테이블까지 밀려 들어가지 못해 인덱스 활용이 나빠질 수 있다.²

WITH paid_orders AS MATERIALIZED (
  SELECT *
  FROM orders
  WHERE status = 'paid'
)
SELECT *
FROM paid_orders
WHERE ordered_at >= timestamp '2026-05-01';

MATERIALIZED는 CTE를 별도로 계산하도록 강제한다. 반복 계산을 막는 데 도움이 될 수 있지만, 조건 푸시다운을 막아 느려질 수도 있다. 반대로 NOT MATERIALIZED는 바깥 쿼리와 합쳐 최적화할 여지를 준다.

WITH paid_orders AS NOT MATERIALIZED (
  SELECT *
  FROM orders
  WHERE status = 'paid'
)
SELECT *
FROM paid_orders
WHERE ordered_at >= timestamp '2026-05-01';

CTE를 성능 장벽으로 오해해서 무조건 피할 필요는 없다. 기준은 실행 계획이다. CTE 덕분에 비싼 계산을 한 번만 수행하면 이득이고, CTE 때문에 필요한 조건이 늦게 적용되면 손해다.

느린 검색 쿼리 개선 과정

게시판 검색 화면을 예로 보자. 사용자는 공개된 글 중 제목에 특정 단어가 들어간 최신 글 30개를 본다.

SELECT post_id, title, created_at
FROM posts
WHERE is_deleted = false
  AND title ILIKE '%postgres%'
ORDER BY created_at DESC
LIMIT 30;

처음 실행 계획이 다음과 같다고 하자.

Limit
  ->  Sort
        Sort Key: created_at DESC
        ->  Seq Scan on posts
              Filter: ((NOT is_deleted) AND (title ~~* '%postgres%'::text))

문제는 세 가지다. 첫째, ILIKE '%postgres%'는 일반 B-tree 인덱스로 찾기 어렵다. 둘째, 삭제되지 않은 글이 많아 Seq Scan 범위가 크다. 셋째, 조건을 통과한 행을 다시 정렬한다.

검색어가 중간 포함 검색이어야 한다면 일반 인덱스 추가보다 전문 검색이나 trigram 인덱스를 검토한다. 확장 기능을 사용할 수 있다면 다음처럼 접근할 수 있다.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_posts_title_trgm_active
ON posts USING gin (title gin_trgm_ops)
WHERE is_deleted = false;

이후 다시 확인한다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT post_id, title, created_at
FROM posts
WHERE is_deleted = false
  AND title ILIKE '%postgres%'
ORDER BY created_at DESC
LIMIT 30;

계획은 대략 Bitmap Index Scan과 Bitmap Heap Scan으로 바뀔 수 있다. 제목 조건으로 후보를 줄인 뒤 테이블을 읽는 방식이다. 하지만 후보가 여전히 많으면 정렬 비용이 남는다.

이때 개선 방향은 업무 요구에 따라 달라진다.

검색어 후보가 적다면 trigram 인덱스만으로 충분하다. 최신순 정렬 비용이 크다면 검색 결과를 별도 검색 엔진으로 보내거나, PostgreSQL 전문 검색 구조로 바꾸는 것이 나을 수 있다. 사용자가 실제로는 최근 1년 글만 검색한다면 조건을 명시해 범위를 줄인다.

SELECT post_id, title, created_at
FROM posts
WHERE is_deleted = false
  AND created_at >= current_date - interval '1 year'
  AND title ILIKE '%postgres%'
ORDER BY created_at DESC
LIMIT 30;

또 다른 방법은 데이터 모델을 조정하는 것이다. 게시판 제목 검색이 핵심 기능이라면 단순 title 열만 두지 말고, 검색용 tsvector 열을 만들거나 별도 검색 테이블을 둘 수 있다. 이 판단은 "인덱스를 더 만들 것인가"가 아니라 "이 화면이 어떤 검색을 안정적으로 제공해야 하는가"에서 출발해야 한다.

마지막으로 통계를 갱신하고 실제 행 수를 다시 비교한다.

ANALYZE posts;

개선 전후를 볼 때는 총 실행 시간만 보지 않는다. 다음 네 가지를 함께 본다.

Seq Scan이 필요한 범위인지, 피할 수 있는 전체 스캔인지
예상 rows와 실제 rows가 크게 어긋나는지
Sort, Hash, 임시 파일 사용처럼 메모리 부담이 큰 노드가 있는지
loops가 큰 노드 안에서 비싼 스캔이 반복되는지

실행 계획은 정답지가 아니라 진단 기록이다. 같은 쿼리라도 데이터 양, 값의 분포, 인덱스, 통계 상태에 따라 계획이 달라진다. 그래서 튜닝의 순서는 항상 같다. 먼저 EXPLAIN (ANALYZE, BUFFERS)로 실제 병목을 찾고, 그다음 인덱스 추가, 조건 재작성, 통계 갱신, 데이터 모델 조정 중 가장 근본적인 방법을 선택한다.

참고

¹PostgreSQL EXPLAIN 문서

²PostgreSQL WITH Queries 문서

책 목록

PostgreSQL 입문부터 실전 운영까지

전체 목차 보기