全文搜索索引修复说明

问题描述

在某些PostgreSQL版本中，使用 to_tsvector() 函数直接创建GIN索引时会出现以下错误：

ERROR: 42P17: functions in index predicate must be marked IMMUTABLE

解决方案

我们创建了两个IMMUTABLE函数来包装 to_tsvector() 调用：

-- 标题全文搜索函数
CREATE OR REPLACE FUNCTION public.content_title_to_tsvector(title TEXT)
RETURNS tsvector
LANGUAGE sql IMMUTABLE STRICT
AS $$
    SELECT to_tsvector('simple', COALESCE(title, ''));
$$;

-- 内容全文搜索函数
CREATE OR REPLACE FUNCTION public.content_body_to_tsvector(content TEXT)
RETURNS tsvector
LANGUAGE sql IMMUTABLE STRICT
AS $$
    SELECT to_tsvector('simple', COALESCE(content, ''));
$$;

索引使用

修复后的索引：

-- 使用IMMUTABLE函数的索引
CREATE INDEX IF NOT EXISTS idx_contents_fts_title ON public.ak_contents 
    USING gin(content_title_to_tsvector(title));

CREATE INDEX IF NOT EXISTS idx_contents_fts_content ON public.ak_contents 
    USING gin(content_body_to_tsvector(content));

查询示例

1. 标题搜索

-- 搜索标题中包含"AI"的内容
SELECT id, title, published_at
FROM ak_contents 
WHERE content_title_to_tsvector(title) @@ to_tsquery('simple', 'AI');

2. 内容搜索

-- 搜索内容中包含"machine learning"的文章
SELECT id, title, LEFT(content, 200) as preview
FROM ak_contents 
WHERE content_body_to_tsvector(content) @@ to_tsquery('simple', 'machine & learning');

3. 组合搜索

-- 在标题或内容中搜索关键词
SELECT id, title, quality_score, published_at
FROM ak_contents 
WHERE content_title_to_tsvector(title) @@ to_tsquery('simple', 'AI | technology')
   OR content_body_to_tsvector(content) @@ to_tsquery('simple', 'AI | technology')
ORDER BY published_at DESC;

4. 模糊搜索

-- 使用plainto_tsquery进行更自然的搜索
SELECT id, title, 
       ts_rank(content_title_to_tsvector(title), query) + 
       ts_rank(content_body_to_tsvector(content), query) as rank
FROM ak_contents, plainto_tsquery('simple', 'artificial intelligence') query
WHERE content_title_to_tsvector(title) @@ query
   OR content_body_to_tsvector(content) @@ query
ORDER BY rank DESC
LIMIT 10;

性能优化建议

1. 预计算tsvector列 (可选)

如果需要更高的查询性能，可以添加预计算的tsvector列：

-- 添加预计算列
ALTER TABLE ak_contents ADD COLUMN title_tsvector tsvector;
ALTER TABLE ak_contents ADD COLUMN content_tsvector tsvector;

-- 更新现有数据
UPDATE ak_contents SET 
    title_tsvector = content_title_to_tsvector(title),
    content_tsvector = content_body_to_tsvector(content);

-- 创建触发器自动更新
CREATE OR REPLACE FUNCTION update_content_tsvector()
RETURNS trigger AS $$
BEGIN
    NEW.title_tsvector := content_title_to_tsvector(NEW.title);
    NEW.content_tsvector := content_body_to_tsvector(NEW.content);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_update_content_tsvector
    BEFORE INSERT OR UPDATE ON ak_contents
    FOR EACH ROW EXECUTE FUNCTION update_content_tsvector();

-- 在预计算列上创建索引
CREATE INDEX idx_contents_title_tsvector ON ak_contents USING gin(title_tsvector);
CREATE INDEX idx_contents_content_tsvector ON ak_contents USING gin(content_tsvector);

2. 使用预计算列的查询

-- 使用预计算tsvector列进行更快的搜索
SELECT id, title, ts_rank(title_tsvector, query) as rank
FROM ak_contents, to_tsquery('simple', 'AI & technology') query
WHERE title_tsvector @@ query
ORDER BY rank DESC;

语言配置说明

当前使用 'simple' 配置以确保最大兼容性。如果需要特定语言的词干提取和停词过滤，可以：

-- 中文配置 (需要安装中文分词扩展)
SELECT to_tsvector('zhparser', '人工智能技术发展');

-- 英文配置
SELECT to_tsvector('english', 'artificial intelligence technology');

-- 多语言支持函数示例
CREATE OR REPLACE FUNCTION content_multilang_to_tsvector(content TEXT, lang TEXT DEFAULT 'simple')
RETURNS tsvector
LANGUAGE sql IMMUTABLE STRICT
AS $$
    SELECT CASE 
        WHEN lang = 'zh' THEN to_tsvector('zhparser', COALESCE(content, ''))
        WHEN lang = 'en' THEN to_tsvector('english', COALESCE(content, ''))
        ELSE to_tsvector('simple', COALESCE(content, ''))
    END;
$$;

注意事项

IMMUTABLE函数: 确保函数标记为IMMUTABLE，这样PostgreSQL才能在索引中使用它们
STRICT函数: 使用STRICT避免处理NULL值的复杂性
COALESCE: 在函数内部处理NULL值，确保返回有效的tsvector
索引维护: 函数式索引会在相关列更改时自动更新，但比普通索引稍慢
查询性能: 使用这些函数的查询会自动利用相应的GIN索引

测试验证

-- 测试函数
SELECT content_title_to_tsvector('AI技术发展趋势分析');
SELECT content_body_to_tsvector('人工智能正在改变世界');

-- 测试索引使用 (EXPLAIN ANALYZE)
EXPLAIN ANALYZE 
SELECT * FROM ak_contents 
WHERE content_title_to_tsvector(title) @@ to_tsquery('simple', 'AI');

修复完成后，全文搜索索引应该能够正常创建和使用，不会再出现IMMUTABLE函数错误。

5.2 KiB Raw Permalink Blame History Unescape Escape