19. ์ ๋ฌธ ๊ฒ์(Full-Text Search)
19. ์ ๋ฌธ ๊ฒ์(Full-Text Search)¶
ํ์ต ๋ชฉํ¶
- PostgreSQL ์ ๋ฌธ ๊ฒ์(Full-Text Search) ์ํคํ ์ฒ ์ดํดํ๊ธฐ
- tsvector์ tsquery ๋ฐ์ดํฐ ํ์ ํจ๊ณผ์ ์ผ๋ก ์ฌ์ฉํ๊ธฐ
- ๋น ๋ฅธ ํ ์คํธ ๊ฒ์์ ์ํ GIN ์ธ๋ฑ์ค ์์ฑํ๊ธฐ
- ๊ฐ์ค์น๋ฅผ ํ์ฉํ ๋ญํน ๊ฒ์ ๊ตฌํํ๊ธฐ
- ๋ค๊ตญ์ด ๊ฒ์ ์ง์ ์ค์ ํ๊ธฐ
- ํผ์ง ๋งค์นญ(Fuzzy Matching)์ ์ํ pg_trgm ํ์ฉํ๊ธฐ
๋ชฉ์ฐจ¶
- ์ ๋ฌธ ๊ฒ์ ๊ฐ์
- tsvector์ tsquery
- ๊ฒ์ ๊ตฌ์ฑ
- GIN๊ณผ GiST ์ธ๋ฑ์ค
- ๋ญํน๊ณผ ๊ฐ์ค์น
- ๊ณ ๊ธ ๊ฒ์ ๊ธฐ๋ฒ
- ํผ์ง ๋งค์นญ์ ์ํ pg_trgm
- ์ฐ์ต ๋ฌธ์
1. ์ ๋ฌธ ๊ฒ์ ๊ฐ์¶
1.1 ์ ์ ๋ฌธ ๊ฒ์์ธ๊ฐ?¶
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
โ LIKE vs ์ ๋ฌธ ๊ฒ์(Full-Text Search) โ
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโค
โ โ
โ LIKE / ILIKE: โ
โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ โ
โ โ SELECT * FROM articles โ โ
โ โ WHERE body ILIKE '%database%'; โ โ
โ โ โ โ
โ โ โ ์ธ๋ฑ์ค ์ฌ์ฉ ๋ถ๊ฐ (์์ฐจ ์ค์บ) โ โ
โ โ โ ์ธ์ด ์ธ์ ๊ธฐ๋ฅ ์์ โ โ
โ โ โ ๋ญํน ๊ธฐ๋ฅ ์์ โ โ
โ โ โ "databases"๊ฐ "database"์ ๋งค์น ์ ๋จ โ โ
โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ โ
โ โ
โ ์ ๋ฌธ ๊ฒ์(Full-Text Search): โ
โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ โ
โ โ SELECT * FROM articles โ โ
โ โ WHERE to_tsvector(body) โ โ
โ โ @@ to_tsquery('database'); โ โ
โ โ โ โ
โ โ โ GIN ์ธ๋ฑ์ค ์ง์ (๋น ๋ฆ) โ โ
โ โ โ ํํ์ ๋ถ์ (databases โ database) โ โ
โ โ โ ๊ด๋ จ์ฑ์ ๋ฐ๋ฅธ ๋ญํน โ โ
โ โ โ ๋ถ๋ฆฌ์ธ ์ฐ์ฐ์ (AND, OR, NOT) โ โ
โ โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ โ
โ โ
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
1.2 ์ํคํ ์ฒ ๊ฐ์¶
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
โ ์ ๋ฌธ ๊ฒ์ ํ์ดํ๋ผ์ธ โ
โ โ
โ ๋ฌธ์ ํ
์คํธ โ
โ โ โ
โ โผ โ
โ โโโโโโโโโโโโโโโโโโโ โ
โ โ Parser โโโโถ ๋จ์ด๋ก ํ ํฐํ โ
โ โโโโโโโโโโโโโโโโโโโ โ
โ โ โ
โ โผ โ
โ โโโโโโโโโโโโโโโโโโโ โ
โ โ Dictionaries โโโโถ ์ ๊ทํ (๋ถ์ฉ์ด, ์ด๊ฐ) โ
โ โโโโโโโโโโโโโโโโโโโ โ
โ โ โ
โ โผ โ
โ โโโโโโโโโโโโโโโโโโโ โ
โ โ tsvector โโโโถ ์ ๋ ฌ๋ ๋ ์ฌ ๋ฆฌ์คํธ + ์์น โ
โ โโโโโโโโโโโโโโโโโโโ โ
โ โ โ
โ โผ โ
โ โโโโโโโโโโโโโโโโโโโ โ
โ โ GIN Index โโโโถ ๋ ์ฌ๋ณ ๋น ๋ฅธ ๊ฒ์ โ
โ โโโโโโโโโโโโโโโโโโโ โ
โ โ
โ ์ฟผ๋ฆฌ ํ
์คํธ โโโถ tsquery โโโถ tsvector์ ๋งค์น โ
โ โ
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
2. tsvector์ tsquery¶
2.1 tsvector โ ๋ฌธ์ ํํ¶
-- ๊ธฐ๋ณธ ๋ณํ
SELECT to_tsvector('english', 'The quick brown foxes jumped over the lazy dogs');
-- ๊ฒฐ๊ณผ: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
-- ์ฃผ์: ๋ถ์ฉ์ด ์ ๊ฑฐ๋จ ("the", "over"), ๋จ์ด ์ด๊ฐ ์ถ์ถ๋จ ("foxes" โ "fox")
-- tsvector๋ ๋ ์ฌ์ ์์น์ ํจ๊ป ์ ์ฅ
SELECT to_tsvector('english', 'A fat cat sat on a mat. A fat cat ate a fat rat.');
-- ๊ฒฐ๊ณผ: 'ate':10 'cat':3,8 'fat':2,7,11 'mat':6 'rat':12 'sat':4
2.2 tsquery โ ์ฟผ๋ฆฌ ํํ¶
-- ๊ธฐ๋ณธ ์ฟผ๋ฆฌ
SELECT to_tsquery('english', 'cats & dogs');
-- ๊ฒฐ๊ณผ: 'cat' & 'dog'
-- ๋ถ๋ฆฌ์ธ ์ฐ์ฐ์
SELECT to_tsquery('english', 'cat | dog'); -- OR
SELECT to_tsquery('english', 'cat & !dog'); -- AND NOT
SELECT to_tsquery('english', 'cat <-> dog'); -- FOLLOWED BY (๊ตฌ๋ฌธ)
SELECT to_tsquery('english', 'cat <2> dog'); -- 2๋จ์ด ์ด๋ด
-- plainto_tsquery: ๋ ๊ฐ๋จํ ๊ตฌ๋ฌธ (์๋ฌต์ AND)
SELECT plainto_tsquery('english', 'fat cats');
-- ๊ฒฐ๊ณผ: 'fat' & 'cat'
-- phraseto_tsquery: ์ ํํ ๊ตฌ๋ฌธ ๋งค์นญ
SELECT phraseto_tsquery('english', 'fat cats');
-- ๊ฒฐ๊ณผ: 'fat' <-> 'cat'
-- websearch_to_tsquery: ์น ์คํ์ผ ๊ตฌ๋ฌธ (PostgreSQL 11+)
SELECT websearch_to_tsquery('english', '"fat cats" -dogs');
-- ๊ฒฐ๊ณผ: 'fat' <-> 'cat' & !'dog'
SELECT websearch_to_tsquery('english', 'cats or dogs');
-- ๊ฒฐ๊ณผ: 'cat' | 'dog'
2.3 ๋งค์น ์ฐ์ฐ์(@@)¶
-- tsvector @@ tsquery
SELECT to_tsvector('english', 'The fat cats sat on the mat')
@@ to_tsquery('english', 'cat & mat');
-- ๊ฒฐ๊ณผ: true
SELECT to_tsvector('english', 'The fat cats sat on the mat')
@@ to_tsquery('english', 'cat & dog');
-- ๊ฒฐ๊ณผ: false
-- ์ค์ฉ์ ์ธ ์์
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO articles (title, body) VALUES
('PostgreSQL Full-Text Search', 'PostgreSQL provides powerful full-text search capabilities using tsvector and tsquery.'),
('Database Indexing Guide', 'Indexes are crucial for database performance. B-tree, GIN, and GiST are common index types.'),
('Introduction to SQL', 'SQL is a standard language for managing relational databases. SELECT, INSERT, UPDATE, DELETE.'),
('Advanced Query Optimization', 'Query optimization involves analyzing execution plans and choosing efficient access paths.');
-- ๋ฌธ์ ๊ฒ์
SELECT title, ts_rank(to_tsvector('english', body), query) AS rank
FROM articles, to_tsquery('english', 'database & index') AS query
WHERE to_tsvector('english', body) @@ query
ORDER BY rank DESC;
2.4 ์ ์ฅ๋ tsvector ์ปฌ๋ผ¶
-- ์ฑ๋ฅ ํฅ์์ ์ํ ์์ฑ๋ tsvector ์ปฌ๋ผ ์ถ๊ฐ
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED;
-- ์ด์ ์ฟผ๋ฆฌ๋ ๋ฏธ๋ฆฌ ๊ณ์ฐ๋ ์ปฌ๋ผ์ ์ฌ์ฉ
SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'database');
3. ๊ฒ์ ๊ตฌ์ฑ¶
3.1 ํ ์คํธ ๊ฒ์ ๊ตฌ์ฑ¶
-- ์ฌ์ฉ ๊ฐ๋ฅํ ๊ตฌ์ฑ ๋ชฉ๋ก
SELECT cfgname FROM pg_ts_config;
-- ์ผ๋ฐ์ ์ธ ๊ตฌ์ฑ:
-- 'simple' โ ํํ์ ๋ถ์ ์์, ๋ถ์ฉ์ด ์์
-- 'english' โ ์์ด ํํ์ ๋ถ์ ๋ฐ ๋ถ์ฉ์ด
-- 'german' โ ๋
์ผ์ด ํํ์ ๋ถ์
-- 'spanish' โ ์คํ์ธ์ด ํํ์ ๋ถ์
-- ํ์ฌ ๊ธฐ๋ณธ๊ฐ ํ์
SHOW default_text_search_config;
-- ๊ธฐ๋ณธ๊ฐ ์ค์
SET default_text_search_config = 'english';
-- ๊ตฌ์ฑ ๋น๊ต
SELECT to_tsvector('simple', 'The running dogs are quickly jumping');
-- ๊ฒฐ๊ณผ: 'are':4 'dogs':3 'jumping':6 'quickly':5 'running':2 'the':1
SELECT to_tsvector('english', 'The running dogs are quickly jumping');
-- ๊ฒฐ๊ณผ: 'dog':3 'jump':6 'quick':5 'run':2
3.2 ์ฌ์ ๊ณผ ๋ถ์ฉ์ด¶
-- ๊ตฌ์ฑ์ ๋ํ ์ฌ์ ํ์
SELECT * FROM ts_debug('english', 'The quick brown foxes are jumping');
-- ์ถ๋ ฅ์ ํ ํฐ โ ์ฌ์ โ ๋ ์ฌ ๋งคํ์ ํ์
-- Token | Dictionary | Lexemes
-- ----------|-------------|--------
-- The | english_stem | {stop word}
-- quick | english_stem | {quick}
-- brown | english_stem | {brown}
-- foxes | english_stem | {fox}
-- are | english_stem | {stop word}
-- jumping | english_stem | {jump}
3.3 ์ฌ์ฉ์ ์ ์ ๊ตฌ์ฑ¶
-- ์์ด ๊ธฐ๋ฐ ์ฌ์ฉ์ ์ ์ ๊ตฌ์ฑ ์์ฑ
CREATE TEXT SEARCH CONFIGURATION my_english (COPY = english);
-- ๋์์ด ์ฌ์ ์ถ๊ฐ
CREATE TEXT SEARCH DICTIONARY my_synonyms (
TEMPLATE = synonym,
SYNONYMS = my_synonyms -- $SHAREDIR/tsearch_data/my_synonyms.syn ์ฐธ์กฐ
);
-- ๊ตฌ์ฑ์ ์ถ๊ฐ
ALTER TEXT SEARCH CONFIGURATION my_english
ALTER MAPPING FOR asciiword
WITH my_synonyms, english_stem;
4. GIN๊ณผ GiST ์ธ๋ฑ์ค¶
4.1 ์ ๋ฌธ ๊ฒ์์ ์ํ GIN ์ธ๋ฑ์ค¶
-- tsvector ์ปฌ๋ผ์ GIN ์ธ๋ฑ์ค ์์ฑ
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
-- ๋๋ ํํ์์ (๋น๋๋ ๋๋ฆฌ์ง๋ง, ์ ์ฅ๋ ์ปฌ๋ผ ๋ถํ์)
CREATE INDEX idx_articles_body_gin ON articles
USING GIN (to_tsvector('english', body));
-- ์ฟผ๋ฆฌ๋ ์๋์ผ๋ก ์ธ๋ฑ์ค ์ฌ์ฉ
EXPLAIN ANALYZE
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('english', 'database');
-- Bitmap Index Scan on idx_articles_search
4.2 GIN vs GiST ๋น๊ต¶
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
โ ์ ๋ฌธ ๊ฒ์์ ์ํ GIN vs GiST โ
โโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโค
โ โ GIN โ GiST โ
โโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโค
โ ๋น๋ ์๋ โ ๋๋ฆผ โ ๋น ๋ฆ โ
โ ๊ฒ์ ์๋ โ ๋น ๋ฆ (3๋ฐฐ) โ ๋๋ฆผ โ
โ ์ธ๋ฑ์ค ํฌ๊ธฐ โ ํผ โ ์์ โ
โ ์
๋ฐ์ดํธ ๋น์ฉ โ ๋์ โ ๋ฎ์ โ
โ ์ ํํ ๊ฒฐ๊ณผ โ ์ โ ๊ฑฐ์ง ๊ธ์ ๊ฐ๋ฅ โ
โ ์ต์ ํ โ ์ ์ ๋ฐ์ดํฐ, โ ์์ฃผ ์
๋ฐ์ดํธ๋๋ ๋ฐ์ดํฐ, โ
โ โ ์ฝ๊ธฐ ์์ฃผ โ ์ฐ๊ธฐ ์์ฃผ โ
โโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
-- GiST ์ธ๋ฑ์ค (๋์)
CREATE INDEX idx_articles_search_gist ON articles USING GiST (search_vector);
-- fastupdate๊ฐ ์๋ GIN (๊ธฐ๋ณธ๊ฐ on, ๋ฐฐ์น ์ฝ์
์ ์ข์)
CREATE INDEX idx_articles_search_gin ON articles
USING GIN (search_vector) WITH (fastupdate = on);
4.3 ์ธ๋ฑ์ค ์ ์ง๋ณด์¶
-- ์ธ๋ฑ์ค ํฌ๊ธฐ ํ์ธ
SELECT pg_size_pretty(pg_relation_size('idx_articles_search'));
-- ํ์์ ์ฌ์ธ๋ฑ์ฑ
REINDEX INDEX idx_articles_search;
-- ์ฟผ๋ฆฌ ํ๋๋๋ฅผ ์ํ ๋ถ์
ANALYZE articles;
5. ๋ญํน๊ณผ ๊ฐ์ค์น¶
5.1 ts_rank¶
-- ๊ธฐ๋ณธ ๋ญํน
SELECT
title,
ts_rank(search_vector, to_tsquery('english', 'database')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'database')
ORDER BY rank DESC;
-- ์ ๊ทํ ์ต์
(๋นํธ๋ง์คํฌ)
-- 0 = ๊ธฐ๋ณธ๊ฐ (๋ฌธ์ ๊ธธ์ด ๋ฌด์)
-- 1 = 1 + log(๋ฌธ์ ๊ธธ์ด)๋ก ๋๋
-- 2 = ๋ฌธ์ ๊ธธ์ด๋ก ๋๋
-- 4 = ๋ฒ์ ๊ฐ ํ๊ท ์กฐํ ๊ฑฐ๋ฆฌ๋ก ๋๋
-- 8 = ๊ณ ์ ๋จ์ด ์๋ก ๋๋
-- 16 = 1 + log(๊ณ ์ ๋จ์ด)๋ก ๋๋
-- 32 = ์๊ธฐ ์์ + 1๋ก ๋๋
SELECT
title,
ts_rank(search_vector, query, 2) AS rank_normalized -- ๊ธธ์ด๋ก ์ ๊ทํ
FROM articles, to_tsquery('english', 'database') AS query
WHERE search_vector @@ query
ORDER BY rank_normalized DESC;
5.2 ts_rank_cd (์ปค๋ฒ ๋ฐ๋)¶
-- ์ปค๋ฒ ๋ฐ๋ ๋ญํน์ ๋งค์นญ ์ฉ์ด์ ๊ทผ์ ์ฑ์ ๊ณ ๋ ค
SELECT
title,
ts_rank_cd(search_vector, to_tsquery('english', 'database & index')) AS rank_cd
FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & index')
ORDER BY rank_cd DESC;
5.3 ๊ฐ์ค์น ๊ฒ์¶
-- ๊ฐ์ค์น: A (1.0), B (0.4), C (0.2), D (0.1)
-- ์ฌ์ฉ์ ์ ์ ๊ฐ์ค์น ๋ฐฐ์ด: {D, C, B, A}
-- ์ฌ์ฉ์ ์ ์ ๊ฐ์ค์น ์ ์ฉ
SELECT
title,
ts_rank('{0.1, 0.2, 0.4, 1.0}', search_vector, query) AS weighted_rank
FROM articles, to_tsquery('english', 'database') AS query
WHERE search_vector @@ query
ORDER BY weighted_rank DESC;
-- ๊ฐ์ค์น๊ฐ ์๋ tsvector ์์ฑ
SELECT
setweight(to_tsvector('english', 'PostgreSQL Guide'), 'A') ||
setweight(to_tsvector('english', 'A comprehensive database tutorial'), 'B') ||
setweight(to_tsvector('english', 'Learn SQL queries and optimization'), 'C');
5.4 ๊ฒ์ ๊ฒฐ๊ณผ ํ์ด๋ผ์ดํ ¶
-- ts_headline์ ๋งค์นญ ์ฉ์ด๋ฅผ ํ์ด๋ผ์ดํธ
SELECT
title,
ts_headline('english', body, to_tsquery('english', 'database'),
'StartSel=<b>, StopSel=</b>, MaxWords=35, MinWords=15'
) AS highlighted
FROM articles
WHERE search_vector @@ to_tsquery('english', 'database');
-- ์ต์
:
-- StartSel / StopSel โ ํ์ด๋ผ์ดํธ ๋ง์ปค
-- MaxWords / MinWords โ ์ปจํ
์คํธ ์๋์ฐ ํฌ๊ธฐ
-- ShortWord โ ํ์ํ ์ต์ ๋จ์ด ๊ธธ์ด
-- MaxFragments โ ์กฐ๊ฐ ์ (0 = ์ ์ฒด ๋ฌธ์)
-- FragmentDelimiter โ ์กฐ๊ฐ ๊ฐ ๊ตฌ๋ถ์
6. ๊ณ ๊ธ ๊ฒ์ ๊ธฐ๋ฒ¶
6.1 ๋ค์ค ์ปฌ๋ผ ๊ฒ์¶
-- ๋ค๋ฅธ ๊ฐ์ค์น๋ก ์ฌ๋ฌ ์ปฌ๋ผ์ ๊ฑธ์ณ ๊ฒ์
CREATE TABLE blog_posts (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
summary TEXT,
body TEXT NOT NULL,
tags TEXT[],
search_vector tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(summary, '')), 'B') ||
setweight(to_tsvector('english', coalesce(body, '')), 'C') ||
setweight(to_tsvector('english', coalesce(array_to_string(tags, ' '), '')), 'A')
) STORED
);
CREATE INDEX idx_blog_search ON blog_posts USING GIN (search_vector);
6.2 ๊ตฌ๋ฌธ ๊ฒ์¶
-- ์ ํํ ๊ตฌ๋ฌธ: "full text search"
SELECT title FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'full text search');
-- ๊ทผ์ ์ฑ: N๊ฐ ์์น ๋ด์ ๋จ์ด
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('english', 'full <3> search');
-- "full"๊ณผ "search"๊ฐ ์๋ก 3๋จ์ด ์ด๋ด
6.3 ํํฐ๊ฐ ์๋ ๊ฒ์¶
-- ์ ๋ฌธ ๊ฒ์๊ณผ ์ผ๋ฐ WHERE ์ ๊ฒฐํฉ
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database') AS query
WHERE search_vector @@ query
AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY rank DESC
LIMIT 10;
-- ๊ฒฐํฉ ๊ฒ์์ ์ํ ๋ณตํฉ ์ธ๋ฑ์ค
CREATE INDEX idx_articles_date_search ON articles
USING GIN (search_vector) WHERE created_at >= '2024-01-01';
6.4 ์๋ ์์ฑ / ์ ๋์ด ๊ฒ์¶
-- :*๋ฅผ ์ฌ์ฉํ ์ ๋์ด ๋งค์นญ
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('english', 'dat:*');
-- ๋งค์น: database, data, date ๋ฑ
-- ์ค์ฉ์ ์ธ ์๋ ์์ฑ ํจ์
CREATE OR REPLACE FUNCTION search_autocomplete(search_term TEXT, max_results INT DEFAULT 10)
RETURNS TABLE(title TEXT, rank REAL) AS $$
BEGIN
RETURN QUERY
SELECT a.title, ts_rank(a.search_vector, query) AS rank
FROM articles a, to_tsquery('english', search_term || ':*') AS query
WHERE a.search_vector @@ query
ORDER BY rank DESC
LIMIT max_results;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM search_autocomplete('post');
6.5 ๊ฒ์ ํต๊ณ¶
-- ์ฝํผ์ค์์ ๊ฐ์ฅ ํํ ๋จ์ด
SELECT word, ndoc, nentry
FROM ts_stat('SELECT search_vector FROM articles')
ORDER BY nentry DESC
LIMIT 20;
-- ํน์ ์ฟผ๋ฆฌ์ ๋ํ ๋จ์ด ๋น๋
SELECT word, ndoc
FROM ts_stat('SELECT search_vector FROM articles')
WHERE word LIKE 'data%'
ORDER BY ndoc DESC;
7. ํผ์ง ๋งค์นญ์ ์ํ pg_trgm¶
7.1 ํธ๋ผ์ด๊ทธ๋จ ๊ธฐ์ด¶
-- ํ์ฅ ํ์ฑํ
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- ํธ๋ผ์ด๊ทธ๋จ ํ์
SELECT show_trgm('PostgreSQL');
-- ๊ฒฐ๊ณผ: {" p"," po","gre","osq","pos","ostg","pgr","ql ","res","sql","stg","tgr"}
-- ์ ์ฌ๋ ์ ์ (0~1)
SELECT similarity('PostgreSQL', 'Postgresql'); -- ~0.75
SELECT similarity('PostgreSQL', 'MySQL'); -- ~0.09
7.2 ํธ๋ผ์ด๊ทธ๋จ ์ธ๋ฑ์ค¶
-- GIN ํธ๋ผ์ด๊ทธ๋จ ์ธ๋ฑ์ค
CREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);
-- GiST ํธ๋ผ์ด๊ทธ๋จ ์ธ๋ฑ์ค (๊ฑฐ๋ฆฌ ์ฐ์ฐ์ ์ง์)
CREATE INDEX idx_articles_title_trgm_gist ON articles USING GiST (title gist_trgm_ops);
-- ์ ์ฌ๋ ๊ฒ์
SELECT title, similarity(title, 'Postgre') AS sim
FROM articles
WHERE title % 'Postgre' -- % ์ฐ์ฐ์๋ ์ ์ฌ๋ ์๊ณ๊ฐ ์ฌ์ฉ
ORDER BY sim DESC;
-- ์ ์ฌ๋ ์๊ณ๊ฐ ์ค์ (๊ธฐ๋ณธ๊ฐ 0.3)
SET pg_trgm.similarity_threshold = 0.2;
-- ๊ฑฐ๋ฆฌ ์ฐ์ฐ์ (GiST๋ง)
SELECT title, title <-> 'Postgre' AS distance
FROM articles
ORDER BY title <-> 'Postgre'
LIMIT 5;
7.3 ํธ๋ผ์ด๊ทธ๋จ ์ธ๋ฑ์ค๋ฅผ ์ฌ์ฉํ LIKE/ILIKE¶
-- ํธ๋ผ์ด๊ทธ๋จ ์ธ๋ฑ์ค๋ LIKE ์ฟผ๋ฆฌ๋ ๊ฐ์ํ
CREATE INDEX idx_articles_body_trgm ON articles USING GIN (body gin_trgm_ops);
-- ์ด์ ์ด ์ฟผ๋ฆฌ๋ค์ ์ธ๋ฑ์ค๋ฅผ ์ฌ์ฉ
SELECT title FROM articles WHERE body LIKE '%database%';
SELECT title FROM articles WHERE body ILIKE '%DATABASE%';
-- ์ ๊ท ํํ์ ์ฟผ๋ฆฌ๋ ์ด์ ์ป์
SELECT title FROM articles WHERE body ~ 'data(base|set)';
7.4 FTS์ pg_trgm ๊ฒฐํฉ¶
-- ์์ชฝ ์ฅ์ : ๊ด๋ จ์ฑ์ ์ ๋ฌธ ๊ฒ์, ์คํ ํ์ฉ์ ํธ๋ผ์ด๊ทธ๋จ
CREATE OR REPLACE FUNCTION smart_search(search_term TEXT, max_results INT DEFAULT 10)
RETURNS TABLE(id INT, title TEXT, score REAL) AS $$
BEGIN
-- ๋จผ์ ์ ํํ ์ ๋ฌธ ๊ฒ์ ์๋
RETURN QUERY
SELECT a.id, a.title,
ts_rank(a.search_vector, websearch_to_tsquery('english', search_term)) AS score
FROM articles a
WHERE a.search_vector @@ websearch_to_tsquery('english', search_term)
ORDER BY score DESC
LIMIT max_results;
-- ๊ฒฐ๊ณผ๊ฐ ์์ผ๋ฉด ํผ์ง ๋งค์นญ์ผ๋ก ํด๋ฐฑ
IF NOT FOUND THEN
RETURN QUERY
SELECT a.id, a.title, similarity(a.title, search_term) AS score
FROM articles a
WHERE a.title % search_term OR a.body % search_term
ORDER BY score DESC
LIMIT max_results;
END IF;
END;
$$ LANGUAGE plpgsql;
8. ์ฐ์ต ๋ฌธ์ ¶
์ฐ์ต 1: ์ ํ ๊ฒ์¶
์ ์์๊ฑฐ๋ ์ ํ ์นดํ๋ก๊ทธ๋ฅผ ์ํ ์ ๋ฌธ ๊ฒ์ ์์คํ ์ ๊ตฌ์ถํ์ธ์.
-- ์์ ๋ต์
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
category TEXT,
price NUMERIC(10,2),
search_vector tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
setweight(to_tsvector('english', coalesce(description, '')), 'B') ||
setweight(to_tsvector('english', coalesce(category, '')), 'C')
) STORED
);
CREATE INDEX idx_products_search ON products USING GIN (search_vector);
-- ์นดํ
๊ณ ๋ฆฌ ํํฐ๊ฐ ์๋ ๊ฒ์ ํจ์
CREATE OR REPLACE FUNCTION search_products(
search_term TEXT,
category_filter TEXT DEFAULT NULL,
max_results INT DEFAULT 20
)
RETURNS TABLE(id INT, name TEXT, price NUMERIC, rank REAL) AS $$
BEGIN
RETURN QUERY
SELECT p.id, p.name, p.price,
ts_rank(p.search_vector, websearch_to_tsquery('english', search_term)) AS rank
FROM products p
WHERE p.search_vector @@ websearch_to_tsquery('english', search_term)
AND (category_filter IS NULL OR p.category = category_filter)
ORDER BY rank DESC
LIMIT max_results;
END;
$$ LANGUAGE plpgsql;
์ฐ์ต 2: ํ์ด๋ผ์ดํ ์ด ์๋ ๊ฒ์¶
ํ์ด๋ผ์ดํธ๋ ๋งค์น๊ฐ ์๋ ๊ฒ์ ๊ฒฐ๊ณผ๋ฅผ ์์ฑํ์ธ์.
-- ์์ ๋ต์
SELECT
id,
ts_headline('english', name, query,
'StartSel=<mark>, StopSel=</mark>') AS highlighted_name,
ts_headline('english', description, query,
'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, FragmentDelimiter= ... ') AS highlighted_desc,
ts_rank(search_vector, query) AS relevance
FROM products, websearch_to_tsquery('english', 'wireless bluetooth') AS query
WHERE search_vector @@ query
ORDER BY relevance DESC;
์ฐ์ต 3: ๋ค๊ตญ์ด ๊ฒ์¶
์์ด์ simple(ํํ์ ๋ถ์ ์์) ํ ์คํธ๋ฅผ ๋ชจ๋ ์ฒ๋ฆฌํ๋ ๊ฒ์ ์์คํ ์ ์ค๊ณํ์ธ์.
-- ์์ ๋ต์
CREATE TABLE multilingual_docs (
id SERIAL PRIMARY KEY,
content_en TEXT,
content_raw TEXT,
search_en tsvector GENERATED ALWAYS AS (
to_tsvector('english', coalesce(content_en, ''))
) STORED,
search_simple tsvector GENERATED ALWAYS AS (
to_tsvector('simple', coalesce(content_raw, ''))
) STORED
);
CREATE INDEX idx_ml_en ON multilingual_docs USING GIN (search_en);
CREATE INDEX idx_ml_simple ON multilingual_docs USING GIN (search_simple);
-- ์์ชฝ์ ๊ฑธ์ณ ๊ฒ์
SELECT id, content_en
FROM multilingual_docs
WHERE search_en @@ websearch_to_tsquery('english', 'search term')
OR search_simple @@ websearch_to_tsquery('simple', 'search term');