Sort search results by relevance

[Nutomic]

By default search results are sorted using "Top all time", which means that most upvoted items are shown first. So relevant results with less upvotes can actually be buried further down.

There is a Postgres function ts_rank to help with this sorting. However it seems to require an extra tsvector column in the searched data.

References:
https://dev.to/tdournet/how-to-optimise-postgresql-like-and-ilike-queries-494i
https://stackoverflow.com/questions/12933805/best-way-to-use-postgresql-full-text-search-ranking?rq=4

[flamingos-cant]

If we do this we should drop the trigram indexes to save space as having both is redundant. We could also avoid dropping the search_combined table by putting it there as the main reason search is currently so slow is because postgres can't use multiple GIN indexes in a query, so is only using the trigram index on one table and doing a sequential scan on all the others.

[dessalines]

I've used ts_vectors in other projects with good success (and could add them fairly easily), and they can do relevance, but I don't think they're a good idea here.

Mainly because they're only suited to one specific language at a time (IE english), so the way you store / cache their tokenized results in a column, would only work for english.