Datastore query is not working, asking more index than expected

<datastore-index kind="Environment" ancestor="false">
    <property name="active" direction="asc" />
    <property name="consumed" direction="asc" />
</datastore-index>

<datastore-index kind="Environment" ancestor="false">
    <property name="active" direction="asc" />
    <property name="creationDate" direction="desc" />
</datastore-index>

I have the above two indexes

When I query as following, it is not working and it says new index is required.

SELECT * FROM Environment
where active = false and consumed = true and creationDate < '2013-09-22'

GQL response is as follows:

no matching index found. The suggested index for this query is:

<datastore-index kind="Environment" ancestor="false">
   <property name="active" direction="asc" />
   <property name="consumed" direction="asc" />
   <property name="creationDate" direction="asc" />
</datastore-index>

What am I doing wrong? Should it not work based on zigzag merge?

Solution

When looking at what indexes are needed for zigzag queries, it is very useful to split the indexes into prefixes and postfixes. The prefix of the index is used to answer the equalities while the postfix is used to answer the inequalities and sorting.

In order to perform the merge join, the postfix of all the indexes involved in the query must match so that the sort orders are the same. So in the case of your query:

SELECT * FROM Environment where active = false and consumed = true and creationDate < '2013-09-22'

The creationDate must be in the postfix; active and consumed must be in the prefix.

The index:

<datastore-index kind="Environment" ancestor="false">
   <property name="active" direction="asc" />
   <property name="consumed" direction="asc" />
   <property name="creationDate" direction="asc" />
</datastore-index>

can satisfy this requirement if we split the index into prefix and postfix between consumed and creationDate. However, you could also satisfy this with two separate indexes:

<datastore-index kind="Environment" ancestor="false">
   <property name="active" direction="asc" />
   <property name="creationDate" direction="asc" />
</datastore-index>

<datastore-index kind="Environment" ancestor="false">
   <property name="consumed" direction="asc" />
   <property name="creationDate" direction="asc" />
</datastore-index>

In this case, the postfix would contain creationDate and the prefix would be active and consumed, respectively. Notice how in this case, the postfix match in both indexes, which is a requirement to perform the zigzag merge join.

For the indexes you currently have, the query can not be answered because

<datastore-index kind="Environment" ancestor="false">
    <property name="active" direction="asc" />
    <property name="consumed" direction="asc" />
</datastore-index>

does not have creationDate as a postfix.