This repository was archived by the owner on May 7, 2026. It is now read-only.
Repository navigation
This repository was archived by the owner on May 7, 2026. It is now read-only.
Too slow query fetch #150
Description
Hi, team!
Imagine we have an entity Order with customer_id (str, uuid4), created_at (datetime) and other fields. I want to get all orders for specified customer and time range.
The query returns about 1500 records.
The problem: it takes about 7-8 seconds!
Environment details
- OS type and version: Docker image
python:3.8-slim - Python version: Python 3.8.6
- pip version: 20.2.2
google-cloud-datastoreversion: 2.0.1
Datastore indexes:
-
kind: Order
properties:- name: customer_id
- name: created_at
direction: asc
-
kind: Order
properties:- name: customer_id
- name: created_at
direction: desc
Code example
query = db_client.query(
kind='Order',
filters=(
('customer_id', '=', customer_id),
('created_at', '>=', datetime_from),
('created_at', '<=', datetime_to),
),
)
result = list(query.fetch())What I do wrong or can you suggest how to speed up this query?
Thanks!
Activity
tseaver commentedon Feb 2, 2021
@umax In order to optimize query performance, you need to create composite indexes for the various queries your application makes.
Per the Datastore docs on index configuration:
umax commentedon Feb 3, 2021
@tseaver does provided composite indexes is not enough for my query?
tseaver commentedon Feb 3, 2021
@umax Firestore doesn't auto-provide composite indexes: it only has indexes on individual fields. You have to define composite indexes manually.
umax commentedon Feb 3, 2021
@tseaver in the description of this issue I provided list of composite indexes from
index.yamlfile. Without these indexes the query does not work at all.BinarSkugga commentedon Feb 12, 2021
Possibly related to #145
umax commentedon Feb 15, 2021
What I did: I created composite index and use
projectionfield to fetch only custom fields of entity. But this not solve problem in general. I think that 7-8 seconds to fetch 1500 is not acceptable for official library for Google services.tseaver commentedon Mar 23, 2021
@umax I'm sorry I missed seeing the index definition in your initial description.
I wonder if the second index (the "descending" one) is causing the back-end to choke?
tsteinruecken commentedon Mar 24, 2021
Hi,
I guess we’ve ran into the same issue (tested on 2.1.0 and several older versions). We have a query that returns 30 entities (no filter, no order). The combined size of them is less than 200kb. Fetching them from an Appengine F2 instance takes close to 0.8 Seconds. To debug this I’ve split the fetch by running the query keys-only (which is fast!) and then calling get_multi separately. As far as i can tell, the time is wasted in client.py:547. Copying these 30 protobufs into entities takes 0.6 seconds of said 0.8 seconds total. I’ve also iterated the list of these protobufs before and had each PB cast to string to ensure there’s no RPC in flight that's being waited on inside entity_from_protobuf. For me it seems to be entirely CPU-Hog there. Our entities contain several lists and nested entities, maybe this is contributing to this slowdown as i can’t see anything obviously wrong with that entity_from_protobuf function?
craiglabenz commentedon Mar 29, 2021
Relatedly, I've been exploring some query slowness here. I'm going to add @tsteinruecken's findings to my search for the culprit.
tseaver commentedon Apr 14, 2021
I have written a script to test
google.cloud.datastore.helpers.entity_from_protobuf, which is the workhorse used in marshaling query results intoEntityobjects.Those times are pretty repeatable.
fix: optimized protobuf access for performance (#155)
tseaver commentedon Apr 20, 2021
Note that the merge of @craiglabenz's PR #155 improves the time for Datastore 2.x on my benchmark, from being a full 10x slower than Datastore 1.15.3 to about 3x slower.
tsteinruecken commentedon May 4, 2021
@tseaver could you please reopen this issue? I don't consider it resolved at all.
I've attached a sample appengine application. It contains a large entity to test.
Deploy it, call /initDB first (it will write the contained entity 30 times into "test_kind").
Then call /testFetch or /testBaseline. The baseline is what i consider should be the time it should take to fetch these entities (it does a roundtrip to the datastore keys-only, then deserializes the locally stored entity for each key received). It's about ~1.3 seconds on the configured F2 instance. Python-datastore==1.15.3 is already 5(!) times slower. 2.1.0 is fiveteen times slower than the baseline, and while there's a small improvement on 2.1.2 it's still fourteen times slower than given baseline (that's ~15 Seconds to fetch these 30 entities).
appengine-datastore-bench.zip
tseaver commentedon May 4, 2021
/cc @craiglabenz, @crwilcox
Added first support for the dbaccelerator (faster get_multi) in respo…
tests: suppress expected warning for bad enum value
perf: further avoid using proto-plus wrapper when unmarshalling entit…