GIN + trigram 실습 노트
1. 버전 확인
SELECT version();결과:
PostgreSQL 18.1 on x86_64-windows, compiled by msvc-19.44.35221, 64-bit2. 확장 설치 여부 확인
SELECT * FROM pg_available_extensions WHERE name IN ('pg_trgm', 'pg_bigm');결과:
"pg_trgm" "1.6" [null] "text similarity measurement and index searching based on trigrams"pg_trgm은 있는데 설치가 안 된 상황임. 설치해줌.
CREATE EXTENSION pg_trgm;결과:
Query returned successfully in 819 msec.3. show_trgm으로 토큰 확인
SELECT show_trgm('hello');
SELECT show_trgm('공원');결과:
hello -> {" h"," he",ell,hel,llo,"lo "}
공원 -> {0xbd6381,0xdce49d,0xf4d1c9}4. 로케일 / 인코딩 확인
SELECT datname, datcollate, datctype FROM pg_database WHERE datname = current_database();결과:
"trgm_lab" "Korean_Korea.949" "Korean_Korea.949"한글 로케일임. 로케일이란 정렬/문자 판단의 규칙이라고 볼 수 있음. 해당 글자를 기반으로 판단 규칙을 정하는 거라고 보면 됨.
SHOW server_encoding;결과:
UTF8인코딩은 그 글자를 컴퓨터 메모리/디스크에 실제로 몇 바이트, 어떤 비트 패턴으로 저장할 것인가임. 즉, 인코딩으로 저장되어 있는 걸 로케일의 규칙으로 어떻게 다룰지 보는 거임.
5. 실험 테이블 준비
이제 정말 테스트할 차례임.
WHERE name LIKE '%공원%'을 실행했을 때, PostgreSQL이 진짜로 인덱스를 이용해서 그 10개만 콕 집어낼 수 있는가?
CREATE TABLE route(
route_id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
location VARCHAR(255),
distance_km NUMERIC(5,2),
description TEXT
);결과:
CREATE TABLE
Query returned successfully in 204 msec.INSERT INTO route(name)
SELECT
CASE WHEN i % 5000 = 0 THEN '서울' || i || '번 공원 코스'
ELSE '테스트코스' || i || '번' END
FROM generate_series(1, 50000) AS i;결과:
INSERT 0 50000
Query returned successfully in 421 msec.6. 인덱스 없는 상태에서 먼저 확인
EXPLAIN ANALYZE
SELECT * FROM route WHERE name LIKE '%공원%';결과:
"Seq Scan on route (cost=0.00..993.00 rows=5 width=587) (actual time=0.734..6.919 rows=10.00 loops=1)"
" Filter: ((name)::text ~~ '%공원%'::text)"
" Rows Removed by Filter: 49990"
" Buffers: shared hit=368"
"Planning:"
" Buffers: shared hit=26"
"Planning Time: 4.045 ms"
"Execution Time: 6.950 ms"풀스캔 났음. 예상대로임.
7. GIN trigram 인덱스 생성 후 확인
인덱스 만듦 (idx_route_name_trgm이라는 이름으로 인덱스 생성, GIN 방식으로 name을 gin_trgm_ops 규칙으로 쪼개서 넣겠다는 거임).
CREATE INDEX idx_route_name_trgm
ON route USING gin (name gin_trgm_ops);결과:
CREATE INDEX
Query returned successfully in 634 msec.다시 실행계획 확인:
EXPLAIN ANALYZE
SELECT * FROM route WHERE name LIKE '%공원%';결과:
"Seq Scan on route (cost=0.00..993.00 rows=5 width=587) (actual time=0.739..5.960 rows=10.00 loops=1)"
" Filter: ((name)::text ~~ '%공원%'::text)"
" Rows Removed by Filter: 49990"
" Buffers: shared hit=368"
"Planning:"
" Buffers: shared hit=25 dirtied=1"
"Planning Time: 4.475 ms"
"Execution Time: 6.022 ms"엥? 인덱스를 만들었는데도 여전히 Seq Scan임. 옵티마이저가 왜 인덱스 안 타는 게 빠르다고 판단했는지 가설을 세워봄.
- 데이터가 짧아서? (가장 유력)
- 한국어라서?
8. enable_seqscan 강제로 꺼서 확인
무조건 인덱스를 쓰도록 강제해서 인덱스가 실제로 어떻게 동작하는지 봄.
SET enable_seqscan = OFF;
EXPLAIN ANALYZE
SELECT * FROM route WHERE name LIKE '%공원%';
SET enable_seqscan = ON;결과:
"Bitmap Heap Scan on route (cost=11065.49..11083.80 rows=5 width=587) (actual time=11.270..19.633 rows=10.00 loops=1)"
" Recheck Cond: ((name)::text ~~ '%공원%'::text)"
" Rows Removed by Index Recheck: 49990"
" Heap Blocks: exact=368"
" Buffers: shared hit=517"
" -> Bitmap Index Scan on idx_route_name_trgm (cost=0.00..11065.49 rows=5 width=0) (actual time=10.606..10.606 rows=50000.00 loops=1)"
" Index Cond: ((name)::text ~~ '%공원%'::text)"
" Index Searches: 1"
" Buffers: shared hit=149"
"Planning:"
" Buffers: shared hit=1"
"Planning Time: 0.231 ms"
"Execution Time: 19.677 ms"성능이 안 좋음. Bitmap Index Scan on idx_route_name_trgm에서 후보로 50000행을 다 올려버림 (rows=50000.00). 그 뒤 Rows Removed by Index Recheck: 49990으로 제거함. 즉, 5만 건 중에 아무것도 걸러내지 못하고 결국 다 후보로 올려버린 거임. 옵티마이저가 인덱스 대신 Seq Scan을 고른 이유가 납득됨 — 이렇게 쓸 거면 인덱스 탈 이유가 없음.
원래는 "데이터가 짧아서(공원이면 세 개밖에 토큰이 안 만들어져서)"라고 생각했는데, 이건 틀린 이유임. 뒤에서 확인하지만 진짜 이유는 개수가 적어서가 아니라, 그 세 개 토큰(
" 공"," 공원","공원 ")이 전부 공백이 낀 조각이라서임.
9. 한국어라서 그런가? 영어로 검증
INSERT INTO route (name)
SELECT 'ab-item-' || i FROM generate_series(1,5) AS i;EXPLAIN ANALYZE
SELECT * FROM route WHERE name LIKE '%ab%';결과:
"Bitmap Heap Scan on route (cost=11147.88..11166.20 rows=5 width=587) (actual time=22.237..22.240 rows=5.00 loops=1)"
" Recheck Cond: ((name)::text ~~ '%ab%'::text)"
" Rows Removed by Index Recheck: 50000"
" Heap Blocks: exact=368"
" Buffers: shared hit=518"
" -> Bitmap Index Scan on idx_route_name_trgm (cost=0.00..11147.88 rows=5 width=0) (actual time=11.344..11.345 rows=50005.00 loops=1)"
" Index Cond: ((name)::text ~~ '%ab%'::text)"
" Index Searches: 1"
" Buffers: shared hit=150"
"Planning:"
" Buffers: shared hit=1"
"Planning Time: 0.201 ms"
"Execution Time: 22.277 ms"언어 문제 아님. 영어도 똑같이 다 올려버림 (50005개).
10. 검색어를 길게 하면 어떻게 되나
UPDATE route SET name = '서울대공원코스' WHERE route_id = 1;EXPLAIN ANALYZE
SELECT * FROM route WHERE name LIKE '%대공원코%';결과:
"Bitmap Heap Scan on route (cost=25.77..44.08 rows=5 width=587) (actual time=0.099..0.100 rows=1.00 loops=1)"
" Recheck Cond: ((name)::text ~~ '%대공원코%'::text)"
" Heap Blocks: exact=1"
" Buffers: shared hit=7"
" -> Bitmap Index Scan on idx_route_name_trgm (cost=0.00..25.76 rows=5 width=0) (actual time=0.044..0.044 rows=1.00 loops=1)"
" Index Cond: ((name)::text ~~ '%대공원코%'::text)"
" Index Searches: 1"
" Buffers: shared hit=6"
"Planning:"
" Buffers: shared hit=1"
"Planning Time: 0.231 ms"
"Execution Time: 0.155 ms"후보가 1개. 정확히 찾아냄. 속도도 매우 빠름. 한글이 안 먹힌 것도 아니고 정상 작동함.
"대공원코" -> " 대" / " 대공" / "대공원" / "공원코" / "원코 " 이러면 5개 토큰이고, 아까 "공원"은 3개 토큰이었음.
11. 왜 그런지 파보기 — 공백 패딩 가설
공원은 공백이 계속 껴있음. 그래서 필터링이 다 안 되는 거 아닐까 싶었음. 만약 "하늘 공원 가자"처럼 진짜 공백이 낀 데이터가 있으면 잡히지 않을까 하는 가설을 세워봄.
근데 이것도 좀 이상한 게, 옵티마이저가 데이터에 공백이 있는지 없는지 미리 알고 구분할 수는 없음. 공백이 올 수도 있고 문자열 맨 처음일 수도 있으니까. 그래서 " 공원" 조각 자체가 아예 필수 조건에서 빠져버릴 가능성이 높다고 예상함.
테스트해봄:
INSERT INTO route (name) VALUES ('하늘 공원 가자');
INSERT INTO route (name) VALUES ('테스트공원목장');EXPLAIN ANALYZE
SELECT * FROM route WHERE name LIKE '%공원%';결과:
"Bitmap Heap Scan on route (cost=11147.88..11166.20 rows=5 width=587) (actual time=11.493..18.817 rows=13.00 loops=1)"
" Recheck Cond: ((name)::text ~~ '%공원%'::text)"
" Rows Removed by Index Recheck: 49994"
" Heap Blocks: exact=368"
" Buffers: shared hit=518 dirtied=1"
" -> Bitmap Index Scan on idx_route_name_trgm (cost=0.00..11147.88 rows=5 width=0) (actual time=10.776..10.776 rows=50008.00 loops=1)"
" Index Cond: ((name)::text ~~ '%공원%'::text)"
" Index Searches: 1"
" Buffers: shared hit=150"
"Planning:"
" Buffers: shared hit=1"
"Planning Time: 0.271 ms"
"Execution Time: 18.864 ms"가설대로 진짜 " 공원" 형태의 데이터를 넣었는데도 50008행을 다 후보로 올려버림. 가설 기각임.
즉 데이터 안에 그 트라이그램이 "존재하냐 안 하냐"가 아니라, PostgreSQL이 검색조건(LIKE '%공원%')을 trigram 필터로 바꾸는 시점에 이미 결정되는 거임. 2글자로는 순수 토큰이 안 나오는 듯함 (정확히는 "2글자냐 아니냐"가 아니라 "패딩 없는 순수 토큰이 하나라도 나오냐"가 기준임 — "공원"은 2글자라 애초에 순수 토큰이 안 나오는 케이스였던 거).
12. 정리 — 왜 이런 현상이 생기는가
1단계 (검색어만 보고 판단, 데이터 안 봄)
검색어 '공원'을 trigram으로 쪼갬 → " 공", " 공원", "공원 " 나옴 → 근데 이 셋 다 공백(위치 마커)이 껴 있어서, "이 조각이 데이터 안에 무조건 있어야 한다"고 확신할 수 있는 게 하나도 없음 → "이 검색은 인덱스로 확실하게 걸러낼 키가 없다"고 결론 남.
2단계 (실제 인덱스/데이터 조회)
1단계에서 "확실한 필터 키가 없다"는 결론이 났으니, 인덱스는 그냥 "다 후보로 줄게, 대신 진짜 맞는지는 네가(PostgreSQL 실행기가) 하나씩 다시 확인해"라는 식으로 동작함 → 그래서 rows=50008처럼 전체가 다 나온 거임.
핵심은 1단계가 데이터를 전혀 안 보고, 오직 '공원'이라는 검색어 텍스트 하나만 가지고 이루어진다는 거임. 그러니까 "하늘 공원 가자"처럼 실제로 딱 맞는 공백 패턴을 가진 데이터가 테이블 안에 들어있어도, 1단계에서 이미 "이 검색어로는 확실한 필터를 못 만든다"고 결론이 나버렸기 때문에, 그 데이터의 존재 여부가 결과에 영향을 줄 수 없는 거임.
