현재 Repository 쿼리는 대략 다음 구조다.
where s.is_active = true
and (
s.search_symbol = :symbol
or s.search_symbol like concat(:symbol, '%')
or s.search_name_ko like concat('%', :nameKo, '%')
or s.search_name_en like concat('%', :nameEn, '%')
)
order by
case
when s.search_symbol = :symbol then 0
when s.search_symbol like concat(:symbol, '%') then 1
when s.search_name_ko like concat('%', :nameKo, '%') then 2
when s.search_name_en like concat('%', :nameEn, '%') then 3
else 4
end,
s.country asc,
s.market asc,
s.symbol asc즉, 실제 검색에는 다음 요소가 함께 들어간다.
- exact 검색
- prefix 검색
- contains 검색
- OR 조건
ORDER BY CASELIMIT
이번 실험의 목적은 단순하다. LIKE 검색이 실제로 어떤 실행 계획을 타는지 보고, 필요한 인덱스를 결정한다.
실험 환경
- PostgreSQL: 17.9
- 데이터 수: 300,006건
기존 인덱스는 다음과 같다.
create index ix_stock_master_search_symbol
on stock_master (search_symbol);
create index ix_stock_master_search_name_ko
on stock_master (search_name_ko);
create index ix_stock_master_search_name_en
on stock_master (search_name_en);
개별 조건 Baseline
먼저 실제 API 쿼리를 구성하는 조건을 따로 떼어서 확인했다.
| Query | 유형 | Plan | Rows | Time |
| search_symbol = 'AAPL' | exact | Index Scan | 1 | 0.122 ms |
| search_symbol LIKE 'AAP%' | prefix | Parallel Seq Scan | 1 | 71.175 ms |
| search_name_ko LIKE '%삼성%' | contains | Seq Scan | 120,001 | 123.354 ms |
exact 검색
SELECT *
FROM stock_master
WHERE search_symbol = 'AAPL';Index Scan using ix_stock_master_search_symbol
Index Cond: search_symbol = 'AAPL'
Buffers: shared hit=2 read=2
Execution Time: 0.122 ms정확 검색은 기존 B-Tree Index를 정상적으로 사용했다.
다만 Index Scan이라고 해서 index만 읽는 것은 아니다. PostgreSQL은 index에서 row 위치를 찾은 뒤 실제 row는 heap에서 다시 읽는다.
prefix 검색
SELECT *
FROM stock_master
WHERE search_symbol LIKE 'AAP%';Gather
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on stock_master
Filter: search_symbol LIKE 'AAP%'
Rows Removed by Filter: 100002
Execution Time: 71.175 msprefix 검색이라 B-Tree Index를 사용할 것으로 예상했다. 하지만 실제로는 Parallel Seq Scan이 발생했다.
문자열은 단순 바이트 순서가 아니라 collation 규칙을 따른다. 그래서 PostgreSQL은 일반 B-Tree Index만으로 LIKE 'prefix%' 범위를 안전하게 좁히기 어렵다고 판단할 수 있다. 이때 검토할 수 있는 것이 text_pattern_ops다.
contains 검색
SELECT *
FROM stock_master
WHERE search_name_ko LIKE '%삼성%';Seq Scan on stock_master
Filter: search_name_ko LIKE '%삼성%'
Rows Removed by Filter: 180005
Execution Time: 123.354 msLIKE '%삼성%'는 앞부분이 %로 열려 있다. B-Tree는 정렬된 구조이기 때문에 시작 지점을 잡아야 빠르게 찾을 수 있다. contains 검색은 시작 지점이 없으므로 기존 B-Tree Index와 맞지 않는다.
prefix 검색 개선: text_pattern_ops
prefix 검색을 위해 인덱스를 추가했다. 적용 후 실행 계획이 바뀌었다.
CREATE INDEX ix_stock_master_search_symbol_pattern
ON stock_master (search_symbol text_pattern_ops);Bitmap Heap Scan on stock_master
Heap Blocks: exact=1
-> Bitmap Index Scan on ix_stock_master_search_symbol_pattern
Index Cond: search_symbol >= 'AAP' AND search_symbol < 'AAQ'
Execution Time: 0.047 ms| 항목 | 개선 전 | 개선 후 |
| Plan | Parallel Seq Scan | Bitmap Heap Scan + Bitmap Index Scan |
| Execution Time | 71.175 ms | 0.047 ms |
| Buffers | hit=6511 read=2760 | hit=4 |
PostgreSQL은 LIKE 'AAP%'를 AAP 이상, AAQ 미만의 범위 탐색으로 바꿨다. 즉 text_pattern_ops 적용 후 prefix 검색이 B-Tree 범위 탐색으로 처리되었다.
contains 검색 개선: pg_trgm + GIN Index
contains 검색을 위해 pg_trgm + GIN Index를 적용했다.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ix_stock_master_search_name_ko_trgm
ON stock_master
USING gin (search_name_ko gin_trgm_ops);
CREATE INDEX ix_stock_master_search_name_en_trgm
ON stock_master
USING gin (search_name_en gin_trgm_ops);| 항목 | 개선 전 | 개선 후 |
| Plan | Seq Scan | Seq Scan |
| Rows | 120,001 | 120,001 |
| Execution Time | 123.354 ms | 70.333 ms |
처음에는 %삼성% 검색도 GIN Index를 사용할 것으로 예상했다. 하지만 결과는 여전히 Seq Scan이었다. 이유는 결과 row가 너무 많기 때문이다. 전체 300,006건 중 120,001건이 %삼성% 조건에 걸렸다.
Planner는 "GIN Index를 타더라도 결국 12만 row를 heap에서 다시 읽어야 한다. 그렇다면 차라리 Seq Scan이 더 싸다." 고 판단했다.
즉 pg_trgm + GIN Index를 만든다고 contains 검색이 무조건 index scan으로 바뀌는 것은 아니다.
planner는 statistics를 기반으로 예상 row 수를 계산하고, index 탐색 비용과 heap 접근 비용을 비교해서 실행 계획을 선택한다.
pg_trgm이 실제로 사용되는 경우
검색어를 %레버리지%로 바꿔 확인했다.
SELECT *
FROM stock_master
WHERE search_name_ko LIKE '%레버리지%';Bitmap Heap Scan on stock_master
Recheck Cond: search_name_ko LIKE '%레버리지%'
Heap Blocks: exact=9271
-> Bitmap Index Scan on ix_stock_master_search_name_ko_trgm
Index Cond: search_name_ko LIKE '%레버리지%'
Execution Time: 53.795 ms| Query | Plan | Rows | Time |
| LIKE '%삼성%' | Seq Scan | 120,001 | 70.333 ms |
| LIKE '%레버리지%' | Bitmap Heap Scan + Bitmap Index Scan | 60,000 | 53.795 |
같은 GIN Index가 있어도 planner의 선택은 달라질 수 있다. 핵심은 index 존재 여부가 아니라 검색 조건의 선택도다.
실제 Repository 쿼리 기준 측정
개별 조건 실험은 부품 테스트에 가깝다. 실제 API 쿼리는 여러 조건이 OR로 묶이고, 결과를 다시 ORDER BY CASE로 정렬한다.
Case 1. keyword = '삼성'
Limit
-> Gather Merge
-> Sort
Sort Method: top-N heapsort
-> Parallel Seq Scan on stock_master
Filter: is_active AND (...)
Rows Removed by Filter: 60002
Execution Time: 125.647 ms| 항목 | 값 |
| Top Plan | Limit → Gather Merge → Sort → Parallel Seq Scan |
| Index | 없음 |
| OR 처리 | Filter에서 직접 평가 |
| Sort | top-N heapsort |
| Rows | 40,000 × 3 |
| Execution Time | 125.647 ms |
삼성은 결과 row가 너무 많았다. planner는 OR 조건을 BitmapOr로 조합하지 않고 Parallel Seq Scan을 선택했다. LIMIT 10이 있어도 ORDER BY CASE 때문에 정렬 단계는 필요했다.
Case 2. keyword = '레버리지'
Limit
-> Gather Merge
-> Sort
Sort Method: top-N heapsort
-> Parallel Bitmap Heap Scan on stock_master
-> BitmapOr
-> Bitmap Index Scan on ix_stock_master_search_symbol_pattern
-> Bitmap Index Scan on ix_stock_master_search_name_ko_trgm
-> Bitmap Index Scan on ix_stock_master_search_name_en_trgm
Execution Time: 76.863 ms| 항목 | 값 |
| Top Plan | Limit → Gather Merge → Sort → Parallel Bitmap Heap Scan |
| OR 처리 | BitmapOr |
| Index | symbol pattern, name_ko trgm, name_en trgm |
| Rows | 20,000 × 3 |
| Execution Time | 76.863 ms |
레버리지 케이스에서는 planner가 OR 조건을 BitmapOr로 조합했다. 특히 search_name_ko LIKE '%레버리지%' 조건에서 ix_stock_master_search_name_ko_trgm 인덱스를 사용했다.
Case 3. keyword = 'AAPL'
Limit
-> Sort
Sort Method: quicksort
-> Bitmap Heap Scan on stock_master
Heap Blocks: exact=1
-> BitmapOr
-> Bitmap Index Scan on ix_stock_master_search_symbol_pattern
-> Bitmap Index Scan on ix_stock_master_search_name_ko_trgm
-> Bitmap Index Scan on ix_stock_master_search_name_en_trgm
Execution Time: 2.566 ms| 항목 | 값 |
| Top Plan | Limit → Sort → Bitmap Heap Scan |
| OR 처리 | BitmapOr |
| Index | symbol pattern, name_ko trgm, name_en trgm |
| Rows | 1 |
| Heap Blocks | exact=1 |
| Execution Time | 2.566 ms |
AAPL은 선택도가 높았다. planner는 OR 조건을 BitmapOr로 조합했고, 실제 heap 접근도 1 block 수준으로 끝났다. 정렬은 발생했지만 대상 row가 1건뿐이라 비용은 거의 없었다.
실제 API 쿼리 결과 정리
| keyword | Top Plan | OR 처리 | Rows | Time |
| 삼성 | Parallel Seq Scan | Filter에서 직접 평가 | 40,000 × 3 | 125.647 ms |
| 레버리지 | Parallel Bitmap Heap Scan | BitmapOr | 20,000 × 3 | 76.863 ms |
| AAPL | Bitmap Heap Scan | BitmapOr | 1 | 2.566 ms |
검색어의 선택도가 좋아질수록 planner는 인덱스를 더 적극적으로 사용한다. 반대로 결과 row가 너무 많은 검색어는 인덱스가 있어도 Seq Scan으로 갈 수 있다.
그리고 ORDER BY CASE 때문에 Sort는 계속 발생한다. LIMIT 10은 정렬 비용을 줄여주지만, 후보 row를 찾고 정렬하는 단계 자체를 없애지는 못한다.
최종 적용할 인덱스
이번 실험 기준으로 유지할 인덱스는 다음과 같다.
-- exact 검색용
CREATE INDEX ix_stock_master_search_symbol
ON stock_master (search_symbol);
-- prefix 검색용
CREATE INDEX ix_stock_master_search_symbol_pattern
ON stock_master (search_symbol text_pattern_ops);
-- contains 검색용
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ix_stock_master_search_name_ko_trgm
ON stock_master
USING gin (search_name_ko gin_trgm_ops);
CREATE INDEX ix_stock_master_search_name_en_trgm
ON stock_master
USING gin (search_name_en gin_trgm_ops);
다만 인덱스를 만든다고 항상 사용되는 것은 아니다. planner는 검색어의 선택도, 예상 row 수, heap 접근 비용을 함께 계산한다.
다음 단계에서는 이 실제 API 쿼리를 기준으로 k6 부하 테스트를 수행한다.
- p95 latency
- p99 latency
- DB CPU 사용률
- DB shared hit/read 변화
- 검색어별 응답 시간 차이
- 동일 검색어 반복 시 Redis Cache 적용 효과
'종목 검색 api' 카테고리의 다른 글
| [종목 검색 API] 5. Redis Sentinel과 CircuitBreaker 장애 대응 실험 (0) | 2026.05.26 |
|---|---|
| [종목 검색 API] 4. Redis 장애 격리 실험: Timeout과 CircuitBreaker로 충분했을까? (0) | 2026.05.23 |
| [종목 검색 API] 3. API 부하 테스트: debounce, Redis Cache, Redis 장애 (0) | 2026.05.22 |
| [종목 검색 API] 1. 자동완성 검색 API는 어떻게 서버를 터뜨리는가 (0) | 2026.05.20 |