Is there a postgres fuzzy match faster than pg_trgm?

Question

I have a Postgres table with about 5 million records and I want to find the closest match to an input key. I tried using trigrams with the pg_trgm module, but it took roughly 5 seconds per query, which is too slow for my needs.

Is there a faster way to do fuzzy match within Postgres?

Answer 1

Soundex is an alternative fuzzy match, but it can be very fuzzy. I would stick with the trigram matching, if you can. Is there another criterion you could use to make the trigram search work on a smaller set of results?

Answer 2

It looks like the estimations of result size in your explain output are way off. This is not unexpected as it is very hard to estimate results of full text search well.

This causes Postgresql to use bad query plan. Try to disable bitmap scan (set enable_bitmapscan=off) and try again.

Answer 3

Depending on what you are looking for, Postgres can also do matches on regular expressions, instead of the standard "like" syntax. It may be a better fit for you.