Query with filter on a Key field and another (string) field very slow on App Engine Datastore

0
votes

I have an entity Kind A with around 1.3 million entities. It has several fields, two of which I use as filters in a Datastore query - K (which is a Key) and S (which is a string).

I perform the following query, and it takes it almost 8 seconds to complete:

SELECT * FROM A WHERE K=KEY('...') AND S='...' LIMIT 1

(I do this by constructing a query with filters in java, but it works just as slow in GQL.)

Is this reasonable? Can this be improved in any way?

Replacing the key query with other fields (or removing completely), speeds things up. Does it make sense to save the key field as a string, and query on that instead of on the original Key type field?

1
google-app-enginegoogle-cloud-datastore
Do you have an explicit index on those two fields together? – Daniel Roseman
Why do you use a key as a property instead of using an entity's id or name? A key is much longer. – Andrei Volgin

1 Answers

2
votes

Can you please send your index.yaml file, or at least paste all the indexes for Kind A? My initial feeling is that you don't have a proper index for K and S, so it's doing a full table scan or using another index to project the results. Check out https://developers.google.com/appengine/docs/python/datastore/indexes for an explanation.

If you're still banging your head, I would suggest turning appstats on and determining the slowness from the visuals on your local dev server (https://developers.google.com/appengine/docs/python/tools/appstats).

Finally, I wanted to point out that single property lookups are fast in App Engine, so either K= or S= lookups would be fast. Together, they require special attention (indexing, memcache, etc.).