The two you mentioned, Melvins and Napalm Death don't actually tune all that low. Some of the heaviest music is in E or drop D. Black metal is nearly Al in standard tuning.
Lower tunings are great for getting that sludgy sound that bands like Eyehategod and Crowbar use. Quite often where low tunings are used in doom and stoner metal, the lowest notes are used sparingly as a doomy accent.
For me, the sweet spot is tuning down to C and some disgusting fuzz on top.
I think this is a perfect example of what low tunings can do. He's tuned to B but doesn't often drop below C#