D
D
Denis Sakharov2017-02-03 10:24:57
MySQL
Denis Sakharov, 2017-02-03 10:24:57

What is the correct way to do a full-text search on an incomplete match in PostgresQL?

I do autocomplete for the search line on the site.
Searching for 8 million text records, I ran into the following problem: postgres full-text search does not always return results for incomplete matches, and through ILIKE it turns out slowly. The same task in MySQL is solved by the queries listed below and is very fast.
Are there similar mysql queries in postgres? Can the problem be solved differently?

SELECT DISTINCT name FROM entities WHERE MATCH (name) AGAINST ('центр*' IN BOOLEAN MODE) LIMIT 10;
SELECT DISTINCT name FROM entities WHERE MATCH (name) AGAINST ('+энергос* +цен*' IN BOOLEAN MODE) LIMIT 10;

Answer the question

In order to leave comments, you need to log in

2 answer(s)
X
xmoonlight, 2017-02-03
@vorahkas

This is done using a "tree": a symbol is a node (ID) and a link to the one following it (there are several lines of them, of course). Such a search "tree" is created based on the text and ID-shniks are indexed.
After that - everything works instantly:
1. viewing depth - set dynamically, depending on the results found.
2. all intermediate requests (their results) - cached!
UPD:

Fly in! Do not be afraid! Read the manuscript ! (with)

A
Alexander Shelemetiev, 2017-02-03
@zoroda

Here is a description of the use of full text search along with fuzzy search. See F.41.5. Text search integration

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question