Hey, friends.
Azure Cosmos DB provides indexing metrics to show both utilized and recommended indexed paths.
But do you know that you can use these indexing metrics to optimize your query
performance? Tim Sander is here to show me how it works –
today on Azure Friday.
Hey friends, it's Azure Friday
and I want to learn from Tim Sander how to do indexing with Azure Cosmos DB.
How are you? Pretty good.
Great to be here on the show again.
I feel like we're always releasing new features in Cosmos DB
and it's awesome Azure Friday to go and go and spotlight them.
Today I'll be talking a little bit about the index metrics
that we recently released for Cosmos DB and if you use Cosmos DB
before you know that it's a really good database to use as a developer.
If you don't know a lot about databases and that's kind of a weird thing to say, right?
It's the best database to use if you aren't a database expert.
But in Cosmos DB, we like to be really developer friendly
by going and indexing every property by default.
And for most customers This is this is awesome.
It allows you to go and develop
really quickly on Cosmos DB without having to think about your database performance
and things like that.
You can just write code, write queries, and get instant great performance.
But in a lot of cases, customers that are maybe operating at a large scale
or going to production, they want the ability to go and fine tune query
performance and go and take it one step further and optimization.
So that's our intention with creating these indexing metrics.
We want to allow customers that maybe aren't an expert
in in databases or expert in Cosmos DB to be able to quickly
and easily get indexing recommendations for optimizing their queries performance.
Okay.
So is this really just about squeezing performance out of it, or is this just about like,
hey, these are some simple and free things that you can do
that are just going to make your life better and just going to make your perf better?
I'd say it's it's a mix of both.
It's something that you definitely don't have to do.
Like if you're watching this video in Cosmos, DB is working great for you right now.
You shouldn't feel like this is something that you absolutely must do to improve performance.
It's definitely something that is an extra optimization that you can do
if you already identify, let's say, a slow query or an expensive query
that you want to go and optimize even further beyond our default indexes that we create.
Cool.
You should have a live demo. I can see.
Yeah.
Let's actually jump over to the Azure Portal where I've gone ahead and uploaded
a simple fictional dataset that has some retail data.
So I've created a shopping database and shopping container.
And in this container we have basically different items that you could buy at a store.
So this is a woman's or at top that has a price of 33.
And the buyer state is, is Montana, for instance, Now this container
I actually just went ahead and created it with our default indexing policy.
You can see here there's a slash star.
So like I mentioned a little bit earlier,
by default and Cosmos DB, we just go and index every single property by default.
So that means if you were to let's say run a simple query, let's say we do select
start from C where C that item is equal to.
Sure.
If I go and run on that or really any query,
it's going to be super cheap in terms of our use and super, super quick.
This is really nice as a developer because when you're writing code,
like the last thing that you want to have to think about is going and needing
to go and create an index for every single property that you want to go in.
Did you find anything with that as a T-shirt or shirt.
I just used the
the first name that comes to mind.
I can. See. That returns a result here, but.
I wasn't sure if we were supposed to get a result or not. And
but I notice that, like you said, two, our use is really nothing, right?
Yeah.
So I mean this since this is using the index, whether you get results
or not, like this one returns a few hundred results.
I mean it's 16 hour use. So also super cheap.
And the reason
this is inexpensive is because the query is leveraging an index on the item property.
I see.
So again, if you're getting started, this is awesome.
It allows you to develop really quickly on customers TV.
Now, there's two reasons that you might not want
this default index in place, but you might want to optimize it further.
The first of which would be if you had a really right heavy workload.
The more properties you index in Cosmos DB,
the more expensive it is to do REITs.
So with this default indexing policy where we're indexing every single property
right. Are going to be expensive. Right.
So in a lot of cases when customers get started, they stick with this default.
But then later on, when they go into production or they get further
into development, they go and they tune their indexing policy
to only include the required parts
Now you have two ways that you can do this.
You can go and instead of
doing a blanket, the slash star means basically everything.
Instead of indexing everything, you can go and exclude
everything by default
and then go in and specify like specific
path specific properties
that you want indexed. Right.
And you can kind of create a list of those.
So are you saying be nonperformance everywhere
except be super performant in these places, or are you just being more specific?
Like, I want to I think that people want the hot path to be ideal,
but they don't want to lose the ability to query anything whenever they feel like it.
That's exactly right.
So if you were querying on this property here to make the example easier, I'll call it item.
If you were querying on item, this would be super performant.
But then if you were querying on any other property, we obviously wouldn't
have an index available.
So the benefit would be, you were doing REITs.
This is this is more efficient because we're only indexing one property.
The drawback, of course, is it's important that your queries only use the item property
if they filter on other properties, you wouldn't be leveraging indexes.
So speaking naively because you said that Cosmos is great for folks
that are not good at databases, why wouldn't I just index everything all the time?
Because it would make REITs expensive.
That's really the only reason.
If you don't care about performance or you don't do a lot of REITs, that's that's totally fine.
To have an indexing everything policy all the time.
Now that will get you reasonable performance.
I guess there is one other case where you might want to go in and customize
the indexing policy, and that's with adding on something called
I'll go and revert this back, adding on something called Composite Indexes and Cosmos DB
and Composite Index is ah something extra
on top of the included and excluded path composite indexes.
You'll add those on to provide an extra optimization for certain queries.
So for instance, if you had an order by a query in Cosmos DB, you'll get reasonable performance
without a composite index, but you can go and add on a composite
index as an extra optimization to further improve quality performance.
I see.
So a hot path is item in and price and that's how all my users do everything.
They always include item in price, item price.
So we can index those together as a composite.
Right, right.
If you use those together.
So that's actually a great transition into just going over the index metrics
because composite index is, is our motivation for creating these index metrics.
A lot of developers, again, we want Cosmos DB to be the best database to use
if you're not an expert in databases and a lot of developers that are using Cosmos DB
do have queries that they want to optimize beyond the default indexing policy.
And very often it's to optimize either write performance or to go and add on a composite index.
So the indexing metrics will help you mainly with the latter, but
they will also help you understand which indexes you can remove to optimize performance.
So I'm going to jump over to a console app that I've written
where
I have a super simple select star query that filters onshore.
Basically, the one that that we had just run together
and it goes and populates the index metrics.
So if I go ahead and run this
and I'll move this this down a bit so you can see
see the query here, I go ahead and run this.
It's going to print out the indexes
that we used when we executed this query.
So in this case, we see that we utilize
single indexes on item and the impact score was high.
So the way you want to interpret this, if you had a select start policy, is
you could have gone ahead and removed every single index
besides the one on item and the query performance wouldn't have changed.
This index on item had a very high probability of being used in the execution.
Therefore, it was really important to have.
And if you and then that information was just a context
that you plucked out of thin air there, it was available to you as a developer,
and now you have more insight into what's going on at that as a result of that specific query.
That's right.
You don't need to be an expert in how Cosmos DB works.
All you need to do is go and look at the index metrics.
And this is evidence to you that this query
used our index on an item.
So I'll go ahead and
add in another query.
So like I touched on earlier, there's
two reasons that you might want to use the index metrics.
The first of which is optimizing right performance by removing indexes that you don't need.
The other is going in adding on an extra optimization to your queries
by adding a composite index.
And one type of query where composite indexes are really useful
is queries that have a filter and order by.
So if I take that, that query that I had earlier that filters on item
where we say item is sure, and then we order by timestamp,
this query
is actually going to benefit from a composite index.
If you remember, we had a default indexing policy that's going to go in
and index every property by default.
But in this case,
the indexing metrics also recommend a composite index.
So the way you'd want to interpret this would be that
we utilize these single indexes here on item and timestamp.
In other words, if we only had indexes on item in timestamp,
this query would have equivalent performance to what we just got now.
So that's the right optimization kind of kind of part of it.
The other take away that you'd want to have from this is we have a potential composite index here.
And if we were to add on this composite
index on item and timestamp, since they're both using the query
there is a high probability that this index would get used and further improve great performance.
Interesting.
And there's these predictions like it?
It's smart.
It identified that potential composite index.
Should I always take that advice or is it just something for me to then do further analysis with.
Now is a great point to touch on.
It's something that you should always try.
I would say, like any advice that you get in life or from a recommendation engine
like if you were shopping on your favorite website, right?
If you were and you got a recommendation for a product to buy,
you wouldn't just go that go and buy that product without researching it.
Right.
However, it probably would make sense to go explore it and see if it made sense.
So our recommendation is if you see a potential composite
index, is it always makes sense to try them out?
But it's something that you want to go in after trying it out,
go and monitor performance and make sure, of course, that it improves your performance.
The best way to do that is actually
once you've added on a potential composite index,
go in and rerun your query later on and double check that
that composite index is actually utilized and in which case you'd actually see it
in the utilize composite indexes section similar to how we see the utilized single index this year.
Nice measure.
Measure twice, cut once, as they say. Right.
And the way to to measure. Right.
And confirm that the query use the index is this utilized section
here like this is this is hard evidence that the query used the index
the potential index is is just a recommendation and we're pretty confident in that recommendation.
But it's definitely something
that you should verify on your own before you go and roll out in production.
Okay.
I'll show you one more query here.
And this is a similar example,
except it doesn't use order by this time.
What this query will do
is it's going to have a filter on the item again and also have a filter on the timestamp
where Timestamp came after this value and where the price is, is
greater than $75
So this is a query with an air quality filter
and then to greater than or less than filters.
So I'll go ahead and print that out.
I'll just make this bigger so you can can see that
these index metrics are showing us
as we use the indexes on item, timestamp and price.
So we could have just had these indexes and the query would have gotten equivalent
to performance to what we had.
Now of course, because we see a composite index here.
We know that if we added on these two composite indexes there's a high probability that a query
engine would go in and use these composite indexes to further improve performance.
So if this was a query that we run very often
and we observed that it has maybe a higher you charge or we want it to be ultra low latency,
our recommendation would be to go and add on these extra composite
index to further improve query performance.
I noticed that in the index spec under Prudential Composite
Index, as you have AC, I presume that means ascending is that saying that
an index specification has to indicate sorting either descending or ascending?
Yeah, that's exactly what it's saying.
And in this case it actually doesn't matter because the query doesn't have order by.
But if you were using order by like that previous example,
the ascending or descending has to match what you have in the order by.
So in this case, it doesn't matter.
It can either use ascending or descending and get the equivalent performance.
But if we had to order by this order must actually match what you have
in an order by clause whether it's ascending order by or descending order by.
Okay.
So making an assumption and a guess naively, if I were to go and build Twitter
on Cosmos and Twitter typically uses a kind of chronological timeline
where I'd be sorting descending, I would want to make sure that I used to sending
in one of my indexes and then maybe later someone else goes and does an ascending thing.
That's certainly not going to be the hot path.
So it would be okay to have one index that was descending
and not have a formal index going in the opposite direction.
Yup, that. That's correct.
You want it to be
you want to use composite indexes for any query that you're running again and again and again.
Now, there's the trade off.
Of course, if you add in a composite index, there's a slight trade off to performance.
It's not very big.
So I'd say the the threshold for when you want to add one on should be pretty low.
If it's a query that's run with any reasonable frequency as the index is probably the way to go.
But you just ran a query occasionally.
You probably don't need to worry about composite indexes.
Okay.
Now, but you said any query that you're going to run again and again
and again, you kind of got lucky because you came with ideas of bad queries
you like showed me here's three queries in how I can optimize them.
But if I've got in a Cosmos application, how do I even start?
How do I identify expensive queries?
And, and it comes up comes at it from a different direction.
Yeah.
The answer to that short answer is diagnostic logs and cosmos DB with diagnostic logs
you have a built in way to go and log every single request the Cosmos DB.
So what I'd recommend doing is go and turn on the diagnostic logs
and do things like log the full query, text log the query r you charges and log latency.
And then after you've done that, the approach you want to take
is just look at your highest latency and highest are you queries.
Once you've identified them, then go and look at the index metrics for those specific queries.
But I mean, that's a great point to make, Scott.
That's a good first step to take
because these index metrics do assume that you already have a slow query in mind.
Very cool.
So this sounds like you're really building
the base of the pyramid of some really strong indexing metrics to do.
Can you give me any any preview?
Is there any chance of a UI on top of this or any kind of way that I could go
and see the long tail in a formal, graphical way in the future?
Yeah.
So I can tell you a little bit about our road map here.
Two big areas that we want to invest in.
The first, obviously, is just showing this in the Azure portal, right?
And having a graphical UI to go and access these.
Right now, they're supported in the DOT Net SDK and the Java SDK.
Obviously, Cosmos DB has lots of other ways that you can interact with the products.
We want to go and expose the index metrics in as many of these places as as possible.
Right, for users.
The other thing that we want to invest in and that we're investing pretty heavily on
in the next year is recommendations, particularly proactive recommendations.
So these index metrics are really nice.
If you've already identified a slow query and you already know there's a problem.
Would it be great if we could proactively tell you a lot of these things
and tell you indexing recommendations, even if you don't know what your queries are?
Even if you haven't turned on diagnostic logs?
So that's something that our team is thinking about
and basically a way that we really want to make the Cosmos DB user experience
easier for developers that don't necessarily know a lot about tuning database performance.
Very cool.
And then in closing, I understand that you had a really good blog post that digs
even more deeply into indexing metrics in Cosmos DB.
Now this blog is on our Cosmos DB blog page here.
This is just what we had published when we released index metrics at night,
and it goes through some nice examples for how to enable the indexing metrics,
interpreting them like we had talked about today in some examples.
All right. Well, fantastic.
Thank you, Tim Sander, for hanging out with me today.
Absolutely. All right.
I am learning all about making my queries better with Azure Cosmos DB
indexing metrics today on Azure Friday.
Hey, thanks for watching this episode of Azure Friday.
Now I need you to like it, comment on it, tell your friends re-Tweeted Watch more Azure Friday.