Mastering PostgreSQL 10

Defining GIN indexes

If you want to apply text search to a column or a group of columns, there are basically two choices:

• Create a functional index using GIN
• Add a column containing ready-to-use tsvectors and a trigger to keep them in sync

In this section, both options will be outlined. To show how things work, I have created some sample data:

test=# CREATE TABLE t_fts AS SELECT comment  FROM pg_available_extensions;SELECT 43 

Indexing the column directly with a functional index is definitely a slower but more space efficient way to get things done:

test=# CREATE INDEX idx_fts_func ON t_fts         USING gin(to_tsvector('english', comment)); 
CREATE INDEX

Deploying an index on the function is easy, but it can lead to some overhead. Adding a materialized ...