$expr not using indices in $match stage

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Duplicate
    • Priority: 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)  
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      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
            Reporter:
            Hermann Baumgartl
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved: