A few years ago when first moving to Seattle I bought a Queen spring mattress which has a thin memory foam pillowtop layer (effectively equivalent to a mattress pad). Less than $300. I've found it to be much better than the pure foam or pure spring beds I've experienced in hotels. I haven't had any overheating problems, except for when I briefly tried adding a plasticky bed bug prevention mattress cover which would have heated up any bed.

https://www.amazon.com/gp/product/B004LQ1RQU/

Thanks! I'll check it out - my $100 Ikea queen being near half a decade old and none too comfy even when new, it's pretty far past time I replaced it with something. This'll be a good place to start looking!

It's the heat retention of foam that kills me.

I use a spring mattress that has a layer of foam on top (see other comment). There are no heat problems, as long as you don't add a mattress cover. If you want to keep things extra cool regardless of mattress, try breathable satin sheets.