[SERVER-44931] Covered queries do not work when $ne/$nin operators are present Created: 03/Dec/19  Updated: 19/Dec/19  Resolved: 19/Dec/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Forest Trimble Assignee: Jacob Evans
Resolution: Duplicate Votes: 0
Labels: qopt-team
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-27646 covered index should be used when nul... Closed
Operating System: ALL
Steps To Reproduce:

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.

Sprint: Query 2019-12-30
Participants:

 Description   

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)



 Comments   
Comment by Forest Trimble [ 19/Dec/19 ]

Brilliant, thank you!

Comment by Jacob Evans [ 19/Dec/19 ]

Hi Forest Trimble,

This was fixed in SERVER-27646 which is rolled into 4.2. Please upgrade to pick up the fix. There are no current plans to backport the patch to the 4.0 series.

Comment by Forest Trimble [ 18/Dec/19 ]

I ran this on 4.0.13

Comment by Jacob Evans [ 13/Dec/19 ]

Hello,

I was unable to reproduce this locally. The provided query produces an IXSCAN followed by a PROJECTION_COVERED on master. Forest Trimble can you provide the version that you witnessed this behavior on? Carl can you provide details of how you recreated a similar behavior?

Comment by Carl Champain (Inactive) [ 04/Dec/19 ]

Hi trimble.forest@gmail.com,

Thanks for taking the time to submit this report.
I was able to successfully recreate a similar behavior; I will pass this ticket along to the Query team for further investigation. Please continue to watch this ticket for updates.

Kind regards,
Carl

Comment by Forest Trimble [ 03/Dec/19 ]

seems like this issue is related: https://jira.mongodb.org/browse/SERVER-29300 - i just wanted to point out the utility of covered queries in this type of thing.

Generated at Thu Feb 08 05:07:24 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.