Google Datastore Composite index issue

Question

I am getting below exception :

Exception in thread "main" com.google.cloud.datastore.DatastoreException: no matching index found. recommended index is:
- kind: cp_outbox
  properties:
  - name: format
  - name: occasion_name
  - name: sent_datetime
  - name: status
  - name: send_date

While running below query :

SELECT * FROM cp_outbox where send_date <= '2018-03-14' and sent_datetime is null and format='test1' and status=0 and occasion_name='test'

So I am using inequality operator in query and I have composite index for that :

But still I am getting exception.

By looking at error I think the ordering of properties is the issue. If this is true then why this ordering is important.

Thanks in Advance.

Try to re-order the query to match the index you have - if that works then indeed the property ordering matters :) — Dan Cornilescu, Mar 16 '18 at 13:34
Note that even the index suggested in the error message doesn't have the same property order as the query that caused the message. — Dan Cornilescu, Mar 16 '18 at 14:05
Just to rule out the obvious: you didn't accidentally mix GCP projects and the index was in `Serving` state when the query was performed, right? — Dan Cornilescu, Mar 16 '18 at 14:09

score 4 · Accepted Answer · answered Mar 16 '18 at 17:31

Property order absolutely matters for composite indexes when serving queries. The order is what allows us to serve range queries without having to scan through large sections of the index table. As a general rule, the final property is what you can do the inequality queries on, then the renaming property order defines the order by clause order you can use.

In your query send_date is what you are doing inequality on, whereas your existing composite index only allows for occasion_name.

If you create the composite index exactly as the API has returned your query will work.

Ah, the last property (the one for the inequality) matters. My tests were just flipping the order of the equality filters :) Learned something today... — Dan Cornilescu, Mar 16 '18 at 19:47

Google Datastore Composite index issue

1 Answers1

Linked