When are the melee attacks worth using in Doom II?

Use your fists exclusively against hordes of pinkie demons, chainsaw if you can get them in a choke point. Use chainsaw against former humans (pistol zombies) because the damage they deal you is negligible compared to wasting ammo on them, and any time you get imps in a straight line use the chainsaw on them. Use a chainsaw on the cacodemon as it stunlocks indefinitely when you do this. Use your fists or chainsaw on a Pain Elemental. Use fists on Arch-Viles if you can round a corner well. Use fists and chainsaw on lost souls almost exclusively.

Why fists and not a chainsaw, and why melee against things like demons?

1. Demons have a "wind up" animation to their bite. If you ever notice they have a hard time damaging other monsters during infighting, it's because they are moving - press attack before you are within range, close the distance with a quick sprint forward to connect your punch, and back up again. The Demon's own bite will trigger and miss every single time. Demons are laughably easy to dodge unless they are grouped together in massive numbers - they are more dangerous when you are speedrunning or running away from a bad situation because they are almost impossible to maneuver around, and trying could cause their slow but highly damaging attack to actually hit you. If you ARE fighting a large group and you want to melee, if you cannot get them into a corner, you should circle-strafe and punch constantly. The chainsaw also locks YOU down, making it difficult to escape.
2. Using fists vs. chainsaw on an Arch-Vile is the same principle. The attack only damages on the last frames of animation and thus if you can round a corner easily you should be able to punch an Arch-Vile to death, but chainsawing it could lock you down too long and let you take damage.
3. Pain Elementals shoot Lost Souls as their attack. When you close in on melee range, the lost soul spawns inside your player model, and the lost soul dies. Pain elementals cannot damage you at melee range.
4. Pistol zombies, imps, demons, lost souls, cacodemons, pain elementals are all laughably easy to stunlock with a chainsaw.

Shotgun zombies, Chaingunners, hell knights and barons, mancubus, revenants, and of course the Spider Mastermind and Cyberdemon are all too dangerous to close in on melee without extreme dedication and patience. The Icon of Sin is impossible to kill without rockets in vanilla Doom 2.

Another chainsawing tip: do the same thing as with fists if you need to. Get a feel for how the chainsaw locks you in and how much damage you can do in a burst before you can back away again. The wind up is instant unlike the punch, but the damage holds you in place longer than the punch would. The timing is different.

Rereading my comment I decided it probably sounds ridiculous to melee an Arch-Vile: I've done it, but I usually don't do it. There are very few situations where an Arch-Vile will be close enough to a corner for you to round it quickly at melee range, without the AV rounding the corner itself and chasing you down. You can probably get a few hits in and kill it for laughs, but I still just use a shotgun and lots of cover. Sorry if I made it sound really easy.

The chainsaw does near-continuous damage, so it works well against enemies that sometimes are stunned when they take damage. You can easily stun-lock them and avoid taking damage while you kill them. Demons and Spectres are the most effective enemies for this. Beware using this strategy on Nightmare difficulty though, where attack rates are increased to the point that stun-locking enemies isn't viable.

The linked Wiki page also points out that in some cases you can back into a corner with the chainsaw, and if the corner is small enough, you will force Demons/Spectres to attack single file.

The berserk fist does more damage, but doesn't do it continuously, so you might want to employ hit and run tactics, should you opt to use it at all.

Other than that, melee attacks can be used against weaker enemies to conserve ammo, but you're better off in general keeping your distance from enemies whenever possible.