Does Cosmos DB query cost go up significantly when a query becomes cross-partition (even with partition keys present)?

user_4089318 0
Viewed 129

I need to read several documents from a cosmos db container, for which I know the partition keys.

If I were to do a point read for each, this would be an RU cost of about 1 per document.

So I noticed when running some queries that there may be a cheaper way:

Running a well-indexed query that returns one result costs 3 RUs. But if I have a query that has several OR conditions in the WHERE clause, scaling this out becomes cheaper:

SELECT * FROM c
WHERE c.id = 'a'
OR c.id = 'b'
OR c.id = 'c'
//...

Here, the cost for the first condition was 3, but adding each new condition was only about 0.3 RUs. This made me fairly happy, as it seems to be a good way to optimize cost.

However, I decided to actually create a DB with a bunch of data and high provisioned RUs to make sure that this holds when there are more underlying physical partitions. So I made a container with 40k provisioned RUs and about 300k records (for about 400MB of data). I ran a query like

SELECT * FROM c
WHERE c.id = 'a'
OR c.id = 'b'
OR c.id = 'c'

And with each new condition, the cost went up only 0.3RUs. Nice.

But then when I added a fourth OR condition, the cost went up by 3 RUs. Same for the 5th.

Why does the cost increase by 0.3 initially but then by 3 later on?

Is it the case that the first three records I pulled happened to be on one physical partition, and the others were on their own partitions?

Subsequent OR clause additions seem to increase the cost by anything between 1.5 and 3 RUs...

azure-cosmosdb-sqlapi azure-cosmosdb
2 Answers
user_56415980

The cost of a query depends entirely on the work done to execute the query.

The cost cannot be directly related to the number of filter predicates (OR / AND conditions). However, a couple of major components of query charge is the amount of index pages scanned or documents loaded. If the addition of OR conditions doesn’t change these components, then there won’t be much difference in RU costs.

As indicated in the answer above, if you have id and pk values, use ReadMany and pass an array of id and pk values. If not, then you'll need to use a query. Query is ALWAYS going to be more expensive than equivalent point operations because the service needs to parse your query, build a plan, execute it, return data, etc.

To answer your second question on whether the data is on different physical partitions, it's possible. Impossible to know with the information given.

What I can say for sure is that cross-partition queries will grow increasingly more expensive and slow as you increase RU/s on the container or as the container grows in storage, requiring more physical partitions.

Fundamental in all this is that cross-partition queries do not scale. You need to model your data and implement a partitioning strategy that will allow your database to scale as load grows, making sure you are avoiding cross-partition queries at high volume.

user_8849310
Related