Postgresql 컬럼 값 변형으로 Index를 타지 못하는 문제

환경

Postgresql 14

발생 문제

특정 API의 성능이 너무 느리게 나와서 속도를 측정해보니 하나의 쿼리 수행에 무려 60초나 걸리고 있었다.

이 쿼리는 2개의 테이블을 inner join 하여 특정 컬럼을 index에 태워 보내며 동작한다.

예를 들어 Students(id, name, department), Class(S_id, number) 라는 테이블이 존재할 떄

-- 쿼리
SELECT 
	*
FROM 
	Students inner join Class on Students.id = Class.S_id
WHERE 
	name in ('Kim', 'Jin', 'Lee')
	and split_part(department, '_', 2) in ('CS', 'PM', 'FL')
    and number in ('456', '223', '739')

-- Index
CREATE INDEX idx_01 ON Students USING BTREE (name, department)

문제 원인

name과 department는 Btree로 다중 컬럼 인덱스를 생성하여 인덱스를 사용하고 했다. 아쉽게도
name은 인덱스를 정상적으로 타지만, department는 인덱스를 타지 않았다.

만약

SELECT 
	*
FROM 
	Students inner join Class on Students.id = Class.S_id
WHERE 
	name in ('Kim', 'Jin', 'Lee')
	and department in ('CS', 'PM', 'FL')
    and number in ('456', '223', '739')
 

이런 구조였다면 무난하게 인덱스를 탈 수 있지만, split_part를 사용하여 원본에서 변형되었기 때문에 name만 인덱스를 타고 department는 인덱스를 탈 수 없게 됐다.

아이디어

department가 인덱스를 타지 못하기 때문에 쿼리의 전체 실행속도가 매우 느려졌다. 그러므로 가장 좋은 방법은 department를 인덱스에 같이 태워 보내는 방법이다.

Like를 활용한 인덱스

처음에는 like를 사용하여 like '%CS' 와 같은 방법을 사용 했다.
하지만 like에서 인덱스를 태울 수 있는 경우는 department like 'CS%' 와 같이 특정 문자열이 먼저 앞으로 나오고 뒤에 %가 붙어야 한다. 그래서 이 방법은 사용하지 못 했다.

서버에서 처리 하기1

department의 데이터 구조는 (col1 의 데이터)_(TEXT)의 구조를 지니고 있다. 쉽게 말하면

name	department
Kim	Kim_CS
Lee	Lee_CS
Jin	Jin_PM
Jadon	Jadon_FL

이런 구조를 지니고 있다. 그렇지만 기존의 서버에서는 department의 CS, PM, FL 과 같이 _ 뒤에 있는 정보만 가지고 있었다. 그래서 해당 데이터를 데이터베이스에 저장된 Kim_CS와 같이 형태를 맞추면 split_part를 사용하지 않고 바로 쿼리에 작성할 수 있으므로 해결할 수 있다고 생각했다.
하지만 해당 name과 department를 매핑 하는 과정이 추가적으로 필요했고, 과정 자체도 프론트와 서버 모두 손봐야 하는 작업이어서 진행하지 않았다.

서버에서 처리 하기2

해당 쿼리에서 col2가 인덱스를 타지 못 해 느려지기 때문에 해당 컬럼을 제외하고 쿼리를 수행한다.

그 뒤에 받아온 데이터를 Java의 Stream과 filter를 사용하여 처리할 수 있다. 이 과정은 쿼리 수행을 단축 시킬 수 있지만, 근본적인 문제를 해결할 수 는 없었다.

그리고 쿼리에서 받아온 데이터를 다시 한 번 순회해야 하므로 추가 시간이 소요됐다.

DB 구조 바꾸기

서버에서 처리하기 1을 보면 해당 데이터는 정규화 3NF를 만족하지 않음을 알 수 있다. 왜냐하면 department를 알면 name은 자동으로 알 수 있게 되기 때문이다. 그래서 해당 department에서 _ 뒤의 값만 저장하게 된다면, 서버에서 쿼리를 날릴 때 문제없이 인덱스를 타게 될 것이다.

하지만 이는 프론트엔드에서 department의 값만 불러서 대시보드에 올려 차트로 볼 때 해당 컬럼만으로 어떤 name인지 알 수 있어야 했다. 그래서 데이터 구조 자체를 바꾸는 것은 불가능 했다.

GIN(Generalized Inverted iNdex)

like를 활용한 인덱스를 찾던 중 GIN 을 알게 됐다.

간단히 정리하면, GIN은 역색인 구조를 활용하여 특정 keyword가 들어간 데이터를 찾아낸다. 그렇기 때문에 특정 단어가 포함된 문자열을 빠르게 찾을 수 있다.

GIN을 사용하면 like '%CS%와 같은 조건도 인덱스를 타게 할 수 있다.
이는 trgm을 활용하여 가능하게 만든 것이다.

trgm은 trigram을 활용하여 인덱스를 생성하여 데이터 내에 특정 문자열이 들어있는지 빠르게 확인할 수 있게 한다.

이를 사용하기 위해

-- pg_trgm extension을 추가
CREATE extension pg_trgm;

-- index 생성
CREATE INDEX gin_idx01 ON Students USING GIN (name gin_trgm_ops, department gin_trgm_ops);

다음과 같이 인덱스를 생성할 수 있다. trgm의 경우 기본으로 제공 되지 않아 extension을 추가해야 한다.

분명 나는 GIN으로 모든 문제를 해결 할 수 있을 것이라 생각했다. 왜냐하면 이렇게 하게되면 두 컬럼 모두 자연스럽게 인덱스를 태울 수 있으니 말이다. 하지만 결과는 생각 처럼 나오지 않았다.

우선 기존에 존재하던 index는 name, department를 모두 Btree 인덱스로 만들어 사용 했다. 그래서 name과 departments를 인덱스를 GIN 인덱스로 변경한 뒤 실행을 해봤다.

하지만 결과는 예상과는 달랐다. 우선 기존에 Btree로 만든 index에서 department만 제외한 인덱스와 GIN으로 새로 만든 인덱스 2개를 다음과 같이 만들었다.

-- Btree
CREATE INDEX btree_idx01 ON Students USING BTREE (name);

-- GIN
CREATE INDEX gin_idx01 ON Students USING GIN (name gin_trgm_ops, department gin_trgm_ops);

위와 같은 두 개의 인덱스를 만들고 EXPLAIN ANALYZE를 통해 쿼리의 실행 계획을 확인하며 분석하니 GIN 인덱스를 타는 것이 아닌 Btree 인덱스를 타고 있었습니다.

분명 Postgresql에서 GIN 인덱스를 선택할 것이라고 예상했는데 아니었습니다.

결론

우선 위에서 제시한 아이디어 중 GIN이 가장 마음에 들고 합리적인 해결책이라고 생각했는데, 마음대로 동작하지 않아서 아쉬움이 컸다. 또한 사수분께서 인덱스를 변경하면 다른 API에서 문제가 발생할 수 있으니, 서버단에서 처리하는 것이 좋아보인다고 말씀해주셨다. 그래서 결국 서버단에서 department를 제외한 데이터를 가져와 department 부분만 stream, filter를 사용하여 걸러냈다.
해결하긴 했지만 뭔가 찜찜한 기분이다...

Reference

https://medium.com/vuno-sw-dev/postgresql-gin-%EC%9D%B8%EB%8D%B1%EC%8A%A4%EB%A5%BC-%ED%86%B5%ED%95%9C-like-%EA%B2%80%EC%83%89-%EC%84%B1%EB%8A%A5-%EA%B0%9C%EC%84%A0-3c6b05c7e75f
https://www.postgresql.org/docs/current/indexes-bitmap-scans.html