Querying Datastore using some of the indexed properties

Question

I am trying out Cloud Datastore's indexes, and I cannot figure out the configuration I need to resolve my queries.

I have created a few entities of the same kind (named "object"), all of them with 5 properties named equally (property_0, property_1, ..., property_4). Then, I have created a composite index for that kind, indexing all 5 properties, and setting property_4 in the last place because I want to apply inequality filters on it.

The definition of the index is:

indexes:
- kind: object
  ancestor: no
  properties:
  - name: property_0
    direction: asc
  - name: property_1
    direction: asc
  - name: property_2
    direction: asc
  - name: property_3
    direction: asc
  - name: property_4
    direction: asc

The queries I am trying to resolve will always apply an inequality filter on property_4, and there may be filters on some of the other properties. Some examples:

• SELECT * FROM object WHERE property_4 > 5 AND property_0 = true
• SELECT * FROM object WHERE property_4 > 5 AND property_4 < 10 AND property_1 = 'approved'
• SELECT * FROM object WHERE property_4 > 8 AND property_2 = 100 AND property_3 = true
• SELECT * FROM object WHERE property_4 > 15 AND property_0 = true AND property_1 = 'draft' AND property_2 = 10 AND property_3 = false

The only query that works is if I filter on every property in the index, the rest of them show the error message "Your Datastore does not have the composite index (developer-supplied) required for this query."

Shouldn't all the queries be resolved by the created index? Or do I need to create an index for every combination of the filters I want to apply? (i.e. one index for filtering property_4 and property_0; another one for property_4 and property_1; another one for property_4, property_2 and property_3; ...)

Answer 1

You are correct, you will need to create indexes for every combination of the filter you want to apply.

You can specify the indexes for separate queries in the index.yaml file the following way:

indexes:
- kind: object
  properties:
  - name: property_0
  - name: property_1
  - name: property_2
  - name: property_3
  - name: property_4

- kind: object
  properties:
  - name: property_3
  - name: property_4
  - name: property_0

...