PostgreSQL 이메일 조회 쿼리가 데이터 늘어난 뒤 갑자기 느려졌는데 EXPLAIN에 Seq Scan이 떠요

Question

안녕하세요. 며칠째 원인을 못 찾아서 글 남깁니다.회원 로그인할 때 이메일로 조회하는 아주 단순한 쿼리인데, 초반엔 빨랐다가 회원이 30만 명쯤 넘어가니까 갑자기 1~2초씩 걸립니다. 인덱스는 분명히 걸어놨거든요.-- 인덱스 CREATE INDEX idx_users_email ON users (email); -- 실제 쿼리 (애플리케이션에서 대소문자 무시하려고) SELECT id, name FROM users WHERE lower(email) = lower('Minjae@Example.com');EXPLAIN ANALYZE 돌려보면 이렇게 나옵니다.Seq Scan on users (cost=0.00..8931.00 rows=1 width=36) Filter: (lower(email) = 'minjae@example.com'::text) Rows Removed by Filter: 312440 Planning Time: 0.12 ms Execution Time: 1487.221 ms인덱스가 있는데 왜 Seq Scan을 타는 걸까요? 인덱스 이름이 잘못된 건 아닌 것 같은데 도무지 모르겠습니다.

김영삼 · Accepted Answer

안녕하세요. 결론부터 말씀드리면 인덱스는 잘 만들어졌는데, 쿼리 조건이 인덱스가 걸린 형태와 달라서 못 쓰는 상황입니다.

인덱스는 email 컬럼 원본 값 기준으로 만들어졌는데, 쿼리는 lower(email)이라는 함수 결과로 비교하고 있죠. 옵티마이저 입장에서 lower(email)은 컬럼이 아니라 계산된 값이라, idx_users_email과 매칭이 안 됩니다. 그래서 전체를 훑는 Seq Scan으로 빠지는 거예요.

해결은 함수 기반 인덱스(expression index)를 만들면 됩니다.

CREATE INDEX idx_users_email_lower ON users (lower(email));

-- 만든 뒤 통계 갱신
ANALYZE users;

이렇게 하면 쿼리를 그대로 두어도 Index Scan using idx_users_email_lower로 바뀝니다. 조건식의 lower(email) 형태가 인덱스 정의와 정확히 일치해야 하니, 앱에서 날리는 함수와 인덱스 함수를 똑같이 맞춰주세요.

추가로, 이메일은 사실상 대소문자 구분 없이 유일해야 하는 값이라 UNIQUE 함수 인덱스로 만들어서 중복까지 막는 걸 권장드립니다.

CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));

정석대로 가려면 citext 확장을 써서 컬럼 타입 자체를 대소문자 무시로 바꾸는 방법도 있는데, 기존 스키마 바꾸는 부담이 크면 위의 함수 인덱스만으로도 충분히 해결됩니다.

pg_minjae · Answer

와 바로 Index Scan으로 바뀌고 1.4초에서 0.8ms로 줄었습니다. 함수로 감싸면 인덱스가 무력화된다는 걸 처음 알았네요. 며칠 헤맨 게 5분 만에 해결됐습니다. 정말 감사합니다 🙏

댓글 2