The mods/main game comparison is one of the most gratuitously untenable notion gamers cling to. It's not even apples and oranges, it's apples and monkeys.
1. "But modders do it in their spare time!", you say? True, but it's not actually the disadvantage you make it sound. Doing things as personal projects, in your spare time, actually comes with two massive advantages: 1)Mods don't have deadlines and 2)mods don't have budgets. The only limit on a modder's ability to put time and effort into a project is their interest in the project (and their lifespan). Mods can have staggering amount of man-hours sunk into them over the course of years, far beyond what most games can reasonably expect. Get a team together and you can easily sink more hours into the mod than could ever be put in the game. And all that without any obligation to meet, without needing to sell - meaning there is far more time to take risks and experiment, and far more room to make a mod that has considerably higher system requirements. Also, even if they do work alone, modders can rely on the gigantic knowledge pool of dozens of other modders - something game devs rarely have.
2. Major games, on the other hands, are not projects of love. They're business ventures. They have investors expecting them to deliver certain benchmarks by certain date below a certain cost, which limits development resources. They have an obligation to aim for the most sales possible (which means keeping system reqs within reason, even in the patches - so an AI improvement that requires significantly more RAM may just not be feasible at all, for example). And if they fail to turn a profit, or if the investors decide a project is too resource intensive, it's the existence of the studio that's at stake. So while they have large team of paid staff - they're limited in how much they can use them. That's even more so for patches, which don't actually bring in money.
3. Mods don't have to build any of the foundations. Self-evident, I know, but the bulk of the base work (engine, base AI, etc) is already made when the modders start their work. Not so the game makers who start largely from scratch and who have to spend significant time and energy on getting those done before they can do the fixing, refining and improving that are the bread and butter of modders.
in short, modders can and should be expected to do stuff the base game will never match. Not because the devs are bad, but because they don't have nearly the resources that modders do, and have to meet obligations modders don't.
expecting the devs to match modder work will always leave you disappointed.