SQLite Forum

What indexes help Window Functions
Your latter query best demonstrates what it is that the query planner does.

Taking the indexes and tables available it then enumerates all the possible solution methods to the query.  It may also consider generating indexes just for the duration of the single query.  It then decides which of the possible millions of alternate solution methods is likely to have the "lowest cost" given all the available information (including distribution statistics for the tables and indexes, if available).

In the 'olden days' professional programmers were responsible for designing the access path and writing the code to execute the retrieval efficiently.

The whole purpose of SQL is that it is a declarative language.  You describe 'what' you want, not 'how' to get it.  And the computer using all the information available to it computes the most efficient 'how' to to get your 'what' each time it is requested.  After all, the purpose of computers is to compute.

The creation of indexes is not really an optimization.  The Query Planner will obtain your 'what' according to its computation of the most efficient 'how' giving appropriate consideration to the extant circumstances.  Adding indexes and statistics gives the Query Planner more 'possibilities' to consider when generating the 'how' to get 'what' you asked for but does not actually change the process or the result, merely perhaps the ability to generate a better 'how' for your 'what'.