My early studied guitar training was in classical guitar (my bachelors degree). It was my experience that your hand(s) should be as relaxed as possible all the time. To be clear, you must have some tension in your right hand (if you flat pick a guitar or any other instrument) just to hold the pick, but all of your other fingers that are not being used in the production of notes at any given moment should be as relaxed as possible.
As an example, when I was learning Requerdos de la Alhambras, it took months to be able to get the tremolo in my right hand smooth, even and relaxed, and my hand often was sore after extended practices. After another month or so of rehearsal on the same tune, I was able to completely relax my right hand and get a monumentally smooth, even stroke. In addition, my hand never seemed to tire, but instead felt looser and and faster the longer I played.
I think you are right on the money here. I’ve been lucky enough to spend enough time on a few different techniques and tunes to get them to the point of effortlessness. And each time I was able to play without any physical effort but instead was able to focus completely on the musicality of the piece.
There is one caveat though, if you stop practicing on those pieces or techniques, you can and will often lose the ability (over time) to be completely relaxed with those abilities.