postgres16中文全文检索

前言

postgres 小集群情况下 实在是比mysql要厉害太多了
不管是从功能性 还是从性能
mysql也有全文检索 不过mysql太耗费内存了 个人服务器承载不起
只能用pgsql 这里记录一下pgsql的全文检索安装到使用

debian-apt安装postgresql
jieba分词插件
aliyun上pg_jieba插件使用

环境准备

• 安装postgres

sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt -y install postgresql
# 安装 postgres server 的开发包  避免后续安装jieba插件无法找到postgres.h  
sudo apt -y install postgresql-server-dev-16	
# 查看有没有postgres server开发包相关文件    
ls  /usr/include/postgres/16/server    

• 安装pg_jieba插件

git clone https://github.com/jaiminpan/pg_jieba

# initilized sub-project
cd pg_jieba && git submodule update --init --recursive
mkdir build
cd build
#增加设定postgres的server开发包组件配置   cmake ..
cmake -DPostgreSQL_TYPE_INCLUDE_DIR=/usr/include/postgresql/16/server ..
make
make install 
# try "sudo make install"

注意安装插件一定要有 Postgres server lib postgresql-server-dev-16

• 插件

-- 启用pg_jieba插件 
CREATE EXTENSION pg_jieba;
-- 禁用pg_jieba插件  
drop extension pg_jieba;
select *
from to_tsquery('jiebacfg', '是拖拉机学院手扶拖拉机专业的。不用多久，我就会升职加薪，当上CEO，走上人生巅峰。');

中文词库
jiebamp: 使用mp
jiebahmm: 使用hmm
jiebacfg: 混合mp hmm 大多数情况推荐使用
jiebaqry: 先使用Mix，然后使用full。与网络搜索引擎使用的类似。

使用检索

主要就是 to_tsvector 和 to_tsquery 使用

官方文档

无索引使用

SELECT *
FROM 表名
WHERE to_tsvector('jiebacfg', 字段名) @@ to_tsquery('jiebacfg', '搜索内容');

GIN索引

-- 建立单列索引
CREATE INDEX 索引名 ON 表名 USING GIN (to_tsvector('jiebacfg', 字段名));
-- 建立多列索引
CREATE INDEX 索引名 ON 表名 USING GIN (to_tsvector('jiebacfg', 字段A || ' ' || 字段A));

--单据建立列 
ALTER TABLE 表名
    ADD COLUMN tsvector字段名 tsvector GENERATED ALWAYS AS (to_tsvector('jiebacfg', coalesce(字段A, '') || ' ' ||
                                                                                    coalesce(字段B, ''))) STORED;
CREATE INDEX 索引名称 ON 表名 USING GIN (tsvector字段名);

-- 触发器 
-- tsvector_update_trigger(tsvector_column_name, config_name, text_column_name [, ... ]);
-- tsvector_update_trigger_column(tsvector_column_name, config_column_name, text_column_name [, ... ]);
CREATE TRIGGER 触发器名称
    BEFORE INSERT OR UPDATE
    ON 表名
    FOR EACH ROW
EXECUTE FUNCTION
    tsvector_update_trigger(tsv字段名, 'jiebacfg', 字段A, 字段B...);

-- 收集信息
--ts_stat(sqlquery text, [ weights text, ]
--OUT word text, OUT ndoc integer,
--        OUT nentry integer) returns setof record

示例

• 建立带tsvector字段的表

CREATE TABLE t_test
(
    title      text,
    body       text,
    search_tsv tsvector
);

• 建立触发器

要注意这里需要标记 schema 也就是 schema.jiebacfg SELECT current_schema();

CREATE TRIGGER t_test_search_tsv_trigger
    BEFORE INSERT OR UPDATE
    ON t_test
    FOR EACH ROW
EXECUTE FUNCTION
    tsvector_update_trigger(search_tsv, 'public.jiebacfg', title, body);   

• 建立索引

CREATE INDEX idx_t_test_search_tsv ON t_test USING GIN (search_tsv);

• 更新历史数据

UPDATE t_test
SET search_tsv = to_tsvector('jiebacfg', coalesce(title, '') || ' ' || coalesce(body, ''));

• 搜索

SELECT *
FROM t_test
WHERE search_tsv @@ to_tsquery('jiebacfg', '搜索内容');

问题

• 每条链接第一次检索很慢
  因为默认配置情况下 是每次建立链接的时候 会引入pg_jieba.so（windows下为pg_jieba.dll）依赖 所以在链接建立第一次访问会很慢
  只需要配置 postgresql.conf shared_preload_libraries就行
  apt安装的配置目录: /etc/postgresql/16/main

sudo vim /etc/postgresql/16/main/postgresql.conf 
# 检查安装目录有pg_jieba.so 没有 
ls /usr/lib/postgresql/16/lib |grep pg_jieba.so
# 添加到 postgresql.conf中 
shared_preload_libraries = 'pg_jieba.so'  
# 重启 
sudo systemctl restart postgresql

总结

postgres数据库的全文检索 配合jieba 还可以
适合就用一个db实现各种复杂的查询功能