인덱스를 만든 날과 인덱스가 실제로 쓰인 날이 13일 떨어져 있다

2026년 3월 12일 커밋 361bc9e에서 GIN trigram 인덱스를 넣었다. 그런데 그 인덱스가 실제로 쿼리 플랜에 등장하기 시작한 건 3월 25일 커밋 4fb9cbd부터다. 그 사이 13일 동안 인덱스는 디스크만 차지하고 아무 일도 하지 않았다.

이 글은 그 13일에 대한 이야기다.

문제

Spring Boot 3.2 + PostgreSQL 15 기반의 B2B 산업용 관리 시스템이다. 장비 목록 화면에서 장비번호·고객명·관리번호로 부분일치 검색을 한다. 즉 쿼리가 이렇게 생겼다.

WHERE c.name LIKE '%' || #{keyword} || '%'

앞에 %가 붙은 LIKE는 B-tree 인덱스를 탈 수 없다. 선행 문자열이 고정돼야 범위 스캔이 되는데, 앞이 와일드카드면 그럴 수가 없다. 그래서 매 검색이 풀스캔이었다.

1차 시도: pg_trgm + GIN 인덱스

PostgreSQL에는 이걸 위한 도구가 있다. pg_trgm 확장은 문자열을 3글자 단위(trigram)로 쪼개고, gin_trgm_ops 연산자 클래스를 쓰면 GIN 인덱스로 %keyword% 형태를 지원한다.

V149 마이그레이션에서 확장을 켜고 인덱스를 걸었다.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX IF NOT EXISTS idx_meter_no_trgm
    ON client_meter USING GIN (meter_no gin_trgm_ops)
    WHERE is_deleted = FALSE AND removal_date IS NULL;

부분 인덱스(WHERE 절)로 만든 건 의도적이다. 목록 조회는 항상 삭제되지 않은 행만 본다. 인덱스에서 죽은 행을 빼면 크기가 줄고 갱신 비용도 줄어든다.

같은 계열로 V150에서 주소·장비 식별자 컬럼에도 GIN 인덱스를 추가하고, LATERAL JOIN이 매 행마다 실행되던 구간에 커버링 인덱스를 얹었다.

여기까지 하고 커밋했다. 인덱스를 만들었으니 빨라졌을 거라고 생각했다.

그런데 안 빨라졌다

13일 뒤 다시 들여다봤을 때, 실행 계획에 여전히 Seq Scan이 찍히고 있었다. 인덱스는 분명히 존재하는데 옵티마이저가 쓰지 않았다.

원인은 쿼리 쪽이었다. 매퍼 XML의 실제 조건절은 이렇게 생겨 있었다.

LOWER(c.name) LIKE LOWER('%' || #{keyword} || '%')

대소문자 구분 없이 검색하려고 양쪽에 LOWER()를 감싼 것이다. 흔한 패턴이고, 그 자체로 틀린 코드도 아니다.

문제는 인덱스가 name에 걸려 있지 LOWER(name)에 걸려 있지 않다는 것이다. PostgreSQL 입장에서 name과 LOWER(name)은 전혀 다른 표현식이다. 인덱스 정의와 쿼리 표현식이 문자 그대로 일치해야 인덱스를 후보로 올린다. 함수를 한 번 감싸는 순간 그 인덱스는 없는 것과 같아진다.

선택지는 둘이었다.

방법	내용	판단
표현식 인덱스로 맞추기	`GIN (LOWER(name) gin_trgm_ops)` 로 재생성	인덱스가 커지고, 모든 검색 컬럼마다 별도 인덱스 필요
쿼리를 인덱스에 맞추기	`LOWER(x) LIKE LOWER(y)` → `x ILIKE y`	인덱스 그대로 사용 가능

ILIKE는 PostgreSQL의 대소문자 무시 LIKE다. 의미가 LOWER() LIKE LOWER()와 같으면서, trigram GIN 인덱스가 지원하는 연산자다. 후자를 택했다.

어떻게 고쳤나

커밋 4fb9cbd에서 매퍼 XML 전반의 LOWER(...) LIKE LOWER(...) 패턴을 ILIKE로 일괄 전환했다. 130개소 남짓이었다. 조직 관련 매퍼 하나만 373줄이 바뀌었다.

기계적인 치환처럼 보이지만 한 가지 함정이 있다. LOWER(a) LIKE LOWER(b)와 a ILIKE b는 ASCII 범위에서는 동일하지만, 로케일에 따라 특수 문자에서 갈릴 수 있다. 검색 대상이 장비번호·고객명 수준이라 실질 위험은 없다고 판단하고 진행했다.

덤: 인덱스가 아예 필요 없어진 케이스

같은 커밋에서 제조사 검색 UI를 손봤다. 원래는 사용자가 제조사명을 텍스트로 입력하면 부분일치로 찾는 방식이었다. 이걸 드롭다운 선택으로 바꿨다. 제조사 목록을 내려주는 엔드포인트를 하나 추가하고, 화면은 목록에서 고르게 했다.

그 결과 조건절이 ILIKE '%...%'에서 = '...'로 강등됐다. 정확매칭은 일반 B-tree 인덱스로 충분하다. trigram GIN이 아예 필요 없어진 것이다.

가장 빠른 부분일치 검색은 부분일치를 하지 않는 것이다. 검색어의 후보 집합이 유한하고 사용자가 그중에서 고르면 되는 상황이라면, 자유 텍스트 입력을 고집할 이유가 없다.

남는 교훈

이 일에서 실제로 배운 건 인덱스 문법이 아니다.

인덱스를 만든 것과 인덱스가 쓰이는 것은 별개의 사건이다. 그리고 그 둘 사이에는 아무런 자동 연결이 없다. CREATE INDEX는 성공했다고 알려주지만, “이 인덱스는 아무 쿼리도 타지 않습니다”라고는 말해주지 않는다. 13일 동안 조용했던 이유다.

DDL을 커밋하기 전에 EXPLAIN으로 대상 쿼리가 실제로 그 인덱스를 타는지 확인했다면 13일이 아니라 13분이면 끝났을 일이다. 인덱스 작업의 완료 조건은 “인덱스 생성”이 아니라 “플랜에 인덱스가 등장”이어야 한다.

부수적으로, 이 사건은 함수로 감싼 조건절이 얼마나 조용하게 인덱스를 무력화하는지도 보여준다. LOWER(), CAST(), COALESCE() 같은 것들을 조건절 좌변에 쓰고 있다면 그 컬럼의 인덱스는 대체로 쓰이지 않고 있다고 보면 된다.

전은성 Dev

Contact