[PostgreSQL] 함수 volatility — 표현식 인덱스가 안 만들어지거나 안 타는 이유

설치·접속: PostgreSQL 설치와 접속

부제: lower(email) 대신 커스텀 정규화 함수로 표현식 인덱스를 걸려는데 인덱스가 안 생기거나 안 탈 때

-- plpgsql 함수는 기본 VOLATILE. 이 상태로 표현식 인덱스를 걸면?
CREATE FUNCTION norm(t text) RETURNS text
  LANGUAGE plpgsql AS $$ BEGIN RETURN lower(trim(t)); END $$;

CREATE INDEX idx_norm ON people (norm(email));

ERROR:  functions in index expression must be marked IMMUTABLE

-- 실제로는 입력이 같으면 항상 같은 결과 → IMMUTABLE로 재선언하면 인덱스가 산다
CREATE OR REPLACE FUNCTION norm(t text) RETURNS text
  LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN RETURN lower(trim(t)); END $$;

CREATE INDEX idx_norm ON people (norm(email));
ANALYZE people;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
  SELECT id FROM people WHERE norm(email) = 'user15000@example.com';

                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using idx_norm on people (actual rows=1 loops=1)
   Index Cond: (norm(email) = 'user15000@example.com'::text)
 Planning Time: 0.086 ms
 Execution Time: 0.031 ms
(4 rows)

함수의 volatility 라벨은 "이 함수가 같은 입력에 대해 얼마나 안정적인가"를 planner에게 알려주는 계약이다. 기본값 VOLATILE은 "매 행마다 결과가 달라질 수 있다"는 뜻이라, PostgreSQL은 이런 함수를 표현식 인덱스에 넣는 것 자체를 거부한다(must be marked IMMUTABLE). 인덱스는 값이 변하지 않는다는 전제로 미리 계산해 저장하는 구조이기 때문이다. 실제로 결정적인 함수인데 라벨만 기본값으로 방치하면, 인덱스를 못 만들거나(위 에러) 함수 조건이 인덱스 스캔에 못 실려 seq scan으로 떨어진다. IMMUTABLE로 재선언하는 순간 같은 함수가 인덱스에 실리고 Index Scan이 살아난다.

반대 방향의 함정이 더 위험하다. 라벨은 강제된 약속이지 검증되지 않는다 — timezone·now()·설정값에 의존하는 함수를 억지로 IMMUTABLE로 달면, planner는 그 말을 믿고 상수 인자를 플랜 시점에 접어버린다(constant folding). 접힌 값은 캐시된 플랜(prepared statement, plpgsql)에서 재사용되며, 세션 timezone이 바뀌어도 갱신되지 않아 stale 값을 낸다.

-- timezone 의존인데 IMMUTABLE로 잘못 라벨링(실제로는 STABLE이어야 함)
CREATE FUNCTION localday(ts timestamptz) RETURNS date
  LANGUAGE sql IMMUTABLE
  AS $$ SELECT (ts AT TIME ZONE current_setting('TimeZone'))::date $$;

SET TimeZone='Asia/Seoul';
EXPLAIN (COSTS OFF) SELECT * FROM people
  WHERE id = 1 AND localday('2026-01-01 20:00:00+00') = DATE '2026-01-02';

       QUERY PLAN
------------------------
 Seq Scan on people
   Filter: (id = 1)
(2 rows)

localday(...) = DATE '2026-01-02' 조건이 Filter에서 통째로 사라졌다. IMMUTABLE이라 믿은 planner가 그 비교를 플랜 시점에 true로 접어 없앤 것이다. 그런데 이 함수는 timezone에 따라 결과가 달라진다:

SET TimeZone='Asia/Seoul';
SELECT localday('2026-01-01 20:00:00+00');  -- 2026-01-02
SET TimeZone='UTC';
SELECT localday('2026-01-01 20:00:00+00');  -- 2026-01-01

   seoul    |    utc
------------+------------
 2026-01-02 | 2026-01-01

같은 인자에 결과가 둘로 갈린다 = IMMUTABLE 약속 위반. 이 함수가 접혀 캐시되면 서머타임/타임존 경계에서 조용히 틀린 값을 낸다. 규칙은 하나다 — "유효한 가장 엄격한 라벨"을 붙이되, 시간·로케일·설정에 의존하면 최대 STABLE까지만.

이렇게도 쓴다

세 라벨의 의미. VOLATILE(기본, 매 행 재평가, 인덱스 조건 불가), STABLE(한 문장 안에서 일관, 인덱스 스캔에 안전), IMMUTABLE(영원히 불변, 상수 폴딩·표현식 인덱스 가능).

CREATE FUNCTION f(int) RETURNS int LANGUAGE sql IMMUTABLE AS $$ SELECT $1*2 $$;

 

함수의 현재 라벨을 확인한다. (조합: pg_proc)

SELECT proname, provolatile  -- i=immutable, s=stable, v=volatile
FROM pg_proc WHERE proname = 'norm';

 

이미 만든 함수의 라벨만 바꾼다(본문 재작성 없이).

ALTER FUNCTION norm(text) IMMUTABLE;

 

SQL 언어 함수는 인라인되면서 라벨이 아니라 본문 식의 volatility로 판정될 수 있다. 확실히 하려면 명시 라벨을 붙인다.

CREATE FUNCTION norm(text) RETURNS text
  LANGUAGE sql IMMUTABLE AS $$ SELECT lower(trim($1)) $$;

 

generated column의 생성식에 쓰는 함수는 반드시 IMMUTABLE이어야 한다. (조합: 생성 컬럼)

ALTER TABLE t ADD COLUMN norm_email text
  GENERATED ALWAYS AS (norm(email)) STORED;

 

seq scan으로 떨어졌는지 진단한다. Filter에 함수가 남아 있으면 인덱스를 못 탄 것. (조합: EXPLAIN)

EXPLAIN (COSTS OFF) SELECT id FROM people WHERE norm(email) = 'x@y.com';