page-cover
🤖
개발
닉네임 자동완성 성능개선 일지 With ChatGPT
sooros5132-avatarsooros51325/27/2024Postgres ChatGPT 적용기
메애기 검색엔 닉네임 자동완성 기능이 있다. 캐릭터 테이블의 약 800만개 rows에서 한글 완성된 자모 1글자 기준으로 닉네임을 검색하면 200~800ms의 검색 시간이 소요되지만 크롤링 작업의 결과물들을 db에 등록하고 있을 땐 검색시간이 종종 timeout(10초) 될 정도로 현저히 느려지는것을 발견하게 됐다. 매우 불쾌한 경험이니 고치기로 마음을 먹는다.

EXPLAIN ANALYZE로 쿼리 분석하기

닉네임 검색 SQL중 일부분을 가져와 EXPLAIN ANALYZE 키워드를 써서 어느 부분이 오래 걸리는지 확인
sql
EXPLAIN ANALYZE
SELECT "...Nickname"
FROM "table" 
WHERE "...Nickname" ILIKE '아%'
ORDER BY
  "...Nickname" ILIKE '아%' ASC,
  CHAR_LENGTH("c"."nickname") ASC,
  "nickname" ILIKE '아%' DESC,
  CHAR_LENGTH("c"."...Nickname") ASC
LIMIT 10;
Bitmap Heap Scan 부분에서 코스트가 상당하고 시간이 꽤 걸리는 것을 발견하게 된다. 사실 DB를 접한지 얼마 안돼서 이 라인들의 의미를 정확히 모르고 있고 ChatGPT의 도움을 받아 모든 라인을 자세하게 설명해 달라고 부탁했다.

ChatGPT 도움받기

🤖
ChatGPT의 답변 중 일부분
  1. Bitmap Heap Scan on "table": 비트맵 힙 스캔을 수행하여 "table" 테이블에서 행을 검색합니다.
    • cost=1878.65..375793.17 rows=166535 width=385: 예상 비용, 예상 행 수 및 행의 폭.
    • actual time=87.498..851.067 rows=227920 loops=1: 실제 소요 시간, 실제 반환된 행 수, 루프 수.
    • Recheck Cond: ("...Nickname" ~~* 'ㅇㅏ%'::text): 인덱스 재검사 조건.
    • Rows Removed by Index Recheck: 1: 인덱스 재검사로 제거된 행 수.
    • Heap Blocks: exact=191628: 정확히 일치하는 힙 블록 수.
  1. Bitmap Index Scan on "…Nickname_idx": 비트맵 인덱스 스캔을 수행하여 "…Nickname_idx" 인덱스에서 행을 검색합니다.
    • cost=0.00..1837.01 rows=166535 width=0: 예상 비용, 예상 행 수.
    • actual time=49.455..49.456 rows=228000 loops=1: 실제 소요 시간, 실제 반환된 행 수, 루프 수.
    • Index Cond: ("...Nickname" ~~* 'ㅇㅏ%'::text): 인덱스 조건.
이 실행 계획에서 가장 시간이 많이 소요되는 단계는 비트맵 힙 스캔과 정렬입니다. 인덱스를 최적화하거나 쿼리 조건을 조정하여 성능을 향상시킬 수 있습니다.
이 답변을 보고 index는 정상 작동하니 아닌거 같고, 정렬쪽에 문제이다 싶어서 ORDER BY 없이 검색 쿼리를 날려보았다. 결과는 20ms 미만으로 매우 빠르게 끝나는것을 확인
ChatGPT에 추가로 질문으로 정렬이나 index 개선사항을 제시해달라고 부탁했다.
🤖
ChatGPT의 답변 중 일부분
4. Materialized View 사용
Materialized View를 사용하여 데이터를 정렬된 형태로 미리 저장할 수 있습니다. Materialized View는 정기적으로 갱신될 수 있습니다.
다른 테이블에서도 Materialized View를 이용해봐서 방법들을 이미 어느정도 알고 있었고 미리 정렬해놓으면 정렬하는 코스트가 안들테니 이 방법이다 싶어서 적용하기로 결심
다른 제안으로도 일반 View를 쓰라고 하는데 이렇게 되면 ILIKE 검색 하는데에 필요한 GIN index를 사용할 수 없어서 제외하게 되었다.

Solution

그렇게 해서 Materialized View에 미리 정렬시켜 만들고 GIN index도 적용, 이 테이블로 ORDER BY 없이 검색하는 방식으로 변경하게 된다.
sql
-- Materialized View SQL 중 일부분, 미리 정렬만 시켜서 저장
SELECT "...Nickname"
FROM "table"
ORDER BY (char_length("...Nickname")), (char_length("nickname")), "...Nickname", "nickname";
sql
-- 실제 검색할때 일부분
SELECT "...Nickname"
FROM "table" 
WHERE "...Nickname" ILIKE '아%'
LIMIT 10
결과는 예상했던 대로 크롤링 작업이 있건 없건 미리 정렬된 테이블이니 모두 20~30ms 최대 50ms를 넘기지 않는 것으로 나왔다.
평소 검색 기준으로 최대 소요시간인 800ms → 50ms로 약 16배 낮출 수 있었다.
주기적으로 동작할 REFRESH도 등록하고 이제 크롤링을 마음대로 할 수 있게 됐다.

후기

혹시나 비슷하게 따라하시는 분이 있을까 싶어 얘기하면 Materialized View는 테이블은 SELECT 했던 결과들이 자동으로 갱신되지 않는다. 쿼리 결과들을 물리적으로 저장을 하므로 한번씩 REFRESH가 필요하니 이 방법도 같이 찾아봐야 한다.
오랜만에 ChatGPT를 켜봤는데 GPT‑4o모델이 설명이나 제안해주는 방식이나 너무 매력적이라 결제할까 싶을 정도로 매우 만족스럽게 사용한거 같다.
View Archive