You can buy mattresses that aren't pillow top. They generally cost less. Comfort depends on a match between what you like and enough firmness to support your weight comfortably. If you want a foam top, you can buy a foam pad as an accessory (Costco carries them) and use it on either side of the mattress.
I agree, it all depends on what you want.
If you sweat a lot, don't go for all latex foam mattress, but look for one with pocket springs so there's a little air flow beneath you. You might also want a washable mattress cover to protect your mattress from yellow sweat stains.
If you get cold easily in winter, look for one with a woollen layer on one side, so you can flip the mattress according to season.
Maybe you want to rotate your mattress as well, then look for one without special enforced zones for shoulders and hips.
And most important: try them before you purchase.
