Google Datastore Index Optimization

0
votes

While designing key-only queries to filter Google Datastore entities, I am generating many composite indexes that are subsets of another index. Is it possible to use the same composite index for queries that filter on a subset of the properties already indexed? For example, if I have the following key-only queries, would it be possible to have less than three indexes?

Query 1: Entities where a = 1, b = 1, c = 1;
Query 2: Entities where a = 1, b = 1;
Query 3: Entities where a = 1;

Here is a sample of the actual query I am working with:

Query<Key> query = Query.newKeyQueryBuilder()
              .setKind("track")
              .setFilter(CompositeFilter.and(PropertyFilter.eq("status", 1), PropertyFilter.eq("bpm", 138), PropertyFilter.eq("artist", "AVB"), PropertyFilter.eq("label", "Armada")))
              .setOrderBy(OrderBy.asc("date"))
              .build();
1
google-cloud-datastore
Yes, I believe that set of queries can use three built-in indexes and zero composite indexes. Check out cloud.google.com/datastore/docs/concepts/optimize-indexes. – Juan Lara
When I try to run a query that filters on a subset of properties, I receive a "no matching index found" exception. – Sven
Are you using non-equality filters like '>'? Note that index merging only works with '=' filters. Can you post a code snippet that shows what kind of filters you need? – Juan Lara
I forgot to mention that the queries have an OrderBy filter as well. I have updated the question to show an example. The other queries are the same as the example, except that they remove property filters one-by-one from the composite and filter until there is just a filter on the "status" property. – Sven
By the way, if you have any feedback on the optimizing indexes docs page, it would be much appreciated. – Juan Lara

1 Answers

1
votes

Datastore can merge smaller indexes together to support larger equality queries, see index merging. Using this feature, a minimal set of indexes for your set of queries would be something like:

index.yaml

indexes:

- kind: Albums
  properties:
  - name: artist
  - name: date

- kind: Albums
  properties:
  - name: bpm
  - name: date

- kind: Albums
  properties:
  - name: label
  - name: date

- kind: Albums
  properties:
  - name: status
  - name: date

This supports equality queries on any number of these properties, sorted by date. Note, however, that index merging has a performance trade-off in some cases.