sungyup's.

PostgreSQL / Postgres Deep Dive / 4.5 Advanced Query Tuning

4.5Advanced Query Tuning

Cost의 개념

앞선 포스팅에서 EXPLAIN 키워드로 얻은 결과의 대략적인 의미를 공부했다. 거기서 나온 Cost의 개념에 대해 보다 상세히 알아보자.

Cost 이해하기

Cost(비용)란, (단순하게 정의하면) 특정 실행 계획(query plan)을 수행하는데 필요한 리소스 소비량, 또는 시간을 의미한다.

현재 우리의 예시에 나온 비용을 직접 계산해보자.

Seq Scan on comments (cost = 0.00..1589.10 ...)

comments 테이블의 모든 데이터를 순차 검색(Seq Scan)하는 과정을 나눠보면 아래와 같다.

  1. comments 힙 파일을 연다.
  2. 첫번째 블록의 모든 데이터를 메모리에 로드한다.
  3. 쿼리에서 요청한 조건에 따라 각 행들을 하나하나 처리(필터링)한다.
  4. 해당 블록에서 처리가 끝나면 다음 블록으로 넘어가 반복한다.

우리의 예시에서 comments 테이블엔 60,410 행이 있고 블록(페이지)은 985개가 있다. 여기서 정확한 숫자는 모르니 전제를 하자면,

  • 한 페이지를 처리하는 로드 비용: 1
  • 한 열(튜플)을 처리하는 비용: 0.01

위와 같이 전제한다면, 985개 페이지의 60,410행을 처리하는데 드는 비용은 아래와 같이 계산할 수 있다.

(985 * 1) + (60,410 * 0.01)

이 값은 1,589.1인데, 앞서 봤던 EXPLAIN의 결과에 보면 있는 0.00..1589.10 에서 봤던 숫자임을 알 수 있다! 즉, PostgreSQL도 쿼리 계획의 비용을 추정할 때 이런 식의 로직을 통해 계산하고, 이 추정된 비용으로 실행 경로를 비교하고 선택한다.

실제 PostgreSQL의 cost 계산식

이 계산식은 포스트그레스큐엘 공식 문서에 있으며, 아래의 값들을 다 더해 cost를 산출한다.

오른쪽에 곱해지는 값들은 기본적으로 상수이며, 사용자가 재정의할 수도 있다. 하지만 물론 이 상수들은 PostgreSQL이 근거를 가지고 산출한 것이므로 굳이 바꿀 이유는 적다.

  • (# pages read sequentially) * seq_page_cost(기본: 1.0)
  • (# pages read at random) * random_page_cost(기본: 4.0)
  • (# rows scanned) * cpu_tuple_cost(기본: 0.01)
  • (# index entries scanned) * cpu_index_tuple_cost(기본: 0.05)
  • (# times function/operator evaluated) * cpu_operator_cost(기본: 0.025)

참고로, 이외에도 parallel query 시에는 별도 요소가 추가될 수 있다. 하지만 기본적으로는 위의 계산식으로 cost에 영향을 끼치는 변인들을 이해하면 무리가 없다.

Startup Cost와 Total Cost

이전 포스팅에서 본 쿼리 계획 결과에서, Hash Join의 cost로 나온 값은 2개(8.31, 1756.11)였다.

이 중 앞의 값은 첫번째 행을 생성하기 위한 비용(Startup cost), 뒤의 값은 모든 행을 생성하기 위한 비용(Total cost)이다. 이 값들의 편차는 해당 쿼리 계획 단계가 뭔지에 따라 달라지는데, 예를 들어 Seq Scan의 경우 앞의 값이 늘 0.00이다. Hash는 앞의 값과 뒤의 값이 같다. 앞의 값과 뒤의 값이 같은 경우, 모든 행들이 로드되어야만 데이터 처리를 시작할 수 있다는 의미다.

상위 노드는 하위 노드들의 비용의 합이다. 예를 들어, Hash Join은 8.31부터 시작하는데, 이는 하위 노드인 Seq Scan(0.0)과 Hash(8.3)의 합계다.

Index가 있어도 사용을 안 하는 경우

우리의 likes 테이블에는 752,009행의 데이터가 있다. 이 중 created_at < '2013-01-01'은 62,989행으로 10% 미만이다.

아래의 쿼리를 실행해보면, 쿼리 계획은 Seq Scan on likes고 cost는 0.00..14248.11로 나온다.

sql
EXPLAIN SELECT * FROM likes
WHERE created_at < '2013-01-01'

쿼리 성능 개선을 위해 인덱스를 추가해보자.

sql
CREATE INDEX likes_created_at_idx ON likes (created_at);

개선 후, 위의 쿼리를 실행해보면 쿼리 계획은 Bitmap Heap Scan on likes고 cost는 1213.15..6868.78로 나온다. 즉, 성능이 개선되었다.

그런데, created_at 조건을 > '2013-01-01'로, 부호를 뒤집으면 쿼리 계획이 Seq Scan on likes로, 다시 Seq Scan으로 돌아간 것을 확인할 수 있다. 인덱스를 만들었는데 사용하지 않고 Seq Scan을 하는 것은, 90%나 되는 데이터를 조회하는데 굳이 인덱스를 써서 Bitmap Heap Scan을 하는 것보다 Seq Scan을 하는 것이 더 성능적으로 낫다고 PostgreSQL이 판단했기 때문이다.

따라서, 이 경우 굳이 인덱스를 쓰라고 강제하는 것도 도움이 되지 않는다. 오히려, 인덱스를 쓰지 않는 것이 더 빠르기에 쿼리를 이렇게 실행한 것이니 자연스럽다고 할 수 있다.

PreviousBasic Query Tuning
PostgreSQL
NextCommon Table Expressions
PostgreSQL