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

Creating index on array of sub document fields causes unexpected bounds from index use in explain

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Minor - P4 Minor - P4
    • None
    • Affects Version/s: 3.4.2
    • Component/s: Index Maintenance, Querying
    • Labels:
      None
    • ALL
    • Hide

      If I insert the following document:

      db.test.insertOne({ main_data : 100, sub_docs: [{ data : 22 },{ data : 859 },{ data: 151 }]}

      And create an index on it using:

      db.test.createIndex({"sub_docs.data" : 1})

      When I perform a query to try and match the data using:

      db.text.explain().find({ sub_docs: { $elemMatch: { data: { $gte: 110, $lt: 160 }}}})

      The explain plan shows:

      "indexBounds" : {
    "sub_docs.data" : [
        "[110.0, inf.0]"
    ]
}
      Show
      If I insert the following document: db.test.insertOne({ main_data : 100, sub_docs: [{ data : 22 },{ data : 859 },{ data: 151 }]} And create an index on it using: db.test.createIndex({ "sub_docs.data" : 1}) When I perform a query to try and match the data using: db.text.explain().find({ sub_docs: { $elemMatch: { data: { $gte: 110, $lt: 160 }}}}) The explain plan shows: "indexBounds" : { "sub_docs.data" : [ "[110.0, inf.0]" ] }

      If you create a multikey index on an array of sub document fields and then perform a range query using that index it will only use one bound of the index. One bound will be one of the values from that range query whilst the other will be either Inf.0 or -Inf.0.
      The query is constructed using $elemMatch.

      This is a problem because if a user queries for a range that matches index keys close to the middle of the index, then the database will scan half the documents before getting to the correct documents to return.

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            Jackson147 Richard Jackson [X]
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: