I think the effect is different for everyone. I personally have extremely vivid (and not necessarily pleasant) dreams when I take a 3mg pill. 1mg or less works great, though.
As with any medication, side effects vary. When I took melatonin, I would very quickly fall asleep but would still wake up extremely tired. I also experienced very severe depression when on melatonin, that started about 2 days after use, and ended about a day after stopping use.
I do know people that have had it work very well for them, however.
I found that the higher doses lead to me falling asleep fine but I tend to wake up in the middle of the night. I assumed the pill was getting my melatonin to high and when it went back to natural levels it triggered my waking up.
I've been using 1mg lately (the lowest I can find) and they work much better. Still help make me drowsy, but without waking up in the middle of the night.
I've found that 0.3 mg will have me waking up very briefly in the night, but that my sleep is still refreshing. I assumed that this might have something to do with the 'dual sleep' that humans are ostensibly supposed to have had, before electricity came along.
I recommend experimenting with amounts (I break my pills up) and finding the amount that works for you.
It seems the only real study/evidence for the dual sleep idea is from one source and a separate study of 8 people. Sleep experts seem to suggest that now is is impractical and unlikely you will be able to adapt, because of how prolific electric light is.
Since it's a non-linear metabolic process, and relies on a specific level of increase over time, when it increases too fast (because you took too much), won't necessarily help you get to sleep. It will also give you a 'sleep hang over' in the morning.
You also have to take it an hour or so >before< bed.
I take 5mg every weeknight as a insomnia treatment. I start getting sleepy about 45-60 minutes after taking it. If I fall asleep then, I'll sleep the entire night.