As someone who has tried at least 15 or more commercial home AVs over the years, I can say that there are indeed those that are difficult to configure, have an impact on the system, and can cause inconvenience to the user. However, there are security solutions that are the opposite, and their presence on modern computers is almost invisible if we look at CPU or RAM usage.
For example, I will say something about my experience with Norton Security, which I consider almost perfect. RAM consumption is generally less than 100 MB, while CPU goes from mostly 2-5%. By comparison, Firefox with 4 open tabs consumes over 1 GB of RAM and close to 10% CPU. For a computer with 8 GB of RAM and a modern processor, no AV will be a problem with resource consumption.
I agree that protection depends on what the antivirus definition database is and how often it is updated - but also on how good
heuristic analysis it has, which means that it can fight against those threats that have not yet been added to the antivirus database.
See, I've found Norton to be quite intrusive, the thing is as computers advance, so does their hardware, so the effect of the program isn't noticed as much as time goes on. Put Norton on a older system, which doesn't have the latest ram, and CPU, and you'll see an issue. Although, I can't personally claim either way if its good or not in terms of resources, since I haven't seen it personally.
Although, why I find it intrusive is because it constantly tries to push additional products onto you. For example, I have friends that use it, and I've seen that they try to push Norton's own version of a VPN. The only saving grace is that they do claim not to keep logs for their VPN's. Although, according to
this they do collect some data, which is too broad to see if any of that information could be deemed invasive. However, I'm not a fan of the advertising that these services do, even if it is their own products. I believe they're enabled by default, and can be turned off through the settings.
As for their capabilities in protecting its users, I'd have to see some compelling data to change my mind on it. Heuristic_analysis generally is very limited, and more often than not identifies false positives. I've had code I've written been flagged by anti virus systems, so I know they're definitely flagging up false positives. Most operating systems have built in systems these days, Windows has Windows Defender, which practically does the same thing, a part from a few variations. Obviously, Linux can differ per its distribution. Mac OS I'm not entirely sure how that works, as I've never touched one of them for long enough to take a look. If you have a built in system that you can't disable (Windows Defender), then you are effectively adding another similar application to the current usage of that application too. So, that should be factored in.
I'm going to sound like a broken record here once again, but if you truly care about security, and internet security there's no better operating system than Qubes OS. I've recommended this so many times, I almsot feel like a personal sales agent for them, but it's completely free, and uses virtualisation technology to separate instances known as Qubes, which means even if one of them was compromised, it's unlikely to be able to infect others, which reduces your risk massively.
For example, you could have a dedicated Bitcoin wallet qube which you plug your hardware wallet into, sys-net the qube that handles the network supplies the connection to that qube. If sys-net gets compromised, it doesn't mean that the qube will, and you could potentially set sys-net as disposable so upon reboot it resets to the default, and effectively gets rid of any threat. The best anti virus to me, is compartmentalizing your system by default, so if you're compromised it doesn't compromise you entirely. Anti Virus's do attempt to isolate the threat before it does any damage, but there are no guarantees of that. I'm not going to go into this rabbit hole as I'm afraid it'll go too off topic. However, while I agree with the OP's statement that you should be careful, the only way you can truly be sure, is via isolation, even then there are definitely risks, but it's the most secure way other than not using the internet at all. Obviously, I'd recommend against using hot wallets when not necessary anyhow, and strictly use cold storage.
This all obviously depends on your threat model. Most viruses aren't very sophisticated, and they don't need to be, because they aren't meant to be attacking high profile people. Instead, they often try to advertise or collect data.