skip to content

In Cloud Firestore on Android, why does whereEqualTo on assigneeId combined with orderBy on dueDate fail at runtime, and how do you fix it?

level: middleimportance: must knowfreq 75%

answer

  1. an index behind every query
  2. automatic indexes cover single fields
  3. an error carrying a console link
  4. firestore.indexes.json under version control

basics

~20 s

Firestore serves every query from an index, and automatic indexes cover single fields only. Equality on one field plus ordering by another needs a manual (composite) index. Until one exists the query fails with FAILED_PRECONDITION and a link to create it.

solid answer

~40 s

Firestore never scans a collection. Every query must map onto an index, which is why query cost tracks the size of the result set. By default Firestore keeps ascending and descending single-field indexes for each field, plus an array-contains index for arrays. Those cover single-field filters and chains of equality filters. But `whereEqualTo("assigneeId", uid).orderBy("dueDate")` needs entries sorted by `assigneeId` and then `dueDate`, which only a manual index provides. Firestore now calls these manual indexes; most people still say composite. Until one exists, `get()` or the listener fails with a `FirebaseFirestoreException` whose code is `FAILED_PRECONDITION`. Its message carries a link that opens the console with the index pre-filled. I create the index there or in `firestore.indexes.json`, deploy it with `firebase deploy --only firestore:indexes`, and keep the file in version control so every environment matches.

code

kotlin · 20 lines
kotlin
import com.google.firebase.Firebase
import com.google.firebase.firestore.FirebaseFirestoreException
import com.google.firebase.firestore.firestore

fun loadMyTasks(projectId: String, uid: String) {
    Firebase.firestore.collection("projects").document(projectId)
        .collection("tasks")
        .whereEqualTo("assigneeId", uid)
        .orderBy("dueDate")
        .limit(20)
        .get()
        .addOnFailureListener { e ->
            if (e is FirebaseFirestoreException &&
                e.code == FirebaseFirestoreException.Code.FAILED_PRECONDITION
            ) {
                // The message carries the console link that creates the missing index.
                println(e.message)
            }
        }
}

go deeper

for a junior

Recall that every Firestore query needs an index, that single-field indexes are automatic, and that the missing-index error links to creating one.

for a middle

Explain which filter and sort combinations need a manual index, why sort direction matters, and how firestore.indexes.json is deployed.

for a senior

Show that you keep indexes in source control, add exemptions for unqueried fields to cut write fanout, and know the 30-disjunction and not-in limits before production finds them.

for a principal

Weigh index count and write latency against query flexibility, and decide which queries the product supports instead of indexing every combination.

## Every query reads an index Cloud Firestore requires an **index for every query**. It never falls back to scanning a collection. That is the source of its main performance promise: query cost depends on the size of the **result set**, not on the size of the collection. The price is that a query with no matching index is **rejected** at runtime. It does not just run slowly. Firestore has two kinds of index. The documentation now calls them **automatic** and **manual**, the names that replaced *single-field* and *composite*. You will still hear both pairs in interviews. ## What automatic indexes cover By default Firestore creates indexes with **collection scope**: - for every non-array, non-map field, one **ascending** and one **descending** index; - for every array field, an **array-contains** index, plus ascending and descending indexes on the whole value; - for map fields, the same treatment recursively for each subfield. These indexes serve single-field filters (`<`, `<=`, `==`, `>=`, `>`, `in`), `array-contains` and `array-contains-any`, and a sort on one field. Firestore can also **merge** single-field indexes to answer a chain of **equality** filters. Automatic indexes with **collection-group scope** are not maintained by default. ## When a manual index is required | Query on `tasks` | Index needed? | |---|---| | `whereEqualTo("status", "open")` | No, automatic | | `whereEqualTo("status", "open").whereEqualTo("projectId", p)` | No, merged equality indexes | | `whereEqualTo("assigneeId", uid).orderBy("dueDate")` | Yes, `assigneeId` + `dueDate` ascending | | same, with `orderBy("dueDate", Query.Direction.DESCENDING)` | Yes, a second index with `dueDate` descending | | `whereEqualTo("status", "open").whereLessThan("dueDate", t)` | Yes | | `collectionGroup("tasks").whereEqualTo("assigneeId", uid)` | Yes, collection-group scope | The rule: as soon as a query combines an equality filter with a **range** filter or a **sort on a different field**, you need a manual index. The guide also recommends a manual index when `array-contains` is combined with other clauses, to avoid the performance cost of index merging. ## The runtime failure and the fix 1. The query is built without complaint, because the Android SDK cannot know which indexes exist on the server. 2. When it runs, the backend rejects it. `get()` fails, or the listener's callback receives a `FirebaseFirestoreException` with code `FAILED_PRECONDITION`. The SDK also logs it as a warning, since a missing index usually means a developer forgot one. 3. The error message contains a **link** to the Firebase console with the index pre-filled. Following it and clicking Create starts a build. Even on an empty database, a build takes a few minutes. 4. For a repeatable setup, declare the index in `firestore.indexes.json` and deploy it with `firebase deploy --only firestore:indexes`. If you add an index in the console, copy it into the local file too, or the environments drift apart. ```json { "indexes": [ { "collectionGroup": "tasks", "queryScope": "COLLECTION", "fields": [ { "fieldPath": "assigneeId", "order": "ASCENDING" }, { "fieldPath": "dueDate", "order": "ASCENDING" } ] } ], "fieldOverrides": [] } ``` In the index file, `collectionGroup` holds the collection ID even for a collection-scoped index. `queryScope` decides whether the index serves one collection or a collection group. ## Query limits interviewers probe - **Implied existence.** Ordering by a field returns only documents where that field exists. An inequality filter implies ordering by its field, so it filters the same way. - **One `!=` or `not-in` per query.** `not-in` takes at most 10 values and cannot be combined with `in`, `array-contains-any` or `or`. - **30 disjunctions.** Firestore rewrites queries with `or`, `in` and `array-contains-any` into disjunctive normal form and allows at most 30 disjunctions. This limit is fixed. `in` on 5 values AND `in` on 10 values already produces 50. - **Range filters on several fields.** These have been allowed since firebase-firestore 25.0.0. Each one adds fields to the index the query needs. - **No offsets on Android.** Page with `startAfter(lastSnapshot)`. The best-practices guide warns that offsets still read, and bill, the skipped documents. ## What indexes cost Indexes are not free. They add to storage, and every write updates each index that includes the document. The guide calls this **index fanout** and names it the main contributor to write latency. Without billing, a database can hold 200 composite indexes; with billing, 1,000. A document can have at most 40,000 index entries. Add single-field **exemptions** for long strings you never query, for large arrays or maps, and for a sequentially increasing timestamp you never filter on. An indexed sequential field caps writes to its collection at 500 per second.

  • Why does whereIn("projectId", ids) fail when a member belongs to 40 projects?
    `in` combines up to 30 equality clauses. Firestore also rewrites every query into disjunctive normal form and caps it at 30 disjunctions, and that limit is fixed. Forty IDs exceed both. Split the IDs into chunks of at most 30 and merge the results in the app. Or store the member's ID on each task in an array and use `whereArrayContains`, which is a single disjunction.
  • Why does the dueDate-ordered query silently skip tasks that have no dueDate field?
    Ordering by a field returns only documents where that field exists. An index includes a document only when every indexed field has a value. Tasks written without `dueDate` are never in the index, so neither `get()` nor a listener ever returns them. Write the field on every task when you create it, or run a separate query for tasks that lack it.
  • How should the task list page through results on Android?
    Use query cursors. Keep the last `DocumentSnapshot` of a page and build the next query with the same `orderBy`, followed by `startAfter(lastSnapshot)` and `limit(n)`. Passing the snapshot lets Firestore resume at that document's values. The Android `Query` has no offset method, and the best-practices guide warns that offsets still read, and bill, every skipped document.

  • Firestore scans the whole collection when no index matches; it is just slower.
  • Firestore builds composite indexes automatically the first time a query needs them.
  • An equality filter plus orderBy on another field runs on the default indexes.
  • Indexes added in the console need no matching entry in firestore.indexes.json.
  • A whereIn filter accepts as many values as the app needs.

Assumes firebase-firestore 25.0.0 or later, which enabled range and inequality filters on multiple fields; earlier releases allowed range filters on only one field.