The correct answer here is that any single query lacks the context of the entire suite of queries that will be performed against the database and their relative

The correct answer here is that any single query lacks the context of the entire suite of queries that will be performed against the database and their relative importance and sensitivity to performance. Indexes compete with each other and with table data for space in memory cache(s). They also require significant cpu and IO resources at write time to be kept up to date. Having too many indexes can lead to having them not being used (best case) or having them evict each other or more important data over and over from caches. In many cases it is better to not have one index that helps some specific query perform 50% better if it means another index is able to stay in memory that makes some other run in 1/500th as much time. It's often better to just scan data and have queries take an hour if they are for nightly reports rather than create the enormous multi-column indexes required to speed them up, since you don't care about the reporting queries taking an hour over night (at least in this hypothetical).