[SERVER-15167] Improve explain visibility of $min/$max indexBounds Created: 08/Sep/14  Updated: 24/Sep/14  Resolved: 24/Sep/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.4, 2.7.5
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Kevin Pulo Assignee: Ramon Fernandez Marina
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-10448 Revamp explain() formatting Closed
Participants:

 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
                        }
                }
        }
}



 Comments   
Comment by David Storch [ 10/Sep/14 ]

Hi kevin.pulo@10gen.com,

There's a new explain() in the works and is under very active development. The index bounds are a property of the index scan query execution stage. Whenever "explain 2.0" lists an execution plan with an IXSCAN type stage, there will be an associated indexBounds field. Here is an example where the plan is an OR with two child index scans:

> t.ensureIndex({a: 1})
{
	"createdCollectionAutomatically" : true,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> t.ensureIndex({b: 1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 2,
	"numIndexesAfter" : 3,
	"ok" : 1
}
> t.find({$or: [{a: {$gt: 1, $lt: 5}}, {b: {$in: [4, 5, 6]}}]}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"parsedQuery" : {
			"$or" : [
				{
					"$and" : [
						{
							"a" : {
								"$lt" : 5
							}
						},
						{
							"a" : {
								"$gt" : 1
							}
						}
					]
				},
				{
					"b" : {
						"$in" : [
							4,
							5,
							6
						]
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "SUBPLAN",
			"inputStage" : {
				"stage" : "FETCH",
				"inputStage" : {
					"stage" : "OR",
					"inputStages" : [
						{
							"stage" : "IXSCAN",
							"keyPattern" : {
								"a" : 1
							},
							"isMultiKey" : false,
							"indexBounds" : "field #0['a']: (1.0, 5.0)"
						},
						{
							"stage" : "IXSCAN",
							"keyPattern" : {
								"b" : 1
							},
							"isMultiKey" : false,
							"indexBounds" : "field #0['b']: [4.0, 4.0], [5.0, 5.0], [6.0, 6.0]"
						}
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"nReturned" : 0,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 0,
		"totalDocsExamined" : 0,
		"executionStages" : {
			"stage" : "SUBPLAN",
			"nReturned" : 0,
			"executionTimeMillis" : 0,
			"inputStage" : {
				"stage" : "FETCH",
				"nReturned" : 0,
				"executionTimeMillis" : 0,
				"docsExamined" : 0,
				"inputStage" : {
					"stage" : "OR",
					"nReturned" : 0,
					"executionTimeMillis" : 0,
					"inputStages" : [
						{
							"stage" : "IXSCAN",
							"nReturned" : 0,
							"executionTimeMillis" : 0,
							"keysExamined" : 0,
							"keyPattern" : {
								"a" : 1
							},
							"isMultiKey" : false,
							"indexBounds" : "field #0['a']: (1.0, 5.0)"
						},
						{
							"stage" : "IXSCAN",
							"nReturned" : 0,
							"executionTimeMillis" : 0,
							"keysExamined" : 0,
							"keyPattern" : {
								"b" : 1
							},
							"isMultiKey" : false,
							"indexBounds" : "field #0['b']: [4.0, 4.0], [5.0, 5.0], [6.0, 6.0]"
						}
					]
				}
			}
		},
		"rejectedPlansExecution" : [ ]
	},
	"serverInfo" : {
		"host" : "Macintosh.local",
		"port" : 27017,
		"version" : "2.7.6",
		"gitVersion" : "a8828ca2f6d50eb9f183e3bc5d317a7e356083c7",
		"os" : {
			"type" : "Darwin",
			"name" : "Mac OS X",
			"version" : "13.3.0\u0000"
		}
	}
}

Generated at Thu Feb 08 03:37:09 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.