How can I simulate a slow connection for page load?

FireFox Throttle is a FireFox extension that should do it. Sloppy is a proxy which slows down your connection so it should work across browsers.

Source

MODERATOR EDIT 2013-02-02

This extension is no longer available but a repo has been created on Github.

Fiddler Web Debugger is an excellent HTTP proxy debugging tool for Windows that includes a modem speed simulation feature (Main menu > Rules > Performance > simulate modem speed).
Fiddler is freeware.

I also like Charles Web Debugging Proxy, a similar tool. Charles can also throttle the connection speed. Charles is commercial software, but has a free trial available. What I like most about Charles is that it is cross-platform: Being Java-based, it can run on Windows, Mac OS X, and Linux. (You didn't mention which platform you're on, but it's probably one of those :-)

In Chrome, you can open the developer tools, click in the device icon (1), and then select the connection throttling (2).

Edit (2015-11-03)

Since around Chrome 45, it actually got a little bit easier: you don't have to be in device mode anymore.