[PostgreSQL] 표현식 인덱스(expression index)로 LOWER(email) 검색을 태운다

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

부제: 대소문자 섞인 이메일을 LOWER(email)='...'로 대소문자 무시 조회할 때, 일반 인덱스는 안 먹힌다

-- 컬럼이 아니라 "함수 결과"에 인덱스를 만든다
CREATE INDEX idx_users_lower_email ON demo_users(LOWER(email));

SELECT * FROM demo_users WHERE LOWER(email)='user12345@example.com';

-- 일반 인덱스 demo_users(email)는 LOWER()가 씌워지면 못 쓴다: Seq Scan, 1728 buffers, 18.7 ms
 Bitmap Heap Scan on demo_users (actual time=0.021..0.021 rows=1 loops=1)
   Recheck Cond: (lower(email) = 'user12345@example.com'::text)
   ->  Bitmap Index Scan on idx_users_lower_email
         Index Cond: (lower(email) = 'user12345@example.com'::text)
 Execution Time: 0.035 ms

인덱스는 컬럼 원본값을 정렬해 저장한다. 그래서 WHERE LOWER(email)=...처럼 컬럼에 함수를 씌우면, 저장된 원본값과 매칭이 안 돼 인덱스를 버리고 20만 행을 전부 훑는다(18.7ms). 해법은 컬럼이 아니라 LOWER(email)이라는 표현식 자체에 인덱스를 만드는 것. 그러면 쿼리의 표현식과 인덱스의 표현식이 똑같아 매칭돼 0.03ms로 끝난다. 핵심 규칙: 조회에서 쓰는 표현식과 인덱스에 넣은 표현식이 문자 그대로 일치해야 한다. 함정은 표현식 인덱스는 IMMUTABLE 함수에만 만들 수 있다는 것(now()처럼 값이 변하면 불가).

이렇게도 쓴다

대소문자 무시 UNIQUE 제약을 표현식 인덱스로 건다. (조합: UNIQUE)

CREATE UNIQUE INDEX uq_email_ci ON demo_users(LOWER(email));

 

JSONB 필드 안의 특정 키를 뽑아 인덱싱한다. (조합: ->>)

CREATE INDEX idx_meta_country ON orders((metadata->>'country'));

 

날짜에서 연-월만 뽑아 월별 집계 조회를 태운다. (조합: date_trunc)

CREATE INDEX idx_orders_month ON demo_orders(date_trunc('month', created_at));

 

이름을 이어붙인 전체이름으로 검색한다. (조합: 문자열 연결)

CREATE INDEX idx_fullname ON users((first_name || ' ' || last_name));

 

숫자 계산 결과(할인가)에 인덱스를 걸어 범위 조회를 빠르게 한다.

CREATE INDEX idx_final_price ON products((price * 0.9));

 

플래너가 표현식 인덱스를 실제로 타는지 확인한다. (조합: EXPLAIN)

EXPLAIN (COSTS OFF) SELECT * FROM demo_users WHERE LOWER(email)='user1@example.com';