SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 結(jié)果: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

SELECT to_tsquery('english', 'quick & fox');  -- 同時(shí)包含
SELECT to_tsquery('english', 'quick | fox');  -- 包含其一
SELECT to_tsquery('english', 'jump & !lazy'); -- 包含 jump 但不含 lazy

SELECT to_tsvector('english', 'a fat cat') @@ to_tsquery('english', 'fat & cat');
-- true

SELECT title, content
FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & performance');

ALTER TABLE articles ADD COLUMN content_ts tsvector;

UPDATE articles 
SET content_ts = to_tsvector('english', coalesce(content, ''));

CREATE INDEX idx_articles_content_ts ON articles USING GIN(content_ts);

SELECT title, content
FROM articles
WHERE content_ts @@ to_tsquery('english', 'database & performance');

CREATE TRIGGER tsvector_update_trigger
BEFORE INSERT OR UPDATE OF content ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(content_ts, 'pg_catalog.english', content);

-- 構(gòu)建帶權(quán)重的 tsvector
UPDATE articles SET content_ts = 
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(content, '')), 'B');

-- 查詢（權(quán)重影響排序）
SELECT title, ts_rank(content_ts, query) AS rank
FROM articles, to_tsquery('english', 'database') query
WHERE content_ts @@ query
ORDER BY rank DESC;

-- 搜索 "quick brown" 作為短語
SELECT * FROM articles 
WHERE content_ts @@ phraseto_tsquery('english', 'quick brown');

-- 搜索以 "run" 開頭的詞（running, runner）
SELECT * FROM articles 
WHERE content_ts @@ to_tsquery('english', 'run:*');

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_articles_title_trgm ON articles USING GIN(title gin_trgm_ops);

-- 搜索相似詞
SELECT title FROM articles 
WHERE title % 'databse';  -- 匹配 "database"

-- 中文需額外配置（見下文）
SELECT to_tsvector('french', 'Les données sont importantes');
-- 結(jié)果: 'donn':2 'import':4

-- 查看支持的語言
SELECT cfgname FROM pg_ts_config;

# Ubuntu/Debian
sudo apt install postgresql-contrib
git clone https://github.com/amutu/zhparser.git
cd zhparser
make && sudo make install

CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e,l,x WITH simple;

SELECT to_tsvector('chinese', '中華人民共和國成立70周年');
-- 結(jié)果: '中華':1 '人民':2 '共和國':3 '成立':4 '70':5 '周年':6

-- 創(chuàng)建索引
CREATE INDEX idx_articles_chinese ON articles USING GIN(to_tsvector('chinese', content));

CREATE EXTENSION jiebacfg;
-- 用法類似 zhparser

-- 先按 rank 排序，再 LIMIT
SELECT *, ts_rank(content_ts, q) AS rank
FROM articles, to_tsquery('english', 'database') q
WHERE content_ts @@ q
ORDER BY rank DESC
LIMIT 20;

CREATE INDEX idx_articles_covering ON articles USING GIN(content_ts) INCLUDE (title, id);

CREATE TABLE logs_2026_01 PARTITION OF logs FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
-- 每個(gè)分區(qū)獨(dú)立建 FTS 索引

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    brand TEXT,
    search_vector tsvector
);

CREATE TRIGGER product_search_update
BEFORE INSERT OR UPDATE OF title, description, brand ON products
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(
    search_vector, 
    'pg_catalog.english', 
    title, description, brand
);

CREATE OR REPLACE FUNCTION update_product_search() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B') ||
        setweight(to_tsvector('english', coalesce(NEW.brand, '')), 'A');
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER product_search_update
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION update_product_search();

-- 基礎(chǔ)搜索
SELECT id, title, ts_rank(search_vector, q) AS rank
FROM products, websearch_to_tsquery('english', 'wireless headphones') q
WHERE search_vector @@ q
ORDER BY rank DESC
LIMIT 20;

-- 短語搜索
SELECT * FROM products 
WHERE search_vector @@ phraseto_tsquery('english', 'noise cancelling');

-- 前綴搜索
SELECT * FROM products 
WHERE search_vector @@ to_tsquery('english', 'headphon:*');

SELECT 
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE indexname LIKE '%ts%';

ANALYZE products;  -- 確保優(yōu)化器準(zhǔn)確估算

REINDEX INDEX idx_products_search;  -- 在低峰期執(zhí)行

SELECT ts_headline('english', content, q, 'StartSel=<b>, StopSel=</b>') 
FROM articles, to_tsquery('english', 'database') q
WHERE content_ts @@ q;

-- zhparser 支持自定義詞典
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR ...;