As a real dummy on the subject, maybe you could help me understand where vector search tends to fall over?

I use it to retrieve tool functions by description and it has worked very well for me, but I expect I'm in the "very simple use cases" category that you mentioned.