pg_textsearch

pig build pkg pg_textsearch         # 构建 RPM / DEB 包

pig repo add pgsql -u          # 添加仓库并更新缓存

CREATE EXTENSION pg_textsearch;

CREATE EXTENSION pg_textsearch;

CREATE TABLE documents (id bigserial PRIMARY KEY, content text);
INSERT INTO documents (content) VALUES
    ('PostgreSQL is a powerful database system'),
    ('BM25 is an effective ranking function'),
    ('Full text search with custom scoring');

-- 创建 BM25 索引
CREATE INDEX docs_idx ON documents USING bm25(content) WITH (text_config='english');

-- 使用 <@> 运算符查询（返回负 BM25 分数，越低匹配越好）
SELECT * FROM documents
ORDER BY content <@> 'database system'
LIMIT 5;

-- 从列自动检测索引
SELECT * FROM documents
ORDER BY content <@> 'database system'
LIMIT 5;

-- 显式指定索引
SELECT * FROM documents
WHERE content <@> to_bm25query('database system', 'docs_idx') < -1.0;

CREATE INDEX ON documents (category_id);
SELECT * FROM documents
WHERE category_id = 123
ORDER BY content <@> 'search terms'
LIMIT 10;

SELECT * FROM documents
WHERE content <@> to_bm25query('search terms', 'docs_idx') < -5.0
ORDER BY content <@> 'search terms'
LIMIT 10;

CREATE INDEX ON documents USING bm25(content)
  WITH (text_config='english', k1=1.5, b=0.8);

-- 语言特定配置
CREATE INDEX ON french_docs USING bm25(content) WITH (text_config='french');
CREATE INDEX ON german_docs USING bm25(content) WITH (text_config='german');

SELECT to_bm25query('search query text', 'docs_idx');
-- docs_idx:search query text