PGroonga versus textsearch and pg_trgm

PostgreSQL provides two full text search modules. They are textsearch and pg_trgm.

The textsearch module is a built-in full text search feature. It supports GiST and GIN indexes. If you use GiST or GIN index, you can improve full text search performance.

The pg_trgm module is a contrib module. pg_trgm provides full text search feature. pg_trgm also supports GiST and GIN indexes. If you use GiST or GIN index, you can improve full text search performance.

This documents describes about difference between PGroonga, textsearch and pg_trgm.

Characteristics

Here are characteristics of each modules.

PGroonga

PGroonga implements both language specific full text search and not language specific full text search. PGroonga uses not language specific full text search by default. Normally, PGroonga provides satisfactory full text search result by default.

PGroonga supports all languages by default because PGroonga uses not language specific full text search by default. You don't need to change source code.

PGroonga is fast for searching because it doesn't need "recheck".

PGroonga is also fast for searching while updating because it doesn't block searching while updating. PGroonga doesn't decrease search performance while updating.

PGroonga is also fast for updating.

PGroonga index is large because it keeps index target text that is already stored in PostgreSQL.

The textsearch module

The textsearch module implements language specific full text search. For example, English terms are stemmed. All of "work", "works" and "worked" are converted to "work". It'll improve recall.

Language specific full text search may improve full text search feature but "language specific" means someone implements "language specific" process. PostgreSQL 9.6.1 supports 15 languages such as English, French and Russian but other many languages aren't supported such as Asian languages including Japanese, Chinese and Korean.

The textsearch module is fast for searching because it doesn't need "recheck". (If you use weight, you need "recheck".)

The textsearch module can't process very large text. 1MiB - 1B is the max bytes of text. If you put 1MiB or more text, you will get the following error:

string is too long for tsvector (XXX bytes, max 1048575 bytes)

The pg_trgm module

The pg_trgm module implements not language specific full text search. But pg_trgm disables non ASCII characters support. It means that pg_trgm doesn't support many Asian languages such as Japanese and Chinese by default. You need to comment out the following line in contrib/pg_trgm/trgm.h to enable non ASCII characters support:

#define KEEPONLYALNUM

If you're using Debian based system, you can enable non ASCII characters support without changing pg_trgm source code by using C.UTF-8 locale.

The pg_trgm module is slow when many documents are matched and each document is long. Because pg_trgm need "recheck" after index search.

Summary

Benchmark

This section shows benchmark result against English Wikipedia. You can find benchmark script at postgresql.sh.

Summary

Here is the summary of full text search index creation benchmark result:

• PGroonga is the fastest module.

• pg_trgm is about 30% slower than PGroonga.

• textsearch is about 2 times slower than PGroonga.

Here is the summary of full text search index size benchmark result:

• pg_trgm is the smallest module.

• textsearch is about 60% larger than pg_trgm.

• PGroonga is about 5 times larger than pg_trgm.

Here is the summary of full text search performance benchmark result:

• Full text search performance by pg_trgm is very slower than other modules.

• Full text search performances of PGroonga and textsearch are similar.

• Full text search performance of Groonga, full text search engine by PGroonga, is faster than PGroonga and textsearch. It's 10 times or more faster.

Here is the full text search performance graph without pg_trgm because pg_trgm is too slow:

Environment

Here is the benchmark environment:

Version

Here are the software versions:

Data

Here are statistics of the target data:

It's important for textsearch that the max body length in byte is less than 1MiB. If it's equals to 1MiB - 1B or more, you can't create textsearch index with the following error message:

string is too long for tsvector (1618908 bytes, max 1048575 bytes)

Data load

Here is the benchmark result of data load. It's not related with full text search modules. Because any indexes aren't created yet.

Here is the SQL to load data:

COPY wikipedia FROM 'en-all-pages.csv' WITH CSV ENCODING 'utf8';

You can download the CSV data at en-all-pages.csv.xz.

Here is the SQL to define the wikipedia table:

CREATE TABLE wikipedia (
  id integer PRIMARY KEY,
  title text,
  text text
);

Index creation

Here are benchmark results of creating full text search indexes:

(*) Here is the error message of the case:

NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.

Here is the index definition of PGroonga:

CREATE INDEX wikipedia_index_pgroonga ON wikipedia
 USING pgroonga (title, text);

Here is the index definition of textsearch:

CREATE INDEX wikipedia_index_textsearch ON wikipedia
 USING GIN (to_tsvector('english', title),
            to_tsvector('english', text));

Here is the index definition of pg_trgm:

CREATE INDEX wikipedia_index_pg_trgm ON wikipedia
 USING GIN (title gin_trgm_ops, text gin_trgm_ops);

Full text search

Here are benchmark results of full text searches.

• "Groonga" means pgroonga.command('select ...') result. See also pgroonga.command.

• "Relative elapsed time" is the ratio between the target elapsed time and the fastest case elapsed time. Larger means slower.

Query: "animation"

Query: "database"

Query: "PostgreSQL OR MySQL"

Query: "America"