PostgreSQL: Fastest Index For Autocomplete

Question

I have a table containing one column and 100 million rows of text. The text is simple 1 - 5 words sentences.

My goal is to have an ultra-fast query which could be used with an autocomplete. So the user types and I get results as fast as possible.

I tried many different indexes and the tsvector feature.

1. What is a good solution/combination for my needs?
2. What other solutions can i use to achieve my goal and an be setup up relativly quick ?

Answer 1

If you want to match the complete text with a prefix, the SQL query would be

SELECT words FROM phrases WHERE words LIKE 'user input%';

A regular B-Tree index with the text_pattern_ops operator class should do the trick.

If the phrases are too long to be indexed or you want to save space, index and query just a prefix:

CREATE INDEX ON phrases (substr(words, 1, 50) text_pattern_ops);

Then query with

SELECT words FROM phrases WHERE substr(words, 1, 50) LIKE 'user input%';