in the past decade planetary gearboxes have been improved on so much that i'm sure any new car today with a traditional auto will be more fuel efficient than the same car with a manual transmission
how i see it:
- Dual-clutch transmission (DCT): allow the fastest shifting times (8ms upshifts in the case of VW's DSG (but apparently 600ms downshifts:/ )) and the shifts are buttery smooth (pro or con), but you still have to deal with replacing the clutch (two of them, actually) and they feel "soulless"
- Planetary/traditional auto: In a quick summary: not fun to drive. Require minimal maintenance tho
- Manual transmission: require maximum involvement (aka they're fun) and have a high skill ceiling, but with the worst mpg (relative to newer cars), a max shift time of like 250ms if you can even get that good, and a clutch to replace. being able to manipulate the clutch and gears manually allows for the execution of special driving techniques which can help in some driving events like rally or drifting or whatever else
- Sequential/auto-manual/semi-auto: literally a manual transmission with hydraulics/electronics moving the clutch and gears. sometimes it's even just a piggyback system put onto an existing mt (see: m3 csl). not as fast as a dct, but you can still get thrown back in your seat when you upshift under hard throttle, like with a normal manual (aka shifting isn't super smooth). lexus used these in it's LFA over a DCT specifically for the more raw feeling it brings while still having an optional full-auto function (since, you know, it's a lexus)
- Continuously variable (CVT): it doesn't shift at all. best mpg (i would think) and performance but probably the most boring