Maybe this supports or complements your argument: platforms like Azure SQL will tell you when you need an index and even automatically create it for you if you wish. (And automatically undo it if the index doesn’t help or hurts performance) So, other than a primary key index, you could do nothing and let the database decide what’s best.

How about foreign key indexes? :)