[PostgreSQL] PostgreSQL로 보는 Database 실행 계획 & Explain Option

데이터베이스 실행 계획(Database Execution Plan) 또는 쿼리 실행 계획(Query Execution Plan)은 가능한 가장 효율적인 방법으로 데이터를 검색하거나 조작하기 위해 database management system's (DBMS) query optimizer에서 생성하는 일련의 단계입니다.
데이터베이스 실행 계획과 Explain 명령어에 대해서 알아보도록 하겠습니다.

 

????

 

PostgreSQL 데이터베이스 실행 계획

---

PostgreSQL의 맥락에서 실행 계획은 쿼리 처리 파이프라인의 중요한 구성 요소로, 시스템이 쿼리를 빠르고 효율적으로 수행할 수 있도록 도와줍니다.

PostgreSQL 쿼리 실행 프로세스는 여러 단계로 구성됩니다.

 

PostgreSQL Query Process Pipe Line

 

SELECT first_name, last_name FROM employees WHERE department_id = 10 ORDER BY last_name;

이 쿼리는 department_id가 10인 직원들의 first_name과 last_name을 last_name 순으로 정렬하여 반환합니다.

이제 각 단계에서 일어나는 작업을 살펴봅시다.

 

Parsing
클라이언트가 SQL 쿼리를 전송하면 PostgreSQL Parser가 이를 분석하여 쿼리 문이 올바르게 작성됐는지 확인하고parse tree를 생성합니다.

parse tree는 테이블, 열, 연산자, 절을 포함한 쿼리 구성 요소의 계층 구조를 나타냅니다.

위 예시에서는 SELECT, FROM, WHERE, ORDER BY 절을 포함하고, 대상 테이블(employees)과 대상 열(first_name, last_name)을 나타냅니다.

 

Rewriting

PostgreSQL Rewriting 단계에서는 쿼리에 대해 적용해야 하는 view나 rule이 있는지 확인합니다.

예를 들어, employees 테이블의 특정 열에 대한 view가 있다면, rewriting 단계에서 해당 view가 적용됩니다.

이 예제에서는 별도의 view나 rule이 없다고 가정하겠습니다.

 

Planning

query optimizer도 하는 PostgreSQL Planner는 구문 분석 트리의 정보와 데이터베이스의 테이블 및 인덱스에 대한 통계를 기반으로 다양한 database 실행 전략을 고려합니다.

목표는 쿼리를 실행하는 가장 효율적인 방법을 찾는 것.

이 과정에서 데이터베이스 통계 및 인덱스 정보가 사용됩니다.

예를 들어, department_id에 인덱스가 있다면, Planner는 이 인덱스를 사용하여 효율적인 계획을 생성합니다.

최적의 계획은 last_name 열의 정렬 작업과 department_id에 대한 필터링 작업을 포함합니다.

 

Execution

PostgreSQL Execution은 Planner가 선택한 계획을 실행하여 필요에 따라 데이터를 가져오고 조작합니다.

PostgreSQL의 쿼리 실행 계획은 plan node라는 노드 트리로 표시됩니다.

각 노드는 쿼리 실행 중에 사용되는 특정 연산 또는 알고리즘을 나타냅니다.

필요한 데이터를 가져와서 department_id가 10인 직원들을 필터링하고, last_name 순으로 정렬한 다음, 결과 집합에 first_name과 last_name 열을 포함시킵니다.

 

plan node

---

그렇다면 plan node는 어떻게 구성될까요?

가장 일반적인 plan node는 다음과 같습니다.

 

index scan을 기준으로 살펴본다면

SELECT first_name, last_name FROM employees WHERE department_id = 10 ORDER BY last_name;

 

Index Scan

department_id에 인덱스가 있다고 가정하면, PostgreSQL은 Index Scan을 사용하여 department_id가 10인 행을 빠르게 찾습니다.

이 단계에서 시작 비용과 총비용, 반환되는 행 수 및 반환되는 행의 평균 너비를 추정합니다.

 

Sort

조회된 행에 대해 last_name을 기준으로 정렬하는 Sort 작업을 수행합니다.

이 작업의 비용 추정치와 반환되는 행 수 및 너비가 포함됩니다.

 

Projection

최종 결과 집합을 생성하기 위해 필요한 열(first_name과 last_name)만 선택하는 Projection 작업이 수행됩니다.

이 작업의 시작 비용, 총 비용, 반환되는 행 수 및 너비를 추정합니다.

 

이렇게 생성된 실행 계획의 각 plan node는 실행 시간과 리소스 사용량을 최적화하기 위해 PostgreSQL Planner에 의해 결정됩니다.

실행 계획을 분석하면 다음과 같은 중요한 정보가 표시됩니다.

 

Startup Cost

Index Scan을 시작하는 데 드는 예상 비용입니다.

시작 비용이 낮을수록 쿼리가 더 빠르게 시작됩니다.

 

Total Cost
Index Scan, Sort 및 Projection을 포함하여 실행 계획 전체를 실행하는 데 드는 예상 총비용입니다.

총비용이 낮을수록 쿼리 실행이 더 효율적입니다.

 

Rows

각 plan node에서 반환될 것으로 예상되는 행의 수입니다.

반환되는 행 수가 많을수록 처리해야 할 데이터가 더 많아집니다.

 

Width

각 plan node에서 반환되는 행의 예상 평균 너비(바이트)입니다.

평균 너비가 작을수록 더 적은 메모리를 사용하여 쿼리를 실행할 수 있습니다.

 

index scan 외에 Sequential scan과 bitmap index scan 등이 실행될 수도 있으며
Join이 들어가는 경우 Nested Loop , Merge Join, HashJoin 등이 실행될 수도 있습니다

 

SELECT e.first_name, e.last_name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 50000
ORDER BY e.last_name;

Sequential Scan

테이블에서 모든 행을 읽고 지정된 조건(salary > 50000)에 따라 필터링하는 Sequential Scan이 사용될 수 있습니다.

인덱스가 없거나 인덱스를 사용하지 않는 경우에 이 작업이 수행됩니다.

 

Bitmap Index Scan

salary에 인덱스가 있다면, 비트맵 인덱스 스캔을 사용하여 salary가 50,000보다 큰 행을 찾은 다음 비트맵 힙 스캔을 통해 실제 행을 검색할 수 있습니다.

 

Nested Loop

employees 테이블과 departments 테이블을 조인하기 위해 Nested Loop 조인 알고리즘이 사용될 수 있습니다.

이 알고리즘은 첫 번째 테이블의 각 행을 두 번째 테이블의 각 행과 비교하여 일치하는 행을 찾습니다.

 

Merge Join

department_id가 정렬된 상태라면, Merge Join 알고리즘을 사용하여 두 입력 테이블을 병합할 수 있습니다.

이 알고리즘은 지정된 조인 조건에 따라 정렬된 두 입력 테이블을 병합합니다.

 

Hash Join

큰 테이블의 조인 키 값에 대한 해시 테이블을 생성하여 다른 테이블의 행을 조사하고 일치하는 행을 찾는 Hash Join 알고리즘이 사용될 수 있습니다.

이 알고리즘은 일반적으로 Nested Loop보다 더 빠른 조인 성능을 제공합니다.

 

 

Sort, Aggregation이 상황에 따라서 실행 될 수 도 있으며 
이후에 Projection이 진행됩니다.

 

일종의 이런 흐름도로 한눈에 확인할 수 있습니다.

 

Explain option

---

PostgreSQL에서 실행 계획을 보려면 쿼리 전에 EXPLAIN 명령을 사용할 수 있습니다.

그러면 쿼리를 실제로 실행하지 않고 예상 계획이 반환됩니다.

 

SELECT a.id, a.name, b.total_sales
FROM customers a
JOIN sales b ON a.id = b.customer_id
WHERE a.active = true
ORDER BY b.total_sales DESC
LIMIT 10;

 

이 쿼리는 총매출이 가장 높은 상위 10명의 활성 고객을 검색합니다.

쿼리를 실제로 실행하지 않고 예상 실행 계획을 보려면 쿼리 앞에 EXPLAIN 명령을 사용하면 됩니다.

 

EXPLAIN
SELECT a.id, a.name, b.total_sales
FROM customers a
JOIN sales b ON a.id = b.customer_id
WHERE a.active = true
ORDER BY b.total_sales DESC
LIMIT 10;

 

출력 결과

 

postgres=# EXPLAIN
SELECT a.id, a.name, b.total_sales
FROM customers a
JOIN sales b ON a.id = b.customer_id
WHERE a.active = true
ORDER BY b.total_sales DESC
LIMIT 10;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Limit  (cost=50.46..50.48 rows=10 width=552)
   ->  Sort  (cost=50.46..51.96 rows=600 width=552)
         Sort Key: b.total_sales DESC
         ->  Hash Join  (cost=12.28..37.49 rows=600 width=552)
               Hash Cond: (b.customer_id = a.id)
               ->  Seq Scan on sales b  (cost=0.00..22.00 rows=1200 width=36)
               ->  Hash  (cost=11.40..11.40 rows=70 width=520)
                     ->  Seq Scan on customers a  (cost=0.00..11.40 rows=70 width=520)
                           Filter: active
(9 rows)

 

쿼리를 실행하고 실제 실행 통계를 제공하는 EXPLAIN ANALYZE를 사용할 수도 있다.

 

EXPLAIN ANALYZE
SELECT a.id, a.name, b.total_sales
FROM customers a
JOIN sales b ON a.id = b.customer_id
WHERE a.active = true
ORDER BY b.total_sales DESC
LIMIT 10;

 

참조

PostgreSQL: Documentation: 15: EXPLAIN 

Explore the secrets of SQL Server execution plans (sqlshack.com)