DevOps 지식 허브
DevOps 지식 허브

EXPLAIN ANALYZE 마스터하기: PostgreSQL 쿼리 계획 최적화 가이드

포괄적인 EXPLAIN ANALYZE 가이드로 PostgreSQL 성능을 잠금 해제하세요. 쿼리 실행 계획 해석, 병목 지점 식별, SQL 쿼리 최적화 방법을 알아보십시오. 이 가이드는 필수 개념, 노드 유형, 출력 해석 및 실행 가능한 예제를 통한 실용적인 최적화 전략을 다룹니다. PostgreSQL이 쿼리를 실행하는 방식을 이해하여 데이터베이스 성능을 마스터하십시오.

37 조회수

EXPLAIN ANALYZE 마스터하기: PostgreSQL 쿼리 실행 계획 최적화 가이드

PostgreSQL을 다룰 때, 데이터베이스가 SQL 쿼리를 어떻게 실행하는지 이해하는 것은 최적의 성능을 달성하는 데 매우 중요합니다. 아무리 잘 설계된 스키마라도 기본 실행 계획이 비효율적이면 느린 쿼리 시간으로 어려움을 겪을 수 있습니다. PostgreSQL은 이러한 계획을 검사하기 위한 강력한 도구를 제공하는데, 그 핵심에는 EXPLAIN과 EXPLAIN ANALYZE가 있습니다. 이 가이드는 쿼리 실행 계획을 해독하고, 성능 병목 현상을 식별하며, 궁극적으로 SQL 쿼리를 최적화하여 속도를 크게 향상시키기 위해 EXPLAIN ANALYZE를 사용하는 복잡한 과정을 안내할 것입니다.

EXPLAIN ANALYZE를 효과적으로 활용하면 개발자와 데이터베이스 관리자는 쿼리 실행 프로세스에 대한 깊은 통찰력을 얻을 수 있습니다. 비용 예측, 실제 실행 시간, 그리고 각 단계에서 처리된 행의 수를 이해함으로써 쿼리가 대부분의 시간을 어디에 소비하고 있는지 정확히 찾아낼 수 있습니다. 이러한 지식은 인덱싱, 쿼리 재구성, 데이터베이스 구성에 대해 정보에 입각한 결정을 내릴 수 있도록 하여, 더 빠르고 효율적인 PostgreSQL 환경을 구축하는 데 기여합니다.

EXPLAIN 대 EXPLAIN ANALYZE 이해하기

EXPLAIN ANALYZE를 자세히 살펴보기 전에, 더 단순한 버전인 EXPLAIN과 차이점을 이해하는 것이 중요합니다.

EXPLAIN

쿼리 앞에 EXPLAIN을 붙여 실행하면, PostgreSQL은 실제로 쿼리를 실행하지 않고 예정된 실행 계획을 생성합니다. 이는 다음 용도로 유용합니다:

  • 계획 미리보기: PostgreSQL이 쿼리를 실행하는 데 가장 좋은 방법이라고 생각하는 것을 확인할 수 있습니다.
  • 비용 예측: 계획의 각 노드에 대한 비용 예측을 제공하여 리소스 사용량에 대한 상대적인 아이디어를 얻을 수 있습니다.

예시:

EXPLAIN SELECT * FROM users WHERE registration_date > '2023-01-01';

EXPLAIN ANALYZE

EXPLAIN ANALYZE는 한 단계 더 나아갑니다. 계획된 실행을 보여줄 뿐만 아니라, 쿼리를 실제로 실행한 다음 실제 실행 통계를 보고합니다. 즉, 다음 정보를 얻을 수 있습니다:

  • 실제 실행 시간: 각 단계가 실제로 얼마나 걸렸는지.
  • 실제 행 수: 각 노드에서 실제로 처리된 행의 수.
  • 예측 확인: 예측된 행 수와 실제 행 수를 비교하여 PostgreSQL의 플래너가 정확한 예측을 하고 있는지 확인할 수 있습니다.

이로 인해 EXPLAIN ANALYZE는 실제 데이터와 시스템에서 쿼리의 진정한 동작을 보여주므로, 실제 성능 튜닝에 필수적입니다. EXPLAIN ANALYZE는 쿼리를 실행한다는 점을 인지하고, 데이터 수정에 완전히 대비하지 않은 한 프로덕션 시스템에서 UPDATE, DELETE, 또는 INSERT 문에 신중하게 사용해야 합니다.

예시:

EXPLAIN ANALYZE SELECT * FROM users WHERE registration_date > '2023-01-01';

EXPLAIN ANALYZE 출력 해독하기

EXPLAIN ANALYZE의 출력은 처음에는 복잡해 보일 수 있지만, 주요 구성 요소를 이해하는 것이 기본입니다.

핵심 구성 요소:

  • 노드 유형 (Node Type): 수행되는 작업(예: Seq Scan, Index Scan, Hash Join, Nested Loop, Sort, Aggregate)을 식별합니다.
  • 비용 (Cost): (startup_cost .. total_cost) 형식으로 표시됩니다.
    • startup_cost: 첫 번째 행을 검색하는 데 드는 비용.
    • total_cost: 모든 행을 검색하는 데 드는 총 비용.
    • 참고: 비용은 직접적인 시간이나 메모리가 아닌 비교를 위해 사용되는 임의의 단위입니다.
  • 행 (Rows): 플래너가 이 노드에서 반환할 것으로 예상하는 행의 수.
  • 너비 (Width): 이 노드에서 반환되는 행의 예상 평균 너비(바이트).
  • 실제 시간 (Actual Time): (startup_time .. total_time) 형식으로 표시됩니다. 이 노드를 실행하는 데 걸린 실제 시간(밀리초)입니다.
    • startup_time: 첫 번째 행을 반환하는 데 걸린 실제 시간.
    • total_time: 모든 행을 반환하는 데 걸린 실제 시간.
  • 실제 행 (Actual Rows): 이 노드에서 반환된 실제 행의 수.
  • 루프 (Loops): 이 노드가 실행된 횟수. 최상위 노드의 경우 일반적으로 1입니다. 중첩된 작업의 경우 더 높을 수 있습니다.

예시 출력 해석:

대형 테이블에서 Seq Scan (순차 스캔)의 단순화된 예시를 살펴보겠습니다:

Seq Scan on users  (cost=0.00..15000.00 rows=1000000 width=100) (actual time=0.020..150.500 rows=950000 loops=1)
  Filter: (registration_date > '2023-01-01')
  Rows Removed by Filter: 50000

해석:

  • Seq Scan on users: 데이터베이스가 users 테이블의 모든 단일 행을 읽고 있습니다.
  • cost=0.00..15000.00: 플래너는 총 비용을 약 15000 단위로 예측했습니다.
  • rows=1000000: 플래너는 테이블에 100만 개의 행이 있다고 예측했습니다.
  • actual time=0.020..150.500: 스캔 및 필터링을 완료하는 데 실제로 150.5밀리초가 걸렸습니다.
  • rows=950000: (필터링 후) 실제로 950,000개의 행이 반환되었습니다.
  • loops=1: 이 스캔은 한 번 수행되었습니다.
  • Filter: (registration_date > '2023-01-01'): 행을 필터링하기 위해 적용된 조건입니다.
  • Rows Removed by Filter: 50000: 필터에 의해 50,000개의 행이 제거되었습니다.

병목 현상 식별: 특정 노드의 actual time이 다른 노드보다 현저히 높고, 특히 total_cost도 높다면, 이 노드가 최적화를 위한 최우선 후보입니다.

일반적인 쿼리 계획 노드 및 최적화 전략

다양한 유형의 노드와 이를 최적화하는 방법을 이해하는 것이 쿼리 성능을 마스터하는 핵심입니다.

1. 순차 스캔 (Seq Scan)

  • 정의: 테이블의 모든 행을 읽습니다. 특정 조건으로 필터링할 때 대규모 테이블에서는 종종 비효율적입니다.
  • 허용되는 경우: 소규모 테이블이거나 테이블 행의 상당 부분을 검색해야 하는 경우.
  • 최적화: WHERE 절에 사용되는 열에 인덱스를 생성합니다. 이를 통해 PostgreSQL은 선택적인 쿼리에서 훨씬 빠른 Index Scan 또는 Index Only Scan을 사용할 수 있습니다.

2. 인덱스 스캔 (Index Scan)

  • 정의: 인덱스를 사용하여 WHERE 절과 일치하는 행을 찾습니다. PostgreSQL은 인덱스를 탐색한 다음 테이블에서 해당 행을 가져옵니다.
  • 최적화: 인덱스가 올바른 열에 정의되어 있는지, 그리고 쿼리가 이를 활용하도록 작성되었는지 확인합니다. 쿼리에 인덱스에 없는 열도 필요한 경우, 테이블 힙(table heap)을 방문해야 하므로 커버링 인덱스를 사용하여 추가 최적화가 가능할 수 있습니다.

3. 인덱스 전용 스캔 (Index Only Scan)

  • 정의: 쿼리에 필요한 모든 데이터가 인덱스 내에 직접 있는 최적화된 Index Scan입니다. PostgreSQL은 테이블 힙을 방문할 필요가 없습니다.
  • 효율적인 경우: 선택된 모든 열이 인덱스의 일부이며 쿼리가 인덱스에 없는 열을 요구하지 않는 경우.
  • 최적화: 플래너가 자동으로 Index Only Scan을 선택하지 않고 주로 인덱스를 통해 데이터를 검색하는 경우, 커버링 인덱스(예: PostgreSQL 11+에서 INCLUDE 사용 또는 이전 버전에서 필요한 모든 열을 인덱스 정의에 포함) 생성을 고려하십시오.

4. 조인 작업 (Nested Loop, Hash Join, Merge Join)

  • Nested Loop (중첩 루프): 외부 관계의 각 행에 대해 PostgreSQL은 내부 관계를 스캔합니다. 외부 관계가 작거나 내부 관계가 인덱스를 통해 빠르게 액세스될 수 있을 때 효율적입니다.
  • Hash Join (해시 조인): 하나의 관계(빌드 측)로 해시 테이블을 구축하고 다른 관계(프로브 측)의 행으로 이를 프로브합니다. 조인 조건에 인덱스가 유용하지 않은 대규모 테이블에 효율적입니다.
  • Merge Join (병합 조인): 두 관계 모두 조인 키로 정렬되어야 합니다. 정렬된 목록을 병합합니다. 이미 정렬된 대규모 입력에 효율적입니다.
  • 최적화:
    • 조인 열에 인덱스가 있는지 확인합니다.
    • 조인 순서를 검토합니다. PostgreSQL이 일반적으로 좋은 순서를 선택하지만, 때로는 수동 개입이나 힌트가 필요할 수 있습니다(PostgreSQL은 다른 일부 데이터베이스와 달리 힌트를 지원하지 않지만).
    • 조인 노드에서 높은 loops 수나 높은 actual time이 있는지 EXPLAIN ANALYZE를 확인합니다.

5. 정렬 (Sort)

  • 정의: 행의 순서를 지정합니다. 특히 대규모 데이터 세트에서는 계산 비용이 많이 들 수 있습니다.
  • 최적화:
    • 인덱스 정의에 ORDER BY 절을 추가합니다.
    • 더 제한적인 WHERE 절을 추가하여 정렬되는 행의 수를 줄입니다.
    • 정렬이 디스크가 아닌 메모리에서 발생할 수 있도록 충분한 work_mem이 구성되어 있는지 확인합니다.

6. 집계 (Aggregate)

  • 정의: COUNT(), SUM(), AVG(), GROUP BY와 같은 작업을 수행합니다.
  • 최적화:
    • WHERE 절이 효율적인지 확인하여 집계 전에 행의 수를 줄입니다.
    • 집계가 빈번하고 느린 작업인 경우, 사전 집계된 데이터에 대해 구체화된 뷰(materialized views) 사용을 고려합니다.
    • GROUP BY 절에 사용되는 열을 인덱싱합니다.

옵션을 사용하여 EXPLAIN ANALYZE 활용하기

EXPLAIN ANALYZE에는 훨씬 더 자세한 정보를 제공할 수 있는 몇 가지 유용한 옵션이 있습니다.

VERBOSE (자세한 정보)

  • 기능: 스키마 한정 테이블 이름 및 출력 열 이름과 같은 쿼리 계획에 대한 추가 정보를 표시합니다.
EXPLAIN (ANALYZE, VERBOSE) SELECT u.name FROM users u WHERE u.id = 1;

COSTS (비용)

  • 기능: 출력에 예상 비용을 포함합니다. 이는 기본 동작이지만, 명시적으로 끌 수 있습니다.
EXPLAIN (ANALYZE, COSTS FALSE) SELECT COUNT(*) FROM orders;

BUFFERS (버퍼)

  • 기능: 버퍼 사용량(공유, 임시 및 로컬)에 대한 정보를 보고합니다. 이는 I/O 병목 현상을 식별하는 데 도움이 됩니다.
    • shared hit: PostgreSQL의 공유 버퍼 캐시에서 발견된 블록.
    • shared read: 디스크에서 공유 버퍼로 읽어 들인 블록.
    • temp read/written: 임시 파일로 읽거나 쓴 블록(work_mem을 초과하는 정렬 또는 해시 작업에서 자주 발생).
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE category = 'Electronics';

TIMING (시간 측정)

  • 기능: 각 노드의 실제 시작 시간 및 총 시간을 포함합니다. 이는 ANALYZE의 기본 동작입니다.
EXPLAIN (ANALYZE, TIMING FALSE) SELECT * FROM logs LIMIT 10;

옵션 조합하기

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.order_date, COUNT(oi.product_id)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2023-01-01'
GROUP BY o.order_date;

실용적인 팁 및 모범 사례

  • EXPLAIN ANALYZE로 시작: 실제 성능 분석에는 항상 EXPLAIN ANALYZE를 사용하십시오. EXPLAIN 단독으로는 불충분합니다.
  • actual time에 집중: actual time이 가장 높은 노드를 최적화하는 데 우선순위를 두십시오.
  • rows 비교 (예상 대 실제): 큰 불일치는 PostgreSQL의 쿼리 플래너가 부정확한 가정을 하고 있음을 나타냅니다. 이는 ANALYZE <table_name>;을 사용하여 테이블 통계를 업데이트하거나 적절한 인덱스를 생성하여 해결할 수 있습니다.
  • BUFFERS 사용: 버퍼 사용량을 분석하여 쿼리가 I/O 바운드인지 확인합니다.
  • 현실적인 데이터로 테스트: 프로덕션 환경과 유사한 양의 데이터와 데이터 분포를 가진 데이터베이스에서 EXPLAIN ANALYZE를 실행하십시오.
  • 단계별 최적화: 모든 것을 한 번에 최적화하려고 하지 마십시오. 가장 큰 병목 현상부터 먼저 해결하십시오.
  • work_mem 고려: 정렬 또는 해싱(BUFFERS의 temp read/written)에서 상당한 디스크 읽기가 발생하는 경우, work_mem을 (세션별 또는 전역적으로) 늘리는 것이 도움이 될 수 있지만, 메모리 사용량에 유의하십시오.
  • 현명하게 인덱싱: 실제로 사용되고 유익한 인덱스만 생성하십시오. 너무 많은 인덱스는 쓰기 속도를 늦추고 디스크 공간을 소비할 수 있습니다.
  • PostgreSQL 버전 확인: 최신 버전은 종종 개선된 쿼리 플래너와 성능에 영향을 미칠 수 있는 새로운 기능을 제공합니다.

결론

EXPLAIN ANALYZE는 PostgreSQL 성능 튜닝에 필수적인 도구입니다. 출력을 세심하게 분석함으로써 추측을 넘어 목표에 맞는 최적화를 구현할 수 있습니다. 노드 유형, 비용 예측, 실제 실행 시간, 버퍼 사용량을 이해하면 병목 현상을 식별하고, 인덱싱 전략을 최적화하며, SQL 쿼리를 다듬을 수 있습니다. 이러한 기술을 일관되게 적용하면 PostgreSQL 데이터베이스가 훨씬 더 효율적이고 반응성이 좋아질 것입니다.

다음 단계:

  1. 애플리케이션에서 느린 쿼리를 식별합니다.
  2. 해당 쿼리에 대해 EXPLAIN (ANALYZE, BUFFERS)를 실행합니다.
  3. 출력을 분석하고, actual time이 가장 높은 노드에 집중합니다.
  4. 잠재적인 최적화(예: 인덱스 추가, 쿼리 재작성)를 가설로 세웁니다.
  5. 최적화를 구현하고 EXPLAIN ANALYZE를 다시 실행하여 개선 사항을 측정합니다.
  6. 만족스러운 성능이 달성될 때까지 반복합니다.