PGroonga：多语言全文搜索

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

启用扩展 #

🌐 Enable the extension

在仪表板中转到数据库页面。
点击侧边栏的扩展。
搜索 pgroonga 并启用这个扩展。

创建全文搜索索引 #

🌐 Creating a full text search index

给定一个有 text 列的表：

🌐 Given a table with a text column:

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

我们可以用 pgroonga 索引对这一列进行全文搜索:

🌐 We can index the column for full text search with a pgroonga index:

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

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

🌐 To test the full text index, we'll add some data.

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

Postgres 查询优化器足够聪明，它知道对于非常小的表，扫描整个表比加载索引更快。要强制使用索引，我们可以禁用顺序扫描：

🌐 The Postgres query planner is smart enough to know that, for extremely small tables, it's faster to scan the whole table rather than loading an index. To force the index to be used, we can disable sequential scans:

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

现在如果我们对一个在 memos.content 上进行过滤的查询运行执行计划：

🌐 Now if we run an explain plan on a query filtering on memos.content:

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 索引用于检索结果集：

🌐 The pgroonga index is used to retrieve the result set:

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

全文搜索 #

🌐 Full text search

&@~ 操作符执行全文搜索。它会返回任何匹配的结果。不同于 LIKE 操作符，pgroonga 可以搜索包含关键词的任意文本，并且不区分大小写。

🌐 The &@~ operator performs full text search. It returns any matching results. Unlike LIKE operator, pgroonga can search any text that contains the keyword case insensitive.

举个例子：

🌐 Take the following example:

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

结果是：

🌐 And the result:

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

匹配所有搜索词 #

🌐 Match all search words

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

🌐 To find all memos where content contains BOTH of the words postgres and pgroonga, we can use space to separate each words:

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

结果是：

🌐 And the result:

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

匹配任意搜索词 #

🌐 Match any search words

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

🌐 To find all memos where content contain ANY of the words postgres or pgroonga, use the upper case OR:

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

结果是：

🌐 And the result:

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

搜索匹配带有否定的词 #

🌐 Search that matches words with negation

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

🌐 To find all memos where content contain the word postgres but not pgroonga, use - symbol:

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

结果是：

🌐 And the result:

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

资源 #

🌐 Resources

官方 PGroonga 文档