[Postgresql] 쿼리 실행 계획 분석하기

운영 중인 서비스의 전체적인 조회 속도가 느려져 사용하는 데이터베이스를 최적화 하기 위해 ORM의 explain, analyze를 통해 쿼리 실행 계획(Query execution plan) 확인하고 분석하여 최적화를 진행하면서 나온 개념들을 정리하였습니다. 쿼리를 직접 짜지 않아도 ORM과 같은 기술들이 존재하지만 작성한 내용이 슬로우 쿼리가 발생한다면 조금 더 확인하기 위해 쿼리 플랜 뿐만 아니라 옵티마이저에 대한 내용이 알아두면 좋겠다고 생각했습니다.

Query Plan이란?
데이터베이스 엔진은 쿼리를 수행할 때 어떤 방식을 사용하고 어떤 순서로 쿼리를 수행 할지에 대한 계획을 세우게 되는데, 이것을 쿼리 실행 계획(Query execution plan) 또는 쿼리 플랜(Query plan)이라고 부른다.

PostgreSQL은 다음 5가지 스캔 방식을 사용한다.

• Sequential Scan
  Sequential Scan은 테이블의 모든 데이터를 하나씩 확인하는 방법입니다. 주로 인덱스가 없는 column을 조건으로 검색할 경우에 사용됩니다.
• Index Scan
  Index Scan은 말그대로 인덱스를 탐색하는 방식을 말한다. 인덱스가 만들어진 column을 조건문으로 조회할 때 데이터베이스가 선택하는 방식이다.
• Index Only Scan
  Index Only Scan은 인덱스에 필요한 데이터가 있는 경우 사용되는 방식이다. 인덱스에 필요한 값이 있기 때문에, 실제 데이터를 fetch하지 않아도 된다.
• Bitmap Scan
  테이블 랜덤 액세스 횟수를 줄이기 위해 고안된 방식이다.
  Index Scan 방식과 Bitmap Index Scan 방식을 결정하는 기준은 인덱스 칼럼의 Correlation 값이다.
  Correlation이란 인덱스 칼럼에 대한 테이블 레코드의 정렬 상태이다. (클러스터링 팩터)
  즉, Correlation이 좋으면 Index Scan 방식을, 나쁘면 Bitmap Index Scan 방식을 사용한다.
  Bitmap Index Scan 방식은 액세스할 블록들을 블록 번호 순으로 정렬한 후에 액세스한다.
  이로 인해, 테이블 랜덤 액세스 횟수가 크게 줄어든다. (블록당 1회)
  테이블 블록 번호 순으로 액세스하므로, 인덱스 키 순서대로 출력되지 않는다.
• TID Scan
  TID는 Tuple Indecator의 줄임말로, 특정 row의 실제 위치를 나타내는 6byte 값으로, 4byte는 페이지 번호, 2byte는 페이지 내의 튜플 인덱스를 나타낸다고 합니다.