Postgres ilike combined with similarity pg_trgm?

Question

I'm experimenting with pg_trgm in my codebase for search. So far it works really well for search queries > 4 characters, which makes sense given how pg_trgm works.

There are however several situations where exact matches aren't returned with queries that are two characters long. For example, looking for a name of 'L2 Kids':

SELECT "paid_sessions".* FROM "paid_sessions" WHERE (paid_sessions.name ILIKE '%L2%');

Will return the row with the name 'L2 Kids', while the pg_trgrm similarity operator:

SELECT "paid_sessions".* FROM "paid_sessions" WHERE (name % 'L2') ORDER BY similarity(name, 'L2') DESC;

returns nothing.

How do you handle these exact match deficiency in this case? Should I simply use ilike for searches less than 3 characters? Should I combine the two somehow?

Answer 1

With the default settings, your query using % would return 'L2 kids'. You must have changed the pg_trgm.similarity_threshold in order to not find that result.

Should I simply use ilike for searches less than 3 characters?

Do you want ILIKE searches? If so, then you should do that. Otherwise, you should not. Maybe you should use % but with a different threshold. Maybe you should use %>. Maybe you want @@ with tsvectors. A single example is a poor way of explaining what it is you want.