{"id":2373,"date":"2021-02-16T14:22:37","date_gmt":"2021-02-16T22:22:37","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=2373"},"modified":"2021-02-16T14:22:37","modified_gmt":"2021-02-16T22:22:37","slug":"new-ways-to-use-composite-indexes","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/new-ways-to-use-composite-indexes\/","title":{"rendered":"New ways to use composite indexes in Azure Cosmos DB"},"content":{"rendered":"

You can now use\u00a0composite indexes<\/span><\/a> 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.<\/span><\/p>\n

Current use cases for composite indexes:<\/h3>\n\n\n\n\n\n\n\n\n
Use case<\/strong><\/td>\nExample<\/strong><\/td>\n<\/tr>\n
Queries that ORDER BY multiple properties<\/td>\n\n
SELECT * \r\nFROM c\r\nORDER BY c.name, c.age<\/pre>\n<\/td>\n<\/tr>\n
Queries with an equality filter and ORDER BY<\/td>\n\n
SELECT *\r\nFROM c\r\nWHERE c.name = \u201cTim\u201d\r\nORDER BY c.age<\/pre>\n<\/td>\n<\/tr>\n
(NEW)<\/strong> Queries with multiple equality and\/or range filters<\/td>\n\n
SELECT *\r\nFROM c\r\nWHERE c.name = \u201cTim\u201d AND c.age > 18 and c._ts > 100<\/pre>\n<\/td>\n<\/tr>\n
(NEW)<\/strong> Queries with aggregates and equality filters<\/td>\n\n
SELECT SUM(c.total)\r\nFROM c\r\nWHERE c.name = \u201cTim\u201d<\/pre>\n<\/td>\n<\/tr>\n
(NEW)<\/strong> Queries with system functions<\/td>\n\n
SELECT *\r\nFROM c\r\nWHERE c.name = \u201cTim\u201d AND CONTAINS(c.biography, \u201cdeveloper\u201d)\r\nORDER BY c.name, c.biography<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
This post looks at examples of the new use cases for composite indexes. For these tests, we\u2019ve ingested 9 million items into an Azure Cosmos DB container. Here is an example item:<\/p>\n
\"Cosmos<\/a><\/p>\n
Each item is a different product in an online clothing store. Multiple items can belong to the same CartId<\/strong> but each item has its own unique id<\/strong> value.<\/p>\n
 <\/p>\n
Queries with multiple equality and range filters:<\/strong><\/h4>\n
Queries can now use multiple composite indexes in the same filter expression. This allows you to optimize queries that have multiple equality and range filters in the same filter expression. Remember, a single composite index can only apply to one range filter. Therefore, using multiple composite indexes can help optimize queries with multiple range filters.<\/p>\n
Query: <\/strong><\/p>\n
SELECT TOP 10 *\r\nFROM c\r\nWHERE c.BuyerState = \"TN\" and c._ts > 1611947901\u00a0\r\nand c.id > \"9c5e4e08-e1d2-4091-8062-78d1da832523\"<\/pre>\n
Composite indexes:<\/strong><\/p>\n
    \n
  1. (BuyerState ASC, _ts ASC)<\/li>\n
  2. (BuyerState ASC, id ASC)<\/li>\n<\/ol>\n
    Query charge with default indexing policy: <\/strong>533.47 RUs<\/p>\n
    Query charge with both composite indexes: <\/strong>72.23 RUs<\/p>\n
    \u00a0<\/strong><\/p>\n
    This optimization tends to have a higher impact for multiple range filters than queries with only equality filters. Composite indexes will have a bigger impact on high cardinality properties, such as _ts<\/strong> or id<\/strong>.<\/p>\n
    \u00a0<\/strong><\/p>\n
    Queries with aggregates and an equality filters:<\/strong><\/h5>\n
    Aggregate queries (SUM, \u00a0AVG, MIN, or MAX) can now benefit from composite indexes. Previously, composite indexes could only optimize queries with a COUNT(1)<\/strong> aggregate.<\/p>\n
    Query: \r\n<\/strong>SELECT AVG(c._ts)\r\nFROM c \r\nWHERE c.BuyerState = \"TN\"<\/pre>\n
    \u00a0<\/strong><\/p>\n
    Composite index:\u00a0<\/strong>(BuyerState ASC, _ts ASC)<\/p>\n
    Query charge with default indexing policy:<\/strong> 186.25 RUs<\/p>\n
    Query charge with composite index: <\/strong>39.8 RUs<\/p>\n
    \u00a0<\/strong><\/p>\n
    Queries with higher cardinality properties typically benefit more from composite indexes. For example, if the property in the AVG() <\/strong> system function was\u00a0price<\/strong>, instead of _ts<\/strong>, you might not need a composite index in the first place since price has fewer possible values than _ts<\/strong>.<\/p>\n
    Here\u2019s an example:<\/p>\n
    Query: <\/strong><\/p>\n
    SELECT AVG(c.price)\r\nFROM c\r\nWHERE c.BuyerState = \"TN\"<\/pre>\n
    \u00a0<\/strong><\/p>\n
    Composite index:\u00a0<\/strong>(BuyerState ASC, price ASC)<\/p>\n
    Query charge with default indexing policy: <\/strong>5.08 RUs<\/p>\n
    Query charge with composite index: <\/strong>2.9 RUs<\/p>\n
    \u00a0<\/strong><\/p>\n
    Queries with ORDER BY and system functions:<\/strong><\/h5>\n
    Queries with ORDER BY and system functions can now benefit from composite indexes. When you use a high cardinality property in the system function, composite indexes may have a big impact. 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.<\/p>\n
     <\/p>\n
    Here\u2019s an example:<\/p>\n
    Original Query: \r\n<\/strong>SELECT *\r\nFROM c\r\nWHERE c.BuyerState = \"NJ\" AND Contains (c.id, \"abc\", true)<\/pre>\n
    Query with added ORDER BY:\r\n<\/strong>SELECT *\r\nFROM c\r\nWHERE c.BuyerState = \"NJ\" AND Contains (c.id, \"abc\", true)\r\nORDER BY c.BuyerState, c.id<\/pre>\n
    \u00a0<\/strong><\/p>\n
    Composite index: <\/strong>(BuyerState ASC, id ASC)<\/p>\n
    Query charge with default indexing policy: <\/strong>4,138.66 RUs<\/p>\n
    Query charge with composite index: <\/strong>51.3 RUs<\/p>\n
    \u00a0<\/strong><\/p>\n
    Adding an ORDER BY<\/strong> clause for the property in a system function improves system function execution. This is broader than just composite indexes and can often help in any query with a system function on a high cardinality property. Here\u2019s an example that doesn\u2019t involve composite indexes:<\/p>\n
    \u00a0<\/strong><\/p>\n
    Here\u2019s an example:<\/p>\n
    Original Query: <\/strong><\/p>\n
    SELECT *\r\nFROM c\r\nWHERE RegexMatch (c.id, \"abc\")<\/pre>\n
    Query with added ORDER BY: <\/strong><\/p>\n
    SELECT *\r\nFROM c\r\nWHERE RegexMatch (c.id, \"abc\")\r\nORDER BY c.id<\/pre>\n
    Query charge without ORDER BY: <\/strong>8,845.61\u00a0RUs<\/p>\n
    Query charge with ORDER BY: <\/strong>92.71 RUs<\/p>\n
    \u00a0<\/strong><\/p>\n
    Queries with the following system functions are most likely to see big improvements since their efficiency depends on cardinality of the property in the system function:<\/p>\n