I think Kate summed it up best for me - I remember, during a contraction, thinking that I just couldn't handle it and that it was too much, then the contraction stops and you smugly think "Wasn;t that bad actually....!" lol..... then the next one starts. I also remember, whilst pushing, the midwife jokingly asking me if I'd have anymore, and I said definitely, yes, perhaps another 3.
I have been told that crowning (when baby's head is on it's way out) is the worst pain, but I never felt it as I had mine in theatre, and so when they did the episiotomy and stitched me up I didn't feel that either.
I think it depends on your labour, most people I know who started naturally said the contractions progressed naturally from period pains gradually getting worse to contractions, and those I know who were induced or "started off" had very strong contractions and pains, very quickly.