pg_search
ParadeDB BM25算法全文检索插件，ES全文检索
Module:
Categories:
扩展总览
PIGSTY 第三方扩展： pg_search ： ParadeDB BM25算法全文检索插件，ES全文检索
基本信息
- 扩展编号： 2100
- 扩展名称：
pg_search - 标准包名：
pg_search - 扩展类目：
FTS - 开源协议： AGPLv3
- 官方网站： https://github.com/paradedb/paradedb/tree/dev/pg_search
- 编程语言： Rust
- 其他标签：
pgrx - 备注信息： 无
元数据
- 默认版本： 0.15.8
- PG大版本：
17,16,15,14 - 动态加载： 无需动态加载
- 需要DDL： 需要执行
CREATE EXTENSIONDDL - 可重定位： 可以重定位安装至其他模式下
- 信任程度： 受信任，无需超级用户，带
CREATE权限的用户可以直接创建 - 所需模式：
paradedb - 所需扩展： 无
软件包
- RPM仓库：PIGSTY
- RPM包名：
pg_search_$v - RPM版本：
0.15.4 - RPM依赖：无
- DEB仓库：PIGSTY
- DEB包名：
postgresql-$v-pg-search - DEB版本：
0.15.4 - DEB依赖：无
最新版本
| 系统 | 架构 | PG17 | PG16 | PG15 | PG14 | PG13 |
|---|---|---|---|---|---|---|
el8 |
x86_64 |
pg_search_17 PIGSTY 0.15.8 |
pg_search_16 PIGSTY 0.15.8 |
pg_search_15 PIGSTY 0.15.8 |
pg_search_14 PIGSTY 0.15.8 |
|
el8 |
aarch64 |
pg_search_17 PIGSTY 0.15.8 |
pg_search_16 PIGSTY 0.15.8 |
pg_search_15 PIGSTY 0.15.8 |
pg_search_14 PIGSTY 0.15.8 |
|
el9 |
x86_64 |
pg_search_17 PIGSTY 0.15.8 |
pg_search_16 PIGSTY 0.15.8 |
pg_search_15 PIGSTY 0.15.8 |
pg_search_14 PIGSTY 0.15.8 |
|
el9 |
aarch64 |
pg_search_17 PIGSTY 0.15.8 |
pg_search_16 PIGSTY 0.15.8 |
pg_search_15 PIGSTY 0.15.8 |
pg_search_14 PIGSTY 0.15.8 |
|
d12 |
x86_64 |
postgresql-17-pg-search PIGSTY 0.15.8 |
postgresql-16-pg-search PIGSTY 0.15.8 |
postgresql-15-pg-search PIGSTY 0.15.8 |
postgresql-14-pg-search PIGSTY 0.15.8 |
|
d12 |
aarch64 |
postgresql-17-pg-search PIGSTY 0.15.8 |
postgresql-16-pg-search PIGSTY 0.15.8 |
postgresql-15-pg-search PIGSTY 0.15.8 |
postgresql-14-pg-search PIGSTY 0.15.8 |
|
u22 |
x86_64 |
postgresql-17-pg-search PIGSTY 0.15.8 |
postgresql-16-pg-search PIGSTY 0.15.8 |
postgresql-15-pg-search PIGSTY 0.15.8 |
postgresql-14-pg-search PIGSTY 0.15.8 |
|
u22 |
aarch64 |
postgresql-17-pg-search PIGSTY 0.15.8 |
postgresql-16-pg-search PIGSTY 0.15.8 |
postgresql-15-pg-search PIGSTY 0.15.8 |
postgresql-14-pg-search PIGSTY 0.15.8 |
|
u24 |
x86_64 |
postgresql-17-pg-search PIGSTY 0.15.8 |
postgresql-16-pg-search PIGSTY 0.15.8 |
postgresql-15-pg-search PIGSTY 0.15.8 |
postgresql-14-pg-search PIGSTY 0.15.8 |
|
u24 |
aarch64 |
postgresql-17-pg-search PIGSTY 0.15.8 |
postgresql-16-pg-search PIGSTY 0.15.8 |
postgresql-15-pg-search PIGSTY 0.15.8 |
postgresql-14-pg-search PIGSTY 0.15.8 |
扩展安装
使用 pig 命令行工具安装 pg_search 扩展：
pig ext install pg_search
使用 Pigsty剧本 安装 pg_search 扩展：
./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pg_search"]}' # -l <集群名>
dnf install pg_search_17;
dnf install pg_search_16;
dnf install pg_search_15;
dnf install pg_search_14;
apt install postgresql-17-pg-search;
apt install postgresql-16-pg-search;
apt install postgresql-15-pg-search;
apt install postgresql-14-pg-search;
使用以下 SQL 命令在已经安装此扩展插件的 PG 集群上 启用 pg_search 扩展：
CREATE EXTENSION pg_search;
使用方法
https://docs.paradedb.com/documentation/getting-started/quickstart
CREATE EXTENSION pg_search;
ALTER SYSTEM SET paradedb.pg_search_telemetry TO 'off';
CALL paradedb.create_bm25_test_table(
schema_name => 'public',
table_name => 'mock_items'
);
SELECT description, rating, category FROM mock_items LIMIT 3;
CALL paradedb.create_bm25(
index_name => 'search_idx',
schema_name => 'public',
table_name => 'mock_items',
key_field => 'id',
text_fields => paradedb.field('description', tokenizer => paradedb.tokenizer('en_stem')) ||
paradedb.field('category'),
numeric_fields => paradedb.field('rating')
);
SELECT description, rating, category
FROM search_idx.search('(description:keyboard OR category:electronics) AND rating:>2',limit_rows => 5);
CALL paradedb.create_bm25(
index_name => 'ngrams_idx',
schema_name => 'public',
table_name => 'mock_items',
key_field => 'id',
text_fields => paradedb.field('description', tokenizer => paradedb.tokenizer('ngram', min_gram => 4, max_gram => 4, prefix_only => false)) ||
paradedb.field('category')
);
SELECT description, rating, category
FROM ngrams_idx.search('description:blue');