개요
데이터베이스에 쿼리를 실행할 때 기대하는 성능이 나오지 않는 경우가 있다. 대표적으로 데이터 조회에 너무 오랜 시간을 소모하는 상황인데, PostgreSQL의 경우 쿼리 실행 계획을 수립하고, 실행한다. 쿼리 구조와 데이터 속성에 맞는 계획을 선택해야 하기 때문에 데이터베이스는 좋은 계획을 선택하기 위한 플래너를 포함하고 있는데, EXPLAIN 명령을 사용하면 플래너가 생성한 쿼리 계 획을 확인할 수 있다. 필요시 이 계획을 바탕으로 쿼리 성능 개선을 시도해보아야 한다.
EXPLAIN
EXPLAIN은 실행 계획을 확인할 쿼리문 맨 앞에 추가함으로써 사용한다. 기본적으로 텍스트로 결과를 반환한다.
EXPLAIN [ ( option [, ...] ) ] statement쿼리 계획은 트리 구조로 되어 있고, 트리의 맨 아래는 테이블 행을 반환하는 스캔 노드에 해당한다. 테이블 접근 방식에 따라 순차, 인덱스, 비트맵 인덱스 스캔 등 다양한 스캔 노드가 존재한다.
쿼리에 조인, 집계, 정렬 등의 작업이 필요한 경우, 이 작업을 수행하기 위해 스캔 노드 위에 추가 노드가 있다.
EXPLAIN은 계획 트리의 각 노드에 대한 기본 노드 유형과 노드를 실행하기 위핸 만든 비용 추정치를 표시한다. 첫 번째 줄에는 계획의 예상 총 실행 비용이 표시되며, 플래너는 이 수치를 최소화하려고 한다.
아래 예시는 단순 테이블 조회에 대한 EXPLAIN 실행 계획이다.
EXPLAIN
SELECT *
FROM license_integrated
;
쿼리에 조건절이 없어 테이블의 모든 행을 스캔해야 하므로 플래너는 순차 스캔을 사용하기로 선택했다.
괄호 내의 속성 각각 다음과 같은 의미를 가진다.
- cost : 예상 시작 비용(0.00)과 예상 총 비용(98.99)
- rows : 계획 노드에서 출력되는 예상 행 수
- width : 계획 노드에서 출력되는 행의 예상 평균 너비
비용을 계산하는 방법은 일단 생략한다. 당장은 상위 노드의 비용에는 모든 하위 노드의 비용이 포함된다는 점만 기억하고자 한다.
조건절 추가 시
단순 테이블 조회 쿼리에 조건절을 추가해 본 결과이다.
EXPLAIN
SELECT *
FROM license_integrated
WHERE collect_status = 'DONE'
;
WHERE 절로 인해 예상 출력 행의 개수는 줄었다. 하지만 모든 행을 전부 방문해야 하며, 조건을 확인하는데 필요한 추가 CPU를 반영해 예상 비용은 감소하지 않았다.
정렬 추가 시
인덱스 순서와 일치하는 정렬 조건이 있는 쿼리에는 Index Scan이 자주 사용된다. ORDER BY의 순서와 인덱스의 정렬 순서가 동일할 때 인덱스 순서대로 데이터를 뽑아오면 추가 정렬이 필요하지 않기 때문이다.
플래너는 여러 방법으로 ORDER BY를 구현할 수 있는데, 아래 예시는 명시적으로 sort 단계를 추가한 것이다.
EXPLAIN
SELECT *
FROM license_integrated
WHERE collect_status = 'DONE'
ORDER BY license_id
;
참고 문서
https://www.postgresql.org/docs/current/sql-explain.html
https://datarian.io/blog/postgresql-using-explain