As with most hormones and drugs, getting the dosing right can be a bit tricky. And it's not just a one-time thing. I regularly fiddle around with my dose (one reason I like the liquid / liposomal version). Too much one night and I'm groggy the next day. Not enough, and I can't fall asleep.
Regarding whether correctly titrated supra-physiologic doses really help with sleep, I would say "it depends." In some people (me!), it definitely does. I regularly measure my sleep quantity and quality, and can see the results. And with my kids. Holy cow! Without Melatonin, they both had extreme insomnia problems.
Are some people taking more than they really need? Certainly. However, you could say the same about sugar, carbs, and even food in general. We all do the best we can with the experience, knowledge and tools we have available.