full-text search: add a section about how search compares to contains

[matthewmueller]

Under the hood contains uses LIKE, search uses the full-text search capabilities of the database.

https://stackoverflow.com/questions/224714/what-is-full-text-search-vs-like

Full-Text Search also does stemming, given "Quick brown fox"

• contains: "foxes" will return nothing
• search: "foxes" will return the result

Also if you add indexes, full text search can be quite a bit faster for querying.

[molebox]

Thats an odd thing. So contains makes sense, because the sentence doesnt contain foxes, but search foxes will return because its found the word fox within foxes? This should definitely be documented

[boredland]

search: "foxes" will return the result

while this works, I think if our text is "Quick brown foxes", it will not return on search: "fox" which I would have expected from the stemming. Do you know how to achieve that?

[matthewmueller]

You'd need to split "Quick brown foxes" into words. There's an example in #2233

[boredland]

so I'd be creating facets on the client instead of sth. like trigrams. Meh.

[maxmorlocke]

I would suggest that the documentation talk about how to handle multiple word searches (e.g. Los Angeles needs to be broken down into Los | Angeles or Los & Angeles). The existing documentation somewhat hints at the need for this, but doesn't explicitly state it. This is a common trap for people doing full text search for the first time.

We should probably also talk about prefix matching, etc. such as adding :* to each token if someone wants to match on Los An for Los Angeles or similar.

Some reference material for common pain points:
https://stackoverflow.com/questions/2513501/postgresql-full-text-search-how-to-search-partial-words
https://czep.net/17/full-text-search.html

[jharrell]

👋 we're cleaning up our backlog of issues and are closing everything older than two years. If you still feel that this issue is relevant, please feel free to re-open and our team will take a look. Thank you!