PostgreSQL & EDB PAS - 실행 계획 재사용( Plan Reuse)

글을 작성하기 전에

이 포스팅에서는 PostgreSQL SQL 처리 과정 시 리소스를 줄일 수 있는 실행 계획 재사용 여부에 대해 확인해보려고 합니다. 실행계획 재사용에 대해 확인을 위해 SQL 처리 과정에 대한 내용도 간단하게 포함되어 있습니다. 추후 PostgreSQL SQL 처리 과정에 대한 포스팅도 작성 할 예정입니다.

 

PostgreSQL & EDB PAS - 실행 계획 재사용( Plan Reuse)

결론을 먼저 기술하자면, PostgreSQL은 세션 전체적으로 실행계획을 공유하지 않습니다. 각 세션마다 실행 계획을 공유할 수 있으나 그것은 특정한 PREPARE 구문 사용시에만 적용됩니다.

SQL Parsing

SQL요청이 Client로부터 들어오게 되면 DB 내부에서 다양한 과정을 거쳐 쿼리가 수행되게 됩니다.

대부분의 데이터베이스는  Syntax(문법)를 분석하고, SQL이 실제로 적용 가능한지 Semantics(의미)를 분석하고 옵티마이저가 다양한 실행계획을 수립하여 최적의 플랜을 선택하는 과정을 거쳐 수행되는데요. 이 큰 맥락에서 각 DBMS 마다 세부 로직은 일부 다르고, 부르는 명칭이 다릅니다. (ex)  세부 프로세스는 다르지만 Semantics 분석 시 각각의 부르는 명칭이 다름. MySQL - Preprocessor 과정, PostgreSQL - Analyzer 과정, Oracle - Parse 과정에서 전부 처리)  

 

여기서 말하는 SQL Parsing란 문법을 분석하여 SQL 문자열을 의미있는 토큰으로 분해하여 Parse Tree를 만드는 과정을 말합니다.

 

Soft Parsing vs Hard Parsing

Oracle의 경우 성능향상을 위해 Shared Pool에서 실행계획들을 저장하고 있음. DB 인스턴스에서 쿼리가 수행되게 되면 Shared Pool 그 중 Library cache를 확인하고 Soft Parsing이나 Hard Parsing 에 의해 Parse 프로세스를 거치게 된다.

 

Soft Parsing (오라클 기준)

Library cache에 이전에 실행했던 쿼리가 있을 경우 저장되어 있는 실행계획 바로 사용(재사용)

 

Hard Parsing(오라클 기준)

Library cache에 이전에 실행했던 쿼리가 없을 경우 다양한 실행계획을 수립하고 최적의 플랜을 선택하는 과정((in oracle. Optimization, Row source Generation)을 거쳐야함

 

PostgreSQL에서의 Parsing

PostgreSQL & EPAS 에서는 SQL 실행 계획을 공유 메모리 차원에서 저장하지 않음. 따라서 오라클과 동일한 Soft Parsing / Hard Parsing 분류 기준을 적용할 수 없음.

새로운 세션(프로세스)에서 쿼리가 수행될 때는 필수적으로 아래 그림의 모든 과정들이 수행되어야 하며, 쿼리 재작성과 플랜수립을 생략하는 실행 계획을 재사용 하기 위해서는 PREPARE구문을 사용해야함.

PostgreSQL 에서 쿼리 수행 과정

 

 

PostgreSQL에서의 실행 계획 재사용

- 같은 프로세스에서 동일한 쿼리가 수행되더라도 PostgreSQL에서는 무조건적으로 오라클에서 Hard Parsing이라고 불리우는 작업이 수행되어야 하며, 실행계획을 재사용하기 위해서는 PREPARE구문을 사용해야함.

- JDBC와 같이 클라이언트에서 Prepared Statements를 통해 PREPARE 구문을 사용한 것 처럼 쿼리의 플랜을 재사용할 수 있지만, 클라이언트단의 설정도 고려해야함. (Default - prepareThreshold : 5, 실제 prepare 구문을 사용하는 Threshold )

- 실제 PREPAFE 구문을 사용할 때도 Custom Plan 과 generic plan에 따라서 실제 사용하는 플랜이 달라지며 PREPARE 구문을 사용하더라도 5번까지는 사용자가 입력한 변수에 따라 Custom Plan으로 수행되고 , 그 이후에 통계정보를 활용하여 generic plan으로 수행됨

- plpgsql의 경우 일반적인 쿼리 수행이 되더라도 PREPARE처럼 수행되도록 암시적으로 설정되어 있음

 

 

테스트

PostgreSQL 코드 레벨에서도 확인할 수 있으나, Prepare 구문 활용시 6번 수행 이후에 Planning Time이 줄어들고, 단순 일반 수행 시 쿼리 플랜시간이 줄어들지 않는 모습을 확인 할 수 있다.

Prepare 여부는 pg_prepared_statement View를 통해 확인한다. 

 

--Prepare 구문
prepare u(integer) as select * from information_schema.table_privileges where 1=$1;
explain(analyze,buffers,timing) execute u(1);


--일반 구문
explain(analyze,buffers,timing) select * from information_schema.table_privileges where 1=1;

 

Prepare 구문 사용 시

 

prepare 구문 미사용시

 

참고자료 및 그림 출처)

https://www.interdb.jp/pg/pgsql03.html

https://dev.to/yugabyte/postgres-query-execution-plpgsql-284h

https://www.postgresql.org/docs/current/sql-prepare.html#SQL-PREPARE-NOTES