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

$expr does not use partial index

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major - P3
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Backlog
    • Component/s: Querying
    • Labels:
      None
    • Operating System:
      ALL
    • Steps To Reproduce:
      Hide

      Given the Query:

      db.getCollection('contacts').find({ 
       $expr: {
         $eq: ["$name", "A"]
       }
      }).explain()
      

      Explain Result With RegularIndex:

      db.getCollection('contacts').createIndex({name: 1, email: 1})

       "winningPlan" : {
                  "stage" : "FETCH",
                  "filter" : {
                      "$expr" : {
                          "$eq" : [ 
                              "$name", 
                              {
                                  "$const" : "A"
                              }
                          ]
                      }
                  },
                  "inputStage" : {
                      "stage" : "IXSCAN",
                      "keyPattern" : {
                          "name" : 1.0,
                          "email" : 1.0
                      },
                      "indexName" : "name_1_email_1",
                      "isMultiKey" : false,
                      "multiKeyPaths" : {
                          "name" : [],
                          "email" : []
                      },
                      "isUnique" : false,
                      "isSparse" : false,
                      "isPartial" : false,
                      "indexVersion" : 2,
                      "direction" : "forward",
                      "indexBounds" : {
                          "name" : [ 
                              "[\"A\", \"A\"]"
                          ],
                          "email" : [ 
                              "[MinKey, MaxKey]"
                          ]
                      }
                  }
              },

      Explain Result With Partial Index:

      db.getCollection('contacts').createIndex({name: 1, email: 1}, { partialFilterExpression: { name: { $eq: "A" } } })
      

      "winningPlan" : {
                  "stage" : "COLLSCAN",
                  "filter" : {
                      "$and" : [ 
                          {
                              "$expr" : {
                                  "$eq" : [ 
                                      "$name", 
                                      {
                                          "$const" : "A"
                                      }
                                  ]
                              }
                          }, 
                          {
                              "name" : {
                                  "$_internalExprEq" : "A"
                              }
                          }
                      ]
                  },
                  "direction" : "forward"
              },

      Show
      Given the Query: db.getCollection( 'contacts' ).find({ $expr: { $eq: [ "$name" , "A" ] } }).explain() Explain Result With RegularIndex: db.getCollection( 'contacts' ).createIndex({name: 1 , email: 1 }) "winningPlan" : { "stage" : "FETCH" , "filter" : { "$expr" : { "$eq" : [ "$name" , { "$const" : "A" } ] } }, "inputStage" : { "stage" : "IXSCAN" , "keyPattern" : { "name" : 1.0 , "email" : 1.0 }, "indexName" : "name_1_email_1" , "isMultiKey" : false , "multiKeyPaths" : { "name" : [], "email" : [] }, "isUnique" : false , "isSparse" : false , "isPartial" : false , "indexVersion" : 2 , "direction" : "forward" , "indexBounds" : { "name" : [ "[\"A\", \"A\"]" ], "email" : [ "[MinKey, MaxKey]" ] } } }, Explain Result With Partial Index: db.getCollection( 'contacts' ).createIndex({name: 1 , email: 1 }, { partialFilterExpression: { name: { $eq: "A" } } }) "winningPlan" : { "stage" : "COLLSCAN" , "filter" : { "$and" : [ { "$expr" : { "$eq" : [ "$name" , { "$const" : "A" } ] } }, { "name" : { "$_internalExprEq" : "A" } } ] }, "direction" : "forward" },

      Description

      In the following example:

       

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

              • Created:
                Updated: