WHERE에 함수를 씌우면 인덱스가 죽는다: 표현식 인덱스와 생성 컬럼

한서윤 · 2026년 11월 2일 · 인덱스와 성능

로그인 API가 가끔 느리다는 말을 처음 들은 건 모바일 앱을 새로 내놓은 직후였다. 앱 입력창이 이메일 첫 글자를 대문자로 바꿔 보내는 바람에 [email protected]과 [email protected]이 서로 다른 회원으로 갈리는 일이 생겼다. 급한 마음에 조회 쿼리를 WHERE lower(email) = lower(?)로 고쳤다. 대소문자 문제는 그날로 사라졌는데, 그 대신 로그인 한 번에 수백 밀리초가 걸리기 시작했다.

email 컬럼에는 UNIQUE 인덱스가 멀쩡히 걸려 있었다. 그런데 EXPLAIN을 돌리자 PostgreSQL은 Seq Scan on users를 내놓았다. 답은 내가 바꾼 한 줄에 있었다. 인덱스에 들어 있는 건 email이지 lower(email)이 아니었다.

날짜 조건도 사정이 같다. 일별 매출을 뽑으면서 DATE(created_at) = '2026-10-01'처럼 쓰면 created_at 인덱스는 옆에서 구경만 한다. 아래는 PostgreSQL, MySQL 8, SQLite에서 인덱스를 다시 태운 기록이다.

인덱스는 저장된 값의 순서만 안다

B-트리 인덱스는 컬럼 값을 정렬해 두고 값마다 어느 행에 있는지 가리키는 목록이다. email = '[email protected]'을 찾을 때는 정렬된 목록을 따라 몇 단계만 내려가면 된다. lower(email) 조건은 다르다. 기본 정렬 규칙에서 대문자가 소문자보다 앞에 오면, 목록에서 [email protected]은 대문자 S 자리에 있다. 소문자로 바꾸면 전혀 다른 자리로 옮겨 가야 하는 값이다. 데이터베이스는 함수를 거친 값이 원래 정렬 순서를 지킨다고 믿을 근거가 없으니, 모든 행에 함수를 돌려 보고 하나씩 비교할 수밖에 없다.

길은 두 가지다. 조건에서 함수를 떼어 내 컬럼을 맨몸으로 두거나, 함수를 거친 값으로 따로 정렬한 인덱스를 만든다. 나는 앞쪽이 되면 앞쪽부터 한다.

날짜 함수는 범위 조건으로 풀어 쓴다

DATE(created_at)은 대개 함수 없이 같은 뜻으로 바꿔 쓸 수 있다. 하루치를 원하면 그날 0시 이상, 다음 날 0시 미만으로 적는다.

-- 인덱스를 못 탄다
SELECT id, amount FROM orders WHERE DATE(created_at) = '2026-10-01';

-- created_at 인덱스로 범위 검색을 한다
SELECT id, amount FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02';

예전에는 BETWEEN '2026-10-01 00:00:00' AND '2026-10-01 23:59:59'로 썼다. 그러다 밀리초가 붙은 행이 경계에서 빠지는 걸 보고 끝을 열어 둔 구간으로 바꿨다. 이 범위 조건을 복합 인덱스에 쓴다면 컬럼 순서도 따져야 한다. 그 이야기는 복합 인덱스 컬럼 순서 글에 따로 적었다.

이메일은 이렇게 풀 수가 없다. 가입할 때부터 소문자로 저장하는 게 가장 깔끔하지만, 이미 섞여 들어간 데이터가 수백만 건이면 하룻밤에 끝낼 일이 아니다. 이럴 때 표현식 인덱스를 꺼낸다.

PostgreSQL: 표현식 인덱스

PostgreSQL은 인덱스 정의에 함수나 식을 그대로 적을 수 있다. 함수 호출 하나라면 괄호를 더 두를 필요가 없고, first_name || ' ' || last_name 같은 식은 괄호로 한 번 더 감싼다.

-- 대소문자만 다른 중복이 이미 있는지 먼저 확인
SELECT lower(email), count(*) FROM users GROUP BY 1 HAVING count(*) > 1;

CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_uniq ON users (lower(email));

SELECT id FROM users WHERE lower(email) = lower('[email protected]');

인덱스를 만들고 다시 EXPLAIN하자 Index Scan using users_email_lower_uniq가 찍혔다. 수백 밀리초 걸리던 로그인이 1밀리초 아래로 내려왔다. UNIQUE로 만든 덕분에 대소문자만 다른 중복 가입도 DB에서 막힌다. 대신 그런 중복이 이미 있으면 생성이 실패하므로 첫 줄로 중복부터 찾아 정리했다.

timestamptz에 date를 씌우면

날짜 쪽에서는 한 번 막혔다. timestamptz 컬럼에 ((created_at::date))로 인덱스를 만들려 하자 functions in index expression must be marked IMMUTABLE 오류가 났다. timestamptz를 날짜로 바꾼 결과는 세션의 시간대 설정에 따라 달라지니 인덱스에 담을 수 없다. 메일링 리스트에서 권하는 대로 시간대를 식 안에 못 박으니 만들어졌다.

CREATE INDEX orders_created_day_idx
  ON orders (((created_at AT TIME ZONE 'Asia/Seoul')::date));

SELECT id FROM orders
WHERE (created_at AT TIME ZONE 'Asia/Seoul')::date = DATE '2026-10-01';

쿼리도 인덱스와 똑같은 식으로 써야 이 인덱스를 탄다. 공짜도 아니다. 표현식 인덱스는 행을 넣을 때마다, 그리고 HOT이 아닌 업데이트마다 식을 다시 계산한다. 조회가 잦고 쓰기가 덜한 테이블에 어울린다.

MySQL 8: 함수 인덱스와 생성 컬럼

MySQL은 8.0.13부터 함수 인덱스를 지원한다. 처음엔 문법에서 걸렸다. 식을 괄호로 한 번 더 감싸지 않으면 문법 오류가 난다.

-- 오류
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- 동작
CREATE INDEX idx_users_email_lower ON users ((LOWER(email)));

함수 인덱스는 안에서 숨은 가상 생성 컬럼으로 구현된다. 그래서 생성 컬럼에 못 쓰는 함수는 여기에도 못 쓰고, 기본 키에는 넣을 수 없다.

LOWER(email)을 쓰기 전에 콜레이션부터 확인해야 한다. 8.0의 utf8mb4 기본 콜레이션인 utf8mb4_0900_ai_ci는 처음부터 대소문자를 구분하지 않는다. 이걸 모르고 MySQL을 쓰는 다른 서비스에도 같은 인덱스를 붙였는데, 나중에 보니 email = ?만으로도 대문자 주소가 잘 찾아지고 있었다. 허탈했다. 컬럼이 _bin이나 _cs 콜레이션일 때만 의미가 있는 작업이었다.

이름 붙은 생성 컬럼

5.7부터 써 온 방법도 있다. 생성 컬럼을 직접 만들고 거기에 인덱스를 거는 방식이다.

ALTER TABLE orders
  ADD COLUMN created_date DATE AS (DATE(created_at)) VIRTUAL,
  ADD INDEX idx_orders_created_date (created_date);

EXPLAIN SELECT id FROM orders WHERE created_date = '2026-10-01';

옵티마이저는 WHERE의 식이 생성 컬럼 정의와 같으면 컬럼 이름이 없어도 그 인덱스를 쓴다. EXPLAIN 뒤에 SHOW WARNINGS를 보면 식이 컬럼 이름으로 바뀐 쿼리가 나온다. 다만 정의가 f1 + 1이면 1 + f1은 못 알아보고, 결과 타입도 같아야 한다. 그래서 나는 쿼리에 컬럼 이름을 직접 쓴다.

SQLite: 식은 글자 그대로 맞춘다

SQLite는 2015년 10월에 나온 3.9.0부터 표현식 인덱스를 지원한다. 문법은 PostgreSQL과 거의 같지만, 플래너는 식을 계산해서 같은지 따지지 않는다. x+y로 만든 인덱스는 y+x로 쓴 조건에 쓰이지 않는다.

CREATE INDEX idx_users_email_lower ON users (lower(email));
CREATE INDEX idx_events_day ON events (date(created_at));

EXPLAIN QUERY PLAN
SELECT id FROM events WHERE date(created_at) = '2026-10-01';

데스크톱 앱의 로컬 DB에 이 인덱스를 붙였을 때 한 화면만 계속 느렸다. 그 화면의 쿼리만 lower(trim(email))로 쓰여 있어서 그 경로는 내내 풀 스캔이었다. 날짜 함수는 3.20.0부터 인자에 따라 결정적 함수로 취급되어 인덱스에 쓸 수 있다. 'now'나 'localtime' 수식어가 들어가면 거부한다. 표현식 인덱스가 든 DB 파일은 3.9.0보다 오래된 버전에서 못 읽는다는 점도 챙겨 둔다.

요즘 내가 고르는 순서

먼저 함수를 걷어 낼 수 있는지 본다. 날짜 조건은 거의 다 범위로 풀린다. 그게 안 되면 표현식 인덱스를 만들고, 쿼리에 쓰는 식은 코드 한 곳에 모아 두고 가져다 쓴다. 누가 trim을 하나 더 끼워 넣는 순간 인덱스가 조용히 빠지기 때문이다. 인덱스를 만든 다음에는 반드시 EXPLAIN으로 실제로 타는지 눈으로 확인한다.

표현식 인덱스를 쓸 때마다 저장할 때 데이터를 정리하지 못한 값을 치른다는 생각이 든다. 로그인 건도 결국 가입할 때 이메일을 소문자로 저장하도록 바꾸고 기존 데이터를 정리했다. 그래도 lower(email) 인덱스는 지우지 않았다. 이제는 속도 때문이 아니라, 앱이 또 대문자를 섞어 보낼 때 중복 가입을 막는 UNIQUE 제약으로 남아 있다.