Unfortunately, if the CDN only rely on BGP steering (or conversely if you are a user who is stuck on an ISP monopoly), there are cases where this is not necessarily the nearest network-wise (or performant network-wise) if there are peering disputes. If the said ISP is a virtual monopoly or (worse) state-sanctioned to collect network "toll fees" (like in South Korea), non-preferred and international routes are (intentionally) congested.*
If you use a third-party DNS, you basically lose this DNS optimization, and ECS does not fully solve this (because sometimes the DNS override are placed only on the ISP's recursive DNS servers). You're basically in a lose-lose position: either use third-party servers and the IP addresses served to you on popular CDNs are in the congested path, or use the often-unreliable and heavily-logged ISP-provided DNS.
* Usually. There are exceptions, but this comment is just a simplification of the complexities of real-life networking (where RFCs and mutual cooperation die out without fanfare).
Edit for further reading: DNS is the new BGP by Geoff Huston of APNIC (https://ispcol.potaroo.net/2023-09/service-routing.html), How LinkedIn used PoPs and RUM to make dynamic content download 25% faster from the old LinkedIn engineering team (Archived at https://web.archive.org/web/20160310065302/https://engineeri...), Wikimedia's mapping of their CDNs (https://gerrit.wikimedia.org/r/plugins/gitiles/operations/dn...)
I would suspect some of non-optimized scenarios are eyeball network operator decisions on their networks that DNS providers and others do not have much control over. Like, Cloudflare resolves an IP that is closest to them, which is likely also the closest to the end user (and the eyeball ISP), but the eyeball ISP BGP path to that resolved IP takes a roundabout path because of their own BGP policy because $reasons.
BGP's default route selection is to use the choice with the shortest AS Path.
If your ISP and your CDN peer in some locations, but not all, you can easily run into longer latency.
Ex: customer in Seattle, but ISP and CDN peer in Portland. CDN has a PoP in Seattle but not peered with the ISP.
BGP (without a lot of tuning) will prefer to send traffic through Portland, rather than through transit in Seattle, because the AS path through Portland is ISP -> CDN and the AS path in Seattle is ISP -> Transit ISP -> CDN
Of course, CDNs try to get peering in all common locations to address this, but that's not always possible, and not always because the ISP is unreasonably uncooperative. Sometimes the best path to resolution is by targetting the ISP dns server, but it doesn't catch all the customers.