[260413 TIL] 검색 쿼리 최적화 트러블슈팅2

텍스트 검색 쿼리 1초 → 0.6ms 최적화 삽질기 (2탄)

TL;DR

1탄에서 10초 → 1초까지 줄였던 검색 쿼리가, 사실 pg_trgm GIN 인덱스를 전혀 타지 않고 있었다. EXPLAIN ANALYZE로 실행 계획을 직접 확인한 결과 매번 Seq Scan(풀스캔)이 돌고 있었고, 검색 방식을 ILIKE → 단어 배열(text[]) + GIN array_ops로 교체하여 최종 0.6ms까지 단축했다.

이전 글 요약

• MV + search_text 컬럼(caption + hashtags 결합) + pg_trgm GIN 인덱스로 1초 달성
• 이 시점에서 "GIN 인덱스 덕에 빨라졌다"고 결론 내림
• 이 결론이 완전히 틀렸다.

삽질기: 1초 → 0.6ms까지의 여정

7단계: 아직도 5초?

1탄에서 1초를 달성했다고 썼지만, 사실 프론트에서 실제로 치는 쿼리 패턴(검색어 + 정렬 + 페이지네이션 조합)에서는 여전히 5-6초가 걸리고 있었다.

8단계: EXPLAIN ANALYZE를 처음 찍어보다

이전까지는 "인덱스를 걸었으니 탈 것이다"라고 믿고 있었다. 이번에 처음으로 EXPLAIN (ANALYZE, BUFFERS)를 찍어봤다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM public.reel_growth_rate_economy g
WHERE g.search_text ILIKE '%경제%'
ORDER BY g.likes DESC NULLS LAST
LIMIT 20;

결과:

Index Scan using idx_reel_growth_rate_economy_likes
  Filter: (search_text ~~* '%경제%'::text)
  Rows Removed by Filter: 4408
  Buffers: shared hit=7638

 

GIN trigram 인덱스가 아니라 likes B-tree 인덱스를 타고 있었다. 플래너가 ORDER BY likes DESC LIMIT 20을 보고 "likes 순으로 스캔하면서 ILIKE 조건 맞는 20개만 찾으면 되겠다"고 판단한 것이다. 문제는 "경제"가 전체 4.2만 행 중 1,538행(3.6%)에만 매칭되기 때문에, 20개를 채우려면 거의 전체를 스캔해야 한다는 것.

ILIKE 조건은 인덱스가 아닌 필터(Filter) 로 적용되고 있었다.

9단계: 그러면 GIN 인덱스를 강제로 태우면?

SET enable_seqscan = off;로 강제로 GIN 인덱스를 태워봤다.

Bitmap Index Scan on idx_reel_growth_rate_economy_search_text
  Index Cond: (search_text ~~* '%경제%'::text)
  → 47,368 rows
Bitmap Heap Scan
  Recheck Cond: ...
  Rows Removed by Index Recheck: 40,720

 

GIN 인덱스가 47,368행을 반환했다. MV 전체(42,258행)보다 많다. 인덱스 레벨에서 거의 모든 행이 후보로 잡히고, Heap에서 실제 ILIKE를 다시 검증해서 대부분 버리는 구조. Seq Scan보다 더 느렸다(9초 → 21초).

10단계: pg_trgm의 근본적 한계를 이해하다

원인은 한글 2글자 검색어와 trigram의 궁합이었다.

pg_trgm은 문자열을 3글자(trigram) 단위로 쪼개서 인덱싱한다.

"경제"는 2글자라 생성되는 trigram이 극히 적고, 인덱스 레벨에서의 필터링 효과가 거의 없다.
결과적으로 인덱스를 탄다 해도 후보군이 전체와 비슷하게 나와서 의미가 없었던 것이다.

1탄의 결론이었던 "pg_trgm GIN 인덱스 덕에 1초를 달성했다"는 완전히 틀린 결론이었다.
실제로는 MV 자체의 효과(매번 JOIN → 미리 계산된 테이블 조회)와 다른 B-tree 인덱스들 덕이었지,
trigram 인덱스는 처음부터 아무 역할도 하지 않고 있었다.

11단계: ILIKE를 버리고 단어 배열로 전환

ILIKE 부분 매칭을 포기하고, 공백 기준으로 단어를 쪼개서 배열(text[])로 저장 + GIN array_ops 인덱스로 exact match 검색으로 전환했다.

-- MV에 search_words 컬럼 추가
array(
  select distinct w
  from unnest(string_to_array(lower(caption || ' ' || hashtags), ' ')) as w
  where w <> ''
) as search_words

-- GIN 인덱스
CREATE INDEX idx_search_words
  ON reel_growth_rate_economy USING gin (search_words array_ops);

-- 쿼리: "이 배열에 '경제'라는 원소가 있는가"
WHERE search_words && ARRAY['경제']::text[]

결과: 0.56ms. 기존 대비 약 16,000배 빨라졌다.

12단계: 결과가 절반으로 줄었다?

그런데 검색 결과가 1,200건 → 675건으로 줄었다.

원인은 명확했다. 기존 ILIKE '%경제%'는 부분 매칭이라 "경제뉴스를", "한국경제", "경제적" 등이 모두 잡혔지만,
배열 exact match에서는 정확히 "경제"라는 단어만 매칭된다. 한글은 조사가 붙고 합성어가 많아서 공백 기준 토큰화의 한계가 있다.

서브스트링(N-gram)을 배열에 넣는 방법도 검토했지만, 평균 57개인 배열이 수백 개로 불어나기 때문에 비현실적이었다. 결국 exact match로 가되 프론트에서 검색 가이드를 제공하는 방향으로 결정했다.

교훈

"인덱스를 걸었으니 타겠지"는 위험한 가정이다

인덱스를 만드는 것과 인덱스가 실제로 사용되는 것은 완전히 다른 문제다. EXPLAIN ANALYZE를 찍어보기 전까지는 인덱스가 타는지 알 수 없다. 이번에 pg_trgm GIN 인덱스를 만들어놓고 한 번도 확인하지 않은 채 "덕분에 빨라졌다"고 결론 내렸던 게 대표적인 사례다.

플래너는 나보다 똑똑하지만, 내 의도를 모른다

PostgreSQL 쿼리 플래너는 통계를 기반으로 가장 비용이 낮은 실행 계획을 선택한다. ORDER BY likes DESC LIMIT 20 + ILIKE 조건이 있으면, 플래너는 "likes 인덱스로 정렬 순서를 공짜로 얻고, 필터로 걸러내자"고 판단한다. 매칭 비율이 높으면 이 전략이 효율적이지만, 매칭 비율이 낮으면(이번 경우 3.6%) 사실상 풀스캔이 된다. 플래너의 선택이 항상 최선은 아니다.

pg_trgm은 만능이 아니다 — 특히 한글 짧은 검색어에서

pg_trgm은 3글자 단위로 인덱싱한다. 검색어가 2글자("경제")면 생성되는 trigram이 적어서, 인덱스 레벨에서 후보군을 좁히지 못한다. "ILIKE가 느리면 pg_trgm을 쓰면 된다"는 공식이 항상 성립하는 건 아니다. 데이터의 언어, 검색어 길이, 매칭 비율에 따라 전혀 다른 전략이 필요할 수 있다.

인덱스 전략은 데이터 특성에서 출발해야 한다

이번에 효과적이었던 접근:

1. EXPLAIN ANALYZE로 현재 실행 계획 확인 — 추측이 아닌 사실 기반
2. 인덱스를 강제로 태워서 비교 — SET enable_seqscan = off
3. 인덱스가 비효율적인 이유를 이해 — trigram + 한글 2글자의 구조적 한계
4. 검색 요구사항 재정의 — "부분 매칭이 꼭 필요한가?" → exact match로 충분
5. 데이터 구조 자체를 변경 — ILIKE 문자열 검색 → 단어 배열 + GIN array_ops

"어떤 인덱스를 걸까"보다 먼저, "이 데이터에서 이 검색 패턴이면 어떤 자료구조가 맞는가"를 고민해야 한다.

EXPLAIN ANALYZE는 DB 작업의 console.log다

프론트에서 뭔가 이상하면 콘솔부터 열 듯이, DB 쿼리가 느리면 EXPLAIN ANALYZE부터 찍어야 한다. 이번에 처음 찍어봤는데, 진작 했으면 1탄에서 "pg_trgm 덕에 빨라졌다"는 오진을 하지 않았을 것이다.

참조

• PostgreSQL EXPLAIN 공식 문서
• PostgreSQL GIN 인덱스 — Array 연산자
• pg_trgm — Trigram Matching (한계점 이해용)