What do the APN settings on the phone actually do?
The APN settings contain information required to set up an internet data connection while you are on a mobile network (that is, not connected to wifi).
In regards to the access point name and the primary proxy server, What exactly happens when I tell Safari to load a webpage?
Most of the magic of APN settings happens at a much lower level and is handled on an OS-wide basis.
But one of these is the HTTP proxy / port settings - these are a higher level feature that is used by HTTP clients (eg, browsers). Your device allows you to set an HTTP proxy dependent on the network that's being used which is the reason you can set it in your APN settings, but really it's used by the HTTP client. Your device communicates these settings to the HTTP client (eg browser) which then will remember that proxy setting and use it when communicating with websites.
When loading a web page in your browser it will consult its HTTP proxy setting to determine whether it should directly connect to a server or go via a proxy.
Why do I have to enter the proxy server a second time in Firefox? Shouldn't my computer traffic be transparently sent through my phone's mobile internet, exactly the same as Mobile Safari?
Proxy settings are relevant to the HTTP client being used rather than to the system as a whole. Most operating systems including your phone will communicate these proxy settings to other compliant software that runs on the same operating system but when tethering you're crossing over to another separate system and the proxy settings would need to be set up there separately.
I never have to enter any proxy settings into Firefox when I'm using normal tethering with my AT&T mobile internet plan. What makes Speakout so different?
Different providers have different requirements. Many providers these days run everything through a transparent proxy at their end so you don't need to set up proxy settings in your client software. It sounds like this provider doesn't do that.
Providers don't need to use proxies at all, but proxies can help them with various things like modifying websites for your device, calculating/indicating whether you're accessing sites within a "free"/"unmetered" zone, blocking access to certain sites, etc.