How is the order of the fields in firestore composite indexes decided?

Question

This is my query:

db.requests
      .where('userId', '==', uid)
      .where('placeId', '==', placeId)
      .where('endTime', '>=', Date.now())
      .where('isFullfilled', '==', false);

So I manually wrote this index in firestore.indexes.json:

    {
      "collectionGroup": "requests",
      "queryScope": "COLLECTION",
      "fields": [
        {
          "fieldPath": "userId",
          "order": "ASCENDING"
        },
        {
          "fieldPath": "placeId",
          "order": "ASCENDING"
        },
        {
          "fieldPath": "endTime",
          "order": "ASCENDING"
        },
        {
          "fieldPath": "isFullfilled",
          "order": "ASCENDING"
        }
      ]
    },

When run, I get an error "This query requires an index". And the automatically created index looks like this:

My manually created index on the other hand looks like this in GUI:

Why does it not accept my own index? Does the order of fields matter? I am not ordering query results. Is there any kind of pattern to index creation? This is really confusing and I can't find anything on this in the docs. It's really annoying to have to run every query against the cloud database to get the proper composite index field order.

Frank van Puffelen · Accepted Answer · 2021-07-01T22:00:35.607

Does the order of fields matter?

Yes, the order of the fields very much matters to the resulting index. I pretty much see such an index as:

Firestore creates a composite value for each document by concatenating the value of the fields in that index.
For a query it then can only query if the fields and order exactly match (with some exceptions for subsets of fields, and cases where it can do a zig-zag-merge-join).
For such a query Firestore finds the first entry in the index that matches the conditions. From there it then returns contiguous results (sometimes called a slice). It does not skip any documents, nor jump to another point in the index, nor reverse the index.
The field that you order on, or do a range query on (your >=) must be last in the index.

Note that this is probably not how it really works, but the model holds up pretty well - and is in fact how we recommend implementing multi-field filtering on Firebase's other database as explained here: Query based on multiple where clauses in Firebase

I see, that makes sense. Thank you. Now I know how to structure my indexes. I wish this was clarified in the documentation. — fabyeah, Jul 01 '21 at 22:03
That's good feedback to leave, and there should be a link at the bottom of each documentation page. I guess it's much harder to document this, as we'd have to make sure the explanation is both exactly accurate and understandable, where here I can get away with just reasonable accuracy. :) — Frank van Puffelen, Jul 01 '21 at 22:09

How is the order of the fields in firestore composite indexes decided?

1 Answers1

Linked