Plans which fetch different numbers of documents can tie

XMLWordPrintableJSON

    • Query Optimization
    • ALL
    • Query 2019-04-08, Query 2019-07-29, Query 2019-08-12, Query 2019-08-26, Query 2019-09-09
    • 0
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      The nscanned value is the same for both plans, but we choose the one with a much higher nscannedObjects:

      t.drop();
t.ensureIndex({type: 1});
t.ensureIndex({type: 1, folder: 1});
for (var i = 0; i < 110; i++) { t.insert({type: "folder", folder: "b"}); }
t.insert({type: "folder", folder: "m"});
t.find({type: "folder", folder: /m/}).explain(true)

      I get the following explain output from a 2.6.3 server:

      {
	"cursor" : "BtreeCursor type_1",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 111,
	"nscanned" : 111,
	"nscannedObjectsAllPlans" : 112,
	"nscannedAllPlans" : 222,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 1,
	"nChunkSkips" : 0,
	"millis" : 1,
	"indexBounds" : {
		"type" : [
			[
				"folder",
				"folder"
			]
		]
	},
	"allPlans" : [
		{
			"cursor" : "BtreeCursor type_1",
			"isMultiKey" : false,
			"n" : 1,
			"nscannedObjects" : 111,
			"nscanned" : 111,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"type" : [
					[
						"folder",
						"folder"
					]
				]
			}
		},
		{
			"cursor" : "BtreeCursor type_1_folder_1",
			"isMultiKey" : false,
			"n" : 1,
			"nscannedObjects" : 1,
			"nscanned" : 111,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"type" : [
					[
						"folder",
						"folder"
					]
				],
				"folder" : [
					[
						"",
						{

						}
					],
					[
						/m/,
						/m/
					]
				]
			}
		}
	],
	"server" : "Macintosh.local:27017",
	"filterSet" : false,
	"stats" : {
		"type" : "KEEP_MUTATIONS",
		"works" : 113,
		"yields" : 1,
		"unyields" : 1,
		"invalidates" : 0,
		"advanced" : 1,
		"needTime" : 110,
		"needFetch" : 0,
		"isEOF" : 1,
		"children" : [
			{
				"type" : "FETCH",
				"works" : 112,
				"yields" : 1,
				"unyields" : 1,
				"invalidates" : 0,
				"advanced" : 1,
				"needTime" : 110,
				"needFetch" : 0,
				"isEOF" : 1,
				"alreadyHasObj" : 0,
				"forcedFetches" : 0,
				"matchTested" : 1,
				"children" : [
					{
						"type" : "IXSCAN",
						"works" : 111,
						"yields" : 1,
						"unyields" : 1,
						"invalidates" : 0,
						"advanced" : 111,
						"needTime" : 0,
						"needFetch" : 0,
						"isEOF" : 1,
						"keyPattern" : "{ type: 1.0 }",
						"boundsVerbose" : "field #0['type']: [\"folder\", \"folder\"]",
						"isMultiKey" : 0,
						"yieldMovedCursor" : 0,
						"dupsTested" : 0,
						"dupsDropped" : 0,
						"seenInvalidated" : 0,
						"matchTested" : 0,
						"keysExamined" : 111,
						"children" : [ ]
					}
				]
			}
		]
	}
}

      This happens because the plans tie: both return the same number of documents in the same amount of work cycles, even though one plan has to fetch more documents.

            Assignee:
            [DO NOT USE] Backlog - Query Optimization
            Reporter:
            David Storch
            Votes:
            7 Vote for this issue
            Watchers:
            32 Start watching this issue

              Created:
              Updated:
              Resolved: