I have seen this presentation from a company that used PG Full Text search for a pretty complex use case. Interesting -> http://matheusoliveira.s3-website-us-east-1.amazonaws.com/pr... (updated thanks to ddevault)
http://matheusoliveira.s3-website-us-east-1.amazonaws.com/pr...
From what i know - PG fulltext seaarch does not implement relevance.
SELECT ..
-- Get the fts
IN ( FTS QUERY)
ORDER BY
-- The relevance is hardcoded? mayber in another table that store th rankings?
(
Products,
Inventory,
Invoices,..
)
This have the nice property that our search results are ver good and better: Never return non-sensical stuff! (like searching for a apple in the store and get and blog post!)
Given a certain variable, tfidf/bm-25 will order by relevance and not by match. So it answers the question, what if the name match was off by two characters and the inventory number is less than 200.
tf-idf does not tell you what to order by...but it takes care of all the edge cases of ordering.
now if ur not using text match anywhere and only using business variables...then this entire thread is not for u. But FTS and lucene attack full text search primarily, and that's where the relevance vs ordering discussion comes from
this is important, cos if a word is too common, its considered less significant for a document match. When we calculate IDF, it will be very low for the most occurring words such as stop words (“is” is present in almost all of the documents, and tf-idf will give a very low value to that word).
there's someone who implemented this, its pretty cool. but definitely performance takes a hit versus a separate elasticsearch cluster. https://codebots.com/crud/How-to-efficiently-search-text-usi...