I’ve done some reading, probably not enough but I’m feeling a bit overwhelmed so I’m hoping someone can kind of point me in the right direction. (It would help if I wasn’t so sleep deprived because my current mattress is from Hell itself.)
The specifics:
Queen size bed, two sleepers, occasionally one large dog
Sleeper 1: ~200lbs, side sleeper, psoriatic arthritis with associated bursitis in hip and shoulder intermittently. Occasionally bad days joint-wise can mean spending most of the day in bed. Latex allergies if relevant. Mattress being too soft results in significant amounts of joint and muscle pain, especially in the back. Happy with good quality memory foam in the past.
Sleeper 2: ~150lbs, stomach sleeper, generally no musculoskeletal issues from sleeping, one of those annoying “can sleep pretty much anywhere” people. Does sometimes toss and turn a bit trying to get to sleep just due to ADHD-related restlessness, which can be an issue on mattresses that transfer movement a lot. Finds some memory foam to be a bit hot but tolerable.
Dog: 105lbs American Bulldog who has his own bed for overnight but enjoys a good people cuddle in the morning and likes to nap on the bed during the day.
Current mattress: Some “medium-firm” thing from CostCo that was okay to start with but seems to lose all support over time, especially on the side with the heavier sleeper + dog. It does seem to recover somewhat if left alone, but we don’t travel away from the house often enough for that to be a successful management technique. 
The mattress set up we have in our RV is actually more comfortable, although it feels like it could be better. That’s an 8" medium-firm short queen from Brooklyn Bedding which by itself seems to compress a little too much overnight under the hips/shoulders pressure points, but is pretty good with a no-name 2" memory foam topper added on.
That leads me to think that we really want to go more in that direction - maybe a firm base (rather than medium-firm) with a good plush top layer to pad enough for the bursitis?
Mattresses are expensive enough and annoying enough to return that we want to narrow down something that will probably work before making a purchase, although right now there is some temptation to just go buy a cheap medium-firm somewhere for temporary improvement over the current one since the current one was okay when new.
Budget-wise we’d like to keep it under $1000 but we can go above if necessary but then warranty and return policy and so on become a lot more important. We are willing to go the DIY route if that’s what makes the most sense - we don’t want to DIY just to DIY if you see what I mean.
Thanks for any help you can offer, I am tired of waking up in pain.