App Engine + Cloud Datastore performance: order in query or in memory?

Question

Question about Google App Engine + Datastore. We have some queries with several equality filters. For this, we don't need to keep any composed index, Datastore maintains these indexes automatically, as described here.

The built-in indexes can handle simple queries, including all entities of a given kind, filters and sort orders on a single property, and equality filters on any number of properties.

However, we need the result to be sorted on one of these properties. I can do that (using Objectify) with .sort("prop") on the datastore query, which requires me to add a composite index and will make for a huge index once deployed. The alternative I see is retrieving the unordered list (max 100 entities in the resultset) and then sorting them in-memory.

Since our entity implements Comparable, I can simply use Collections.sort(entities).

My question is simple: which one is desired? And even if the datastore composite index would be more performant, is it worth creating all those indexes?

Thanks!

Answer 1

There is no right or wrong approach - solution depends on your requirements. There are several factors to consider:

1. Extra indexes take space and cost more both in storage costs and in write costs - you have to update every index on every update of an entity.

2. Sort on property is faster, but with a small result set the difference is negligible.

3. You can store sorted results in Memcache and avoid sorting them in every request.

4. You will not be able to use pagination without a composite index, i.e. you will have to retrieve all results every time for in-memory sort.

Answer 2

It depends on your definition of "desired". IMO, if you know the result set is a "manageable" size, I would just do in memory sort. Adding lots of indexes will have cost impact, you can do cost analysis first to check it.