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 ?

Demography

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

Shooter

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

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

See the extended answer. – Shooter

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

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

Answer 2

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

Hypsography