Index Types for Exact Match and ILIKE Search

Question

(Postgres 12)

I am implementing a text search that allows for both exact match and fuzzy (ILIKE) match:

attributes->>'ID' = 'some-id'
-- OR
attributes->>'ID' ILIKE '%some-%'

(The user declares whether the search will be exact or not, so only one of the above is ever included in the query)

I am putting indexes on the most commonly searched attributes, ID and Name. When I use a GIN w/ gin_trgm_ops, the fuzzy match is much faster. With a BTREE index, the exact match is much faster.

I can have both BTREE and GIN indexes, but I am wondering if that is strictly necessary. Is there a way to nudge postgres into using the GIN index for the exact match search?

Answer 1

Starting in v14, pg_trgm will automatically handle equality. It will not be as efficient as a btree index would be, but it might be good enough so that it is not worthwhile having both indexes.

Until then, the best solution would probably be to just use LIKE without adding % to the fore and aft of the search term (and indeed, escaping any % or _ which happen to exist in the search term) when you want the exact match.