I'm a fairly heavy user, have been for a number of years. I use it primarily to treat depression/anxiety/insomnia, but I also experience chronic pain due to years of abusing my body (rugby, mountain biking, snowboarding, etc.). Nothing crazy, but something is always hurting.
I wouldn't compare its effects to traditional painkillers by any means. I would say it makes me less aware of the pain, and "dulls" it. For example I can tell my knee isn't happy, but it doesn't "hurt". I have however tried some topical cream from a dispensary containing CBD (most strains you'll be smoking have been bred for higher THC and lower CBD), and it's incredibly effective. I experienced hours of absolute painlessness (which is unheard of for me).
As for workouts, I find it helps tremendously with focus and endurance. It's a lot easier to keep pushing on when you would normally give up.
I think it's a very valuable tool, and has relatively few downsides to it (I won't claim it's 100% safe/healthy). Moderation is important as always when using substances.
MS patients have a lot of pain and problems with muscles. When given medical MJ, they don't clinically get better and still have some pain signals from their body. What does change is that they feel better - they simply don't mind the pain/muscle tightness as much and they report better functionality.
Personally, I've smoked for PMS pain. It doesn't get rid of the pain completely, but takes the edge off. I can take half the amount of pain medicine (in this case, mixing tylenol (paracet) and ibuprofen per doctors orders).
https://en.wikipedia.org/wiki/Abdominal_obesity#cite_note-97
The combination of any sort of neurological impairment and the lifting of heavy weights over your head and throat sounds like a bad accident just waiting to happen.