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

Improve explain visibility of $min/$max indexBounds

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Won't Fix
    • Affects Version/s: 2.6.4, 2.7.5
    • Fix Version/s: None
    • Component/s: Querying
    • Labels:
      None

      Description

      In MongoDB 2.4.11, the bounds specified by $min/$max are included in the top-level indexBounds field of explain (as "start" and "end" sub-documents). However, when using $min/$max with MongoDB 2.6, these fields are not present. The index bounds are visible only in the "boundsVerbose" field of the IXSCAN stage ("indexBounds" in 2.7.5/master). Can the index-key-range-based bounds that are set by $min/$max be shown in the top-level indexBounds field, as they were in 2.4?

      For example, given the index { b: 1, e: 1, f: 1, _id: 1 }, these queries behave as below in MongoDB 2.4.11:

      > db.test.find({a: "foo", b: { $gte: 1, $lte: 3 }, c: 1, d: 2014, e: true, f: 1, _id: { $ne: 105 }}).min({b:1,e:true,f:1,_id:0}).max({b:3,e:true,f:1,_id:100000}).explain()
      {
              "cursor" : "BtreeCursor b_1_e_1_f_1__id_1",
              "isMultiKey" : false,
              "n" : 2,
              "nscannedObjects" : 945,
              "nscanned" : 945,
              "nscannedObjectsAllPlans" : 945,
              "nscannedAllPlans" : 945,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 0,
              "nChunkSkips" : 0,
              "millis" : 3,
              "indexBounds" : {
                      "start" : {
                              "b" : 1,
                              "e" : true,
                              "f" : 1,
                              "_id" : 0
                      },
                      "end" : {
                              "b" : 3,
                              "e" : true,
                              "f" : 1,
                              "_id" : 100000
                      }
              },
              "server" : "genique:14804",
              "millis" : 3
      }

      Whereas in MongoDB 2.6.4:

      > db.test.find({a: "foo", b: { $gte: 1, $lte: 3 }, c: 1, d: 2014, e: true, f: 1, _id: { $ne: 105 }}).min({b:1,e:true,f:1,_id:0}).max({b:3,e:true,f:1,_id:100000}).explain()
      {
              "cursor" : "BtreeCursor b_1_e_1_f_1__id_1",
              "isMultiKey" : false,
              "n" : 2,
              "nscannedObjects" : 945,
              "nscanned" : 946,
              "nscannedObjectsAllPlans" : 945,
              "nscannedAllPlans" : 946,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 7,
              "nChunkSkips" : 0,
              "millis" : 6,
              "indexBounds" : {
              },
              "server" : "genique:24802",
              "filterSet" : false,
              "stats" : {
                      "type" : "KEEP_MUTATIONS",
                      "works" : 947,
                      "yields" : 7,
                      "unyields" : 7,
                      "invalidates" : 0,
                      "advanced" : 2,
                      "needTime" : 944,
                      "needFetch" : 0,
                      "isEOF" : 1,
                      "children" : [
                              {
                                      "type" : "FETCH",
                                      "works" : 947,
                                      "yields" : 7,
                                      "unyields" : 7,
                                      "invalidates" : 0,
                                      "advanced" : 2,
                                      "needTime" : 944,
                                      "needFetch" : 0,
                                      "isEOF" : 1,
                                      "alreadyHasObj" : 0,
                                      "forcedFetches" : 0,
                                      "matchTested" : 2,
                                      "children" : [
                                              {
                                                      "type" : "IXSCAN",
                                                      "works" : 946,
                                                      "yields" : 7,
                                                      "unyields" : 7,
                                                      "invalidates" : 0,
                                                      "advanced" : 945,
                                                      "needTime" : 1,
                                                      "needFetch" : 0,
                                                      "isEOF" : 1,
                                                      "keyPattern" : "{ b: 1.0, e: 1.0, f: 1.0, _id: 1.0 }",
                                                      "isMultiKey" : 0,
                                                      "boundsVerbose" : "[{ : 1.0, : true, : 1.0, : 0.0 }, { : 3.0, : true, : 1.0, : 100000.0 })",
                                                      "yieldMovedCursor" : 0,
                                                      "dupsTested" : 0,
                                                      "dupsDropped" : 0,
                                                      "seenInvalidated" : 0,
                                                      "matchTested" : 0,
                                                      "keysExamined" : 946,
                                                      "children" : [ ]
                                              }
                                      ]
                              }
                      ]
              }
      }

      and 2.7.5:

      > db.test.find({a: "foo", b: { $gte: 1, $lte: 3 }, c: 1, d: 2014, e: true, f: 1, _id: { $ne: 105 }}).min({b:1,e:true,f:1,_id:0}).max({b:3,e:true,f:1,_id:100000}).explain()
      {
              "cursor" : "BtreeCursor b_1_e_1_f_1__id_1",
              "isMultiKey" : false,
              "n" : 2,
              "nscannedObjects" : 945,
              "nscanned" : 946,
              "nscannedObjectsAllPlans" : 945,
              "nscannedAllPlans" : 946,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 0,
              "nChunkSkips" : 0,
              "millis" : 2,
              "indexBounds" : {
              },
              "server" : "genique:24802",
              "stats" : {
                      "stage" : "KEEP_MUTATIONS",
                      "nReturned" : 2,
                      "executionTimeMillis" : 2,
                      "works" : 947,
                      "advanced" : 2,
                      "needTime" : 944,
                      "isEOF" : 1,
                      "invalidates" : 0,
                      "inputStage" : {
                              "stage" : "FETCH",
                              "filter" : {
                                      "$and" : [
                                              {
                                                      "$not" : {
                                                              "_id" : {
                                                                      "$eq" : 105
                                                              }
                                                      }
                                              },
                                              {
                                                      "b" : {
                                                              "$lte" : 3
                                                      }
                                              },
                                              {
                                                      "a" : {
                                                              "$eq" : "foo"
                                                      }
                                              },
                                              {
                                                      "c" : {
                                                              "$eq" : 1
                                                      }
                                              },
                                              {
                                                      "d" : {
                                                              "$eq" : 2014
                                                      }
                                              },
                                              {
                                                      "e" : {
                                                              "$eq" : true
                                                      }
                                              },
                                              {
                                                      "f" : {
                                                              "$eq" : 1
                                                      }
                                              },
                                              {
                                                      "b" : {
                                                              "$gte" : 1
                                                      }
                                              }
                                      ]
                              },
                              "nReturned" : 2,
                              "executionTimeMillis" : 2,
                              "works" : 947,
                              "advanced" : 2,
                              "needTime" : 944,
                              "isEOF" : 1,
                              "invalidates" : 0,
                              "docsExamined" : 945,
                              "alreadyHasObj" : 0,
                              "inputStage" : {
                                      "stage" : "IXSCAN",
                                      "nReturned" : 945,
                                      "executionTimeMillis" : 1,
                                      "works" : 946,
                                      "advanced" : 945,
                                      "needTime" : 1,
                                      "isEOF" : 1,
                                      "invalidates" : 0,
                                      "keysExamined" : 946,
                                      "keyPattern" : {
                                              "b" : 1,
                                              "e" : 1,
                                              "f" : 1,
                                              "_id" : 1
                                      },
                                      "isMultiKey" : false,
                                      "indexBounds" : "[{ : 1.0, : true, : 1.0, : 0.0 }, { : 3.0, : true, : 1.0, : 100000.0 })",
                                      "dupsTested" : 0,
                                      "dupsDropped" : 0,
                                      "seenInvalidated" : 0,
                                      "matchTested" : 0
                              }
                      }
              }
      }

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: