> ## Content Index
> Fetch the complete content index at: https://mlog.me/llms.txt
> Use this file to discover other available public pages before exploring further.

# PostgreSQL 부분 인덱스를 안 탈 때: prepared statement의 custom·generic plan 비교
- URL: https://mlog.me/postgresql-partial-index-prepared-statement-generic-plan/
- Published: 2026-09-18T07:12:23.000Z
- Updated: 2026-09-18T07:12:23.000Z
- Description: PostgreSQL 부분 인덱스가 prepared statement에서 사용되지 않는 이유를 custom·generic plan 실험으로 확인합니다. 고정 조건과 바인딩의 차이, pgJDBC 진단 범위와 안전한 비교 SQL을 정리합니다.
- Author: mLog
- Tags: 데이터베이스, PostgreSQL, SQL 튜닝, Spring Boot

SQL 도구에서는 부분 인덱스를 사용하는데 애플리케이션에서는 Seq Scan이 보인다면, 인덱스가 깨졌다고 단정하기 전에 **실제 SQL의 매개변수와 실행 계획 종류**를 비교해야 한다. 특히 `WHERE state = 'W'`인 부분 인덱스와 `WHERE state = $1`인 조회의 조합이 점검 대상이다.

다만 “바인딩을 쓰면 부분 인덱스를 못 쓴다”도 정확하지 않다. 이 글의 실험에서는 상태가 상수로 고정된 조회라면 시간 조건을 바인딩한 generic plan도 부분 인덱스를 사용했다.

[JOIN·GROUP BY와 분리 조회 비교 글](https://mlog.me/postgresql-join-group-by-vs-split-query-performance/)에서 상태별 부분 인덱스 후보를 다뤘다면, 이번 글은 그 인덱스가 prepared statement에서 언제 사용 가능한지 확인하는 후속 실험이다. 조인·집계 전체 튜닝 방법은 반복하지 않는다.

검증일은 2026-09-18이다. 공식 설명은 PostgreSQL 18·pgJDBC 문서를, 실행 결과는 PGlite 0.5.8의 PostgreSQL 18.3 인메모리 엔진을 기준으로 한다. 실제 회사 DB 장애 해결이나 네이티브 서버 성능 측정 기록은 아니다.

## 1\. 부분 인덱스는 “사용해도 결과가 빠지지 않는가”를 먼저 판단한다

다음 인덱스에는 W 상태인 행만 들어간다.

```sql
CREATE INDEX mlog_demo_w_time
ON mlog_plan_demo (created_at)
WHERE state = 'W';

```

조회가 W 행만 찾는다는 사실을 계획 단계에서 알 수 있다면 이 인덱스는 후보가 된다. 반면 모든 가능한 상태를 받아야 하는 공용 계획에서는 W 전용 인덱스만으로 다른 상태의 결과를 구할 수 없다.

여기에는 두 질문이 있다. 첫째는 해당 인덱스를 사용해도 결과가 맞는지, 둘째는 사용 가능한 인덱스 중 무엇이 비용상 유리한지다. 첫 번째를 통과해도 두 번째 때문에 Seq Scan을 선택할 수 있다. 부분 인덱스의 조건을 쿼리가 만족한다는 사실을 플래너가 인식할 수 있어야 한다는 점이 핵심이다. [PostgreSQL 부분 인덱스](https://www.postgresql.org/docs/18/indexes-partial.html?ref=mlog.me)

## 2\. custom plan과 generic plan은 무엇이 다른가

custom plan은 이번 실행에 전달된 매개변수 값을 바탕으로 계획한다. generic plan은 특정 값에 의존하지 않는 계획을 재사용한다. 공용 계획은 반복 계획 비용을 줄일 수 있지만, 값마다 적합한 접근 경로가 크게 다르면 불리할 수 있다. [PostgreSQL plan\_cache\_mode](https://www.postgresql.org/docs/18/runtime-config-query.html?ref=mlog.me#GUC-PLAN-CACHE-MODE)

이 글에서는 자동 선택을 기다리지 않고 두 종류를 각각 강제해 차이를 분리한다. 이것은 원인을 관찰하는 실험 장치이지 운영 서버의 기본 설정을 바꾸라는 권고가 아니다.

## 3\. 별도 실습 세션에서 같은 자료로 비교한다

아래 예제는 합성 자료를 만드는 실습용이다. **운영 연결이나 애플리케이션 커넥션 풀에서 실행하지 말고, 별도 테스트 DB의 새 세션에서 순서대로 실행한다.** 임시 테이블도 메모리·디스크·CPU를 사용한다. 전체 스크립트를 하나의 BEGIN으로 감싸지 않는다. 단계별 ROLLBACK은 설정 비교 범위만 끝내도록 구성돼 있다.

```sql
SET TIME ZONE 'UTC';

CREATE TEMP TABLE mlog_plan_demo (
    id integer PRIMARY KEY,
    state text NOT NULL,
    created_at timestamptz NOT NULL,
    payload text NOT NULL
);

INSERT INTO mlog_plan_demo
SELECT g,
       CASE WHEN g % 100 = 0 THEN 'W' ELSE 'S' END,
       TIMESTAMPTZ '2026-09-18 00:00:00+00'
           + g * INTERVAL '1 second',
       repeat('x', 80)
FROM generate_series(1, 20000) AS g;

CREATE INDEX mlog_demo_w_time
ON mlog_plan_demo (created_at)
WHERE state = 'W';

ANALYZE mlog_plan_demo;

PREPARE mlog_q(text, timestamptz) AS
SELECT id
FROM mlog_plan_demo
WHERE state = $1
  AND created_at >= $2;

```

총 20,000행 중 W는 200행, S는 19,800행이다. 상태 분포를 단순하게 만들기 위해 정한 **실험 입력값**이지 실제 서비스 비율이 아니다. 시간은 고정값을 사용해 실행 날짜가 바뀌어도 동일한 조회 범위를 만든다.

먼저 custom plan을 본다.

```sql
BEGIN;
SET LOCAL plan_cache_mode = force_custom_plan;

EXPLAIN (FORMAT JSON, COSTS OFF)
EXECUTE mlog_q('W', '2026-09-18 05:00:00+00');

ROLLBACK;

```

같은 prepared statement와 같은 값을 유지한 채 generic plan을 비교한다.

```sql
BEGIN;
SET LOCAL plan_cache_mode = force_generic_plan;

EXPLAIN (FORMAT JSON, COSTS OFF)
EXECUTE mlog_q('W', '2026-09-18 05:00:00+00');

ROLLBACK;

```

`SET LOCAL`의 범위는 해당 트랜잭션까지다. 명시적인 BEGIN과 종료가 있어야 하며, 예제는 ROLLBACK으로 원래 설정에 돌아간다. 연결 전체에 남는 SET을 실수로 풀에 반환하지 않도록 이 범위를 구분한다. [PostgreSQL SET](https://www.postgresql.org/docs/18/sql-set.html?ref=mlog.me)

이번 환경에서는 custom plan이 `mlog_demo_w_time` Index Scan, generic plan이 Seq Scan이었다. generic 계획에는 `state = $1`, `created_at >= $2` 필터가 남았다. 실험은 인덱스 사용 경로를 비교했으며 시간을 측정해 “몇 배 느리다”고 판정하지 않았다.

## 4\. 시간만 바인딩하면 generic plan도 부분 인덱스를 쓸 수 있다

이번에는 “W만 조회한다”는 업무 조건을 SQL 구조에 고정하고, 시간만 매개변수로 둔다.

```sql
PREPARE mlog_w(timestamptz) AS
SELECT id
FROM mlog_plan_demo
WHERE state = 'W'
  AND created_at >= $1;

BEGIN;
SET LOCAL plan_cache_mode = force_generic_plan;

EXPLAIN (FORMAT JSON, COSTS OFF)
EXECUTE mlog_w('2026-09-18 05:00:00+00');

ROLLBACK;

```

이 generic plan에서는 `mlog_demo_w_time` Index Scan을 관찰했다. `Index Cond`에는 시간 매개변수 `$1`이 그대로 남았다. 인덱스에 포함될 상태는 이미 W로 정해져 있고, 바뀌는 것은 인덱스 안에서 찾을 시간 범위이기 때문이다.

세 조회의 결과를 각각 실행해 ID를 정렬한 뒤 비교했을 때 모두 같은 21개였다. 원래 SQL에는 ORDER BY가 없으므로 반환 순서까지 같다고 주장하지 않는다. 결과 건수가 같다는 것만으로 모든 결과가 동일하다고 판단한 것도 아니다. 검증 코드에서 실제 ID 배열을 비교했다.

이 대조군의 결론은 **문제와 무관한 바인딩까지 없애지 말자**는 것이다. 업무상 W 전용 조회를 별도 SQL로 정의하는 것과, 사용자 입력을 문자열 결합으로 붙이는 것은 다르다. 후자는 이 실험의 해결책이 아니다. 날짜·시각·식별자 등 외부 값은 계속 바인딩한다.

## 5\. 여러 상태를 조회해야 한다면 전체 복합 인덱스도 비교한다

조회가 모든 상태를 받아야 한다면 특정 상태 전용 SQL만으로 해결할 수 없다. 같은 실습에서 전체 복합 인덱스를 추가해 비교했다.

```sql
CREATE INDEX mlog_demo_state_time
ON mlog_plan_demo (state, created_at);

BEGIN;
SET LOCAL plan_cache_mode = force_generic_plan;

EXPLAIN (FORMAT JSON, COSTS OFF)
EXECUTE mlog_q('W', '2026-09-18 05:00:00+00');

ROLLBACK;

```

이 단계에서는 전체 복합 인덱스의 Bitmap Index Scan을 포함한 Bitmap Heap Scan을 관찰했다. `state = $1`이라는 매개변수 자체가 일반 인덱스 사용을 금지한 것은 아니었다.

상태 동등 조건 다음 시각 범위를 두는 이 예제에는 `(state, created_at)`가 비교 후보가 된다. 그러나 실제 조회 시작점과 조건 분포가 다르면 컬럼 순서·인덱스 선택도 달라질 수 있다. B-tree 복합 인덱스는 선두 컬럼 조건의 영향을 받으므로 “이 순서가 모든 조회의 정답”이라고 복사하지 않는다. [PostgreSQL 복합 인덱스](https://www.postgresql.org/docs/18/indexes-multicolumn.html?ref=mlog.me)

이번 실습은 비교를 위해 부분 인덱스와 전체 인덱스를 함께 남겨 뒀다. 운영에서 둘 다 영구 유지하라는 뜻은 아니다. 저장 공간과 쓰기 비용, 대표 쿼리 계획을 보고 최소 구성을 정한다.

이번 실행 결과를 모으면 다음과 같다.

| 비교 조건                           | 관측한 접근 경로                                    |
| ------------------------------- | -------------------------------------------- |
| 상태 바인딩 + custom                 | W 부분 인덱스의 Index Scan                         |
| 상태 바인딩 + generic                | Seq Scan                                     |
| W 상수 + 시각 바인딩 + generic         | W 부분 인덱스의 Index Scan                         |
| 전체 복합 인덱스 추가 + 상태 바인딩 + generic | 전체 인덱스의 Bitmap Index Scan + Bitmap Heap Scan |

이는 PGlite 합성 자료에서 얻은 결과다. 운영 환경에서 같은 스캔 종류나 같은 성능을 보장하는 표가 아니다.

## 6\. “다섯 번 실행하면 느려진다”로 설명하면 안 된다

PostgreSQL의 `plan_cache_mode=auto`는 매개변수가 있는 prepared statement에서 처음 다섯 실행의 custom 계획 비용과 generic 계획 비용을 비교하는 규칙을 사용한다. 그다음 무조건 generic으로 바뀌는 것은 아니다. 실제 계획은 `EXPLAIN EXECUTE`로 확인한다. [PostgreSQL PREPARE](https://www.postgresql.org/docs/18/sql-prepare.html?ref=mlog.me)

Java 쪽에는 별개로 pgJDBC의 `prepareThreshold`가 있다. 공식 문서의 기본값은 5이며 named server-prepared statement를 사용하기 시작하는 드라이버 측 기준이다. DB의 custom/generic 선택 규칙과 같은 카운터가 아니다. [pgJDBC Server Prepared Statements](https://jdbc.postgresql.org/documentation/server-prepare/?ref=mlog.me)

따라서 “다섯 번째 API 호출부터 generic이 된다”처럼 단정하지 않는다. 실제 JDBC 버전·설정, 같은 물리 연결에서의 재사용, 풀과 프록시 구성을 확인해야 한다. 이번 실험은 SQL PREPARE를 직접 사용했으며 pgJDBC·Spring·MyBatis 통합과 auto 전환은 재현하지 않았다.

또한 `prepareThreshold=0`이나 전역 `force_custom_plan`을 모든 애플리케이션의 기본 처방으로 넣지 않는다. 각각 재사용 방식과 계획 비용에 영향을 줄 수 있으므로 문제 쿼리를 특정한 다음 비교해야 한다.

## 7\. 운영에서는 같은 세션의 근거부터 수집한다

첫 단계는 SQL을 바꾸는 것이 아니라 실제 버전과 설정을 기록하는 것이다.

```sql
SELECT version();
SHOW plan_cache_mode;

SELECT name,
       parameter_types,
       from_sql,
       generic_plans,
       custom_plans
FROM pg_prepared_statements;

```

이 뷰는 **현재 세션에서 사용 가능한 prepared statement**를 보여 준다. 별도 DataGrip 연결에서 조회해 비어 있다고 애플리케이션이 prepared statement를 안 쓴다고 결론내릴 수 없다. 출력 전체를 공개할 때는 이름·타입에도 내부 정보가 없는지 확인한다. 쿼리 원문은 필요할 때만 별도로 검토한다. [pg\_prepared\_statements](https://www.postgresql.org/docs/18/view-pg-prepared-statements.html?ref=mlog.me)

실험에서 generic/custom 카운터를 본 것은 선택된 계획 종류를 확인하기 위해서다. EXPLAIN EXECUTE도 계획을 선택하므로 카운터를 그대로 업무 요청 횟수나 성공한 처리 건수로 해석하지 않는다.

실제 진단에서는 다음 순서가 유용하다.

1. 애플리케이션이 실행한 SQL 모양과 바인딩 타입·값을 비식별화해 기록한다.
2. 인덱스의 실제 조건과 쿼리 조건이 대응하는지 확인한다.
3. 통계·자료 분포·세션 설정이 같은 테스트 환경에서 custom과 generic을 비교한다.
4. 같은 결과를 반환하는지 먼저 확인하고, 그다음 계획·버퍼·실행 비용을 비교한다.
5. 쿼리 분리, 전체 인덱스, 제한적인 설정 변경 중 필요한 범위만 선택한다.

`EXPLAIN (ANALYZE, BUFFERS)`는 실제로 문장을 실행한다. 일반 EXPLAIN과 구분하고, 운영 부하와 변경 부작용을 검토해야 한다. 특히 쓰기 SQL을 분석 옵션과 함께 실행하는 것을 단순 조회로 취급하지 않는다. [PostgreSQL EXPLAIN](https://www.postgresql.org/docs/18/sql-explain.html?ref=mlog.me)

세션·락·장기 쿼리까지 봐야 한다면 [PostgreSQL 모니터링 쿼리](https://mlog.me/postgresql-monitoring-queries/)에서 관찰 범위를 넓힌다. 인덱스 선택 외에 대기·I/O 등 다른 원인이 있을 수 있으므로 Seq Scan 한 줄만으로 장애를 확정하지 않는다.

변경 뒤에는 [pg\_stat\_statements 스냅샷 차이](https://mlog.me/postgresql-pg-stat-statements-snapshot-delta-slow-queries/)처럼 같은 구간의 호출량과 비용을 확인한다. 이때도 호출 증가와 쿼리당 비용 증가를 구분하고, 이번 작은 실습의 결과를 운영 개선율로 대신하지 않는다.

## 결론

부분 인덱스가 사용되지 않을 때 확인할 핵심은 “바인딩이 있나” 하나가 아니다. **어떤 조건을 바인딩했는지, 계획 시점에 그 값을 아는지, 실제 선택된 계획이 무엇인지**를 함께 봐야 한다.

이번 실험은 상태를 모르는 generic plan과 상태가 고정된 generic plan을 나눴다. 상태 상수를 명확히 한 조회는 시간 바인딩을 유지하면서 부분 인덱스를 사용했고, 전체 복합 인덱스는 상태 바인딩이 있는 generic plan의 다른 후보가 됐다. 이 차이를 확인한 뒤 실제 서비스의 자료 분포와 변경 비용을 검토하는 것이 다음 단계다.