PostgreSQL 쿼리 성능 분석: EXPLAIN ANALYZE로 성능 병목 현상 진단하기

소개

관계형 데이터베이스 세계에서 느린 쿼리는 애플리케이션 응답성과 사용자 경험에 영향을 미치는 상당한 병목 현상이 될 수 있습니다. 이러한 성능 문제의 근본 원인을 식별하는 것은 종종 건초 더미 속에서 바늘 찾기와 같습니다. 다행히 PostgreSQL은 EXPLAIN ANALYZE라는 강력한 도구를 제공하여 데이터베이스가 주어진 쿼리를 정확히 어떻게 실행하는지 보여주는 상세한 진단 X-레이 역할을 합니다. 이 출력을 이해하는 것은 효율적인 SQL을 작성하고 데이터베이스 성능을 최적화하려는 모든 개발자 또는 DBA에게 매우 중요합니다. 이 글에서는 EXPLAIN ANALYZE의 실질적인 적용 방법을 안내하고, 해당 출력을 명확하게 설명하며, 정보에 입각한 최적화 결정을 내릴 수 있도록 지원합니다.

쿼리 실행 계획 이해하기

EXPLAIN ANALYZE를 자세히 살펴보기 전에 몇 가지 기본 개념을 명확히 해 봅시다.

핵심 용어

• 쿼리 최적화기 (Query Optimizer): 주어진 SQL 쿼리에 대해 가장 효율적인 실행 계획을 생성하는 데이터베이스 관리 시스템(DBMS)의 구성 요소입니다. 데이터 분포, 사용 가능한 인덱스, 테이블 통계 등 다양한 요소를 고려하여 최상의 전략을 선택합니다.
• 실행 계획 (Execution Plan): 데이터베이스가 쿼리를 실행할 단계별 설명입니다. 각 노드가 연산(예: 스캔, 조인, 정렬)을 나타내고 해당 분기가 연산 간의 데이터 흐름을 나타내는 트리 구조입니다.
• EXPLAIN: 문장에 대한 계획된 실행 전략을 보여주는 PostgreSQL 명령입니다. 쿼리를 실제로 실행하지 않고 비용(CPU 주기, 디스크 I/O 등)을 추정합니다.
• EXPLAIN ANALYZE: 쿼리를 실제로 실행한 다음 해당 실행 계획과 함께 각 단계에 대한 실제 통계(실행 시간, 처리된 행 수, 루프 횟수 등)를 제공하는 EXPLAIN의 향상된 버전입니다. 이 "실제 대 예상" 비교는 불일치 및 성능 병목 현상을 식별하는 데 중요합니다.
• 노드/연산 (Node/Operation): 실행 계획 트리의 각 단계입니다. 일반적인 연산은 다음과 같습니다.
  • 순차 스캔 (Sequential Scan): 테이블의 모든 행을 읽습니다.
  • 인덱스 스캔 (Index Scan): 인덱스를 사용하여 특정 행을 효율적으로 검색합니다.
  • 비트맵 힙 스캔 (Bitmap Heap Scan): 두 단계 프로세스입니다. 먼저 인덱스를 사용하여 페이지 포인터를 찾고(비트맵 인덱스 스캔), 그런 다음 해당 페이지를 힙(테이블 데이터)에서 가져옵니다.
  • 조인 유형 (Join Types - Nested Loop, Hash Join, Merge Join): 두 개 이상의 테이블에서 행을 결합하는 전략입니다.
  • 정렬 (Sort): 지정된 열에 따라 행을 정렬합니다.
  • 집계 (Aggregate): 집계 함수(예: SUM, COUNT, AVG)를 수행합니다.
• 비용 (Cost): 연산의 상대적인 비용을 나타내는 단위 없는 예상 지표입니다. CPU 비용과 디스크 I/O를 나타냅니다. 일반적으로 비용이 낮을수록 좋습니다. 실행 계획은 일반적으로 (cost=start..total rows=count width=bytes)를 표시합니다.
  • start: 첫 번째 행이 반환될 수 있는 예상 비용입니다.
  • total: 모든 행을 반환하는 데 드는 총 예상 비용입니다.
• 행 (Rows): 연산에서 처리하거나 반환한 예상 행 수입니다.
• 너비 (Width): 연산에서 처리한 행의 평균 너비(바이트)입니다.
• 실제 시간 (Actual Time): 연산의 실제 경과 시간(밀리초)입니다. EXPLAIN ANALYZE의 경우 (actual time=start..total rows=count loops=num_loops) 형식으로 표시됩니다.
  • start: 첫 번째 행이 반환될 때까지의 실제 시간입니다.
  • total: 모든 행을 검색하는 데 걸린 총 실제 시간입니다.
• 루프 (Loops): 특정 연산이 실행된 횟수입니다. 이는 루프 내의 연산, 특히 중첩 루프 조인의 내부 측에 특히 유용합니다.

EXPLAIN ANALYZE 작동 방식

EXPLAIN ANALYZE를 SQL 쿼리 앞에 붙이면 PostgreSQL은 다음을 수행합니다.

1. 쿼리 실행: 데이터베이스는 일반적으로 쿼리를 실행합니다.
2. 통계 수집: 실행 중에 선택된 실행 계획의 각 단계에 대한 자세한 타이밍 및 행 수 통계를 가져옵니다.
3. 계획 및 통계 출력: 마지막으로 수집된 실제 통계와 함께 실행 계획을 제시합니다. 예상 값과 실제 값의 이러한 나란히 비교가 마법이 발생하는 곳입니다. 큰 불일치는 종종 누락되거나 오래된 통계, 또는 최적이 아닌 쿼리 계획을 가리킵니다.

실제 적용 및 예제

가상의 users 테이블과 orders 테이블을 사용하여 몇 가지 실제 예제를 살펴보겠습니다.

-- 이러한 테이블이 존재한다고 가정합니다:
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    amount DECIMAL(10, 2) NOT NULL,
    order_date TIMESTAMP DEFAULT NOW(),
    status VARCHAR(50)
);

-- 일부 데이터로 채우기
INSERT INTO users (name, email)
SELECT 'User ' || i, 'user' || i || '@example.com' FROM generate_series(1, 100000) i;

INSERT INTO orders (user_id, amount, status)
SELECT TRUNC(random() * 100000) + 1, random() * 1000, 
       CASE WHEN random() < 0.5 THEN 'completed' ELSE 'pending' END
FROM generate_series(1, 500000) i;

-- 나중에 효과를 보여주기 위해 인덱스 추가
CREATE INDEX idx_orders_order_date ON orders (order_date);
CREATE INDEX idx_users_email ON users (email);

예제 1: 간단한 Select - 순차 스캔

모든 사용자를 선택하는 간단한 쿼리를 분석해 보겠습니다.

EXPLAIN ANALYZE SELECT * FROM users WHERE created_at < '2023-01-01';

출력 (간략화):

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..2020.00 rows=50000 width=128) (actual time=0.063..28.543 rows=100000 loops=1) 
   Filter: (created_at < '2023-01-01 00:00:00'::timestamp without time zone) 
   Rows Removed by Filter: 0 
 Planning Time: 0.089 ms
 Execution Time: 34.502 ms

해석:

• Seq Scan on users: 모든 users 테이블의 모든 행을 읽었으므로 데이터베이스는 순차 스캔을 수행했습니다. created_at에 인덱스가 없으므로 예상되는 결과입니다.
• (cost=0.00..2020.00 rows=50000 width=128): 플래너는 50000개의 행을 반환하는 데 2020.00의 비용을 예상했습니다.
• (actual time=0.063..28.543 rows=100000 loops=1): 실제 실행은 100000개의 행을 반환하는 데 28.543ms가 걸렸습니다.
• 불일치: 예상 행(50000)과 실제 행(100000)을 비교합니다. 이는 created_at 열의 플래너 통계가 오래되었거나 불충분하여 부정확한 비용 추정으로 이어졌음을 나타냅니다. 데이터베이스는 더 적은 행이 반환될 것이라고 생각하여 "더 저렴한" 계획을 선택했습니다. 이것이 매우 다른 계획(예: 다른 조인 전략 사용)으로 이어진다면 심각한 문제를 나타낼 수 있습니다. 간단한 Seq Scan의 경우 덜 중요합니다.
• Filter: (created_at < '2023-01-01...'): 스캔 후 WHERE 절이 적용됨을 보여줍니다.

최적화 통찰: 이 쿼리가 자주 실행되고 created_at이 매우 선택적이라면 created_at에 대한 인덱스가 유용할 것입니다.

예제 2: 인덱스 스캔

인덱스를 추가하고 다시 실행해 보겠습니다.

CREATE INDEX idx_users_created_at ON users (created_at);
ANALYZE users; -- 새 인덱스에 대한 통계 업데이트
EXPLAIN ANALYZE SELECT * FROM users WHERE created_at < '2023-01-01';

출력 (간략화):

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_users_created_at on users  (cost=0.42..362.46 rows=50000 width=128) (actual time=0.026..1.879 rows=100 loops=1) 
   Index Cond: (created_at < '2023-01-01 00:00:00'::timestamp without time zone) 
 Planning Time: 0.158 ms
 Execution Time: 2.222 ms

해석:

• Index Scan using idx_users_created_at on users: 성공했습니다! 데이터베이스가 이제 새 인덱스를 사용하고 있습니다.
• cost=0.42..362.46: 예상 비용이 훨씬 낮아졌습니다.
• actual time=0.026..1.879: 실제 실행 시간은 훨씬 빠릅니다 (Seq Scan의 28.543ms 대비 1.879ms).
• 불일치 (다시): 플래너는 50000개의 행이 반환될 것으로 예상했지만 실제로는 100개의 행만 발견되었습니다. 이는 created_at < '2023-01-01' 조건이 플래너가 예상했던 것보다 훨씬 더 선택적이었음을 나타냅니다. 계획은 좋았지만(인덱스 사용), 이러한 큰 불일치는 때때로 플래너를 더 복잡한 시나리오에서 잘못된 길로 이끌 수 있습니다. ANALYZE를 정기적으로 실행하거나 자동vacuum을 사용하여 통계를 업데이트하는 것이 중요합니다.

예제 3: Join 쿼리

users와 orders 간의 조인을 분석해 보겠습니다.

EXPLAIN ANALYZE
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date > '2024-01-01' AND u.email LIKE '%@example.com'
ORDER BY o.amount DESC
LIMIT 10;

출력 (주요 노드 강조 표시, 간략화):

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1000.00..1000.25 rows=10 width=116) (actual time=14.542..14.549 rows=10 loops=1) 
   ->  Sort  (cost=1000.00..1000.75 rows=30 width=116) (actual time=14.540..14.540 rows=10 loops=1) 
         Sort Key: o.amount DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Merge Join  (cost=0.86..999.00 rows=30 width=116) (actual time=0.089..14.502 rows=33 loops=1) 
               Merge Cond: (u.id = o.user_id)
               ->  Index Scan using users_pkey on users u  (cost=0.43..37.38 rows=1000 width=108) (actual time=0.038..0.540 rows=1000 loops=1) 
                     Filter: (email ~~ '%@example.com'::text) 
                     Rows Removed by Filter: 0
               ->  Sort  (cost=0.43..0.44 rows=30 width=16) (actual time=0.047..0.528 rows=33 loops=1) 
                     Sort Key: o.user_id
                     Sort Method: quicksort  Memory: 25kB
                     ->  Index Scan using idx_orders_order_date on orders o  (cost=0.43..0.98 rows=30 width=16) (actual time=0.016..0.439 rows=33 loops=1) 
                           Index Cond: (order_date > '2024-01-01 00:00:00'::timestamp without time zone) 
 Planning Time: 0.567 ms
 Execution Time: 14.602 ms

해석:

• 최상위 Limit: 정렬 후 첫 10개 행을 반환하기 위해 적용됩니다.
• Sort: 첫 10개 행을 반환하기 전에 데이터베이스가 o.amount DESC 기준으로 결과를 정렬해야 했습니다. top-N heapsort는 작은 N에 대해 효율적입니다.
• Merge Join: 조인 조건(u.id 및 o.user_id)의 양쪽이 이미 정렬되어 있거나 효율적으로 정렬될 수 있었기 때문에 데이터베이스는 Merge Join 전략을 선택했습니다.
  • 왼쪽 분기 (users 테이블): Index Scan using users_pkey on users u: PostgreSQL은 users의 기본 키 인덱스를 스캔한 다음 email LIKE '%@example.com'에 따라 필터링했습니다. actual time은 1000개 행에 대해 0.540ms입니다.
  • 오른쪽 분기 (orders 테이블): Sort -> Index Scan using idx_orders_order_date on orders o:
    • order_date > '2024-01-01' 필터 때문에 orders에서 idx_orders_order_date를 사용하여 먼저 Index Scan을 수행했습니다. 이는 0.439ms에 33개 행을 반환했습니다.
    • 그런 다음 이 33개 행은 Merge Join을 촉진하기 위해 o.user_id 기준으로 Sort되었습니다. 이는 0.528ms가 걸렸습니다.
• 전체: 계획은 합리적으로 보입니다. 인덱스는 해당될 때마다 필터링 및 조인에 사용됩니다. Merge Join은 데이터가 이미 정렬된 경우 일반적으로 효율적입니다.
  • Planning Time: 쿼리 최적화기가 이 계획을 선택하는 데 걸린 시간입니다.
  • Execution Time: 쿼리 실행에 걸린 총 시간입니다.

최적화 통찰:

• email LIKE '%@example.com'이 매우 선택적이지만 많은 사용자가 일치하면 GIN 인덱스가 email에 대해 더 빠를 수 있지만 LIKE 쿼리는 선행 와일드카드를 피하거나 특정 확장 기능이 사용되지 않는 한 인덱스로 종종 어려움을 겪습니다. 이 간단한 와일드카드의 경우 너무 많은 이메일이 패턴과 일치하면 순차 스캔이 결국 선택될 수 있습니다.
• users Index Scan의 실제 rows (1000)는 많은 사용자가 email LIKE '%@example.com' 패턴과 일치함을 시사합니다. 이 필터가 매우 선택적이었다면 Bitmap Heap Scan이 때때로 기본 인덱스만으로는 많은 페이지를 방문하지 피하기에 충분하지 않은 경우 선호될 수 있습니다.

EXPLAIN ANALYZE 출력 읽기를 위한 핵심 요점:

1. 아래에서 위로, 오른쪽에서 왼쪽으로 읽습니다: 가장 안쪽 연산 또는 스캔 노드가 먼저 실행됩니다.
2. 비싼 노드에 집중합니다: actual time이 높은 노드를 찾습니다. 성능 병목 현상이 여기에 있습니다.
3. estimates 대 actual 비교:
   • rows 불일치: 큰 차이는 부정확한 통계를 나타냅니다(관련 테이블에서 ANALYZE 또는 VACUUM ANALYZE 실행). 부정확한 추정은 최적화기가 최적이 아닌 계획을 선택하게 할 수 있습니다.
   • cost 대 actual time 불일치: 비용은 이론적이지만 실제 시간보다 훨씬 높은 실제 시간은 문제를 신호합니다.
4. 비싼 연산 식별:
   • 대형 테이블의 Seq Scan: 일반적으로 경고 신호입니다. 인덱스 추가를 고려하십시오.
   • 대규모 데이터셋의 Sort: 디스크로 스필링하는 경우(Sort Method: external merge Disk: XMB) 특히 비용이 많이 들 수 있습니다. 데이터가 사전 정렬되어 있는지(예: 인덱스 또는 다른 조인 전략 사용) 또는 정렬하기 전에 데이터셋을 제한하여 피할 수 있습니다.
   • 비싼 Join 연산: 내부 루프가 효율적인 인덱스 없이 많이 실행되면 Nested Loop Join이 느릴 수 있습니다. Hash Join 및 Merge Join은 일반적으로 대규모 데이터셋에 대해 더 확장 가능합니다.
5. Filter 또는 Index Cond 찾기: WHERE 절이 언제 적용되는지 이해합니다. Index Cond는 인덱스 스캔 중에 적용되므로 매우 효율적입니다. Filter는 데이터를 검색한 후에 적용되므로 필요한 것보다 더 많은 행을 읽었을 수 있습니다.
6. Loops 카운트: 특히 중첩된 연산에서 내부 연산이 실행된 횟수를 나타냅니다. 내부 연산이 높은 루프 횟수와 느린 연산은 문제를 곱합니다.

결론

EXPLAIN ANALYZE를 마스터하는 것은 PostgreSQL로 작업하는 모든 사람에게 필수적인 기술입니다. 데이터베이스의 내부 작동 방식을 탁월하게 보여주므로 성능 병목 현상을 정확하게 진단할 수 있습니다. 해당 상세 출력의 체계적인 해석, 예상 통계와 실제 통계 비교, 비용이 많이 드는 연산 식별을 통해 느리고 비효율적인 쿼리를 번개처럼 빠르고 최적화된 강력한 쿼리로 변환하여 애플리케이션이 응답성과 확장성을 유지하도록 보장할 수 있습니다. 궁극적으로 EXPLAIN ANALYZE는 최적의 PostgreSQL 쿼리 성능을 잠금 해제하는 주요 도구입니다.