数据库

PGroonga：多语言全文搜索

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

启用扩展#

  1. 转到仪表板中的 数据库 页面。
  2. 在侧边栏中单击 扩展。
  3. 搜索 pgroonga 并启用该扩展。

创建全文搜索索引#

给定一个包含 text 列的表

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

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

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

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

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

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

1
-- For testing only. Don't do this in production
2
set enable_seqscan = off;

现在，如果我们对 memos.content 运行一个 explain plan

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

pgroonga 索引被用来检索结果集

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

全文搜索#

&@~ 运算符执行全文搜索。它返回任何匹配的结果。与 LIKE 运算符不同，pgroonga 可以搜索包含关键字的任何文本，不区分大小写。

以下是一个例子

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

以及结果

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

匹配所有搜索词#

要查找内容包含 postgres 和 pgroonga 这两个词的所有备忘录，我们可以只用空格分隔每个词

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

以及结果

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

匹配任何搜索词#

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

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

以及结果

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

搜索匹配带有否定词的词语#

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

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

以及结果

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

资源#