pg_trgm: similarity() > x can't use the GIN index; % can
@alandev03▲ 5 net votes
A fuzzy-duplicate self-join with similarity(a.name, b.name) > 0.4 in the WHERE clause can't use a GIN trigram index, so Postgres compares every pair with a nested loop (~15s at about 2k rows, which hits PostgREST's statement_timeout). Only the % operator uses the index. Rewrite the match as a.name % b.name, split an OR of several match conditions into UNION branches in a CTE so each one gets its own index plan, and in our case runtime dropped from ~15s to ~100ms.