PREPARE statement

들어가기 전에..

쿼리 프로세싱 참고

https://lsmdd.tistory.com/122

22. PostgreSQL - query processing

PREPARE statement란?

• 성능을 최적화하기 위해 사용하는 server-side object
• When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten.
• When an EXECUTE command is subsequently issued, the prepared statement is planned and executed.
• 세션 레벨에서만 재사용이 가능하며 세션레벨에서 직접 해제가 필요하다면 DEALLOCATE name;을 사용
• 즉, 여러 세션에서 동시에 수행되더라도 각 세션마다 파싱과정이 수행되어야 한다.

이용 시 장점

반복적인 query processing 과정을 생략함으로써 join 등 복잡한 쿼리 실행 시 성능 향상 기대

 

실행 계획 생성 옵션

• Generic Plan: 모든 파라미터에 대해 동일한 실행 계획 사용.
• Custom Plan: 실제 파라미터 값에 따라 별도로 생성된 실행 계획 사용.
• 기본 모드(plan_cache_mode = auto)에서는:
  1. 처음 5회는 Custom Plan 으로 실행.
  2. 이후 평균 비용을 계산해, Generic Plan 과 비교 후 적합한 것을 채택함.

• 필요 시 plan_cache_mode 설정을 통해 forced generic/custom 모드로 고정 가능
  • The allowed values are auto (the default), force_custom_plan and force_generic_plan.

query processing이 다시 수행되는 경우

• DDL 변경, 통계 변경, search_path 변경 등이 발생하면 PREPARE 문장은 query processing을 다시 진행함.

prepared statement 조회 방법

• pg_prepared_statements 뷰 이용

간단 예시

-- INSERT용 PREPARE + EXECUTE 예시
PREPARE fooplan (int, text, bool, numeric) AS
  INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

-- SELECT용 PREPARE + EXECUTE
PREPARE usrrptplan (int) AS
  SELECT * FROM users u, logs l
   WHERE u.usrid = $1
     AND u.usrid = l.usrid
     AND l.date = $2;
EXECUTE usrrptplan(1, current_date);

 

client단에서는 어떻게 세션 레벨에서 캐싱할까?

• JDBC PreparedStatement 사용. (세션 단위 유지)
• PgBouncer를 사용할 경우, PREPARE을 세션 레벨로 유지하고 싶다면, transaction pooling이 아니라 session pooling 모드로 설정해야 함.

요약 정리

목적	세션 레벨에서 반복 실행되는 복잡한 쿼리에서 parse/plan 단계를 생략해 성능 향상
파라미터 지원	$1, $2 등 위치 기반 지정, 타입 지정 또는 유추
실행 계획	generic 또는 custom plan 사용, 자동/강제 방식 설정 가능
유지 및 재분석	세션 단위 유효, DDL/statistics/search_path 변경 시 리플랜
종료	session 종료 시 자동 해제, DEALLOCATE로 명시 해제 가능
모니터링	pg_prepared_statements 조회 가능

 

참고

• https://www.postgresql.org/docs/current/sql-prepare.html
• https://www.pgbouncer.org/features.html
• https://www.postgresql.org/docs/current/runtime-config-query.html