Firestore index merge example

[Aurangzeb Alamgir]

I am trying to use firestore index merge but cannot get it to work.

When querying for two equality parameters with one unrelated sort, according to docs it should be enaugh to build a composite index with one of the equality fields and the sorted field, but firebase always demands all fields for the index.

Is there some working example, tutorial or any hints how to get this done?

[Sam Stern]

Hi Aurangzeb,

Could you provide a short example of the query you're trying to execute as well as the indexes which already exist?

- Sam

--
You received this message because you are subscribed to the Google Groups "Firebase Google Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebase-tal...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/618c7853-b3b6-4af3-80b0-2551a6134533n%40googlegroups.com.

[Aurangzeb Alamgir]

I have now created a  question with example on Stackoverflow.

https://stackoverflow.com/questions/65246473/how-to-get-firestore-index-merge-to-work

[Sam Stern]

Thank you for posting such a clear example, I want to confirm that the answer you posted on StackOverflow is correct.  If you want to use index merging on that query you need 3 composite indexes (a, d), (b, d), and (c, d).

Please note that 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.

- Sam

To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/a0555e06-cda8-4f88-96a6-6e49e362f477n%40googlegroups.com.

[Graham Dickinson]

Thanks for the clarity Sam, very useful. May I suggest that you add that detail as an SO answer to the OP's question. It is much easier for other people to find it then. Thanks.

Sender notified by Mailtrack 11/12/20, 12:54:26

To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/CAHafJBrT-GdyZTbBJxnSfB7xVfdfLFokNBRsNB8CEQx0c%2BDZ4g%40mail.gmail.com.

--

Kind regards,

Graham