Does CF matter, when intermediate ISPs are collecting IP address and DNS query activity and can be subpoenaed?

The answer to both this and parent is yes: partial privacy improvements are still improvements. There are two big reasons for this and many smaller reasons as well:

First, legal actors prioritize who to take action against; some cases are “worth seeing if $law-enforcement-agency can get logs from self-hosted or colo’d servers with minimal legal trouble” but not “worth subpoenaing cloudflare/a vpn provider/ISP for logs that turned out not to be stored on the servers that received the traffic“.

Second, illegal actors are a lot more likely to break into your servers and be able to see traffic information than they are to be able to break into cloudflare/vpn/ISP infrastructure. Sure, most attackers aren’t interested in logs. But many of the kind of websites whose logs law enforcement is interested in are also interesting to blackmailers.

If the authorities come to TFA site with demands, they can't do anything about what CF is doing. All they can do is turn over what they have, and/or prove they don't have what is being asked of them. What some 3rd party does is not germane at all.