1

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 :)

2
  • You have to write it with %. Written using the function call form, it will not use the index
    – jjanes
    May 18 at 21:02
  • Thanks @jjanes I'll make sure to use the operator instead.
    – cicada_
    May 19 at 18:06

1 Answer 1

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.

2
  • 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 ?
    – cicada_
    May 19 at 18:05
  • No, then you should query each table separately and perhaps union the results. May 19 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.