I see where you're coming from. My perspective on the matter though I'd that if I don't have resourced I need because collecting those resources is tedious, thats not fun or interesting, it's frustrating, because to me it feels like I should have them. They were available for me to get but I left them behind. So either I was dumb for leaving important resourced, or the game is dumb for making getting those resources tedious. It's not an interesting choice for me, it's a question of "have everything I need" or "avoid the tedious task I have to do to get what I need." And as for immersion, the result for me is that I'm not immersed because I'm too busy being annoyed and wishing I could get to the fun part.
As for the difference you mention between your paladin and your rogue, this is probably just a me thing, but I just tend not to think things like that really "count" as role-playing if they're not connected to dialogue somehow. In my head my character isn't actually going through everything, they're just going to their destination and maybe occasionally doing necessary foraging. Unless the characters or story directly reference me doing that as part of the section I'm in.
All totally valid of course, and we should all play the game however feels right to us.
But if you’re worried that if you don’t loot everything you’ll run short of resources and end up regretting it, I can reassure you that’s not been my experience. Yes I need to keep an eye on things like how many potions and (if relevant for my characters) arrows and so on that I have, and if I’m starting to run low perhaps start looting more as I move forward, or even go buy some stuff before venturing off on the next instalment of my journey (I’m actually kind of disappointed I’ve never actually had to buy a healing potion), but I’ve never needed to retrace my steps and loot stuff I’d previously ignored. Totally up to you, of course, but if you think you can manage it and do multiple EA runs, I’d recommend at least giving being more lackadaisical about looting a go for at least one playthrough. You might find it liberating! Or you might absolutely hate it and go back to what suits you

.
Totally agree with this. It is very liberating to just walk away from the 75 barrels with 200 lbs of meat that you just don't need but can't help but take because I mean...you went to the trouble of opening the barrel so might as well take it!
I love the idea of the Detect Magic spell being used like you mentioned above.
I too got extremely annoyed trying to see everything and loot every container, trying to figure out which bottles are potions and which are empty on shelves with dozens of bottles, ugh. Not a good experience.
What I really do think would help make looting more enjoyable is well, the Detect Magic idea above, and simply not putting ridiculous useless crap in chests that are obviously meant to hold valuables / and NOT putting rare magical items in a random bucket sitting in a corner somewhere.
It would be nice to be able to play without any worry of missing something really great because you didn't spend countless time searching every single container in the world, but also have the option to spend time searching all those containers because you're a poor ass scavenger and need to make some money early on or you're running from a battle you couldn't win, are down to 10% HP and can't rest, and need some healing potions NOW!