[PostgreSQL] Planner, Optimizer (플래너, 옵티마이저)란? Planner, Optimizer (플래너, 옵티마이저)의 개념과 작동방식

1. Planner / Optimizer (플래너 / 옵티마이저) 란?

Planner / Optimizer는 쿼리의 최적화된 실행 계획을 만들어낸다. 한 개의 SQL 쿼리도 결과는 같지만, 다양한 방법과 순서로 실행될 수 있다. Planner / Optimizer (이후는 Planner로 명칭)는 실행 가능한 각각의 플랜을 검사하여 궁극적으로 가장 빠르게 실행 "기대"되는 플랜을 선택한다. 

1-1. Genetic Query Optimize

가끔 쿼리의 실행 가능한 방식들을 검사하는 데만도 굉장히 큰 시간과 메모리가 소모된다. (특히, 많은 양의 join을 포함한 쿼리를 실행할 때 발생) 합리적인 쿼리 플랜(최고일 필요는 없다)을 합리적인 시간 내에 찾기 위해, PostgreSQL은 join수량이 임계치를 초과하면 Genetic Query Optimizer를 사용한다. Genetic Query Optimize는 최적의 조인 순서를 찾기 위해, 일부 조인 순서를 시도 후 적합성 함수를 통해 조인 순서의 적합성을 평가하여 최적의 plan을 선택한다. 메모리와 시간을 절약할 수 있지만, 항상 최선의 답을 찾는 것은 아니기에 효율성을 보장하지 못한다.

 

플래너의 검색 과정은 paths라고 불리는 데이터 구조를 활용한다. 이는 Planner가 결정을 내리는데 필요한 정보만을 포함하는 간소화된 표현이다. 최적의 paths가 결정되면, 실행기에 전달하기 위한 plan tree가 구축된다. 이 plan tree는 실행기가 쿼리를 실행할 수 있을 만큼 충분한 세부 정보를 가진 실행 계획을 나타낸다. 

2. 실행가능한 Plan 조회

Planner는 쿼리에서 사용되는 각각의 테이블에 대한 스캔 계획을 생성하면서 시작한다. 가능한 Plan들은 각 테이블의 인덱스에 의해 결정된다.

 

• 먼저, 어떤 조건, 구조던지 테이블에 대한 순차적 스캔 (sequential scan)은 언제나 가능하기에, 순차적 스캔 플랜은 무조건 실행된다.
• 그다음으로는, 테이블에 인덱스가 정의(ex. B-tree index) 되어 있고, 조건절에 상수 연산자를 포함(ex, attribute > 50000)하고 있다고 가정해 보자. 만약 조건절의 칼럼이 B-tree인덱스 키와 일치하고, 연산자가 인덱스가 지원하는 비교연산자 중 하나(<,>,= 등)인 경우, B-tree 인덱스를 사용하여 테이블을 스캔하는 또 다른 계획이 실행된다.
• 추가적인 인덱스가 존재하고, 쿼리의 조건절이 인덱스 키와 일치하는 경우, 추가적인 플랜들이 고려된다.
• 쿼리의 ORDER BY 절이나 병합 조인에 유용하게 쓰일 수 있는 정렬순서를 가진 인덱스에 대해서도 인덱스 스캔 플랜이 생성된다.

쿼리가 2개 이상의 테이블을 조인할 경우, 1개의 테이블을 스캔하는 모든 실행계획을 찾은 이후에, 관계를 조인하기 위한 plan이 계획된다. 다음은 테이블을 조인할 때 데이터에 접근하는 3가지 방식이다.

• Driving table - JOIN 할 때 먼저 접근되어 PATH를 주도하는 테이블 (아래 설명에서는 선행 테이블)
• Driven table  - 플래너에 의해 Driving 테이블이 결정된 후 나머지 테이블 (아래 설명에서는 후행 테이블)

2-1. Nested loop join 

후행 테이블이 선행테이블의 모든 열에 대해 스캔을 한다. 간단한 방식이지만 시간이 많이 든다. (하지만 후행 테이블이 인덱스 스캔에 의해 스캔될 수 있다면 매우 좋은 전략이 될 수 있다. 각 ROW를 스캔할 때 선행테이블의 각 키를 후행 테이블의 인덱스 키로 사용할 수 있기 때문)

2-2. Merge join

각 테이블이 조인이 시작되기 전에 조인 속성에 따라 각각 정렬된다. 그 후 두 테이블을 병렬로 스캔하고 일치하는 row들이 결합되어 JOIN ROWS를 생성한다. 각 테이블이 1번만 스캔되면 되기에 매력적인 방식이다. 머지 조인의 경우, 조인이 실행되기 전 정렬 과정에서 두 테이블은 조인 키 기준으로 정렬되어있어야 하는데, 다음 두 가지 방법으로 이루어질 수 있다.

• 명시적인 정렬 단계 - 데이터 베이스가 직접 데이터를 조인 키에 따라 정렬하는 작업 수행
• 인덱스를 사용한 스캔 - 이미 조인 키에 대해 인덱스가 구성되어 있을 경우, 인덱스를 사용하여 데이터를 정렬된 순서대로 접근하고 스캔한다. 추가적인 정렬과정 없이도 데이터를 정렬된 상태로 처리할 수 있게 해 준다.

2-3. Hash join

후행 테이블이 먼저 스캔되고 join 되는 칼럼을 hash key로 사용한 hash table 형태로 저장된다. 선행 테이블을 스캔하며 join 칼럼을 hash key로 사용하여 후행 테이블에서 생성된 해쉬 테이블 내에서 해당하는 값을 빠르게 찾은 후 두 테이블 간의 일치하는 ROWS를 결합한다. 두 테이블 간의 조인을 효율적으로 수행할 수 있게 해 주며 대량 데이터 처리에 유용하다.

 

(각 조인 방식에 대한 개념은 다시 정리할 예정)

 

쿼리가 두 개 이상의 테이블을 포함할 경우, 최종 결과는 한 번에 연산되는 것이 아니라, 각 조인 단계에서 2개의 테이블을 조인하며, 단계별로 조인된 결과를 트리 구조를 이루며 최종 결과를 단계적으로 구축해 간다.  Planner는 가능한 서로 다른 Join 순서를 확인하여 가장 빠른 경우의 수를 찾는다.

 

쿼리가 임계치 보다 적은 수의 테이블을 참조한다면, 최선의 조인순서를 찾기 위해 거의 모든 경우를 탐색한다. Planner는 조건 절에 직접적인 조인 조건이 존재할 경우 해당 조건의 테이블 간의 조인을 우선적으로 고려한다. 반면에 서로 직접적인 조인 조건이 없는 테이블 간에는 선택지가 없을 경우에만 수행된다. 이러한 순서로 join 조합을 찾으며 거의 모든 plan을 비교하여 가장 코스트가 낮은 것으로 예상되는 실행계획을 선택한다. 반면에 조인되는 테이블 수가 임계치를 넘을 경우, 위에서 설명한 Genetic Query Optimize 방식을 사용한다.

 

Paths에 의해 완성된 Plan tree에는 각 테이블들의 순서와 인덱스 스캔 정보, 조인 방식 (nested-loop, merge, hash 조인 노드), 정렬이나 집계함수 노드 같은 보조 단계들로 구성되어 있고 각 plan node 들은 다음과 같은 2가지 기능을 가지고 있다.

• selection - 특정 조건에 부합하지 않는 rows들을 삭제
• projection - 지정된 칼럼 값에서 파생된 새로운 칼럼의 연산 (ex, 칼럼 A, B의 합, 칼럼 C에 10배를 한 값 등)

Planner는 WHERE 조건절과 부합하는 selection조건과 결과 값과 일치하는 projection을 연결하여 최적의 노드를 plan tree에서 찾게 해 준다.

 

 

 

 

 

참고

https://www.postgresql.org/docs/16/planner-optimizer.html