Imagine you were doing the task manually – yourself, using pencil and paper – but had a magic device which made indexes for you. Like the people whose job it is to make the index for a book. Figure out which index you'd use for each task. If your task involves a window, you have to pretend to do that yourself too. Remember that each query can use either zero or one index, no more. There's no way to switch between two indexes during one search. Also remember that indexes can contain multiple columns. Don't think of each column being a candidate for an index, think of each SELECT or WHERE clause as potentially having a "most helpful" index, perhaps a compound (multicolumn) index. For instance, in indexing a long phone book you wouldn't have two indexes, one on givenname, one on familyname; you'd be better off with a single index on (familyname, givenname). Creating indexes you don't use is, as you wrote, wasteful. Not only does the work of maintaining the index take time, but the extra pages of space that the index uses in the database file make it bigger, more annoying to handle, and need more resources each time you back it up. One does try to avoid it.