My default choice if I look for a route in Google Maps is Auto with Avoid Highways. This is in part because for unknown places it seems to select short sections of trail and often go out of the way to do so. In addition, often the directions seem to add a huge number of small turns, some of which there would probably be signage.
I also don't go out of my way to find a tour specifically to tour on an off-road trail.
With that said, there are occasions I'll find myself riding some trails along the way:
1. In some countries such as the Netherlands, that is the default infrastructure for many parts and seems to work well.
2. If I am checking a bicycle map of say an urban area I want to cross and it looks like there is an extended trail as part of the bicycle network - then I may try it and then bail out if turns out to have too many turns/etc.
3. If I am on a long busy highway without too much in way of cross-streets, and a parallel trail - I'll also try that.
4. If I have gotten to my end destination and next morning the main road goes one-way and I want to go the opposite direction - I may take a trail/sidewalk a short distance to get back in intended direction. An example might be if I've stayed in a motel along a one-way frontage road and I want to get back to the last cross-road to go under the major road and head the other way.