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

Covered queries do not work when $ne/$nin operators are present

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Index Maintenance
    • ALL
    • Hide

      You can create a very simple database with an index on only a single field and query on $ne on that field, your query will need to look at every document that matches the query rather than just returning the index.

      The more complicated example above was purely to illustrate that there is some utility in being able to leverage covered queries for this type of thing.

      Show
      You can create a very simple database with an index on only a single field and query on $ne on that field, your query will need to look at every document that matches the query rather than just returning the index. The more complicated example above was purely to illustrate that there is some utility in being able to leverage covered queries for this type of thing.
    • Query 2019-12-30

      For queries that have $ne/$nin operators, they seem to be unable to use covered queries. This is seemingly not particularly useful for indexes that apply only to that field, but if the $ne is a compound index, we could potentially be dropping a dramatically large amount of data even with the $ne filter. For example, consider this query:

       

       mongos> filter = { 
         'target._id':{ 
            $in:idsList
         }
      },
      actor:{ 
         $ne:'system'
      },
      published:{ 
         $gte:moment().subtract(120,
         'days'   ).valueOf()
      }
      }
      mongos> var exp = db.activities.explain('executionStats')
      mongos> exp.find(filter,
      { 
         _id:0,
         published:1
      })
      
      ..."executionStats":{ 
         "nReturned":117287,
         "executionTimeMillis":587,
         "totalKeysExamined":120551,
         "totalDocsExamined":117287,
         "executionStages":{ 
            "stage":"SINGLE_SHARD",
            "nReturned":117287,
            "executionTimeMillis":587,
            "totalKeysExamined":120551,
            "totalDocsExamined":117287,
            "totalChildMillis":NumberLong(571),
            "shards":[ 
               { 
                  "shardName":"beta-blend-d",
                  "executionSuccess":true,
                  "executionStages":{ 
                     "stage":"PROJECTION",
                     "nReturned":117287,
                     "executionTimeMillisEstimate":176,
                     "works":120551,
                     "advanced":117287,
                     "needTime":3263,
                     "needYield":0,
                     "saveState":946,
                     "restoreState":946,
                     "isEOF":1,
                     "invalidates":0,
                     "transformBy":{ 
                        "_id":0,
                        "published":1
                     },
                     "inputStage":{ 
                        "stage":"FETCH",
                        "filter":{ 
                           "actor":{ 
                              "$not":{ 
                                 "$eq":"system"
                              }
                           }
                        },
                        "nReturned":117287,
                        "executionTimeMillisEstimate":166,
                        "works":120551,
                        "advanced":117287,
                        "needTime":3263,
                        "needYield":0,
                        "saveState":946,
                        "restoreState":946,
                        "isEOF":1,
                        "invalidates":0,
                        "docsExamined":117287,
                        "alreadyHasObj":0,
                        "inputStage":{ 
                           "stage":"IXSCAN",
                           "nReturned":117287,
                           "executionTimeMillisEstimate":151,
                           "works":120551,
                           "advanced":117287,
                           "needTime":3263,
                           "needYield":0,
                           "saveState":946,
                           "restoreState":946,
                           "isEOF":1,
                           "invalidates":0,
                           "keyPattern":{ 
                              "target._id":1,
                              "published":-1,
                              "actor":1,
                              "verb":1
                           },
                           "indexName":"target.id_1_published-1_actor_1_verb_1",
                           "isMultiKey":true,
                           "multiKeyPaths":{ 
                              "target._id":[ 
                                 "target"
                              ],
                              "published":[ 
      
                              ],
                              "actor":[ 
      
                              ],
                              "verb":[ 
      
                              ]
                           },
                           "isUnique":false,
                           "isSparse":false,
                           "isPartial":false,
                           "indexVersion":2,
                           "direction":"forward",
                           "indexBounds":{ 
                              "target._id":[ 
                                 ... // a bunch of target._id bounds
                              ],
                              "published":[ 
                                 "[inf.0, 1565026916796.0]"
                              ],
                              "actor":[ 
                                 "[MinKey, \"system\")",
                                 "(\"system\", MaxKey]"
                              ],
                              "verb":[ 
                                 "[MinKey, MaxKey]"
                              ]
                           },
                           "keysExamined":120551,
                           "seeks":3264,
                           "dupsTested":117287,
                           "dupsDropped":0,
                           "seenInvalidated":0
                        }
                     }
                  }
               }
            ]
         }
      },
      ...
      }
      

      It seems like the fact that there is a FETCH stage in this plan (even though it will never eliminate any documents as the index scan prevents it) prevents us from being able to use a covered query (`published` is part of the index, so should not require examining any documents)

            Assignee:
            jacob.evans@mongodb.com Jacob Evans
            Reporter:
            trimble.forest@gmail.com Forest Trimble
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: