How does DISTINCT ON work in GCP datastore

user_7083440 0
Viewed 154

Let's say I have a kind named 'audit' and it has the following entries:

tenantId traceId eventId
tenant1 traceId1 event1
tenant1 traceId1 event2
tenant1 traceId2 event3
tenant1 traceId2 event4

I need to get all the rows unique on traceId whichever is the first entry so the query should result in:

tenantId traceId eventId
tenant1 traceId1 event1
tenant1 traceId2 event3

For the above, I am using select distinct on(traceId) * from audit

Even though it's a simple query, my concern is the performance of this query as my entries grow. I will have hundreds of thousands of entries in the datastore, but out them, 50% might be unique on traceId.

I have read datastore is not for aggregations. So, my questions are:

  1. Is distinct on considered an aggregation query?
  2. Does distinct on work on index scan?
  3. Will distinct on increase my read cost?
  4. Will the built-in index handle the distinct on or should we define a composite index?
google-cloud-platform google-cloud-datastore
1 Answers

Is distinct on considered an aggregation query?

distinct on clause ensures that only the first result for each distinct combination of values for the specified properties will be returned. So it is not considered as an aggregation query. Also Datastore does not support aggregation queries.

The index-based query mechanism supports a wide range of queries and is suitable for most applications. However, it does not support some kinds of query common in other database technologies: in particular, joins and aggregate queries aren't supported within the Datastore mode query engine.

You can read about it in this document

Does distinct on work on index scan?

Yes, distinct on works on index scan and you can not apply distinct on to any of the unindexed properties.

Will distinct on increase my read cost?

If you are using projection queries then using distinct on will increase the cost as it will make the query out of small operations as mentioned here. If you are not using projection queries then it will charge based on the entity reads.

Will the built-in index handle the distinct on or should we define a composite index?

If you are applying distinct on to a single property i.e. select distinct on(traceId) * from audit, then it will work with the built in index that is created during the entity creation. If you are applying distinct on to multiple properties i.e. select distinct on(traceId,eventId) * from audit then it will not work with the built in index and you have to create a composite index.

user_157467490
Related