PostgreSQL: pg_store_plans

※ PostgreSQL: pg_store_plans.

※ Version: Linux 8.10 (Rocky), PostgreSQL 17.4, pg_store_plans 1.9.

 

안녕하세요. 듀스트림입니다.

 

오늘은 오랜만에 익스텐션 관련 내용입니다.

 

GitHub - ossc-db/pg_store_plans: Store execution plans like pg_stat_statements does for queries.

---

1. pg_store_plans?

pg_store_plans는 PostgreSQL의 실행 계획을 저장하고 관리하는 익스텐션입니다.

구분	설명
목적	모든 SQL 문장의 실제 실행 계획을 샘플링이 아니라 누적 통계로 저장
활용	pg_stat_statements 의 queryid와 조인해 “어떤 쿼리가 어떤 계획으로 얼마나 자주/느리게” 실행됐는지 분석
주요 View	pg_store_plans, pg_store_plans_info
주요 함수	pg_store_plans_reset(), pg_store_plans_*plan() (text/json/xml/yaml 변환)
필수 조건	shared_preload_libraries에 로드, compute_query_id = auto|on

---

2. 설치

이미 PostgreSQL이 설치되어 있다는 가정 하에 패키지는 git만 추가로 설치해 주시면 됩니다.

 

▸ git clone

cd ~
git clone https://github.com/ossc-db/pg_store_plans.git

 

▸ 설치

cd pg_store_plans

make USE_PGXS=1
make install USE_PGXS=1

 

▸ postgresql.conf 설정

vi $PGDATA/postgresql.conf

 

shared_preload_libraries = 'pg_store_plans'
compute_query_id = auto # auto|on
pg_store_plans.max = 10000
pg_store_plans.max_plan_length = '10000'
pg_store_plans.track = all  # 함수 내부 호출까지 수집
pg_store_plans.plan_storage = file # file | shmem

 

▸ 익스텐션 추가

psql
CREATE EXTENSION pg_store_plans WITH SCHEMA test;

 

+ 파라미터 목록

파라미터	기본값	설명
pg_store_plans.max	1000	추적·저장할 실행 계획(row) 최대 개수 초과 시 실행 횟수가 가장 적은 계획부터 제거됨
pg_store_plans.track	top	누적 대상 범위 top (클라이언트 최상위 문장), all (함수 내부 등 포함), verbose (시스템 명령까지 전부), none 중 선택
pg_store_plans.max_plan_length	5000	raw(JSON-short) 형식으로 저장할 단일 계획 문자열 최대 바이트 수 (초과분은 잘림)
pg_store_plans.plan_storage	file	계획 문자열 보관 위치 file (임시파일) vs shmem (공유메모리)
pg_store_plans.plan_format	text	plan 컬럼 표현 형식 text / json / xml / yaml / raw 중 선택
pg_store_plans.min_duration	0 ms	실행 시간이 이 값 이상일 때만 계획 저장 0이면 모든 문장을 기록
pg_store_plans.log_analyze	off	EXPLAIN ANALYZE 결과(실행 시간·실제 row 등)를 포함 켜면 오버헤드 ↑
pg_store_plans.log_buffers	off	EXPLAIN … BUFFERS 통계를 함께 저장 log_analyze와 무관하게 사용 가능
pg_store_plans.log_timing	on	노드별 Timing 수집 여부 false로 두면 시스템 시계 호출 비용을 줄일 수 있음
pg_store_plans.log_triggers	off	트리거 실행 통계를 포함 log_analyze = on일 때만 의미 있음
pg_store_plans.log_verbose	off	EXPLAIN VERBOSE 형태로 세부 플랜 노드를 기록
pg_store_plans.save	on	서버 종료 시 통계 파일을 저장할지 여부 $PGDATA/pg_stat_tmp/pgsp_plan_texts.stat에 저장

 

++ plan_storage: file vs shmem

• plan_storage=file (Default)
  
  • pg_store_plans.max가 충분히 크면 GC 거의 없음 → 무난
• plan_storage=shmem
  
  • 초고 빈도 트래픽(수만 QPS) 환경에서 파일 I/O 타임스탬프 지연을 제거
  • shared_buffers 외 별도 shared memory 할당이 필요 → shared_memory_size 계산 필수

---

3. 사용법

▸ 실행 계획 확인

SELECT queryid, plan, calls, total_time/ calls AS ms_per_exec
FROM   pg_store_plans
ORDER  BY total_time DESC
LIMIT  10;

 

▸ 쿼리:계획 매핑(with pg_stat_statements)

SELECT s.query,
       p.plan,
       p.calls AS plan_calls,
       s.calls AS stmt_calls
FROM   pg_stat_statements s
JOIN   pg_store_plans     p USING (queryid)
WHERE  p.calls < s.calls;         -- 계획이 여러 번 바뀐 경우

 

▸ 리셋(통계 초기화)

SELECT pg_store_plans_reset();

 

▸ 계획 변경 추척

-- 같은 queryid 에서 planid 가 2 이상이면 계획 변동 발생
SELECT queryid, count(DISTINCT planid) AS plan_versions
FROM   pg_store_plans
GROUP  BY queryid
HAVING count(DISTINCT planid) > 1;

 

+ 슬로우 쿼리 후보만 수집

pg_store_plans.min_duration = 500 # 500ms 이상만
pg_store_plans.log_buffers  = on

---

4. 성능 및 리소스 영향

체크포인트	영향
Shared Memory	pg_store_plans.max * 300B (메타) + max * max_plan_length(shmem일 때)
WAL 부하	없음 – 통계는 shared memory/임시파일에만 기록
CPU 오버헤드	실행 계획 계산 시간·JSON 직렬화 → log_timing 꺼서 최소화 가능
디스크 I/O	plan_storage=file + GC 시 플러시 발생 → SSD 권장

---

오늘은 여기까지~