Query charge with default indexing policy: 4,138.66 RUs WHERE c.BuyerState = "NJ" AND Contains (c.id, "abc", true)Ĭomposite index: (BuyerState ASC, id ASC) WHERE c.BuyerState = "NJ" AND Contains (c.id, "abc", true) Query with added ORDER BY: In fact, the impact can be so significant, that it can be worthwhile to rewrite some queries with system functions to use ORDER BY so that they can leverage composite indexes. When you use a high cardinality property in the system function, composite indexes may have a big impact. Queries with ORDER BY and system functions can now benefit from composite indexes. Queries with ORDER BY and system functions: Query charge with composite index: 2.9 RUs Query charge with default indexing policy: 5.08 RUs For example, if the property in the AVG() system function was price, instead of _ts, you might not need a composite index in the first place since price has fewer possible values than _ts.Ĭomposite index: (BuyerState ASC, price ASC) Queries with higher cardinality properties typically benefit more from composite indexes. Query charge with composite index: 39.8 RUs Query charge with default indexing policy: 186.25 RUs Query:Ĭomposite index: (BuyerState ASC, _ts ASC) Previously, composite indexes could only optimize queries with a COUNT(1) aggregate. Queries with aggregates and an equality filters:Īggregate queries (SUM, AVG, MIN, or MAX) can now benefit from composite indexes. Composite indexes will have a bigger impact on high cardinality properties, such as _ts or id. This optimization tends to have a higher impact for multiple range filters than queries with only equality filters. Query charge with both composite indexes: 72.23 RUs Query charge with default indexing policy: 533.47 RUs Therefore, using multiple composite indexes can help optimize queries with multiple range filters. Remember, a single composite index can only apply to one range filter. This allows you to optimize queries that have multiple equality and range filters in the same filter expression. Queries can now use multiple composite indexes in the same filter expression. Queries with multiple equality and range filters: Multiple items can belong to the same CartId but each item has its own unique id value. Here is an example item:Įach item is a different product in an online clothing store. For these tests, we’ve ingested 9 million items into an Azure Cosmos DB container. This post looks at examples of the new use cases for composite indexes. WHERE c.name = “Tim” AND CONTAINS(c.biography, “developer”) (NEW) Queries with aggregates and equality filters WHERE c.name = “Tim” AND c.age > 18 and c._ts > 100 (NEW) Queries with multiple equality and/or range filters Queries with an equality filter and ORDER BY Queries that ORDER BY multiple properties Current use cases for composite indexes: Use case You can now use composite indexes in Azure Cosmos DB to optimize additional cases of the most common inefficient queries! Anytime you have a slow or high request unit (RU) query, you should consider optimizing it with a composite index.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |