PostgreSQL built-in FTS provides a score for each row based just on the data for that row.
Relevance algorithms like BM25 take overall corpus statistics into account. If you search for a bunch of words and some of them are less common than others in the overall set of documents, documents that match THOSE words will score higher than matches for other words in your search.
That's what all of these additional extensions are providing.
If you read deep into this, they claim much better performance than the built in search; they also imply that the built-in search is missing features they provide but don’t make clear which ones (I think it is just support in the same index for queries covering other conditions on other columns, because every other feature they claim seems to line up with the built in search features, which have been around for about 20 years.)
- superior performance
- superior operational overhead
- no second copy of data in tsvector form
- BM25 scoring support with optimized top-k output
- runtime configurable scoring knobs
- expression-attached score boosting
- sophisticated span query support -- this is proximity search on steroids (https://github.com/planetscale/lead/tree/main/tinql/docs)
- lossless term positions
- index-answerable negative expressions (find all docs that don't contain a word)
- full document hit highlighting
- optimized exact `count(\*)`
- term expansion via any of fuzzy matching, wildcards, regular expressions, and dictionary ranges
- intentionally smaller user-facing SQL API surface
There's a lot we didn't cover in the announcement blog. I'm sure we'll do more as time goes on.
As an aside, something I personally think is cool, and I suppose you can do this with Postgres' built-in `@@` too, is that you can use TIN's full query language (linked above) against any text datum. This is a valid query:
SELECT pid, query
FROM pg_stat_activity
WHERE query ==> 'select OR copy'
in other words, you don't need an index at all to use TIN's full query language against any text field in any query.
The built in search can't do any scoring mechanism that involves corpus-wide stats, so things like tfidf and bm25 are right out. If you don't need that then great, but in my experience the results are much worse.
If you mean tsvector/tsquery - https://www.postgresql.org/docs/9.6/textsearch-intro.html - it's very good, but it's missing an important feature: ranking based on the overall document collection.
PostgreSQL built-in FTS provides a score for each row based just on the data for that row.
Relevance algorithms like BM25 take overall corpus statistics into account. If you search for a bunch of words and some of them are less common than others in the overall set of documents, documents that match THOSE words will score higher than matches for other words in your search.
That's what all of these additional extensions are providing.
> ranking based on the overall document collection.
Thanks for pointing out a real difference.
If you read deep into this, they claim much better performance than the built in search; they also imply that the built-in search is missing features they provide but don’t make clear which ones (I think it is just support in the same index for queries covering other conditions on other columns, because every other feature they claim seems to line up with the built in search features, which have been around for about 20 years.)
Over Postgres' FTS, TIN provides at least:
There's a lot we didn't cover in the announcement blog. I'm sure we'll do more as time goes on.As an aside, something I personally think is cool, and I suppose you can do this with Postgres' built-in `@@` too, is that you can use TIN's full query language (linked above) against any text datum. This is a valid query:
in other words, you don't need an index at all to use TIN's full query language against any text field in any query.For most applications an index is a feature not a bug because I can tailor it and inspect.
The built in search can't do any scoring mechanism that involves corpus-wide stats, so things like tfidf and bm25 are right out. If you don't need that then great, but in my experience the results are much worse.
It has it.
Incredible? No.
I think it’s fantastic.
You want to use this thing instead?
...yes, obviously?
Keep me posted. I would love to hear the results.