제가 단일 쿼리 성능을 개선하면서 놓친 부분이 있었습니다.
검색 키워드가 오타로 없거나, 혹은 여러개의 키워드의 조합일때 검색 성능입니다.
\set ON_ERROR_STOP on
\echo ''
\echo '=== E1: keyword typo (1) — Nikke ==='
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM products
WHERE name ILIKE '%Nikke%' OR brand ILIKE '%Nikke%'
ORDER BY created_at DESC, id DESC
OFFSET 0
LIMIT 12;
\echo ''
\echo '=== E2: keyword combination (1) — keyword OR + category + gender + price band ==='
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM products
WHERE (name ILIKE '%Nike%' OR brand ILIKE '%Nike%')
AND category = 'SHOES'
AND gender = 'MEN'
AND price_amount >= 40000
AND price_amount <= 180000
ORDER BY created_at DESC, id DESC
OFFSET 0
LIMIT 12;
| E1 | 키워드 오타 — Nikke (ILIKE '%Nikke%', name) |
| E2 | 키워드 조합 — Nike + Hoodie |
=== E1: keyword typo (1) ??Nikke ===
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..10007.04 rows=12 width=16) (actual time=46692.687..46692.688 rows=0 loops=1)
Buffers: shared hit=10654 read=255543
-> Index Scan using idx_products_created_at_id on products (cost=0.43..831382.94 rows=997 width=16) (actual time=46692.686..46692.687 rows=0 loops=1)
Filter: ((name)::text ~~* '%Nikke%'::text)
Rows Removed by Filter: 10000500
Buffers: shared hit=10654 read=255543
Planning:
Buffers: shared hit=189 read=8
Planning Time: 2.835 ms
Execution Time: 46692.725 ms
(10 rows)
=== E2: keyword combination (1) ??one search box, multiple words (same ILIKE as API keyword) ===
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..10007.04 rows=12 width=16) (actual time=5660.368..5660.369 rows=0 loops=1)
Buffers: shared hit=10654 read=255543
-> Index Scan using idx_products_created_at_id on products (cost=0.43..831382.94 rows=997 width=16) (actual time=5660.366..5660.367 rows=0 loops=1)
Filter: ((name)::text ~~* '%Nike Hoodie%'::text)
Rows Removed by Filter: 10000500
Buffers: shared hit=10654 read=255543
Planning Time: 0.166 ms
Execution Time: 5660.437 ms
(8 rows)
키워드의 조합이 있거나 오타가 있는경우, 데이터에서 검색결과를 보여주지 못할 뿐더러, 그 과정이 매우 느립니다.
특히 캐싱이 되기 전 첫 쿼리 콜드 스타트의 경우, 46초까지 걸립니다.
그 이후에도 5초이상 걸리고 있습니다. 매칭이 되지 않아서 끝까지 스캔한 형태에 가깝습니다.
저는 postgreSQL의 pg_trgm을 적용해서 이를 해결하겠습니다.
pg_trgm은 문자열을 3글자 단위(trigram)로 쪼개서 분석하는 기능입니다.
V6_product_name_trgm.sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_lower_trgm
ON products
USING gin (lower(name) gin_trgm_ops);
전부 소문자로 변환해서 GIN Index를 만듭니다.
GIN Index는 Generalized Inverted Index, 즉 역색인 인덱스입니다.
name 컬럼의 문장들을 3글자 조각으로 쪼개고, 조각을 가진 행들을 리스트로 묶어서 역색인 구조를 만듭니다.
따라서 사용자가 문장을 단어 조각의 조합으로 검색하는것이 최적화됩니다.
천만행 데이터에 대한 토큰화가 매우 무거운 작업일 수도 있다고 생각했는데, 우려한 것보다 매우 빠르게 생성되었습니다.
Repository 코드 수정
private BooleanExpression keywordContains(String keyword) {
if (!hasText(keyword)) {
return null;
}
String[] tokens = keyword.trim().toLowerCase(Locale.ROOT).split("\\s+");
BooleanExpression combined = null;
for (String token : tokens) {
if (token.length() < KEYWORD_MIN_LENGTH) {
continue;
}
BooleanExpression tokenMatch = Expressions.booleanTemplate(
"lower({0}) like {1}",
QProduct.product.name,
"%" + token + "%"
);
combined = combined == null ? tokenMatch : combined.and(tokenMatch);
}
return combined;
}
사용자가 입력한 keyword를 공백을 기준으로 2글자 이상의 단어만 토큰단위로 쪼갭니다.
그리고 모든 토큰을 포함하는 문장을 찾습니다.
=== E2: keyword combination (1) ??multi-word AND (name is "Nike MEN Hoodie ...", not contiguous "nike hoodie") ===
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1294.23..1294.26 rows=12 width=16) (actual time=120.777..120.779 rows=12 loops=1)
Buffers: shared hit=1431 read=15358
-> Sort (cost=1294.23..1294.29 rows=26 width=16) (actual time=120.775..120.776 rows=12 loops=1)
Sort Key: created_at DESC, id DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=1431 read=15358
-> Bitmap Heap Scan on products (cost=1189.96..1293.63 rows=26 width=16) (actual time=59.809..117.634 rows=16498 loops=1)
Recheck Cond: ((lower((name)::text) ~~ '%nike%'::text) AND (lower((name)::text) ~~ '%hoodie%'::text))
Heap Blocks: exact=15889
Buffers: shared hit=1425 read=15358
-> Bitmap Index Scan on idx_products_name_lower_trgm (cost=0.00..1189.96 rows=26 width=0) (actual time=57.932..57.932 rows=16498 loops=1)
Index Cond: ((lower((name)::text) ~~ '%nike%'::text) AND (lower((name)::text) ~~ '%hoodie%'::text))
Buffers: shared hit=441 read=453
Planning:
Buffers: shared read=1
Planning Time: 0.102 ms
Execution Time: 121.280 ms
(17 rows)
키워드 조합 검색 속도가 5000ms -> 120ms로 단축됐습니다.
하지만 여전히 키워드의 오타는 검색이 안됩니다.
예를들어 nike -> nikke라고 오타 시, 결과가 전혀 나오지 않고 속도도 느립니다.
이를 위해 유사도 검색 기능을 추가합니다.
Repository 코드 수정
private BooleanExpression keywordContains(String keyword) {
if (!hasText(keyword)) {
return null;
}
String[] tokens = keyword.trim().toLowerCase(Locale.ROOT).split("\\s+");
BooleanExpression combined = null;
for (String token : tokens) {
if (token.length() < KEYWORD_MIN_LENGTH) {
continue;
}
BooleanExpression tokenMatch = keywordTokenMatch(token);
combined = combined == null ? tokenMatch : combined.and(tokenMatch);
}
return combined;
}
private BooleanExpression keywordTokenMatch(String token) {
return Expressions.booleanTemplate(
"(lower({0}) like {1} or word_similarity({2}, lower({0})) > {3})",
QProduct.product.name,
"%" + token + "%",
token,
WORD_SIMILARITY_THRESHOLD
);
}
word_similarity는 postgre의 빌트인 SQL 함수입니다.
pg_trgm으로 쪼개진 토큰 인덱스에서 사용자가 입력한 단어와의 유사도를 빠르게 계산합니다.
WORD_SIMILARITY_THRESHOLD (유사도 임계값) = 0.35로 지정했습니다.
nikke jemper로 검색하니 nike jumper 자료들이 검색되는 것을 확인할 수 있었습니다.
=== E1: keyword typo (1) ??Nikke (substring OR word_similarity > 0.35) ===
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..3.78 rows=12 width=16) (actual time=0.247..0.786 rows=12 loops=1)
Buffers: shared hit=8
-> Index Scan using idx_products_created_at_id on products (cost=0.43..931387.62 rows=3344167 width=16) (actual time=0.246..0.783 rows=12 loops=1)
Filter: ((lower((name)::text) ~~ '%nikke%'::text) OR (word_similarity('nikke'::text, lower((name)::text)) > '0.35'::double precision))
Rows Removed by Filter: 193
Buffers: shared hit=8
Planning:
Buffers: shared hit=220 read=7
Planning Time: 5.664 ms
Execution Time: 0.836 ms
(10 rows)
=== E2: keyword combination (1) ??multi-word AND (name is "Nike MEN Hoodie ...", not contiguous "nike hoodie") ===
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..11.66 rows=12 width=16) (actual time=0.820..26.056 rows=12 loops=1)
Buffers: shared hit=204
-> Index Scan using idx_products_created_at_id on products (cost=0.43..1056393.87 rows=1129668 width=16) (actual time=0.820..26.052 rows=12 loops=1)
Filter: (((lower((name)::text) ~~ '%nike%'::text) OR (word_similarity('nike'::text, lower((name)::text)) > '0.35'::double precision)) AND ((lower((name)::text) ~~ '%hoodie%'::text) OR (word_similarity('hoodie'::text, lower((name)::text)) > '0.35'::double precision)))
Rows Removed by Filter: 7471
Buffers: shared hit=204
Planning:
Buffers: shared hit=2
Planning Time: 0.071 ms
Execution Time: 26.094 ms
(10 rows)
개선 전 약 5000ms
개선 후
오타 검색 - 0.8ms
키워드 조합 검색시 - 26ms
'백엔드 엔지니어링 일지' 카테고리의 다른 글
| 마켓 백엔드 엔진 12 : ElasticSearch - 1 (0) | 2026.05.26 |
|---|---|
| 마켓 백엔드 엔진 11 : k6 검색 로드 테스트 - 1 (0) | 2026.05.22 |
| 마켓 백엔드 엔진 9 : k6 부하 테스트 - Prometheus, Grafana 연동 (0) | 2026.05.13 |
| 마켓 백엔드 엔진 8 : 검색 쿼리 튜닝 (EXPLAIN ANALYZE, Index, Page, Slice) (0) | 2026.05.03 |
| 마켓 백엔드 엔진 7 : Prometheus + Grafana 모니터링 (0) | 2026.04.30 |
