PostgreSQL 실행계획 분석하기 1편 (실행계획 읽는 방법)

PostgreSQL 실행계획 분석(기본)

 

개요

• 개발을 진행하면서 종종 실행 계획을 참조하게 되지만, 대부분은 실행 시간과 인덱스를 잘 타는지 정도만 확인했다.
• 실행 계획의 전체적인 구성이나 옵티마이저의 결정 과정에 대한 이해가 부족하다고 느꼈다.
• 그래서 틈틈히 실행계획을 읽는 방법이나 분석하는 방법을 찾아보았고 이번 기회에 합습한 내용들을 정리해보려고 한다.
• 실행계획을 분석하는 방법은 6편 정도에 걸쳐서 정리해 볼 생각이다.
• 이번 편에서는 실행계획을 구성하는 구성요소와 실행계획을 읽는 방법에 대해서 알아보자.

 

필수 사전 지식

• PostgreSQL 옵티마이저

 

테이블 생성

• 테스트에 사용될 3개의 테이블을 만든다.

-- customers 테이블 생성
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100)
);

-- products 테이블 생성
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    product_price NUMERIC
);

-- orders 테이블 생성
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

데이터 삽입

• 위에서 만든 테이블에 적절한 수의 데이터를 넣는다.

-- customers 더미 데이터 삽입 (1000건)
INSERT INTO customers (customer_name, customer_email)
SELECT
    'Customer ' || g,
    'customer' || g || '@example.com'
FROM generate_series(1, 1000) AS g;

-- products 더미 데이터 삽입 (10000건)
INSERT INTO products (product_name, product_price)
SELECT
    'Product ' || g,
    (RANDOM() * 100)::NUMERIC
FROM generate_series(1, 10000) AS g;

-- orders 더미 데이터 삽입 (100000건)
INSERT INTO orders (customer_id, product_id)
SELECT
    (RANDOM() * 999 + 1)::INT,
    (RANDOM() * 9999 + 1)::INT 
FROM generate_series(1, 100000) AS g;

 

테이블과 데이터를 만들었으니 이제 본격적으로 실행계획을 읽는 방법에 대해서 알아보자

실행계획 분석

실행계획을 읽기 위해서는 NODES, Cost, Actual Time, Rows, Width, Loops, Filter, Buffers, Planning Time, Execution Time에 대해서 알아야한다.

NODES

• 노드는 쿼리의 한 부분을 실행하는 개별 작업 단위를 나타낸다.
• 예를 들어, 테이블 스캔, 조인, 정렬 등의 작업이 각각 다른 노드로 표현될 수 있다.
• 실행계획은 노드 단위로 순서에 맞게 읽는 것이 중요하다.
• 각 노드는 ->로 표현된다. 또한 루트노드는 ->가 붙지 않는다.
  • 따라서 노드의 수는 ->의 개수 + 1이다.
• 예를 들어, 아래와 같은 실행계획이 있다고 가정하자.

• 실행계획에 ->가 3개이기 때문에, 루트 노드를 포함해서 총 4 개의 노드로 구성되어 있음을 알 수 있다.
• 또한, 노드를 읽는 순서는 위에서 아래로, 안쪽에서 바깥쪽으로 읽으면 된다.
  • 다시 말해서, 동일한 레벨에서는 위에서 아래로 읽고, 안쪽에 노드가 있다면 안쪽부터 바깥쪽으로 읽는다.
• 따라서 위 사진에서 빨간색으로 표시된 숫자가 순서이다.
• https://explain.dalibo.com/ 사이트에 가면 아래와 같이 실행계획을 노드 단위로 한눈에 볼 수 있게 도와준다.

Cost

• PostgreSQL의 Explain에서는 Startup Cost, Total Cost라는 2개의 비용을 제공한다.

• Startup Cost는 첫 번째 레코드를 fetch 하는데 드는 비용이다. 위 예제에서는 0.00이다.
• Total Cost는 전체 레코드를 fetch 하는데 드는 비용이다. 위 예제에서는 1887.00이다.
• cost 값의 단위는 물리적 단위가 아닌 PostgreSQL 쿼리 옵티마저가 사용하는 추정치이다.
  • 이 추정치는 seq_page_cost, random_page_cost, cpu_operator_cost 등에 따라 결정된다.

Actual Time

• Actual Time은 첫 번째 레코드를 fetch 하는데 소요된 시간과 전체 레코드를 fetch 하는데 소요된 시간을 뜻한다.
• EXPLAIN ANALYZE 명령을 사용할 때 제공된다. (EXPLAIN 만 실행하면 Actual Time은 나오지 않는다.)
• 단위는 ms이다.

 

 

Rows

• ROWS는 PostgreSQL의 Explain에서 특정 노드(node)가 출력하는 행의 수를 말한다.
• PostgreSQL의 Explain에서는 예측 로우 수와 실제 로우 수를 제공한다.

• 만약 예측 로우 수와 실제 로우 수의 차이가 크면 옵티마이저가 실행계획을 잘못 세울 확률이 크다.

Width

• Width는 레코드의 평균 길이다.
• 아래 그림을 보면 select 하고자 하는 칼럼이 order_id, customer_id, product_id, order_date 이렇게 4개이고, 각각의 평균 크기가 4,4,4,8이다.
• 따라서 4개 칼럼의 평균 크기의 합인 20(4+4+4+8)인 것을 확인할 수 있다.

 

Loops

• LOOPS는 PostgreSQL의 실행 계획에서 특정 노드(node)가 실행된 총횟수를 말한다.

• 노드에서 총 소요된 시간을 계산하려면, LOOPS 값에 ACTUAL TIME의 값을 곱해야 한다.
• 즉, actual time=10.313..12.530 및 loops=10인 경우, 각 실행의 평균 시간이 10.313ms에서 12.530ms임을 의미하고, 이를 10회 반복하므로 총 소요 시간은 각각의 시간에 10을 곱한 값이 되는 것이다.

 

Filter

• Filter 에는 필터링 조건이 나온다.
  • 현재 where 절에 product_id > 7000 이므로 Filter에 해당 조건이 명시된 것을 확인할 수 있다.

• Rows Removed by Filter는 필터 조건에 의해서 필터링된 레코드의 수를 의미한다.
• 위 예제에서는 69965개의 레코드가 필터링된 것을 확인할 수 있다.

Buffers

• Buffers는 쿼리 실행 중에 발생한 IO 횟수를 의미한다.
• EXPLAIN (ANALYZE, BUFFERS) 명령어로 조회 시 조회되는 항목이다.

• Buffers는 아래와 같이 크게 4가지로 구분되어 표현된다.
• shared read
  • 공유 버퍼에서 디스크로부터 읽은 데이터 블록의 횟수이다.
  • shared read가 발생하면, 이는 필요한 데이터가 메모리에 이미 존재하지 않아 디스크에서 읽어야 했음을 의미한다.
  • 이 값이 높으면, 쿼리가 더 많은 디스크 I/O를 발생시키고 있음을 말한다.
• shared hit
  • 공유 버퍼에서 메모리로부터 읽은 데이터 블록의 횟수이다.
  • shared hit가 높으면, 쿼리가 효율적으로 데이터를 메모리에서 빠르게 읽고 있음을 의미한다.
• dirtied
  • 메모리 Read 시에 읽은 더티 블록 수이다.
  • 여기서 더티는 데이터가 수정되어 메모리 내의 버퍼와 디스크 상의 데이터가 불일치하게 되었음을 의미한다.
• written
  • 디스크로 기록한 더티 블록 수이다.
• 따라서, 위 예제에서는 shared hit 만 나와있으므로 디스크에 접근하지 않고 메모리 상에서만 데이터를 읽어왔다는 것을 알 수 있다.

Planning Time

• 쿼리 파싱 시간을 의미한다.

 

Execution Time

• 쿼리 실행 시간을 의미한다.
• 따라서, 최종적으로 가장 중요한 수치라고 할 수 있다.

• 따라서, Execution Time을 비교하면서 쿼리를 튜닝하면 된다.

References

• PostgreSQL 9.6 성능이야기, 저자 김시연, 최두원
• https://www.palantir.com/docs/foundry/slate/references-query-perf/index.html
• https://www.postgresql.org/docs/current/sql-explain.html