Search code examples
google-cloud-firestore

Understanding how Firestore selects and combines indexes

Considering a collection in Cloud Firestore on which we do the following three queries:

1

c.where('a', '==', 'value1')
  .where('b', '==', 'value2')
  .where('c', '>=', 1)
  .orderBy('c', 'desc')

2

c.where('a', '==', 'value1')
  .where('c', '>=', 1)
  .orderBy('c', 'desc')

3

c.where('b', '==', 'value2')
  .where('c', '>=', 1)
  .orderBy('c', 'desc')

That is we order on field c, and filter on either or both of a and b.

Firestore tells me I need 3 separate indexes, one index for each of those queries, i.e.

  1. a ASCENDING, b ASCENDING, c DESCENDING
  2. a ASCENDING, c DESCENDING
  3. b ASCENDING, c DESCENDING

Is there a way I can structure these queries or indexes so that Firestore can commonise or merge some or all of the indexes?

This is a specific illustration for clarity, but it would help anyone to understand how Firestore can and cannot combine indices.

I have all single-field indexes disabled on the collection. The key motivation is to optimise index storage size (money) on large collections (and perhaps performance).

Solution

  • Firstly, I suggest reading through the documentation on Firestore indexes, especially the section on composite indexes and index properties. It contains everything you need to know about how indexing works.

    An index that allows the query to be executed most efficiently is defined by the following properties:

    • Fields used in equality filters
    • Fields used in sort orders
    • Fields used in range and inequality filters (that are not already included in sort orders)
    • Fields used in aggregations (that aren't already included in sort orders and range and inequality filters)

    Since your example queries each have different sets of equality filters, they each need a different index for optimal querying in order to avoid considering documents that could never be part of the result set. This avoids over-scanning irrelevant data, which can be costly at scale (and I'm sure you'd rather pay the cost of the index rather than the cost of the scanning at scale).

    In some cases, Firestore can use index merging to use a single index for multiple types of queries, but your case doesn't meet the criteria it uses for that. Specifically, it's the fact that you have range filter on c.