Building indexes

Designing indexes for full-text search queries in PostgreSQL is a delicate art. Here are some tips to help guide developers to find an index definition that works for their application.

Please update this page with any new tips or tricks you might discover.

Using SQL

You must use GIN or GIST indexes for full text search.

Essentially you look in a SQL statement for the operator (% or @@) and grab the operand that isn't the query.

Then you do something like this:

CREATE INDEX my_cool_index ON my_cool_table USING gist (([expression]));

or (for trigram only)

CREATE INDEX my_cool_index ON my_cool_table USING gist (([expression]) pg_trgm_ops);

Trigram seems to underperform the other types.

You can also use a trigger to automatically populate a column of type tsvector with the [expression] from above, and then use the :tsvector_column option in pg_search to tell it to use that column instead. Then you'd make a very similar index as the above but put the column in instead of [expression]

You'll need enough columns in your table for EXPLAIN ANALYZE to show up using the index, and the index is brittle on the expression, so if you change the pg_search configuration, you'll have to change the index.

With ActiveRecord and/or Rails

As of ActiveRecord 4.0, you can add indexes and specify the "type" of index:

add_index(:posts, :tsv_body, using: 'gin')

For the triggers, you can use hair_trigger to add database triggers via your migrations:

create_trigger(compatibility: 1).on(:posts).before(:insert, :update) do
  "new.tsv := tsvector_update_trigger(tsv_body, 'pg_catalog.english', body);"
end

To update tsv_body column for existing records, you can simply run Post.find_each(&:touch).