PostgreSQL: 바인드 변수 실행 계획 최적화

※ PostgreSQL: Execution Plan Optimization for Bind Variables.
 
안녕하세요. 듀스트림입니다.
 
오늘도 재밌는 사례를 하나 가져왔습니다.
 
고객사에서 오래 걸리는 쿼리에 대해 진단 요청을 받아 방문했습니다.
진단 후, 엔진 및 인덱스 튜닝을 통해 쿼리 수행 시간을 4초에서 0.2초로 단축시켰습니다.

문제는 여기서 끝나지 않았습니다. 다음 날, 고객사로부터 다시 연락이 왔습니다.
"튜닝 후 DB 서버에서 수행할 때는 분명 빠른데, APP에서는 여전히 느립니다."

상황을 듣자마자 떠오르는 게 하나 있어서 질문을 던졌습니다.
"바인드 변수를 사용하고 계신가요?"
예상대로 바인드 변수를 사용하고 있었습니다.
 
이번 포스팅에서는 바인드 변수를 PostgreSQL 엔진 관점에서 살펴보겠습니다.

---

1. 바인드 변수란?

바인드 변수는 SQL 쿼리에서 하드코딩된 값 대신 플레이스홀더를 사용하여 실행 시 값이 바인딩되도록 하여 SQL 재사용성과 보안을 강화하는 기법입니다.

---

2. 바인드 변수의 이점

• 바인드 변수를 사용하면 동일한 SQL 템플릿에 대해 실행 계획을 캐싱하여 성능을 최적화할 수 있습니다.
→ 트랜잭션이 많은 애플리케이션에서 반복적으로 실행되는 SQL 쿼리에 유리합니다.
 
• SQL 인젝션(SQL Injection) 방어에 효과적입니다.
→ 하드코딩된 값 대신 플레이스홀더를 사용하므로, 사용자 입력 값이 쿼리 구문에 직접 삽입되지 않습니다.
 
• 바인드 변수는 코드 재사용성과 유지보수성을 높여줍니다.
→ 특정 조건 값만 변경하면서 동일한 쿼리 패턴을 재활용할 수 있습니다.

---

3. PostgreSQL에서 바인드 변수의 작동 방식

• PREPARE/EXECUTE 사용
→ PostgreSQL은 바인드 변수와 함께 동적 SQL을 지원합니다.

PREPARE stmt (integer, text) AS -- PREPARE는 실행 계획을 생성하고 캐싱합니다.
SELECT * FROM users WHERE id = $1 AND status = $2; -- $1, $2는 바인드 변수입니다.

EXECUTE stmt(35, 'active'); -- EXECUTE는 준비된 계획에 값을 바인딩하여 실행합니다.

 
• PL/pgSQL에서 사용
→ PL/pgSQL에서 동적 SQL과 함께 바인드 변수를 활용할 수 있습니다.

DO $$
DECLARE
    user_id INT := 1;
BEGIN
    EXECUTE 'SELECT * FROM users WHERE id = $1' USING user_id; -- USING 키워드는 바인드 변수를 전달하는 데 사용됩니다.
END $$;

---

4. 주의사항

바인드 변수가 지나치게 일반화된 경우 실행 계획이 비효율적일 수 있습니다.
 
딱, 이번 사례에 해당합니다.
바인드 변수로 값을 바인딩하면, PostgreSQL은 이 값이 무엇인지 미리 알지 못하므로 일반적인(Generic) 계획을 선택합니다.

---

5. 해결 방법

• 동적 실행 계획 강제
→ 값에 최적화된 실행 계획(Custom Plan)을 매번 새로 생성하도록 강제합니다.

SET plan_cache_mode = 'force_custom_plan';

PREPARE stmt (integer) AS SELECT * FROM users WHERE id = $1;
EXECUTE stmt(1);   -- 실행 계획: Custom Plan 생성
EXECUTE stmt(1000); -- 실행 계획: 값에 맞게 새로 Custom Plan 생성

 
• 동적 SQL 사용
→ 바인드 변수 대신 동적 SQL에서 값을 직접 포함하여 실행 계획을 매번 새로 생성하도록 유도합니다.

DO $$
DECLARE
    user_id INT := 100000;
BEGIN
    EXECUTE 'SELECT * FROM users WHERE id = ' || user_id; -- 값이 쿼리에 포함되게 만듭니다.
END $$;

 
• 특정 실행 계획 유도
→ 특정 유형의 실행 계획을 사용하도록 설정합니다.
(SET enable_seqscan = off;를 사용해 테이블 풀스캔을 비활성화하고 인덱스를 사용을 강제하도록 설정할 수 있습니다.)
 
+ 이 외에도 pg_hint_plan Extension 사용, STATISTICS 튜닝 등의 방법이 있습니다.
 
이번 사례에서 제가 사용한 방법은 '동적 실행 계획 강제'입니다.

---

6. Custom Plan이 적합한 경우

• 데이터 분포가 비대칭적인 경우
→ 예를 들어, age가 대부분 20~30 사이에 몰려 있고, 특정 값(예: age = 100)이 매우 적은 경우.
 
• 결과 행의 개수가 크게 다른 경우
→ 예를 들어, id < 10은 10개의 행만 반환하지만, id > 100000은 수십만 개의 행을 반환하는 경우.
 
• 데이터 범위가 넓고 다양할 때
→ 값에 따라 최적의 실행 계획이 달라지는 경우.

---

7. Genric Plan이 적합한 경우

일반적으로는 Generic Plan이 성능이 좋을 확률이 높습니다
 
• 데이터 분포가 고른 경우
→ 테이블의 데이터가 고르게 분포되어 있어 특정 값에 최적화할 필요가 없는 경우.
→ 예를 들어, id가 1부터 1,000,000까지 균등하게 분포된 경우.

• 쿼리가 자주 반복되는 경우
→ 동일한 쿼리가 다양한 값으로 여러 번 실행될 때, 실행 계획 생성 비용을 줄이기 위해 Generic Plan이 적합합니다.
→ 예를 들어, "사용자 정보를 검색"하는 API에서 같은 쿼리가 수백, 수천 번 실행될 때.

• 복잡하지 않은 쿼리
→ 조인이나 서브쿼리가 없는 간단한 쿼리에서는 Generic Plan으로도 충분히 효율적인 성능을 낼 수 있습니다.
 

Generic Plan과 Custom Plan 비교 표

특징	Generic Plan	Custom Plan
계획 생성 시점	PREPARE 시점	EXECUTE 시점
계획 생성 비용	낮음 (한 번만 생성)	높음 (매 실행 시 생성)
재사용성	높음	없음
값에 대한 최적화	없음	있음
효율성	값에 따라 적당히 효율적	특정 값에 대해 매우 효율적
적합한 상황	데이터 분포가 고르고, 쿼리가 반복적으로 실행됨	값에 따라 성능 차이가 클 때

---

정리하자면,

• Generic Plan은 실행 계획 생성 비용을 절감하고, 대부분의 경우 적당히 효율적인 성능을 제공합니다.
→ 데이터 분포가 균등하거나, 쿼리가 반복적으로 실행될 때 유리합니다.

• Custom Plan은 특정 값에 따라 실행 계획이 크게 달라질 경우 적합합니다.
→ 하지만 Custom Plan은 매번 실행 계획을 생성하므로, 실행 계획 생성 비용이 부담스러운 워크로드에서는 Generic Plan이 더 효율적입니다

---

오늘 포스팅은 여기까지입니다.
다음 포스팅도 기대해주세요 :)