Fuzzy searching through multiple fields in postgreSQL

Question

I've to do fuzzy search on multiple fields (in an attempt to create something like autocomplete similar to product search in amazon). I tried this through ElasticSearch but was wondering if there's something equivalent to it in postgreSQL.

Here's sample code for elasticsearch: (both the fields, title and description, are index as type: text)

GET index_name/_search
{
 "query": {
 "bool": {
 "must": [
 {
 "multi_match": {
 fields: ["description", "title"],
 query: "postgres",
 fuzziness: 1
 }
 }
 ]
 }
 }
}

I've tried the same using pg_tram in postgreSQL, it worked for one field with similarity() (% operator) but I don't know how to extend this on multiple fields.

This is what I did in postgreSQL, not sure if it's good way though:

select * from table t
 where similarity("title", "postgres") > 0.5;

select * from table t
 where similarity("title", "postgres") > 0.5 OR similarity("description", "postgres") > 0.5;

Also is there any way to introduce fuzziness in ts_vector (FTS) query ?
Will appreciate any help/guidance in this context.
P.S: let me know if my description is missing something.

Thanks :)

• jjanes: You have to write it with %. Written using the function call form, it will not use the index
• cicada_: Thanks @jjanes I'll make sure to use the operator instead.

Answer 1

You could use the word similarity operator <%:

SELECT ... FROM tab
WHERE 'postgres' <% concat(title, ' ', description);

To speed that up, you can create a GIN index on that expression:

CREATE INDEX ON tab USING gin (concat(title, ' ', description) gin_trgm_ops);

You can adjust the parameter pg_trgm.word_similarity_threshold to get the desired sensitivity.

• cicada_: Thanks for your answer. Just one more doubt let's say if this fuzzy search is on fields from separate tables then would it be better to create a Materialized view on union of those fields (from separate tables) and create an index on the new field in this view ?
• Laurenz Albe: No, then you should query each table separately and perhaps union the results.