Firestore: Composite indexes seem too restrictive

[jimmyff]

Hi, I've been prototyping a service in Firestore for the last week but I am finding the indexes to be too restrictive. I hope maybe there is something I've overlooked and there is a simple solution to my problem. I have the following data structure:

{
    "bloomfFilter": [1,2,3,4, ... ],
    "type": {
        "first": false,
        "second": false,
        "third": false,
        "fourth": true,
        "fifth": false,
        "sixth": false,
        "seventh": false
    },
    "lookingForTypes": [
        "second", "third", "sixth"
    ],
    "value": 200,
    "rank": 100
}

For any given item (like above) I'm then trying to find compatible items. The logic is that each item has a 'type' (in example above the type is 'fourth') and then each item has a 'lookingForTypes' array which is what they are looking for. I need to use a bloom filter so I'm currently representing that as an array of ints. This consumes the singe 'array contains' that I'm restricted to per query.

So a query would look something like this:

where bloomFilter arrayContains(2654)
where type.first == false
where type.second == false
where type.sixth == false
where typesLookingFor.third == true
where value > 150
where value < 250
order by rank DESC

This is querying types: (third, fourth, fifth or seventh) that are looking for type third. The problem is Firestore requires me to create a very specific composite index to answer this. There would be hundreds of possible combinations for a single collection supporting 7 'types'. I ideally need to support 50 of these collections in a single database.

I've tried creating an index that contains each of these (like below) but Firestore does not use it as it requires the index to exactly match the query, no additional fields allowed.

{
  "indexes": [
    {
      "collectionId": "search_4",
      "state": "READY",
      "fields": [
        {
          "fieldPath": "bloomFilter",
          "mode": "ARRAY_CONTAINS"
        },
        {
          "fieldPath": "typesLookingFor.first",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "typesLookingFor.second",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "typesLookingFor.third",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "typesLookingFor.fourth",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "typesLookingFor.fifth",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "typesLookingFor.sixth",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "typesLookingFor.seventh",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "type.first",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "type.second",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "type.third",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "type.fourth",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "type.fifth",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "type.sixth",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "type.seventh",
          "mode": "ASCENDING"
        },
        {
          "fieldPath": "rank",
          "mode": "DESCENDING"
        }
      ]
    }
  ]
}

I really want to use Firestore for this project but I'm finding the composite indexes to be too restrictive to build what I need. Does anyone have any ideas of how I can solve my issue within the current constraints?

Below are suggested alterations to Firebase that would solve this kind of issue (I have no idea if these are feasible):

• Allowing more than one array contains per query
• Creating an index of an array/map would magically index all the items in that array/map (so not requiring an index for each)
• Allowing a big index that overcompensates for a query, indexing more fields than actually required. 

Thanks,

Jimmy

[Sam Stern]

Hey Jimmy,

Thank you for the great feedback and sorry for the long silence.  We talked about this quite a bit on an internal side-thread trying to agree on the best solution for you.  Unfortunately we didn't uncover any secret magic answer here.  Let me first address your suggestions:

• Allowing more than one array contains per query - this is possible and we have considered changing our minds.  There are some edge cases we were trying to avoid here, but they're not complete blockers.  Your feedback helps as a vote in this discussion.
• Creating an index of an array/map would magically index all the items in that array/map (so not requiring an index for each) - right now we have no plans to allow queries on properties of objects within arrays.  As you know within a "map" we do index the fields of each entry, but there is no automatic composite index creation.
• Allowing a big index that overcompensates for a query, indexing more fields than actually required. - one of the key principles of Firestore is that query performance scales with the size of the result set.  This would break that, since we'd be reading from an index but potentially filtering out most values.  But I do understand how an educated Firestore user could use this responsibly.
  
  In the end, it may be the case that your workload is just not a good fit for Firestore.  Sorting/filtering on so many columns is a classic SQL workload but is really hard to do quickly in a NoSQL document database like Firestore.  So here are some options, none of them is perfect but just throwing them out there:
  
  

1. Ignore Firestore and use a managed SQL database solution like Cloud SQL.  This will immediately give you the query expressiveness you're looking for but require you to pretty much build the mobile API from the ground up.
2. Use a combination of Firestore and BigQuery.  You could export key data from Firestore to BQ and then run these queries in BQ.  This comes with all the obvious consistency issues of having two sources of truth and you'd have to put up a small API system to serve BQ responses to your app, but it could be workable.

If you do come up with something that works for you, let us know! And if we make any changes to the product that change the tradeoffs above we'll be sure to come back to this discussion.

- 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 post to this group, send email to fireba...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/c543a5bb-7915-4dd0-a562-5640015db0c4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[jimmyff]

Hey Sam, thanks for your reply, I really appreciate it.

I'm a little gutted that I couldn't find a pure firebase solution for this project as I love the simplicity of the platform. It feels like whilst prototyping the product I solved all the tricky NoSQL problems but then got tripped up unexpectedly by the indexes at the 11th hour.

I'm now looking in to Big Query as per your suggestion and it looks really promising. I had always assumed this product was exclusively for super large enterprise projects but it looks ideal for my requirements.

Thanks again for the response & ideas,
Jimmy

To unsubscribe from this group and stop receiving emails from it, send an email to fireba...@googlegroups.com.