数据库

PGroonga：多语言全文搜索

PGroonga 是一个 PostgreSQL 扩展，它基于 Groonga 添加了全文搜索索引方法。虽然原生 PostgreSQL 支持全文索引，但仅限于基于字母和数字的语言。PGroonga 提供了更广泛的字符支持，使其适用于包括日语、中文等在内的 PostgreSQL 支持的语言超集。

启用扩展

  1. 进入 Dashboard 中的数据库页面
  2. 点击侧边栏中的 Extensions
  3. 搜索 pgroonga 并启用该扩展

创建全文搜索索引

假设有一个包含 text 列的表：

1
2
3
4
create table memos (  id serial primary key,  content text);

我们可以使用 pgroonga 索引为该列创建全文搜索索引：

1
create index ix_memos_content ON memos USING pgroonga(content);

为了测试全文索引，我们先添加一些数据。

1
2
3
4
5
6
insert into memos(content)values  ('PostgreSQL is a relational database management system.'),  ('Groonga is a fast full text search engine that supports all languages.'),  ('PGroonga is a PostgreSQL extension that uses Groonga as index.'),  ('There is groonga command.');

Postgres查询规划器非常智能，它知道对于极小的表来说，扫描整个表比加载索引更快。为了强制使用索引，我们可以禁用顺序扫描：

1
2
-- 仅用于测试。生产环境中不要这样做set enable_seqscan = off;

现在如果我们对 memos.content 进行过滤查询并查看执行计划：

1
2
3
4
5
6
7
explain select * from memos where content like '%engine%';                               QUERY PLAN-----------------------------------------------------------------------------Index Scan using ix_memos_content on memos  (cost=0.00..1.11 rows=1 width=36)  Index Cond: (content ~~ '%engine%'::text)(2 rows)

pgroonga 索引被用于检索结果集：

1
2
3
| id  | content                                                                  || --- | ------------------------------------------------------------------------ || 2   | 'Groonga is a fast full text search engine that supports all languages.' |

全文搜索

&@~ 运算符用于执行全文搜索。它会返回所有匹配结果。与 LIKE 运算符不同，pgroonga 可以执行不区分大小写的关键词搜索。

请看以下示例：

1
select * from memos where content &@~ 'groonga';

搜索结果：

1
2
3
4
5
6
id | content  ----+------------------------------------------------------------------------2 | Groonga is a fast full text search engine that supports all languages.3 | PGroonga is a PostgreSQL extension that uses Groonga as index.4 | There is groonga command.(3 rows)

匹配所有搜索词

要查找内容同时包含 postgres 和 pgroonga 这两个词的所有备忘录，只需用空格分隔每个词：

1
select * from memos where content &@~ 'postgres pgroonga';

搜索结果：

1
2
3
4
id | content  ----+----------------------------------------------------------------3 | PGroonga is a PostgreSQL extension that uses Groonga as index.(1 row)

匹配任意搜索词

要查找内容包含 postgres 或 pgroonga 任意一个词的所有备忘录，使用大写的 OR：

1
select * from memos where content &@~ 'postgres OR pgroonga';

搜索结果：

1
2
3
4
5
id | content  ----+----------------------------------------------------------------1 | PostgreSQL is a relational database management system.3 | PGroonga is a PostgreSQL extension that uses Groonga as index.(2 rows)

带否定条件的搜索

要查找内容包含 postgres 但不包含 pgroonga 的所有备忘录，使用 - 符号：

1
select * from memos where content &@~ 'postgres -pgroonga';

搜索结果：

1
2
3
4
id | content  ----+--------------------------------------------------------1 | PostgreSQL is a relational database management system.(1 row)

相关资源