So the answer is “yes, it has to download the index first”? None of these comments answer my question.

The B-Tree is a tree that in this case is perfectly balanced. So if you do a query with an index in a database it will fetch an logarithmic amount of data from the index and then a constant amount of data from the table.

For the example the wdi_data table is 300MB and an index on it is 100MB in size. This index has a tree depth of 4 - which means SQLite has to read exactly 4 pages (4KiB) to get to the bottom of it and find the exact position of the actual row data.

you can check the depth of the b-trees with `sqlite3_analyzer`.

Everything in SQLite is stored in B-Trees. Data or indexes. So you don't need to download the whole index first; you only need to download the necessary pages of the trees to access data, whether it's part of an index or actual data

At least the information describing what tables and indices there are and where to find them - and then it gets what it needs once a query is run. Just like sqlite would if running from a local file on disk.

It just needs to download the pages (x KB chunks) to traverse from the header to the particular index and table schema, a fraction of the whole data.

It has to download some parts ("pages") of the index as the query execution proceeds, and some header/schema description pieces of it first before execution starts.