PostgreSQL 全文搜索：tsvector、GIN 索引与排名

PostgreSQL FTS：中小型应用常优于 Elasticsearch

设置（加权搜索）

ALTER TABLE posts ADD COLUMN search_vector tsvector;

UPDATE posts SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'B');

CREATE INDEX posts_search_idx ON posts USING GIN(search_vector);

-- 自动更新触发器
CREATE FUNCTION update_fts() RETURNS TRIGGER AS $
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B');
  RETURN NEW;
END; $ LANGUAGE plpgsql;

CREATE TRIGGER fts_update BEFORE INSERT OR UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION update_fts();

带排名和摘要的搜索

SELECT title,
  ts_rank(search_vector, query) AS rank,
  ts_headline('english', body, query, 'MaxWords=30, StartSel=<mark>, StopSel=</mark>') AS snippet
FROM posts, plainto_tsquery('english', 'postgresql performance') query
WHERE search_vector @@ query
ORDER BY rank DESC LIMIT 20;

三元组相似度

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX name_trgm ON users USING GIN(name gin_trgm_ops);

SELECT name, similarity(name, 'Jhon Doe') sim
FROM users WHERE name % 'Jhon Doe' ORDER BY sim DESC;

-> 使用 JSON Viewer 格式化搜索结果。