When you make a shotgun vs handgun comparison, it really becomes apples vs oranges. Due to differences in case design and capacity, barrel lengths, payload weight, pressure curves and limits, etc., a medium burning rate powder in a shotgun becomes a fast burning rate powder in a handgun, and an extremely fast powder (as in unusable for normal loads) in a rifle.
Yes, a slower powder like H110 / 296 will give max velocities with a slower pressure curve in a magnum handgun then faster Red Dot will. But, the slower powder is not nearly as efficient, requiring a lot heavier charge and muzzle blast then a faster powder.
The trick is to find a well balanced load for the caliber, bullet weight and velocity you want to achieve.
Speaking for big bores, which is what I generally reload for, faster powders like Red Dot, Bullseye, etc. are good with lighter target loads. They burn clean, take little powder, and are accurate. I really question the loading density / powder position theory, as small charges of fast powder like Bullseye are proven, accurate target loads by competitive shooters striving for accuracy. I know for years my pet load has been 6 to 6.5 grains of Red Dot in a 44 mag with a cast 240 grain bullet. 900 fps or so, and the most consistent accuracy in all my revolvers in that caliber.
Unique is a slightly slower powder that many shooters like. It works well in a variety of cartridges, though can be a little dirty when loaded light over light bullets.
Slow powders like 296, Lil Gun, H-110 require heavy bullets, relatively high charge weights, and a good crimp to do their best work - I.E. maximum velocities and accuracy with heavy bullets, and are not suitable for reduced loads.
When you get into small capacity, high pressure rounds like the 40 S&W, etc. Optimal burning rates / pressures change, as do powder charges and selections.
A reloading manual is really your best friend when working up a favorite load in any caliber.
Larry