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

EXPLAIN ANALYZE는 PostgreSQL 쿼리가 느리고 일반적인 추측만으로는 부족할 때 사용하는 도구입니다. 애플리케이션 코드에서는 쿼리가 단순해 보일 수 있습니다. 테이블에 인덱스가 있고 모두가 데이터베이스가 이를 사용할 것이라고 가정할 수 있습니다. 스테이징에서는 빠르지만 프로덕션에서는 느릴 수 있습니다. 계획은 이러한 가정이 유지되거나 무너지는 지점입니다.

유용한 습관은 계획을 PostgreSQL이 수행한 작업의 이야기로 읽는 것입니다: 어떤 행을 접촉할 것으로 예상했는지, 실제로 어떤 행을 접촉했는지, 어디에서 조인했는지, 어디에서 정렬했는지, 메모리 내에 머물렀는지, 디스크에서 읽어야 했는지. 이 모든 것을 유용하게 활용하기 전에 모든 계획 노드를 외울 필요는 없습니다. 하지만 속도를 늦추고 추정치를 현실과 비교해야 합니다.

EXPLAIN vs. EXPLAIN ANALYZE 이해하기

EXPLAIN과 EXPLAIN ANALYZE의 차이는 중요합니다. 하나는 예측이고 다른 하나는 측정이기 때문입니다.

EXPLAIN

EXPLAIN을 접두사로 붙여 쿼리를 실행하면 PostgreSQL은 쿼리를 실제로 실행하지 않고 의도된 실행 계획을 생성합니다. 이는 다음과 같은 경우에 유용합니다:

• 계획 미리보기: PostgreSQL이 쿼리를 실행하는 가장 저렴한 방법으로 예상하는 것을 볼 수 있습니다.
• 비용 추정: 계획의 각 노드에 대한 비용 추정치를 제공하여 리소스 사용에 대한 상대적인 아이디어를 제공합니다.

예시:

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

EXPLAIN ANALYZE

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

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

이로 인해 EXPLAIN ANALYZE는 실제 튜닝에 더 나은 도구이지만, 날카로운 단점이 있습니다: 쿼리를 실행한다는 것입니다. SELECT는 많은 데이터를 스캔하거나, 잠금을 걸거나, 캐시를 놓고 경쟁할 수 있기 때문에 여전히 비용이 많이 들 수 있습니다. UPDATE, DELETE 또는 INSERT는 트랜잭션으로 감싸고 롤백하지 않는 한 실제로 데이터를 수정합니다:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE accounts SET status = 'archived' WHERE last_seen_at < now() - interval '2 years';
ROLLBACK;

이 패턴은 유지 관리 기간이나 스테이징 복사본에서 유용합니다. 바쁜 프로덕션 데이터베이스에서 위험한 명령문을 실행할 수 있는 자유로운 통행권이 아닙니다.

예시:

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

EXPLAIN ANALYZE 출력 해독하기

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

핵심 구성 요소:

• 노드 유형: 수행 중인 작업을 식별합니다 (예: Seq Scan, Index Scan, Hash Join, Nested Loop, Sort, Aggregate).
• 비용: (startup_cost .. total_cost)로 표시됩니다.
  • startup_cost: 첫 번째 행을 검색하는 비용.
  • total_cost: 모든 행을 검색하는 비용.
  • 참고: 비용은 비교를 위한 임의의 단위이며, 시간이나 메모리를 직접 나타내지 않습니다.
• 행: 플래너가 이 노드에서 반환할 것으로 예상하는 행의 추정 수.
• 너비: 이 노드에서 반환된 행의 추정 평균 너비(바이트).
• 실제 시간: (startup_time .. total_time)으로 표시됩니다. 이 노드를 실행하는 실제 시간(밀리초)입니다.
  • startup_time: 첫 번째 행을 반환하는 실제 시간.
  • total_time: 모든 행을 반환하는 실제 시간.
• 실제 행: 이 노드에서 반환된 실제 행 수.
• 루프: 이 노드가 실행된 횟수. 최상위 노드의 경우 일반적으로 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만 찾지 마십시오. 또한 여러 번 실행되는 노드를 찾으십시오. 0.2ms가 걸리는 중첩 루프 내부 스캔은 loops=50000이 될 때까지 무해해 보일 수 있습니다. 이 경우 실제 비용은 대략 루프당 시간에 루프 수를 곱한 것입니다.

안쪽에서 바깥쪽으로 읽기

PostgreSQL 계획은 트리입니다. 최상위 노드는 최종 결과를 클라이언트에 반환하지만, 작업은 일반적으로 계획의 더 깊은 곳에서 시작됩니다. 쿼리가 orders, customers 및 order_items를 조인할 때 최상위 줄은 Aggregate일 수 있지만, 실제 문제는 그 아래의 스캔 또는 조인일 수 있습니다.

저는 일반적으로 다음 순서로 계획을 읽습니다:

1. 가장 깊은 스캔 노드부터 시작하여 질문합니다: PostgreSQL이 쿼리가 반환하는 것보다 훨씬 더 많은 행을 읽었습니까?
2. 추정된 rows를 실제 rows와 비교합니다.
3. 비용이 많이 드는 노드에 높은 loops가 있는지 확인합니다.
4. 디스크로 유출되는 Sort, Hash 또는 Materialize 노드를 찾습니다.
5. BUFFERS를 사용하여 쿼리가 주로 CPU/캐시 작업인지 디스크 I/O인지 결정합니다.

다음은 일반적인 예입니다:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

수백만 개의 orders 행에 대한 순차 스캔, 정렬, 제한이 표시되면 데이터베이스가 요청한 20개 행을 반환하기 전에 너무 많은 작업을 수행하고 있는 것입니다. 실용적인 인덱스는 다음과 같을 수 있습니다:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);

그 후, 좋은 계획은 인덱스를 사용하여 해당 고객의 최신 주문으로 직접 이동하고 20개 행 후에 중지할 수 있습니다. 정확한 계획은 테이블 크기, 통계, PostgreSQL 버전 및 데이터 분포에 따라 다르지만, 원칙은 안정적입니다: 실제로 사용하는 필터 및 정렬 패턴과 일치하도록 인덱스를 만드십시오.

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

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

1. 순차 스캔 (Seq Scan)

• 정의: 테이블의 모든 행을 읽습니다. 특히 특정 조건으로 필터링할 때 대규모 테이블의 경우 비효율적인 경우가 많습니다.
• 괜찮은 경우: 소규모 테이블의 경우 또는 테이블 행의 큰 비율을 검색해야 하는 경우. 순차 스캔이 자동으로 나쁜 것은 아닙니다.
• 최적화: 선택적 필터 열에 인덱스를 생성하되 계획으로 확인하십시오. 조건자가 테이블의 대부분을 반환하는 경우 PostgreSQL은 올바르게 순차 스캔을 계속 사용할 수 있습니다.

2. 인덱스 스캔 (Index Scan)

• 정의: WHERE 절과 일치하는 행을 찾기 위해 인덱스를 사용합니다. PostgreSQL은 인덱스를 탐색한 다음 테이블에서 해당 행을 가져옵니다.
• 최적화: 인덱스가 쿼리 형태와 일치하는지 확인하십시오. 복합 인덱스의 경우 열 순서가 중요합니다. (tenant_id, created_at)에 대한 인덱스는 tenant_id로 필터링하고 created_at으로 정렬하는 쿼리에 도움이 됩니다. created_at만으로 필터링하는 쿼리에는 별로 도움이 되지 않을 수 있습니다.

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

• 정의: 쿼리에 필요한 모든 데이터가 인덱스 내에서 직접 사용 가능한 최적화된 Index Scan입니다. PostgreSQL은 테이블 힙을 방문할 필요가 없습니다.
• 효율적인 경우: 선택된 모든 열이 인덱스에서 사용 가능하고 가시성 맵을 통해 PostgreSQL이 많은 힙 검사를 피할 수 있는 경우.
• 최적화: 읽기 집약적인 경로의 경우 INCLUDE와 함께 커버링 인덱스를 고려하되 "만약을 대비해" 모든 열을 추가하지 마십시오. 더 큰 인덱스는 쓰기 시 유지 관리 비용이 더 많이 듭니다.

4. 조인 연산 (Nested Loop, Hash Join, Merge Join)

• Nested Loop: 외부 관계의 각 행에 대해 PostgreSQL은 내부 관계를 스캔합니다. 외부 관계가 작거나 내부 관계를 인덱스를 통해 빠르게 액세스할 수 있는 경우 효율적입니다.
• Hash Join: 한 관계(빌드 측)에서 해시 테이블을 작성하고 다른 관계(프로브 측)의 행으로 프로브합니다. 조인 조건에 인덱스가 유용하지 않은 대규모 테이블에 효율적입니다.
• Merge Join: 두 관계가 조인 키로 정렬되어야 합니다. 정렬된 목록을 병합합니다. 이미 정렬된 대규모 입력에 효율적입니다.
• 최적화:
  • 조인 열에 인덱스가 있는지 확인하십시오.
  • 잘못된 행 추정으로 인해 잘못된 조인 선택이 발생했는지 검토하십시오. PostgreSQL은 일부 데이터베이스와 같은 스타일의 네이티브 최적화 힌트를 지원하지 않으므로 일반적인 수정 방법은 더 나은 통계, 더 나은 인덱스 또는 쿼리 재작성입니다.
  • 조인 노드에서 큰 loops 수 또는 높은 actual time을 확인하려면 EXPLAIN ANALYZE를 사용하십시오.

5. 정렬 (Sort)

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

6. 집계 (Aggregate)

• 정의: COUNT(), SUM(), AVG(), GROUP BY와 같은 연산을 수행합니다.
• 최적화:
  • WHERE 절이 효율적인지 확인하여 집계 전에 행 수를 줄이십시오.
  • 집계가 빈번하고 느린 작업인 경우 사전 집계된 데이터에 대해 구체화된 뷰 사용을 고려하십시오.
  • 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(추정 vs. 실제) 비교: 큰 차이는 PostgreSQL 쿼리 플래너가 부정확한 가정을 하고 있음을 나타냅니다. 이는 ANALYZE <table_name>;을 사용하여 테이블 통계를 업데이트하거나 적절한 인덱스를 생성하여 해결할 수 있는 경우가 많습니다.
• BUFFERS 사용: 버퍼 사용량을 분석하여 쿼리가 I/O 바운드인지 이해하십시오.
• 현실적인 데이터로 테스트: 프로덕션 환경과 유사한 데이터 양과 데이터 분포를 가진 데이터베이스에서 EXPLAIN ANALYZE를 실행하십시오.
• 단계적으로 최적화: 한 번에 모든 것을 최적화하려고 하지 마십시오. 가장 큰 병목 현상을 먼저 해결하십시오.
• work_mem 고려: 정렬 또는 해싱(BUFFERS의 temp read/written)에 대한 상당한 디스크 읽기가 표시되면 work_mem(세션별 또는 전역적으로)을 늘리는 것이 도움이 될 수 있지만 메모리 사용량에 주의하십시오.
• 현명하게 인덱싱: 실제로 사용되고 유용한 인덱스만 생성하십시오. 너무 많은 인덱스는 쓰기 속도를 늦추고 디스크 공간을 소비할 수 있습니다.
• PostgreSQL 버전 확인: 최신 버전은 종종 성능에 영향을 줄 수 있는 향상된 쿼리 플래너와 새로운 기능을 제공합니다.

실용적인 튜닝 패스

다음 쿼리를 살펴보십시오:

SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

계획에 순차 스캔이 표시되면 email에 대한 인덱스만으로는 도움이 되지 않을 수 있습니다. 쿼리가 lower(email)을 적용하기 때문입니다. PostgreSQL은 쿼리의 표현식이 인덱싱된 값과 다를 때 일반 인덱스를 항상 사용할 수 없습니다. 더 나은 옵션은 표현식 인덱스일 수 있습니다:

CREATE INDEX CONCURRENTLY users_lower_email_idx
ON users (lower(email));

그런 다음 다시 실행하십시오:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

스캔된 행 수, 읽은 버퍼 수, 실행 시간이 더 적은지 확인하십시오. 계획이 여전히 인덱스를 사용하지 않으면 테이블이 매우 작은지, 통계가 오래되었는지, 또는 애플리케이션이 보내는 방식과 다르게 쿼리가 작성되었는지 확인하십시오.

또 다른 일반적인 경우는 SQL에서는 괜찮아 보이지만 계획에서 폭발하는 조인입니다:

SELECT o.id, p.sku
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= current_date - interval '7 days';

유용한 인덱스는 orders(created_at), order_items(order_id) 및 products(id)의 기본 키를 포함할 수 있습니다. 그러나 지난 7일이 orders 테이블의 대부분을 포함하는 경우 orders(created_at)이 주요 수정 사항이 아닐 수 있습니다. 계획은 실제 문제가 날짜 필터, 조인 팬아웃 또는 하위 테이블의 누락된 인덱스인지 알려줍니다.

좋은 PostgreSQL 쿼리 튜닝은 "계획이 변경될 때까지 인덱스를 추가하는 것"이 아닙니다. 그것은 루프입니다: 실제 계획을 측정하고, 하나의 방어 가능한 변경을 하고, 다시 측정하고, 실제로 관심 있는 워크로드를 개선하는 경우에만 변경을 유지하십시오.