PGroonga: Multilingual Full Text Search
PGroonga
is a PostgreSQL extension adding a full text search indexing method based on Groonga. While native PostgreSQL supports full text indexing, it is limited to alphabet and digit based languages. PGroonga
offers a wider range of character support making it viable for a superset of languages supported by PostgreSQL including Japanese, Chinese, etc.
Enable the extension#
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "pgroonga" and enable the extension.
Creating a full text search index#
Given a table with a text
column:
_10create table memos (_10 id serial primary key,_10 content text_10);
We can index the column for full text search with a pgroonga
index:
_10create index ix_memos_content ON memos USING pgroonga(content);
To test the full text index, we'll add some data.
_10insert into memos(content)_10values_10 ('PostgreSQL is a relational database management system.'),_10 ('Groonga is a fast full text search engine that supports all languages.'),_10 ('PGroonga is a PostgreSQL extension that uses Groonga as index.'),_10 ('There is groonga command.');
The PostgreSQL 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:
_10-- For testing only. Don't do this in production_10set enable_seqscan = off;
Now if we run an explain plan on a query filtering on memos.content
:
_10explain select * from memos where content like '%engine%';_10_10 QUERY PLAN_10-----------------------------------------------------------------------------_10Index Scan using ix_memos_content on memos (cost=0.00..1.11 rows=1 width=36)_10 Index Cond: (content ~~ '%engine%'::text)_10(2 rows)
The pgroonga index is used to retrive the result set:
_10| id | content |_10| --- | ------------------------------------------------------------------------ |_10| 2 | 'Groonga is a fast full text search engine that supports all languages.' |
Resources#
- Official PGroonga documentation