The issue is that this mechanism applies to photos (/data) on the local device that wouldn't otherwise make its way up to iCloud.
The EFF posts says it applies to 2 cases: photos being uploaded to iCloud, and photos through iMessage if the account holder is a child and the feature hasn't been disabled.
https://www.eff.org/deeplinks/2021/08/apples-plan-think-diff...
You're right that only scanning photos uploaded to iCloud is currently pointless. But maybe Apple plans to do semi-end-to-end encryption on iCloud photos in the future, and then this feature would be useful.
[1] https://www.apple.com/child-safety/pdf/CSAM_Detection_Techni...
Today photos are not end-to-end encrypted, there is nothing preventing Apple from decrypting your photos if they want (or if they are asked by law enforcement). If a part of this implementation is to make it so only the user keys OR the CSAM keys in the case of a match are able to decrypt the photos then that is a clear step in the right direction over the current system. It's not real end-to-end encryption, but it still prevents Apple from just decrypting your photos without probable cause of a very specific crime.
If that is the case they should have made it a lot clearer in the initial announcement though.