PostgreSQL에서 문자열 검색을 위한 인덱싱

다음과 같은 레코드가 정의 되어 있다고 가정하자

// Group
{
    "id": "UUID",
    "name": "varchar(255)",
    "description": "varchar(500)",
    "createdAt": "timestamp",
    ...
}

위와 같은 레코드가 정의되어 있고, name 과 description, createdAt 에 각각 B트리 기반 인덱스가 걸려있다고 가정하자.

그룹 검색 시나리오

1. 사용자는 관심이 있는 그룹을 검색하기 위해 특정 키워드를 입력
2. 키워드를 포함하는 키워드를 name 또는 description 에서 검색
3. 이 때 검색 결과를 가장 최근 생성된 그룹 순으로 노출

위와 같은 시나리오에서 SQL문은 보통 다음과 같은 간단한 형태로 나타난다.

SELECT id, name, description, createdAt ...
FROM Group
WHERE name like "%keyword%"
ORDER BY createdAt DESC

이 때 인덱스는 바람과 다르게 createdAt 인덱스만 사용된다.

문자열에 인덱싱을 걸면 기본적으로 문자열의 시작 문자를 기준으로 순차적으로 인덱싱이 되기 때문에 % 키워드를 통해 앞글자를 확정할 수 없는 경우 사실상 FULL SCAN이 발생한다.

문자열에 인덱싱이 필요한 경우

MySQL에서는 FULLTEXT 인덱스 PostgreSQL에서는 pg_trgm 확장을 이용하여 인덱스를 걸면 검색 효율을 높일 수 있다.

# MySQL
CREATE FULLTEXT INDEX idx_fulltext ON group(name);
# PostgreSQL
CREATE INDEX idx_trgm ON Group USING gin (name gin_trgm_ops)

그리고 이 경우 쿼리는 다음과 같이 변경해야 한다.

# MySQL
# Full Text를 이용하는 경우
SELECT * FROM Group WHERE MATCH(name) AGAINST ('값 IN BOOLEAN MODE) ORDER BY createdAt;

# PostgreSQL
# GIN 확장을 이용하는 경우
SELECT * FROM Group WHERE name ILIKE '%값%' ORDER BY createdAt DESC;