composite index + ordering of entries = strange behavior

[Juraj Vitko]

Someone from the GAE team please enlighten me, whether the following 2
queries can really have different requirements on the ordering of the
properties in the composite index declaration required for these
queries:

(JDOQL syntax)

A) SELECT WHERE propA == param1 ORDER BY propB ASC, propC ASC

index required: 1. propA asc, 2. propB asc, 3. propC asc


B) SELECT WHERE propA == param1 && propB == param2 && propC > param3
ORDER BY propC ASC

index required: 1. propB asc, 2. propA asc, 3. propC asc


In the query B, the propA and propB both are equality filters -- why
don't they require index declared in the same order?


So query B is refusing to work with the index from query A -- at least
on the Java dev server. So I'm hoping this is a bug in the query index
verification code in the Java dev server.
If not, then the bookmarked paging technique is not implementable
without "relation indexes" or some other hack, as forward/backward
sorting on a single grid column would cost 6 composite indexes.

In the above examples, propC is actually the __key__, if that matters.

[Juraj Vitko]

forward/backward sorting --- I meant "forward/backward paging" of
course.

[Jeff S (Google)]

Hi Juraj,

Great question, it looks like you should be able to perform both of these queries using either index, so just one is needed.

Since BigTable performs queries using a prefix match and linear scan, the results for a query need to be contiguous in it's index. So in query A, the first requirement is that all entities with a particular a value are grouped together. Within an A group, they should then be sorted by B and within an A-B group, sorted by C.

With query B, the same index structure can be scanned to satisfy this three clause query. In this case it seems like the query planner is producing one more index than is needed. Could you try removing the second index from your config file and uploading? Please let me know how it goes :)

Happy coding,

Jeff

[Juraj Vitko]

Hi Jeff,

I'd love to upload and test, but I have since rewritten my app to use
the "relation indexes" technique, so I don't have this particular code
currently at hand.

But I'm going to create a small test app for other tests, so in the
next days I can add this to it, and test on app engine. I will let you
know.

J.

On Aug 18, 7:13 pm, "Jeff S (Google)" <j...@google.com> wrote:
> Hi Juraj,
>
> Great question, it looks like you should be able to perform both of these
> queries using either index, so just one is needed.
>
> Since BigTable performs queries using a prefix match and linear scan, the
> results for a query need to be contiguous in it's index. So in query A, the
> first requirement is that all entities with a particular a value are grouped
> together. Within an A group, they should then be sorted by B and within an
> A-B group, sorted by C.
>
> With query B, the same index structure can be scanned to satisfy this three
> clause query. In this case it seems like the query planner is producing one
> more index than is needed. Could you try removing the second index from your
> config file and uploading? Please let me know how it goes :)
>
> Happy coding,
>
> Jeff
>