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) 형태가 인덱스 정의와 정확히 일치해야 하니, 앱에서 날리는 함수와 인덱스 함수를 똑같이 맞춰주세

pg_minjae · Answer

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

댓글 0