On Mon, 2018-12-03 at 18:41 +0000, Scott Rankin wrote: > Upon further analysis, this is - unsurprisingly - taking place when we have > multiple prefixed search terms in a ts_query going against a tsvector index. > > We have roughly 30 million rows in the table, and the search column is > basically a concatenation of a location's name (think "Walmart #123456") and > its street address. > > We use these searches mostly for autocompleting of a location search. So the > search for that record above might be "Walmart 123", which we change to be > to_tsquery('walmart:* &123:*'). We prefix both terms to correct for > misspellings or lazy typing. > > Is it unrealistic to think that we could have sub-1000ms searches against > that size of a table? >
We've found trigram indexes to be much faster and more useful for these types of searches than full-text. https://www.postgresql.org/docs/10/pgtrgm.html Might be worth a try, if you haven't tested them before.