-- tsvector 컬럼 생성 + GIN 인덱스
ALTER TABLE articles ADD COLUMN search_vec tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX idx_articles_fts ON articles USING GIN (search_vec);

-- 검색 + 관련성 정렬
SELECT id, title,
       ts_rank(search_vec, query) AS rank
FROM articles,
     to_tsquery('english', 'postgresql & index') query
WHERE search_vec @@ query
ORDER BY rank DESC
LIMIT 20;

-- tsvector 변환 결과 확인
SELECT to_tsvector('english', 'The quick brown foxes are jumping over lazy dogs');
-- 'brown':3 'dog':9 'fox':4 'jump':6 'lazi':8 'quick':2
-- "The", "are", "over" 등 불용어 제거, 복수형·진행형 어간 추출

-- 검색 연산자
SELECT to_tsquery('english', 'postgresql & (index | table)');
-- 'postgresql' & ( 'index' | 'tabl' )

-- websearch_to_tsquery: 사용자 입력 직접 사용 가능
SELECT websearch_to_tsquery('english', 'postgresql index -table');
-- 'postgresql' & 'index' & !'tabl'

-- 구문 검색 (단어 순서 보장)
SELECT to_tsquery('english', 'full <-> text <-> search');

-- 매칭 여부 확인
SELECT 'quick brown fox'::tsvector @@ 'fox'::tsquery;  -- true

-- 텍스트를 직접 매칭 (인덱스 미사용)
SELECT to_tsvector('english', 'PostgreSQL full-text search')
  @@ to_tsquery('english', 'search & postgresql');  -- true

-- 제목(가중치 A)과 본문(가중치 B) 결합
SELECT
  setweight(to_tsvector('english', title), 'A') ||
  setweight(to_tsvector('english', body),  'B') AS search_vec
FROM articles;

-- 단어 검색
WHERE search_vec @@ to_tsquery('english', 'postgresql & index')

-- 부분 문자열 검색
WHERE title LIKE '%postgre%'

-- 표현식 인덱스: 컬럼이 아닌 함수 결과에 인덱스
CREATE INDEX idx_articles_fts
  ON articles
  USING GIN (to_tsvector('english', title || ' ' || body));

-- GENERATED STORED 컬럼 방식 (INSERT/UPDATE 시 자동 갱신)
ALTER TABLE articles
  ADD COLUMN search_vec tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

CREATE INDEX idx_articles_search ON articles USING GIN (search_vec);

-- 인덱스 사용 여부 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM articles
WHERE search_vec @@ to_tsquery('english', 'postgresql');
-- Bitmap Index Scan on idx_articles_search

-- ts_rank: 기본 빈도 기반
SELECT
  id, title,
  ts_rank(search_vec, query)    AS rank_basic,
  ts_rank_cd(search_vec, query) AS rank_cover
FROM articles,
     to_tsquery('english', 'full & text & search') query
WHERE search_vec @@ query
ORDER BY rank_cover DESC
LIMIT 10;

-- normalization 파라미터로 점수 정규화
-- 0: 정규화 없음 (기본값)
-- 1: 어휘소 수로 나눔
-- 2: 문서 길이 로그로 나눔
-- 4: 고유 어휘소 수로 나눔 (중복 어휘소 페널티)
SELECT
  id,
  ts_rank(search_vec, query, 1) AS rank_normalized
FROM articles,
     to_tsquery('english', 'index') query
WHERE search_vec @@ query
ORDER BY rank_normalized DESC;

-- 하이라이트 (일치 부분 강조)
SELECT
  ts_headline('english', body,
    to_tsquery('english', 'postgresql & index'),
    'MaxWords=50, MinWords=20, StartSel=<b>, StopSel=</b>'
  ) AS excerpt
FROM articles
WHERE search_vec @@ to_tsquery('english', 'postgresql & index');

-- 실제 데이터와 통계에서 어떤 계획을 택하는지 확인한다.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM articles WHERE body LIKE '%postgresql index%';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM articles
WHERE search_vec @@ to_tsquery('english', 'postgresql & index');

-- pg_trgm + GIN: 부분 문자열 매칭이 요구될 때만 검토한다.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_articles_trgm ON articles USING GIN (title gin_trgm_ops);

-- 인덱스 채택 여부는 패턴, 통계, 비용 추정에 따라 달라진다.
SELECT id, title FROM articles WHERE title LIKE '%postgre%';

-- 단어 의미 검색
WHERE search_vec @@ websearch_to_tsquery('english', 'postgresql index')

-- 부분 문자열 검색
WHERE title LIKE '%postgre%'

SELECT id FROM articles
WHERE body LIKE '%postgresql index%';

SELECT id, title
FROM articles
WHERE search_vec @@ to_tsquery('english', user_input);