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

Query with "_id in empty array" traverses the full collection when given the _id index as a hint

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Fixed
    • Priority: Icon: Major - P3 Major - P3
    • 8.1.0-rc0
    • Affects Version/s: 8.0.1
    • Component/s: None
    • None
    • Query Optimization
    • Fully Compatible
    • ALL
    • v8.0
    • Hide

      Create a database with two documents:

      db.Items.insert({foo: "bar1"});
db.Items.insert({foo: "bar2"});

      Find all items whose ID is in an empty list and provide the ID index as a hint:

      db.Items.explain("executionStats").find({_id: {$in: []}}).hint({_id: 1})

      Expected behavior: the ID index is used and mongo reports no results without having to examine any docs.

      Observer behavior: mongo traverses every single doc in the collection:

      {
  ...
  queryPlanner: {
    ...
    parsedQuery: { '$alwaysFalse': 1 },
    ...
  executionStats: {
    ...
    totalKeysExamined: 2,
    totalDocsExamined: 2,

       

      Show
      Create a database with two documents: db.Items.insert({foo: "bar1" }); db.Items.insert({foo: "bar2" }); Find all items whose ID is in an empty list and provide the ID index as a hint: db.Items.explain( "executionStats" ).find({_id: {$in: []}}).hint({_id: 1}) Expected behavior : the ID index is used and mongo reports no results without having to examine any docs. Observer behavior : mongo traverses every single doc in the collection: {   ...   queryPlanner: {     ...     parsedQuery: { '$alwaysFalse' : 1 },     ...   executionStats: {     ...     totalKeysExamined: 2,     totalDocsExamined: 2,  

      When performing a query that amounts to...

      db.collection.find({ _id: { $in: [] }).hint({ _id: 1 })

      ... mongo 8 traverses the entire collection before reporting there are no results.

       

      Under the hood, it seems that mongo 8 converts the find query to...

       

      { '$alwaysFalse': 1 }

      ... before executing it, where providing an index hint with the latter results in the full collection traversal.

       

       

      We discovered this by noting a major performance regression in our application when migrating from mongo 7 to mongo 8. I don't believe that mongo 7 performs the above "under the hood" conversion, which means the query + hint works immediately as expected.

       

      One can, of course, put in client-side logic to avoid making the above query since we know a-prior it will return no results, but it's fragile to assume this will always be done as it's reasonable for a dev to regard this as a trivial optimization.

       

      It's also worth noting that we're aware hints should only be used if absolutely necessary, and have high confidence they're being used appropriately in our context.

       

            Assignee:
            jess.balint@mongodb.com Jess Balint
            Reporter:
            mirek@rynly.com Mirek Kukla
            Votes:
            0 Vote for this issue
            Watchers:
            18 Start watching this issue

              Created:
              Updated:
              Resolved: