Firestore: Does a composite index perform better on queries with multiple where clauses?

Question

I have the following code:

foldersRef.where("ParentID", "==", "1").where("Deleted", "==", false)

1. Will a composite index (that indexes ParentID and Deleted) be preferable to a single-field index for this query?
2. If I also have individual single-field indexes for ParentID and Deleted, will Firestore know to use the composite index?
3. When I create the composite index, does the order of fields matter?
4. Does the order of my .where() clauses / function calls matter?

Answer 1

1. Will a composite index (that indexes ParentID and Deleted) be preferable to a single-field index for this query?

If you are using the following line of code:

foldersRef.where("ParentID", "==", "1").where("Deleted", "==", false)

Without any ordering (ASCENDING or DESCENDING), there is no need to create a composite index. Firestore will create the needed index automatically.

2. If I also have individual single-field indexes for ParentID and Deleted, will Firestore know to use the composite index?

No. Single-field indexes are also created automatically by Fiestore. So there is no need to creat any index for a single filed. Beside that, if you have separate field indexes with orderding, it doesn't mean that you also have a composite index for those fields. You need to create yourself.

3. When I create the composite index, does the order of fields matter?

Yes, if you change the order of your where() calls, you also need to create the corresponding index accordingly.

4. Does the order of my .where() clauses / function calls matter?

In terms of speed, as long as you create the correct index, no.

Answer 2

1. Yes, for reads, a composite index will perform better than a merge of single-field indexes. It allows Cloud Firestore to use one index instead of merging multiple single-field indexes.
2. Yes, Cloud Firestore will give preference to the composite index.
3. (Also 4.) Yes, the order of the fields matters in both your index definition and your query. foldersRef.where("ParentID", "==", "1").where("Deleted", "==", false) and foldersRef.where("Deleted", "==", false).where("ParentID", "==", "1") would require two different composite indexes.