Full-text search tiếng Việt — tsvector, unaccent, GIN FTS trong PostgreSQL

EXPLAIN ANALYZE
SELECT id, title, body
FROM tasks
WHERE body LIKE '%deploy%';

Seq Scan on tasks  (cost=0.00..24532.00 rows=1000 width=312)
                   (actual time=0.051..2487.312 rows=3241 loops=1)
  Filter: (body ~~ '%deploy%'::text)
  Rows Removed by Filter: 996759
Planning Time: 0.082 ms
Execution Time: 2531.447 ms

-- tsvector: preprocessed document
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
-- Stopwords (the, over) bị strip
-- Stemming: jumps -> jump, lazy -> lazi
-- Position number giữ lại cho ranking

-- tsquery: biểu thức tìm kiếm
SELECT to_tsquery('english', 'quick & brown');
-- 'quick' & 'brown'

SELECT to_tsquery('english', 'deploy | deployment');
-- 'deploy' | 'deploy'  -- cả hai stem về 'deploy'

-- Operator @@ : tsvector match tsquery
SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'quick & fox');
-- true

SELECT to_tsvector('english', 'deploy production') @@ to_tsquery('english', 'deployment');
-- true -- vì 'deployment' stem về 'deploy'

-- plainto_tsquery: AND của tất cả token, không parse operator
SELECT plainto_tsquery('english', 'quick brown');
-- 'quick' & 'brown'

SELECT plainto_tsquery('english', 'deploy production');
-- 'deploy' & 'product'  -- "production" stem về "product"

-- websearch_to_tsquery (PG 11+): Google-style syntax
SELECT websearch_to_tsquery('english', '"deploy production" -staging OR test');
-- 'deploy' <-> 'product' & !'stage' | 'test'
-- "phrase" = phrase search, - = NOT, OR = alternate

-- to_tsquery: operator parsing đầy đủ (& | ! <->)
-- Dùng khi bạn build query programmatically
SELECT to_tsquery('english', 'deploy & prod & !staging');
-- 'deploy' & 'prod' & !'stage'

-- Them generated column tsvector (tinh toan khi INSERT/UPDATE, luu vao disk)
ALTER TABLE tasks
  ADD COLUMN body_tsv tsvector
    GENERATED ALWAYS AS (
      to_tsvector('english', coalesce(body, ''))
    ) STORED;

-- GIN index tren tsvector column
CREATE INDEX idx_tasks_body_fts ON tasks USING gin(body_tsv);

-- Search: dung query variable de planner optimize
SELECT id, title, ts_rank(body_tsv, query) AS rank
FROM tasks,
     plainto_tsquery('english', 'deploy production') query
WHERE body_tsv @@ query
ORDER BY rank DESC
LIMIT 10;

Bitmap Heap Scan on tasks  (cost=32.45..1234.12 rows=312 width=124)
                            (actual time=1.203..4.892 rows=312 loops=1)
  Recheck Cond: (body_tsv @@ plainto_tsquery('english', 'deploy production'))
  ->  Bitmap Index Scan on idx_tasks_body_fts
        (actual time=1.101..1.101 rows=312 loops=1)
Planning Time: 0.421 ms
Execution Time: 5.234 ms

-- English config: "triển khai" không stem đúng
SELECT to_tsvector('english', 'Khẩn cấp triển khai sản phẩm');
-- 'cấp':2 'khẩn':1 'khai':4 'sản':5 'triển':3
-- Tokens giữ dấu -> "khan cap" không match "khẩn cấp"

-- User gõ không dấu -> không match document có dấu
SELECT to_tsvector('english', 'Khẩn cấp') @@ to_tsquery('english', 'khan & cap');
-- false -- vì English config giữ nguyên dấu

-- Bat unaccent extension
CREATE EXTENSION IF NOT EXISTS unaccent;

-- Test unaccent
SELECT unaccent('Khẩn cấp triển khai sản phẩm');
-- Khan cap trien khai san pham

-- Tao text search configuration riêng cho Vietnamese
CREATE TEXT SEARCH CONFIGURATION vietnamese (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION vietnamese
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, simple;

-- Test config vietnamese
SELECT to_tsvector('vietnamese', 'Khẩn cấp triển khai sản phẩm');
-- 'cap':2 'khai':4 'khan':1 'pham':6 'san':5 'trien':3

-- User gõ không dấu
SELECT to_tsvector('vietnamese', 'Khan cap trien khai san pham');
-- 'cap':2 'khai':4 'khan':1 'pham':6 'san':5 'trien':3
-- Giống hệt nhau sau unaccent!

-- Match: document có dấu, query không dấu
SELECT to_tsvector('vietnamese', 'Khẩn cấp') @@ to_tsquery('vietnamese', 'khan & cap');
-- true

-- Xác nhận: query có dấu cũng match document không dấu
SELECT to_tsvector('vietnamese', 'Khan cap') @@ to_tsquery('vietnamese', 'khẩn & cấp');
-- true

-- Và ngược lại: cả hai đều về cùng token sau unaccent
SELECT to_tsquery('vietnamese', 'khẩn & cấp');
-- 'khan' & 'cap'  -- dấu bị strip trong query cũng

-- ts_rank: TF-IDF-like relevance score (0.0 đến 1.0)
-- Token xuất hiện nhiều lần + ít document chứa token -> rank cao hơn
SELECT
  id,
  title,
  ts_rank(body_tsv, query) AS rank
FROM tasks,
     plainto_tsquery('vietnamese', 'khan cap deploy') query
WHERE body_tsv @@ query
ORDER BY rank DESC
LIMIT 20;

-- ts_headline: extract và highlight đoạn văn chứa match
SELECT
  id,
  ts_headline(
    'vietnamese',
    body,
    query,
    'StartSel=<mark>, StopSel=</mark>, MaxWords=20, MinWords=10'
  ) AS snippet
FROM tasks,
     plainto_tsquery('vietnamese', 'khan cap') query
WHERE body_tsv @@ query;
-- snippet: "...task <mark>khẩn</mark> <mark>cấp</mark> cần deploy ngay..."
-- (ts_headline re-highlight trên text gốc có dấu -- đúng behaviour)

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN trigram index cho title (substring + similarity)
CREATE INDEX idx_tasks_title_trgm ON tasks USING gin(title gin_trgm_ops);

-- Similarity search: typo tolerance
SELECT title, similarity(title, 'dploy') AS sim
FROM tasks
WHERE title % 'dploy'  -- % là similarity operator, threshold mặc định 0.3
ORDER BY sim DESC
LIMIT 10;
-- "deploy production" có similarity khoảng 0.5 -> match dù typo

-- Kết hợp: FTS cho relevance ranking + trigram cho typo fallback
-- Query 1: FTS (nhanh, chính xác)
SELECT id, title, ts_rank(body_tsv, q) AS rank, 'fts' AS source
FROM tasks, plainto_tsquery('vietnamese', 'deploy') q
WHERE body_tsv @@ q

UNION ALL

-- Query 2: trigram fallback (khi FTS không có kết quả)
SELECT id, title, similarity(title, 'deploy')::float AS rank, 'trgm' AS source
FROM tasks
WHERE title % 'deploy'
  AND NOT (body_tsv @@ plainto_tsquery('vietnamese', 'deploy'))

ORDER BY rank DESC
LIMIT 20;

-- Step 1: enable extensions
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Step 2: tạo Vietnamese text search config
CREATE TEXT SEARCH CONFIGURATION vietnamese (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION vietnamese
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, simple;

-- Step 3: thêm generated column kết hợp title + body
-- Title có weight cao hơn: setweight(tsvector, 'A') cho title weight A
-- Body weight B -- ts_rank sẽ ưu tiên title match
ALTER TABLE tasks
  ADD COLUMN search_tsv tsvector
    GENERATED ALWAYS AS (
      setweight(to_tsvector('vietnamese', coalesce(title, '')), 'A') ||
      setweight(to_tsvector('vietnamese', coalesce(body, '')),  'B')
    ) STORED;

-- Step 4: GIN index trên generated column
CREATE INDEX idx_tasks_search ON tasks USING gin(search_tsv);

-- Step 5: API endpoint /api/search?q=khan+cap+deploy
-- $1 = user input từ search box
SELECT
  id,
  title,
  ts_headline(
    'vietnamese',
    coalesce(body, ''),
    query,
    'StartSel=<mark>, StopSel=</mark>, MaxWords=20, MaxFragments=2'
  )                                  AS snippet,
  ts_rank(search_tsv, query)         AS rank
FROM tasks,
     websearch_to_tsquery('vietnamese', $1) query
WHERE search_tsv @@ query
  AND project_id = $2  -- scoped to project, dùng được composite index
ORDER BY rank DESC, created_at DESC
LIMIT 20;