I'm trying to build an autocomplete feature using postgres.

Here's the steps that I followed:

  1. Since the autocomplete is to be done on fields across 2 fields from different tables, I created a materialized view where I merged the two fields on which I'm searching.
  2. Created pg_trgm extension.
  3. Created a gin index on the new merged_field (this is the name of the new field).
  4. Querying for particular products like "ball" using similarity % operator.

The materialized view had a total of 3 million rows.

Here's the list of issues that I encountered :

  1. The queries are slow, sometimes it was making seq. scan, since the word is quite common like "ball" it's present in almost 80% of the rows.
  2. Can't decide on threshold pg_trgm.similarity_threshold, initially I left it at 0.3 but noticed that some records that have 200 chars length were not treated as probable hit when I looked for "bat" (even though there are not that many entries for this word).
  3. As the number of search words increased the query time also starts increasing. I tested this by giving one genuine term, that's there in db, while rest of the words weren't.

Here's the query that I'm using:

create index on my_schema.mvw_autocomplete using gin (merged_fields gin_trgm_ops);

explain analyze select * from my_schema.mvw_autocomplete where merged_fields % 'bat arkham knight';

This query took 9 seconds to run, though it did used index as per query plan.

Is there anything that I can do to improve the runtime and fix the other issues. And also in Explain Analyze output I can see that there's no mention of parallel workers for this one, whereas there are other queries that execute faster and are making use of parallel workers as well.

If anything is missing please let me know.

Peace :)

1 Answer 1


Autocompletion is usually for things which complete the prefix, not for proposing things kind of similar to the entire phrase. That is a different use case and is usually done differently (e.g. waiting for the person to submit, rather than doing it as they type).

And both of those are usually for words and short phrases, neither makes much sense for 200 character phrases.

And you must be doing something quite weird if 'ball' is in 80% of your rows. Maybe you should just declare that a stop word (or sequence of characters) and remove it from the mat view.

noticed that some records that have 200 chars length were not treated as probable hit when I looked for "bat"

Sure. Unless you have one word repeated 50 times, how similar can a 200 char phrase really be to 3 letter word? The problem is not in setting similarity_threshold, it is that you are just plain using the wrong tool. Maybe you should be dividing your phrases into smaller chunks, or using FTS, or using %> or %>>, or using LIKE '%bat%'.

  • I also tried changing the value of gin_fuzzy_search_limit, which I came across here postgresql.org/docs/current/gin-tips.html. Now query time for FTS was reduced by more than half. But nothing changed for fuzzy search i.e similarity comparison. I'll try what you've suggested and will update ya.
    – cicada_
    May 26, 2022 at 4:32
  • I did tried with LIKE '%bat%' and it was faster than the similarity one. But what if user makes a typo in that case it'll not be a match. So that's why I thought of using similarity. And also tried to do prefix search on ts_vector of the merged_fields to_tsquery('ball:* | foot:*'), but for this prefix search as I add more terms the execution time also increases. changing | op with &, it was faster as it's more specific but the only problem is typos.
    – cicada_
    May 26, 2022 at 7:21
  • Typos are usually addressed one word at a time. That is, our mat view would have one row for each distinct word in the source material (perhaps only words that exceed some frequency count, to avoid suggesting/recognizing typos present in the source material as correct spellings)
    – jjanes
    May 26, 2022 at 18:57
  • so for detecting typos, I'll have to create a separate view / table, that'll contain the unique words and then make a similarity check for one word at a time and suggest the user that there's a typo for the given word, is that correct ?
    – cicada_
    May 27, 2022 at 4:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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