0%

postgres16中文全文检索

前言

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

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

环境准备

  • 安装postgres
1
2
3
4
5
6
7
8
9
10
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插件
1
2
3
4
5
6
7
8
9
10
11
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

  • 插件
1
2
3
4
5
6
-- 启用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 使用

官方文档

无索引使用
1
2
3
SELECT *
FROM 表名
WHERE to_tsvector('jiebacfg', 字段名) @@ to_tsquery('jiebacfg', '搜索内容');
GIN索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 建立单列索引
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字段的表
1
2
3
4
5
6
CREATE TABLE t_test
(
title text,
body text,
search_tsv tsvector
);
  • 建立触发器

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

1
2
3
4
5
6
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);
  • 建立索引
1
CREATE INDEX idx_t_test_search_tsv ON t_test USING GIN (search_tsv);
  • 更新历史数据
1
2
UPDATE t_test
SET search_tsv = to_tsvector('jiebacfg', coalesce(title, '') || ' ' || coalesce(body, ''));
  • 搜索
1
2
3
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
1
2
3
4
5
6
7
8
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实现各种复杂的查询功能