PostgreSQL: LIKE vs ILIKE

※ PostgreSQL: LIKE vs ILIKE.

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 PostgreSQL에만 있는 문법인 ILIKE에 대한 내용입니다.

---

1. ILIKE?

• ILIKE는 ANSI SQL 표준에는 존재하지 않는 PostgreSQL 확장 기능입니다.
• ILIKE는 LIKE와 동일한 패턴 매칭 문법을 사용하지만, 대소문자를 구분하지 않습니다(case-insenstive).

간단하죠? 그냥 리눅스에서 grep 옵션 중 -i와 같은 기능입니다.

---

2. 테스트

▸ 테스트용 DDL, DML

-- 테스트 테이블 생성
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- 테스트 데이터 삽입
INSERT INTO users (name) VALUES
('John'),
('john'),
('JOHN'),
('Johnny'),
('Alice');

 

▸ LIKE

SELECT * FROM users WHERE name LIKE 'John%';

 

▸ ILIKE

SELECT * FROM users WHERE name ILIKE 'john%';

 

+ 표준 SQL 방식의 대소문자 무시

SELECT * FROM users WHERE LOWER(name) LIKE LOWER('john%');

---

아래부터는 동작 원리로 조금 어려운 내용입니다.

 

3. 구문·연산자 계층

• 파서는 LIKE / ILIKE를 각각 내부 연산자 ~~ / ~~*로 치환합니다. (실행계획을 보면 ~~ / ~~* 이렇게 나옵니다.)

---

4. 실행 단계: ILKE 매칭 로직 (대소문자 무시 + 패턴 해석)

• 실제 매칭은 src/backend/utils/adt/like.c에서 수행됩니다.
  여기서 ILIKE는 비교 전에 대소문자 무시(case-folding)를 적용한 뒤, LIKE와 동일한 와일드카드(%, _) 규칙으로 패턴을 해석합니다. 단일 바이트 인코딩일 때는 각 문자에 대해 SB_lower_char를 호출하는 최적화 경로가 있고, 멀티바이트에서는 더 일반적인 경로를 탑니다. 또한 비결정적(nondeterministic) 콜레이션은 ILIKE에서 지원되지 않아 에러를 냅니다.
• 콜레이션은 결과의 대소문자 규칙에 직접 영향을 줍니다.
  예컨대 ICU 기반 특정 로케일에서 ILIKE 동작은 해당 로케일 규칙을 따르며, 일부 콜레이션·패턴 조합 제약(비결정적 콜레이션 불가 등)이 존재합니다.

---

5. 계획 단계(Planner/Optimizer): 선택도 추정과 인덱스 지원 판단

• ILIKE/LIKE의 선택도(selectivity) 추정은 like_support.c·selfuncs.c에서 수행됩니다.
  함수명으로는 iclikesel()(ILIKE), likesel()(LIKE) 등이 등록되어 있으며, 내부 공통 루틴에서 접두사 고정 여부(예: 'abc%')를 분석해 선택도를 계산합니다. 이 정보가 인덱스 경로의 이득/비용 계산에 사용됩니다.
• 선택도 추정의 기본 상수값과 철학("인덱스 스캔을 과도하게 포기하지 않도록 기본 추정을 충분히 작게 잡는다" 등)은 selfuncs.h에 주석과 함께 정의돼 있습니다.

---

6. 인덱스 활용 경로: 어떤 경우에는 타고, 어떤 경우에는 못타는지

6.1 B-tree + 패턴 연산자 클래스(text_pattern_ops 등)

• LIKE 'abc%'처럼 접두 패턴이면 B-tree로 범위 탐색이 가능합니다. 다만 기본 콜레이션 정렬은 로케일 규칙을 포함하므로, 문자 단위(strict) 비교를 강제하는 text_pattern_ops/varchar_pattern_ops 같은 패턴 전용 opclass를 쓰는 것이 정석입니다.
• 그러나 ILIKE는 "대소문자 무시" 정렬 개념을 필요로 하기 때문에, 표준 B-tree(심지어 text_pattern_ops)로 곧바로 범위가 정의되지 않습니다. 이 때문에 "그냥 col ILIKE 'abc%'만으로 B-tree 인덱스"는 일반적으로 불가합니다.
  2024년 말 메일링 리스트에도 "앞부분 매칭 ILIKE를 B-tree로 타게 하자"는 제안이 별도 패치로 논의 중이었을 정도로, 현행 동작은 기본적으로 미지원입니다.

 

▸ 우회 방법(표현식 인덱스)

CREATE INDEX ON t (lower(col));        -- 표현식 인덱스
SELECT ... FROM t WHERE lower(col) LIKE 'abc%';

→ 이 경우는 ILIKE가 아니라 lower(col) LIKE 이므로 B-tree 범위 탐색이 성립합니다(접두 패턴 한정). 이는 오퍼레이터 자체를 바꾸는 것이 아니라 질의와 인덱스의 “표현식” 일치를 보장하는 기법입니다. 개념적 근거는 위 패턴-opclass 문서에 동일합니다.

---

6.2 GIN/GiST + pg_trgm Extension

• pg_trgm은 텍스트를 trigram 토큰으로 분해해 부분 문자열 검색을 가속합니다. 이 인덱스는 LIKE뿐 아니라 ILIKE·정규식(~, ~*) 까지도 지원합니다. 즉, '%abc%' 같은 중간 매칭과 ILIKE 모두 인덱스 가능해집니다. 실무에서 ILIKE를 그대로 인덱스에 태우는 대표 경로입니다.

F.35. pg_trgm — support for similarity of text using trigram matching

---

6.3 비트맵 스캔 선택

• 후보 행이 많거나, 여러 인덱스 조건을 OR로 결합할 때(예: user_id ILIKE 'ab%' OR user_nm ILIKE '%ab%'), 플래너는 여러 인덱스의 결과 비트맵을 병합하는 Bitmap Index Scan → Bitmap Heap Scan을 선호합니다. 이때 한쪽은 pg_trgm(부분 매칭), 다른 쪽은 표현식 B-tree(접두 매칭)일 수도 있습니다. 계획 상에서는 BitmapOr 노드와 각각의 Bitmap Index Scan이 보입니다. 기본 원리와 예시는 패턴 인덱싱/선택도 문서 전반에서 일관되게 설명됩니다.

---

7. 성능/정확성에 영향을 주는 추가 논점

• 콜레이션: ILIKE는 활성 콜레이션의 대소문자 규칙을 따릅니다. 로케일마다 규칙이 달라질 수 있으므로, 다국어 환경에서는 테스트가 필요합니다. 비결정적 ICU 콜레이션은 ILIKE에서 금지입니다.
• 선택도 오판 리스크: 통계가 부정확하면 플래너가 시퀀셜 스캔을 택할 수 있습니다. ANALYZE 유지·히스토그램/MCV의 이해가 중요합니다(통계·선택도 개요).

---

8. 요약

• ILIKE는 내부적으로 콜레이션에 따른 case-folding + LIKE 패턴 매칭입니다.
• 기본 btree는 ILIKE를 그대로는 지원하지 않습니다. (col ILIKE 'abc%' → B-tree 인덱스 스캔 불가(현행 기본 동작))
• 인덱스를 타게하려면,
  • lower(col) 표현식 btree 인덱스 + WHERE lower(col) LIKE 'abc%'
    → 접두 매칭 가속 필요시 권장 패턴
  • pg_trgm(GIN/GiST) 인덱스 + WHERE col ILIKE '%abc%'
    → 중간 매칭·복합 조건(OR)·다국어 혼재까지 빠르게 처리하려면 이방식이 가장 실용적 (ILIKE를 그대로 인덱스에 태움)

---

추가 팁으로, SQLite는 LIKE 사용 시 대소문자를 구별하지 않습니다.

 

오늘은 여기까지~