When index merging is possible with Firestore

Question

I am trying to enable a variety of queries in an app using Firestore, and I recently had to add several composite indexes to allow them to work. But I am noticing a lot of overlap in the queries and wondering if it's possible to merge some of them by relying on index merging.

My app needs to filter, sort, and paginate data from Firestore, so I added indexes like this to make them all work:

[
  // allows sorting
  [
    ["price", "desc"],
    ["term", "asc"]
  ],
  [
    ["price", "asc"],
    ["term", "asc"]
  ],
  [
    ["gName", "asc"],
    ["term", "asc"],
    ["title", "desc"]
  ],
  [
    ["term", "asc"],
    ["gName", "asc"],
    ["title", "asc"]
  ],
  // allows filtering (and sorting)
  [
    ["gId", "asc"],
    ["price", "desc"],
    ["term", "asc"]
  ],
  [
    ["gId", "asc"],
    ["price", "asc"],
    ["term", "asc"]
  ],
  [
    ["gId", "asc"],
    ["gName", "asc"],
    ["term", "asc"],
    ["title", "asc"]
  ],
  [
    ["gId", "asc"],
    ["term", "asc"],
    ["gName", "asc"],
    ["title", "asc"]
  ],
]

The first four enabled various sorting options to work, but when I needed to be able to filter the data (by using where("gId", "in", [...]) queries), while keeping the sorting options I'd already got working, I found I needed to add four more indexes using the same values as before, but just starting with additional "gId" constraint.

There seems to be a lot of overlap between these indexes. I was wondering if some of them could be merged to reduce the number of needed indexes. The example on the docs page wasn't clear to me regarding what fields can and can't be merged in my situation.

How can I consolidate these indexes to leverage index merging while still allowing all of my queries to work? Or is this even possible in my case?

Thanks!

Answer 1

From the documentation on Queries supported by single-field indexes:

Using these automatically created single-field indexes, you can run simple queries like the following:

const stateQuery = citiesRef.where("state", "==", "CA");
const populationQuery = citiesRef.where("population", "<", 100000);
const nameQuery = citiesRef.where("name", ">=", "San Francisco");

You can also create in and compound equality (==) queries:

citiesRef.where('country', 'in', ["USA", "Japan", "China"])

// Compound equality queries
citiesRef.where("state", "==", "CO").where("name", "==", "Denver")
citiesRef.where("country", "==", "USA")
        .where("capital", "==", false)
        .where("state", "==", "CA")
        .where("population", "==", 860000)

If you need to run a compound query that uses a range comparison (<, <=, >, or >=) or if you need to sort by a different field, you must create a composite index for that query.

So Firestore is able to use multiple single-field indexes for a query, but only in the cases where the query only uses equality condition on those fields.

For queries that don't meet these conditions (for example any multi-field query where you have to specify an orderBy clause), a dedicated composite index is required.

---

Firestore doesn't look for non-overlapping indexes, but for a set indexes that match a prefix/suffix (depending of where you start considering) of the required fields until it meets an ordering clause or range condition.

If you're interested I recommend checking out my video from a couple of years ago on how to implement geoqueries on NoSQL databases or Alfred's even older video on zigzag merge join in Datastore (the older name for what is now called "index merging").