DB 실행계획 확인법(in PostgreSQL)

일반적인 서버 - 클라이언트 모델에서 서버 측은 데이터의 영속성을 위해 Database를 활용한다. 

이러한 상황에서 데이터의 흐름은 간략하게 표현하면 클라이언트 - WAS - DB 의 형태가 되는데, 일반적인 RDB 기준 데이터들은 모두 Disk에 적재되기 때문에 시스템 콜을 유발하게 되고 서버 환경 내 병목이 가장 많이 발생하는 지점 중 하나가 된다.

결국 WAS - DB 간 응답 속도의 개선이 전체 응답 속도의 개선으로 이어지는 경우가 많기 때문에 쿼리의 개선을 이루어내는 것은 몹시 중요하다.

여기까지 오고나면 개선이 중요하다는 것은 이해가 되는데, 쿼리 수정을 통해 개선이 이루어지고 있는지 판단하는 기준은 무엇으로 삼아야할까?

물론 클라이언트 측에서 개발자 도구를 통해 응답 속도가 어떻게 변화하는지 확인해봐도 되고, 혹은 서버 측에서 DB의 응답 시간을 직접 측정해봐도 되지만 여기에서는 그보다는 더 실전에서 활용할 수 있는 실행 계획에 대해 간단히 다루려고 한다.

 

들어가기 앞서

해당 문서의 예시들은 모두 아래의 테이블 상황을 가정하고 작성

각 테이블들은 모두 더미 데이터가 포함

예시 테이블 ERD

• Company
  • idx: id
  • rows: 200
• Item
  • idx: id
  • rows: 2000
• User
  • idx: id
  • rows: 10000
• Purchase
  • idx: id
  • rows: 500000

 

실행 계획 쿼리 작성법

실행 계획의 확인은 크게 EXPLAIN 문과 EXPLAIN ANALYZE 문으로 나뉜다.

전자의 경우엔 특정 쿼리에 대해 DB가 예상한 결과를 출력하며, 후자는 예상과 더불어 실제 쿼리를 실행시켜 본 결과까지 출력한다.

해당 문서에서는 후자(EXPLAIN ANALYZE) 만을 다루겠음.

실행법

EXPLAIN (ANALYZE, VERBOSE, BUFFERS, TIMING) (
  -- 확인하기 위한 쿼리
  SELECT * FROM purchase WHERE count > 5;
)

결과

구매 이력 테이블에서 한번에 구매한 물품의 수가 5개가 넘는 구매 이력만을 조회하는 쿼리의 실행 계획을 출력

 

실행 계획 해석

읽는 법

노드에 각각 어떤 내용이 들어가는지 확인하기 앞서, 실행 계획을 읽는 방법은 아래와 같다.

1. 실행 계획은 각 작업을 노드로 표현한 그래프 형태로 나타나며, 특정 노드는 하위 노드를 포함할 수 있고 노드 간 뎁스는 화살표로 표현된다.
2. 같은 뎁스의 작업 간에는 위에서부터 실행되고, 뎁스가 다를 경우엔 더 깊은 노드의 작업이 먼저 실행된다.
3. 각 노드 하위에는 해당 작업에 대한 추가적인 설명이 포함된다. (위 예시 기준 Output, Filter 등)

노드 구성 요소

실행 계획의 각 노드에는 아래의 요소들이 포함되어 있다.

• 비용(Cost)
  • 따로 단위가 없는 추상적인 값이지만, 일반적으로 데이터를 가져오는데 필요한 Disk I/O의 빈도와 그 외 사용되는 컴퓨팅 리소스를 종합한 값
  • 컴퓨팅 리소스를 총제적으로 함축하고 있기 때문에 쿼리 실행 시간과 정비례하지는 않지만, 쿼리 실행에서 Disk I/O가 차지하는 비중이 크다보니 어느정도의 경향성은 존재
  • 2개의 값이 반환되며, 하나는 조건에 맞는 첫번째 row를 찾을 때까지 걸리는 비용이고 다른 하나는 조건을 만족하는 모든 데이터를 찾을 때까지 걸리는 비용
• 로우(Rows)
  • 특정 노드의 결과로 반환된 데이터(row)의 수
• 폭(width)
  • 특정 노드의 결과로 반환된 데이터의 평균 width 값(단위: 바이트)
• 버퍼(Buffers)
  • Buffers 옵션을 켰을 때만 나타나는 값
  • 크게는 hit과 read가 존재하는데, hit은 버퍼 캐시에서 읽어온 블록의 수이고 read는 Disk IO를 통해 일겅온 블록의 수(블록 = 일반적으로 8kb, DB의 모든 IO는 블록 단위로 행해짐)
• 노드 타입
  • Seq Scan : 테이블 Full Scan
  • Index Scan : 인덱스를 활용한 테이블 스캔
  • Index Only Scan : 쿼리에서 요구되는 모든 데이터가 인덱스에 전부 존재하여 인덱스만 스캔
  • Nested Loop Join : 테이블을 NL 조인 형식으로 통합
  • Hash Join : 테이블을 해시 조인 형식으로 통합
  • Sort : 테이블 정렬 수행
  • Aggregate : 집계 함수를 통해 데이터 통합

 

다양한 쿼리 예시

1. 인덱스 필터링 예제

쿼리

SELECT * 
FROM item
WHERE id > 21800
ORDER BY id ASC

결과

해석

• item 테이블에서 id가 21800보다 큰 데이터만을 조회하고 이를 id 순으로 정렬하는 쿼리
• id가 인덱스로 설정되어 있기 때문에 Index Scan이 수행. 이 때 결과 rows 수가 200 뿐이기 때문에 Index Scan이 실행되었다고 보는 것이 맞고 인덱스를 통해 찾고자하는 범위가 더 넓을 경우엔 단순 테이블 풀스캔으로 수행될 가능성도 존재
• ORDER BY 절이 있음에도 SORT 노드가 나타나지 않았는데, 이는 이미 정렬된 인덱스를 기준으로 정렬을 요청하였기 때문에 생략 가능

2. 조인 예제

쿼리

SELECT * 
FROM public.purchase p, public.user u
WHERE u.id = p.user_id AND u.avg_income > 80000000

결과

해석

• 연간 평균소득이 8000만원이 넘는 고객의 데이터와 구매 목록을 확인하는 쿼리
• user 테이블에서 연간 평균소득 조건을 만족하는 데이터를 테이블 풀스캔 방식을 통해 확인
• 필터링된 user 데이터를 통해 조인 기준 칼럼인 user.id 값을 키로 갖는 해시테이블 생성
• purchase 테이블에서 user_id 칼럼이 해시테이블의 키 값으로 존재하는 데이터들을 풀스캔 형식으로 획득
• 드라이빙 테이블의 필터링된 결과가 더 적었다면 NL 조인을 선택했을 가능성 UP

3. 통계 예제

쿼리

SELECT u.id AS user_id, SUM(i.price * p.count) AS total_spent
FROM 
	public.purchase p, 
	public.item i,
	(
		SELECT id
		FROM public.user
		ORDER BY avg_income DESC 
		LIMIT 10
	) u
WHERE u.id = p.user_id AND i.id = p.item_id AND p.created_at BETWEEN NOW() - interval '1 month' AND NOW()
GROUP BY u.id

결과

해석

• 연간 수입이 가장 높은 열명의 유저의 지난 한달 간 사용 금액 통계
• user 테이블을 풀스캔하여 필요한 데이터를 가져온 뒤 이를 통해 소득 순으로 상위 10개의 데이터를 컷팅하고 이를 임시 테이블 u로 사용
• u의 데이터를 틍해 id를 기준으로 해시 테이블 생성
• 해시 테이블을 이용하여 u와 purchase 테이블 조인
• item 테이블 내에서 이전 작업을 통해 조인된 테이블의 item_id 값을 이용한 인덱스 스캔 작업 수행
• 한번 해시 조인을 수행한 테이블과 item 테이블에서 인덱스 스캔을 통해 획득한 데이터 간의 NL 조인
• u.id를 기준으로 그룹화 하기 위한 소팅 수행
• 소팅 결과를 바탕으로 통계 값 최종 생성