Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-89783

$expr not using indices in $match stage

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 7.0.8
    • Component/s: None
    • None
    • Query Optimization
    • ALL
    • Hide

      Collection with an field "last_processed_time" which is a regulare datetime. Create a standard index on this field. 

       

          {
              "v" : 2.0,
              "key" : {
                  "last_processed_time" : 1.0
              },
              "name" : "ix_last_processed_time",
              "background" : true
          } 

       

      • e this as $match stage in an aggregation q
      • uery:

       

      {
          "$expr" : {
              "$gt" : [
                  "$last_processed_time",
                  {
                      "$dateSubtract" : {
                          "startDate" : "$$NOW",
                          "unit" : "day",
                          "amount" : 21.0
                      }
                  }
              ]
          }
      } 

      Tested on:

       

      • Atlas hosted MongoDB 7.0.8 (dedicated M30)

       

      Show
      Collection with an field "last_processed_time" which is a regulare datetime. Create a standard index on this field.    { "v" : 2.0, "key" : { "last_processed_time" : 1.0 }, "name" : "ix_last_processed_time" , "background" : true }   e this as $match stage in an aggregation q uery:   { "$expr" : { "$gt" : [ "$last_processed_time" , { "$dateSubtract" : { "startDate" : "$$NOW" , "unit" : "day" , "amount" : 21.0 } } ] } } Tested on:   Atlas hosted MongoDB 7.0.8 (dedicated M30)  

      When using an $expr expression in a $match stage, the query engine does use COLLSCAN instead of an IXSCAN. I used this $match stage. There is an index on "last_processed_time:

      { "$expr" : { "$gt" : [ "$last_processed_time", { "$dateSubtract" : { "startDate" : "$$NOW", "unit" : "day", "amount" : 21.0 } } ] } }  

      Giving an Index hint does not help, it just adds a separete Filter stage to the winning plan.

      {
          "stage" : "FETCH",
          "filter" : {
              "$expr" : {
                  "$gt" : [
                      "$last_processed_time",
                      {
                          "$dateSubtract" : {
                              "startDate" : "$$NOW",
                              "unit" : {
                                  "$const" : "day"
                              },
                              "amount" : {
                                  "$const" : 21.0
                              }
                          }
                      }
                  ]
              }
          }
      } 

      If no $expr stage is used, the query engine uses the existing index. Seems to happen since MongoDB 7.0. In MongoDB 6.0, an IXSCAN is used.

      Also see https://www.mongodb.com/community/forums/t/expr-not-using-indices/276830

            Assignee:
            Unassigned Unassigned
            Reporter:
            hermann.baumgartl@varta-microbattery.com Hermann Baumgartl
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved: