3. 쿼리 속도 측정 방법

쿼리가 얼마나 걸리는지 속도를 측정하려면 어떻게 해야할 까요?

PostgreSQL 에서는 EXPLAIN 명령어를 지원합니다.
https://www.postgresql.org/docs/current/using-explain.html
위 공식내용을 약간 정리하겠습니다.

1) Query Plan

RDBMS는 대부분 쿼리 계획을 수립하고 최적화하기 위한 복잡한 기능을 포함하고 있습니다. 이를 통해 주어진 쿼리에 대해 최적의 실행 계획을 선택하고 데이터에 효율적으로 액세스할 수 있습니다. 이러한 실행 계획을 분석하는 명령어가 'EXPLAIN' 입니다.

Query Plan의 구조는 plan node로 이루어진 tree 구조입니다. 이때 가장 하위에 있는 node들은 scan node입니다. 이러한 scan node에서 순차 스캔(sequential scans), 인덱스 스캔(index scans) 등등의 작업이 일어납니다.

해당 작업이 일어난 후에, 스캔 작업의 결과물을 처리하기 위한 또다른 작업이 필요할 수도 있습니다. 예를들어, 집계함수를 사용 한다거나, 다른 테이블과의 JOIN, sorting 작업이 그러한 경우입니다. 이러한 작업들은 scan node 상위 node에서 처리합니다. 그리고 또 그 상위 노드에서 처리가된 노드를 다시 가공하게 되겠죠. 물론 그게 끝일 수도 있습니다.

이렇게 tree 구조로 query plan이 만들어지고, 상위노드의 작업은 하위 노드의 성능을 모두 포함하게 됩니다.

그래서 결국 DBMS는 최상위 노드의 cost값이 최소인 query를 선택합니다. 그리고 우리는 이 cost값을 줄이기 위해 노력해야 합니다.

• 위 사진에서 맨 윗줄이 해당 쿼리의 총 cost입니다.

• indent 가 되어있는 부분은 해당 node의 속성을 말합니다.

• -> 처리가 되어있는 부분은 하위 node를 의미합니다.

• 그러면 위의 query plan에서는 어떤일이 일어나는지 하위 노드부터 정리해볼까요

  • Parallel Sequential Scan 작업 (병렬처리)

  • 2-1. 1번 작업의 병렬 처리 작업을 하기 위해 Gather 노드가 포함된 계획을 자동으로 생성 및 처리

  • 2-2. index scan을 통해 row 1개를 가져옴

  • Nested Loop Left Join을 통해 2-1 과 2-2 작업의 결과를 join

  • created_date 조건에 맞게끔 sorting

• 최종 실행 결과 분석
  ( cost는 시간이 아니라, I/O, CPU 할당과 같은 작업 비용을 말합니다. )

  • 예상 시작 비용 : 51103.86
    이는 마지막 출력 단계가 시작되기 전에 소요된 비용으로, sorting 비용을 의미합니다.

  • 예상 총 비용 : 51103.86
    이는 모든 노드의 작업이 완료될 때까지 실행된다는 가정하에 걸린 시간입니다.

  • 최종 row 수 : 1
    이 계획 노드에 의해 출력되는 행의 예상 수입니다. 다시 말해, 노드가 완료될 때까지 실행된다는 가정하에 계산됩니다.

  • 총 바이트 : 594
    이 계획 노드에 의해 출력되는 행의 예상 평균 너비(바이트 단위)입니다.

• 뒤의 actual time은 EXPLAIN ANALYZE 명령어의 도출값으로, 실제 걸린 시간과, 반환된 row수를 의미합니다.

2) EXPLAIN vs EXPLAIN ANALYZE

• EXPLAIN
  쿼리 계획 예상 결과를 나타냅니다.

• EXPLAIN ANALYZE
  직접 쿼리를 실행해보고, 걸린 시간과 반환 행 수를 나타냅니다.
  '직접 쿼리를 실행'하기 때문에, DB에 변경사항이 생길 수 있다는 점을 반드시 기억하셔야 합니다.

4. 두가지 쿼리의 실행 결과 분석

• JOIN만 사용한 경우

  

• 서브쿼리를 사용한 경우

  

놀랍게도 차이는 없습니다.
두가지 모두 같은 방식으로 Data를 불러오는 것을 확인할 수 있습니다.

5. 정리

여러분들도 저와 같이 당연히 subquery를 사용하는것이 더 빠를 것이라고 예상하셨는지 모르겠습니다. 그렇지 않다고 생각했다면 존경합니다.

이번 포스트에서는 PostgreSQL의 쿼리 계획 및 실행 결과를 분석해주는 EXPLAIN과 EXPLAIN ANALYZE를 알아보았습니다.

내가 작성한 쿼리가 DBMS 안에서 어떠한 과정으로 결과를 도출해내는지 검증을 통해, 앞으로 작성할 쿼리에 대해 '속도'를 고려하면서 작성하셨으면 좋겠습니다.