index required for firestore fields to use orderBy in query

Question

I was building a simple query that includes ordering at the end.

But firestore is asking me to create index that combines the queried field and the orderBy field. And it is going to be many indexes that will be created for this that combine the order field with the rest fields (or even combination of fields).

I have 4 fields to filter results by, and need to be ordered by another field for which I have to create 24 indexes for the different combinations, including both ascending and descending for order. This way if I have tens of fields? I think I am going to have to create hundreds of indexes.

So is it a good practice to get on and create all those numerous indexes of combination of fields combined with the order by field, even for both directions ASC and DESC? would it create issues if I proceed with that? or what pattern is fit for this simple task way to do this?

My query is

var query = ref.collection("data/devices");

if (params.region) query = query.where("region", "==", params.region);
if (params.color) query = query.where("color", "==", params.color);
query = query.orderBy("price", "ASC ");

in my test, only params.color wasn't included since it was undefined. So the query only had .where("region") and orderBy price

And it generated this error Error: 9 FAILED_PRECONDITION: The query requires an index. You can create it here: [Link to create the index]

There is very little generic advice we can give, beyond what is already in the documentation here: https://firebase.google.com/docs/firestore/query-data/index-overview Rather than telling us that your query is simple, edit your question to show exactly what queries you will run. In addition, I recommend reading my explanation of how Firestore uses indexes here: https://stackoverflow.com/questions/68217144/how-is-the-order-of-the-fields-in-firestore-composite-indexes-decided/68217368#68217368 — Frank van Puffelen, Jul 03 '21 at 16:29
@FrankvanPuffelen Thank you for the resources. I have also edited to add the query. — Abraham, Jul 03 '21 at 16:41

maganap · Accepted Answer · 2021-07-04T13:46:27.497

To handle the query you're showing:

var query = ref.collection("data/devices");

if (params.region) query = query.where("region", "==", params.region);
if (params.color) query = query.where("color", "==", params.color);
query = query.orderBy("price", "ASC");

You need to create 2 composite indexes:

region ASC, price ASC
color ASC, price ASC

Those 2 separate indexes will be merged and will be able to handle the combination you need, even when using both region and/or color. For example:

db.collection('whatever')
  .where('region', '==', 'A')
  .where('color', '==', 'B')
  .where('prop3', '==', 'C')
  .where('prop4', '==', 'D')
  .orderBy('price', 'ASC');

If that query is exactly what you will always use, you can create a single composite index with the 5 fields. But, if the "where" clauses are optional, then you'd need to create 4 different composite indexes:

region ASC, price ASC
color ASC, price ASC
prop3 ASC, price ASC
prop4 ASC, price ASC

You also get the benefit of having different combinations available, like:

.where('region', '==', 'A').where('prop4', '==', 'D').orderBy('price', 'ASC');

for example.

This is called Index Merging and is well explained here.

Edit: Notice that:

Index merging only works with equality comparisons (.where('x', '==', 5)) and not range comparisons (.where('x', '<', 5)).
If you want to sort by price,DESC instead of ASC you will have to create a different set of indexes as explained here.

index required for firestore fields to use orderBy in query

1 Answers1