[PostgreSQL] 검색 결과 하이라이팅 완성 — 검색창 입력을 그대로 받아 관련도순 + 강조 스니펫까지

설치·접속: PostgreSQL 설치와 접속

부제: 게시글 검색에서 사용자가 검색창에 "file search" -crab 처럼 입력하는데, 매칭만 되고 관련도순 정렬도 구글식 하이라이트도 없을 때

@@ 로 매칭만 하고 끝난 검색을 네 단계로 "검색답게" 완성한다. 사용자 입력 파싱(websearch_to_tsquery) → 매칭(@@) → 관련도 정렬(ts_rank_cd) → 강조 스니펫(ts_headline). 제목 가중치는 setweight 로 미리 넣는다. 한글 형태소 사전 없이도 되도록 english config 로 검증한다.

CREATE EXTENSION IF NOT EXISTS unaccent;  -- 필요 시(악센트 무시). 아래 예제엔 없어도 됨

CREATE TABLE doc (id int primary key, title text, body text, tsv tsvector);
INSERT INTO doc(id,title,body) VALUES
 (1,'Linux find command guide','The find command searches files by name. Learn find with practical examples for file search.'),
 (2,'Windows PowerShell basics','PowerShell is a Windows shell. This crab-heavy guide avoids linux entirely and covers windows scripting.'),
 (3,'grep and find together','Combine grep with find to search file contents. A short note on searching files fast.'),
 (4,'File search on Linux','A deep dive into searching files: locate, find, fd. Fast file search techniques for linux users.'),
 (5,'Cooking crab','How to cook crab. Not about linux or files at all.');

-- 제목 A, 본문 D 가중치를 tsvector에 미리 심는다
UPDATE doc SET tsv =
  setweight(to_tsvector('english', title), 'A') ||
  setweight(to_tsvector('english', body),  'D');

1단계 — 검색창 문법을 그대로 파싱한다

to_tsquery 에 사용자 입력을 그대로 넣으면 공백·따옴표·마이너스에서 문법 에러가 난다. websearch_to_tsquery 는 검색엔진식 입력(따옴표 구절, - 제외)을 안전하게 받아 tsquery로 바꾼다.

SELECT websearch_to_tsquery('english', '"file search" -crab') AS parsed_query;

         parsed_query
-------------------------------
 'file' <-> 'search' & !'crab'
(1 row)

"file search" 는 'file' <-> 'search'(FOLLOWED BY, 바로 뒤에 오는 구절)로, -crab 은 & !'crab'(제외)으로 변환됐다. 앱에서 입력을 직접 tsquery 문법으로 조립할 필요가 없다.

2·3단계 — @@ 매칭 + ts_rank_cd 관련도 정렬

같은 파싱 결과를 @@ 에 걸어 매칭하고, ts_rank_cd 로 정렬한다. ts_rank_cd 는 매칭 어휘의 근접도(cover density)까지 반영한다. 첫 인자 {0.1,0.2,0.4,1.0} 은 D·C·B·A 가중치 배열, 마지막 정규화 플래그 32 는 rank/(rank+1) 로 0~1 스케일화한다.

SELECT id, title,
  round(ts_rank_cd('{0.1,0.2,0.4,1.0}', tsv, q, 32)::numeric, 4) AS rank
FROM doc, websearch_to_tsquery('english','"file search" -crab') q
WHERE tsv @@ q
ORDER BY rank DESC;

 id |          title           |  rank
----+--------------------------+--------
  4 | File search on Linux     | 0.5238
  1 | Linux find command guide | 0.0909
(2 rows)

구절 'file' <-> 'search' 가 실제로 인접한 doc 4·1만 남았다. doc 2는 crab 이 있어 !'crab' 로 배제됐고, doc 3은 "search file"/"searching files"라 인접 구절이 아니라 탈락했다. doc 4는 제목(가중치 A)에 "File search"가 있어 rank가 0.52로 크고, doc 1은 본문(D)에만 있어 0.09로 낮다. setweight 로 심은 제목 가중치가 정렬에 그대로 반영된 것이다.

4단계 — ts_headline 로 구글식 강조 스니펫

매칭 부분을 앱에서 문자열 replace 로 강조하면 어간(stem) 매칭이 어긋난다("files"/"searching"을 못 잡는다). ts_headline 은 tsquery 어간 기준으로 정확히 강조하고 발췌 조각까지 만든다.

SELECT id,
  ts_headline('english', body,
    websearch_to_tsquery('english','"file search" -crab'),
    'StartSel=<b>, StopSel=</b>, MaxWords=12, MinWords=4, MaxFragments=2') AS snippet
FROM doc, websearch_to_tsquery('english','"file search" -crab') q
WHERE tsv @@ q
ORDER BY ts_rank_cd('{0.1,0.2,0.4,1.0}', tsv, q, 32) DESC;

 id |                                          snippet
----+-------------------------------------------------------------------------------------------
  4 | <b>files</b>: locate, find, fd. Fast <b>file</b> <b>search</b> techniques for linux users
  1 | find with practical examples for <b>file</b> <b>search</b>
(2 rows)

<b>files</b> 처럼 원형이 "file"인 어간 변형까지 강조됐고, MaxFragments/MaxWords 로 발췌 조각 길이를 제어했다. 네 단계를 합치면 "검색창 입력 → 관련도순 결과 리스트 → 강조 요약"이라는 완성형 검색 화면이 SQL 한 덩어리로 나온다.

결론 — 언제 이 조합을 쓰나

전문검색을 이미 @@ 로 붙였는데 "매칭은 되지만 순서가 엉망이고 미리보기가 없다"는 단계에서 이 네 개를 얹는다. 검색창 입력을 직접 받으면 websearch_to_tsquery, 랭킹엔 근접도까지 보는 ts_rank_cd, 강조엔 어간까지 잡는 ts_headline. 실무 핵심은 setweight 를 tsvector 저장 시점에 미리 심어 두는 것과, ts_rank_cd 정규화 플래그로 문서 길이 편향을 보정하는 것이다.

이렇게도 쓴다

to_tsquery는 사용자 입력에서 에러가 나고, websearch는 안 난다. 이게 갈아타는 이유다.

SELECT to_tsquery('english', '"file search" -crab');       -- ERROR: syntax error in tsquery
SELECT websearch_to_tsquery('english', '"file search" -crab');  -- OK

 

정규화 플래그를 바꿔 문서 길이 보정을 조절한다. 32=rank/(rank+1), 1·2=로그·단순 길이 나눗셈, 조합 가능(비트 OR).

SELECT id, ts_rank_cd(tsv, q, 2|32) FROM doc, websearch_to_tsquery('english','linux') q
WHERE tsv @@ q ORDER BY 2 DESC;

 

가중치 배열을 바꿔 제목 매칭을 더 크게 밀어준다. {D,C,B,A} 순서. (조합: setweight)

SELECT id, ts_rank_cd('{0.1,0.2,0.4,1.0}', tsv, q) FROM doc,
  websearch_to_tsquery('english','linux') q WHERE tsv @@ q ORDER BY 2 DESC;

 

ts_rank_cd 대신 ts_rank는 근접도를 안 본다. 단순 빈도 랭킹이면 이걸로 충분하다.

SELECT id, ts_rank(tsv, q) FROM doc, websearch_to_tsquery('english','file') q
WHERE tsv @@ q ORDER BY 2 DESC;

 

tsv 컬럼에 GIN 인덱스를 걸면 매칭이 빨라진다. 랭킹/헤드라인은 매칭된 소수 행에만 돌면 된다. (조합: GIN)

CREATE INDEX idx_doc_tsv ON doc USING gin (tsv);

 

plainto_tsquery는 구절·제외 없이 모든 단어를 AND로만 묶는다. 검색창이 아니라 내부 필터용.

SELECT plainto_tsquery('english', 'file search');  -- 'file' & 'search'