locked
CosmosDB Composite Index Unused by a query RRS feed

  • Question

  • I am creating a composite Index on a container to cater to queries with multiple filters
    ex: SELECT value count(1) FROM c where c.uId="12445" and c.la>= 1585076195

    Details: uId is the partitionKey

    Custom Index :

    { 
      "indexingMode": "consistent",
      "automatic": true,
      "includedPaths":
         [
            { "path": "/uId/?" },
            { "path": "/la/?" } 
         ], 
      "excludedPaths": 
         [
           { "path": "/*" }, 
           { "path": "/\"_etag\"/?" } 
         ],
      "compositeIndexes": 
         [
          [ 
            { "path": "/uId", "order": "ascending" }, 
            { "path": "/la", "order": "ascending" } 
          ]
         ] 
     }

    Note : After updating the Index the same query consumes more RUs also the Index hit document count is 0 indicating that composite index is unused.
    Also I observed that the index size on the container in the Metric section ,doesn't change after updating the Indexing policy, it remains same as it was for the default Index even after a day or so, it actually increased few MBs. With my understanding it should change right as we are now excluding everything from indexing and referencing less paths with out custom indexing policy.

    Can someone tell me why the query is consuming more RUs after updating the Index and the Composite index not being used by the query also no change in the Index size

    Sunday, May 17, 2020 8:03 AM

All replies

  • Hi Safwan,

    Apologies for the delay in responding to your question. It appears you also asked the same question on Stack Overflow: CosmosDB Composite Index Unused by a query and that has gone unanswered.

    Were you able to run this query without the need of establishing a composite index? If so, which it appears is the case, then the query is essentially a range query, or the filters do not align with the established composite index and the composite index is not leveraged.

    Please see: Queries with filters on multiple properties

    • The properties in the query's filter should match those in composite index. If a property is in the composite index but is not included in the query as a filter, the query will not utilize the composite index.
    • If a query has additional properties in the filter that were not defined in a composite index, then a combination of composite and range indexes will be used to evaluate the query. This will require fewer RU's than exclusively using range indexes.
    • If a property has a range filter (><<=>=, or !=), then this property should be defined last in the composite index. If a query has more than one range filter, it will not utilize the composite index.
    • When creating a composite index to optimize queries with multiple filters, the ORDER of the composite index will have no impact on the results. This property is optional.
    • If you do not define a composite index for a query with filters on multiple properties, the query will still succeed. However, the RU cost of the query can be reduced with a composite index.

    With regard to your Include and Exclude path, because /uld/ and /la/ are not very long paths, you are not benefiting like if they were /a/b/c/d/e/, where the intent is to set the precision on the 6th element.

    Regards,

    Mike

    Thursday, June 4, 2020 2:13 AM