(I’m the author of django-zen-queries)
I’m not a fan of the hyperbole in the article either. Some of the points are genuine problems but none of them should put off beginners.
I generally don't need to optimize my queries because my apps are small, but I should do that by default, because otherwise it's wasteful and it's not hard to do. It's just that Django's default doesn't encourage you too, but hopefully with Zen Queries it will.
Same situation here. I find that being mindful of the ORM is enough to save me from obviously bad queries (fetching 100 objects and then running additional queries for each one), for the rest I get away with it as it's not a problem in practice. Sure, I will probably save an extra milisecond here and there and maybe in the end be able to run my entire DB off a very small computer, but (bare-metal) hardware is so cheap that a few stray queries here and there won't break the bank.
Nevertheless I've installed the package and will be trying it out, but I have yet to be convinced that eliminating additional queries all the time is worth the extra developer effort.
It's just a way to make less mistakes.
In my entire career I've got away with just relying on a "gut feeling" about whether a query needs a select/prefetch_related and it has yet to fail me. Yes, you're going to have a handful of stray queries here and there that could technically be avoided, but is the extra developer effort worth it? Hardware is very cheap (well if you know where to look, definitely don't look in the clouds), take advantage of that.
Unless you're programming purely for the sake of programming, what matters is the end result and the problem your application is solving. Your code doesn't have to be 100% perfect (otherwise why are you using a dynamically-typed, interpreted language?), it just has to work well enough to solve the business problem - other tools such as monitoring will quickly tell you if you've made a mistake and you can go back to the drawing board and add that extra select/prefetch_related you missed the first time.