5.7 옵티마이저 힌트의 부재와 우회

PostgreSQL은 공식적으로 쿼리 힌트(/*+ HINT */)를 제공하지 않는다. Oracle/MySQL에서 온 운영자에게는 답답한 부분이지만, 의도된 설계다 — 옵티마이저를 더 똑똑하게 만드는 게 통계로 우회하는 것보다 옳다는 철학 때문입니다. 그 이유와, 실전에서 plan을 강제해야 할 때 쓰는 우회 방법들을 봅니다.

왜 힌트가 없는가

PostgreSQL 커뮤니티의 공식 입장(메일링 리스트·Wiki):

1. 힌트는 통계 개선을 방해한다 — 운영자가 힌트로 덮어 쓰면 옵티마이저 버그·통계 미스가 드러나지 않음
2. DB 진화가 plan을 깸 — 데이터 분포가 바뀌면 힌트가 오래된 정답을 강요
3. opaque — 코드만 봐서는 왜 그 plan인지 알기 어려움

대안으로 권장되는 것:

• 통계 갱신 (ANALYZE, default_statistics_target, CREATE STATISTICS)
• 인덱스 추가·재설계
• 쿼리 재작성

대부분의 plan 문제는 위 셋으로 해결됩니다. 그러나 마지막 수단으로 실전에서 쓰는 우회는 존재합니다.

우회 1 — enable_* 파라미터

가장 합법적인 우회합니다. 옵티마이저가 특정 노드 타입을 못 쓰게 강제.

운영 사용:

BEGIN;
SET LOCAL enable_nestloop = off;
SET LOCAL enable_mergejoin = off;
-- 이 한 쿼리만 hash join 강제
SELECT ...;
COMMIT;

off는 비용을 매우 크게 만드는 방식이라 옵티마이저가 다른 방법이 없으면 여전히 그걸 씁니다. 진짜 차단이 아니라 비용 페널티입니다.

우회 2 — pg_hint_plan 확장

Oracle 스타일의 주석 힌트 문법을 추가해 주는 확장. 일부 클라우드(EDB·NCP 등)·온프레미스에서 표준으로 깔립니다.

CREATE EXTENSION pg_hint_plan;

/*+ HashJoin(users orders) IndexScan(orders orders_user_id_idx) */
SELECT *
  FROM users u
  JOIN orders o ON o.user_id = u.id
 WHERE u.city = 'Seoul';

지원 힌트:

AWS RDS·Aurora는 pg_hint_plan을 지원하지 않습니다. 매니지드 환경에서 쓸 수 있는지 미리 확인합니다.

우회 3 — OFFSET 0 트릭

서브쿼리에 OFFSET 0을 붙이면 옵티마이저가 그 서브쿼리를 평탄화하지 않고 별 단위로 처리합니다. join 순서를 고정하는 효과입니다.

SELECT *
  FROM (SELECT * FROM users WHERE city='Seoul' OFFSET 0) u
  JOIN orders o ON o.user_id = u.id;

OFFSET 0은 결과를 안 자르므로 부작용은 없지만, 옵티마이저는 평탄화 차단으로 인식합니다. 가장 가벼운 우회합니다.

우회 4 — MATERIALIZED CTE (PG 12+)

PG 12부터 CTE의 평탄화 동작이 바뀌었습니다. 평탄화 강제 또는 차단을 명시할 수 있습니다.

WITH hot_users AS MATERIALIZED (
  SELECT * FROM users WHERE city='Seoul'
)
SELECT * FROM hot_users JOIN orders USING (id);

MATERIALIZED는 CTE를 실제로 구체화해 한 번만 계산하고 결과를 임시 저장합니다. 평탄화가 plan을 망가뜨릴 때 유용합니다.

반대로 NOT MATERIALIZED는 평탄화를 권장합니다.

우회 5 — 쿼리 재작성

힌트보다 자주 효과적인 방법. SQL 구조를 바꿔 옵티마이저가 다른 선택을 하게 유도.

우회 6 — 함수 비용·통계 지정

함수 호출 결과는 옵티마이저가 모릅니다. 명시적으로 알려줄 수 있습니다.

-- 함수에 비용·예상 row 수 설정
CREATE OR REPLACE FUNCTION expensive_check(int)
  RETURNS bool AS $$ ... $$
  LANGUAGE plpgsql
  COST 100        -- 함수 호출 1회 비용 (단위는 cpu_operator_cost 배수)
  ROWS 1;         -- SETOF 함수의 평균 row 수

-- 단일 컬럼 통계 강제
ALTER TABLE big_table ALTER COLUMN tag SET STATISTICS 1000;
ANALYZE big_table;

우회 7 — from_collapse_limit / join_collapse_limit

여러 테이블 join에서 옵티마이저가 너무 많은 후보를 시도해 plan time이 길어지면:

SET join_collapse_limit = 1;     -- 작성 순서 그대로 유지 강제

1로 두면 옵티마이저가 내가 적은 순서를 그대로 따릅니다. 매우 큰 join에서 내가 정답을 알고 있을 때의 강력한 무기.

의사결정 순서

    flowchart TD
  S["plan이 이상함"] --> A["ANALYZE 다시"]
  A --> A2{"개선?"}
  A2 -- "yes" --> END["완료"]
  A2 -- "no" --> B["CREATE STATISTICS<br/>또는 statistics_target 상향"]
  B --> B2{"개선?"}
  B2 -- "yes" --> END
  B2 -- "no" --> C["인덱스 추가·재설계"]
  C --> C2{"개선?"}
  C2 -- "yes" --> END
  C2 -- "no" --> D["쿼리 재작성"]
  D --> D2{"개선?"}
  D2 -- "yes" --> END
  D2 -- "no" --> E["enable_* 또는 pg_hint_plan"]
  E --> END

  classDef step fill:#dbeafe,stroke:#1d4ed8,color:#1e3a8a
  classDef ok fill:#d1fae5,stroke:#047857,color:#064e3b
  classDef force fill:#fed7aa,stroke:#c2410c,color:#7c2d12
  class S,A,B,C,D step
  class END ok
  class E force
  

힌트는 사다리의 가장 마지막. 위 단계 모두 안 통하면 그때 검토합니다.

운영 권장

• 세션 단위 우회는 OK: SET LOCAL로 한 쿼리·한 트랜잭션만
• 클러스터 전역 enable_seqscan = off 같은 설정은 절대 금지 — 모든 쿼리에 영향
• 힌트 사용 시 주석 처리: 왜 힘트가 필요했는지, 통계 미스를 어떻게 검증했는지 메모
• 6개월마다 재평가: 데이터·통계가 바뀌면 힌트가 오히려 손해

정리

• PostgreSQL은 공식 힌트가 없음 — 통계·인덱스·재작성이 우선
• enable_* 파라미터로 노드 타입 비활성 가능 (비용 페널티 방식)
• pg_hint_plan 확장이 Oracle 스타일 주석 힌트 제공 — 클라우드에서 지원 여부 확인
• OFFSET 0, MATERIALIZED CTE, join_collapse_limit로 plan 구조 통제 가능
• 함수에 COST·ROWS 메타데이터 부여로 옵티마이저 정확도 개선
• 힌트는 마지막 수단. 사용 시 이유를 코드 주석으로 남겨야 6개월 뒤 자신이 후회하지 않음

Part V 쿼리 처리가 끝났습니다. 다음 Part VI에서는 옵티마이저의 무기인 인덱스를 타입별로 봅니다.