Learning PostgreSQL 11 - Third Edition

Full-text search indexing

GIN indexes are the preferred indexes for full-text searches. You can simply create an index on the document using the to_tsvector function, as follows:

CREATE INDEX ON <table_name> USING GIN (to_tsvector('english', <attribute name>));-- OR CREATE INDEX ON <table_name> USING GIN (to_tsvector(<attribute name>));

The query that's predicated is used to determine the index definition, for example, if the predicate looks like to_tsvector('english',..)@@ to_tsquey (.., the first index will be used to evaluate the query.

GIST can be used to index tsvector and tsquery, while GIN can be used to index tsvector only. The GIST index is lossy and can return false matches, so PostgreSQL automatically rechecks the returned result ...