An MRI will better help identify what is or isn't going on back there. You need an MRI of your lower spine to either confirm or rule out a disc issue in your lower spine. You sound like you have "sciatica", but as to what is causing it, dunno.
It could be a disc bulging or herniation, a disc that is simply aging, could be an SI joint issue, or as you mentioned the piriformis muscle spasming on you, etc.
I would suggest you lighten up just a bit on your workout regimen and see if it improves the pain or otherwise.
Nobody likes the medical intervention routes no matter how invasive it is, but if it's the only way to get you some relief..
Hang in there.