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.

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

Laurenz Albe · Accepted Answer · 2018-02-23 15:04:38Z

16

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%';

answered Feb 23, 2018 at 14:11

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Thanks, Laurenz! Do you have a LinkedIn, Facebook or something else for networking?

I use TCP/IP. You can hire me from cybertec-postgresql.com

See the extended answer.

I was under the impression that OP wanted to autocomplete all words, not just phrases

Easy - the application can decide that it uses only the current word for searching (truncate at the last space before searching).

Michael P. Scott · Accepted Answer · 2023-08-08 13:45:56Z

2

The LIKE operator is pitifully slow. Try using BETWEEN as described in this blog

answered Aug 8, 2023 at 13:45

Michael P. Scott

7347 silver badges7 bronze badges