How to get Firestore Index Merge to work?

Question

I am having trouble using firestore index merging in order to reduce the number of required indices.

Consider this example situation:

Firestore Collection: test/somedoc

{
  a: '1',
  b: '1',
  c: '1',
  d: '1'
}

This will cause Firestore to create 4 automatic single field indices on test for fields a to d.

Querying this table with a few equality conditions and one unrelated sort:

    await db.collection('test')
        .where('a', '==', '1')
        .where('b', '==', '1')
        .where('c', '==', '1')
        .orderBy('d')
        .get();

This causes Firebase to fail with a prompt for a composite index creation with all fields included. This is expected.

Now according to the docs (https://firebase.google.com/docs/firestore/query-data/index-overview#taking_advantage_of_index_merging), if you already have a composite on e.g. fields c and d, firestore will use index merging instead of requiring a composite index on all fields.

However if you create an index on collection test e.g. with c asc, d asc, the query will still fail with prompting to create the full composite index.

UnhandledPromiseRejectionWarning: FirebaseError: The query requires an index. 

What am I doing wrong here?

Answer 1

Ok finally understood the problem here.

In order to index merge to work, you need composite-indices that cover all related fields. The auto-generated single field indices won't be used for merging.

Consequently, if you create two additional composite indices

• a asc, d asc
• b asc, d asc

then all fields will be covered and index merge will be used automatically.

Answer 2

@Aurangzeb has the correct answer but I want to elaborate

So it is true that if you want to use index merging on that query you need 3 composite indexes (a, d), (b, d), and (c, d).

However using a single (a, b, c, d) index will still be faster in some cases and you should consider adding it anyway. Index merging is slower in the case where the result sets from the 3 indexes are of very different sizes. Consider your query:

collection
  .where(a == 1)
  .where(b == 1)
  .where(c == 1)
  .orderBy(d)

Let's say you have a collection of a million documents and 50% of them have a == 1 or b == 1 but only 0.1% of them have c == 1. In this case reading from the (a, d) index is actually doing quite a bit of wasted work before the (c, d) index eliminates most of the results. A single (a, b, c, d) index does the least work possible. This is why the Firebase SDKs and console always suggest the most exact index even if there is a way to satisfy the query with merged indexes.