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

For queries with .sort(), bad non-blocking plan can be cached if there are zero results

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.6.9, 3.0.0-rc9, 3.1.0
    • Affects Version/s: 2.6.1, 2.7.1
    • Component/s: Querying
    • None
    • Fully Compatible
    • ALL

      Suppose indexes {a:1} and {b:1} exist on a collection, and the user issues the following query:

      find({a: {$gt: 0}, b: {$gt: 0}}).sort({b: 1})
      

      The query planner will unconditionally choose (and cache) the plan using the {b:1} index if no documents match either the {a: {$gt: 0}} or {b: {$gt: 0}} predicates.

      This is because both the {a:1} index plan and the {b:1} index plan have the same "productivity", but the plan using {b:1} hits EOF first because it does not have a blocking SORT stage.

      Note that this issue is not limited to range predicates; it is reproducible with any bounds-generating predicates.

      Reproduce with the following:

      > db.foo.ensureIndex({a:1})
      {
      	"createdCollectionAutomatically" : true,
      	"numIndexesBefore" : 1,
      	"numIndexesAfter" : 2,
      	"ok" : 1
      }
      > db.foo.ensureIndex({b:1})
      {
      	"createdCollectionAutomatically" : false,
      	"numIndexesBefore" : 2,
      	"numIndexesAfter" : 3,
      	"ok" : 1
      }
      > db.foo.find({a:{$gt:0},b:{$gt:0}}).sort({b:1}).explain().cursor
      BtreeCursor b_1
      

      Plan cache detailed info:

      > db.foo.find({a:{$gt:0},b:{$gt:0}}).sort({b:1})
      > db.foo.getPlanCache().getPlansByQuery({a:{$gt:0},b:{$gt:0}},{},{b:1})
      [
      	{
      		"details" : {
      			"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf { b: 1.0 }, pos: 0\n)"
      		},
      		"reason" : {
      			"score" : 1.0003000000000002,
      			"stats" : {
      				"type" : "FETCH",
      				"works" : 1,
      				"yields" : 0,
      				"unyields" : 0,
      				"invalidates" : 0,
      				"advanced" : 0,
      				"needTime" : 0,
      				"needFetch" : 0,
      				"isEOF" : 1,
      				"alreadyHasObj" : 0,
      				"forcedFetches" : 0,
      				"matchTested" : 0,
      				"children" : [
      					{
      						"type" : "IXSCAN",
      						"works" : 1,
      						"yields" : 0,
      						"unyields" : 0,
      						"invalidates" : 0,
      						"advanced" : 0,
      						"needTime" : 0,
      						"needFetch" : 0,
      						"isEOF" : 1,
      						"keyPattern" : "{ b: 1.0 }",
      						"boundsVerbose" : "field #0['b']: (0.0, inf.0]",
      						"isMultiKey" : 0,
      						"yieldMovedCursor" : 0,
      						"dupsTested" : 0,
      						"dupsDropped" : 0,
      						"seenInvalidated" : 0,
      						"matchTested" : 0,
      						"keysExamined" : 0,
      						"children" : [ ]
      					}
      				]
      			}
      		},
      		"feedback" : {
      			"nfeedback" : 0,
      			"averageScore" : 0,
      			"stdDevScore" : 0,
      			"scores" : [ ]
      		},
      		"filterSet" : false
      	},
      	{
      		"details" : {
      			"solution" : "(index-tagged expression tree: tree=Node\n---Leaf { a: 1.0 }, pos: 0\n---Leaf { b: 1.0 }, pos: 0\n)"
      		},
      		"reason" : {
      			"score" : 1.0002,
      			"stats" : {
      				"type" : "FETCH",
      				"works" : 1,
      				"yields" : 0,
      				"unyields" : 0,
      				"invalidates" : 0,
      				"advanced" : 0,
      				"needTime" : 0,
      				"needFetch" : 0,
      				"isEOF" : 1,
      				"alreadyHasObj" : 0,
      				"forcedFetches" : 0,
      				"matchTested" : 0,
      				"children" : [
      					{
      						"type" : "AND_HASH",
      						"works" : 1,
      						"yields" : 0,
      						"unyields" : 0,
      						"invalidates" : 0,
      						"advanced" : 0,
      						"needTime" : 0,
      						"needFetch" : 0,
      						"isEOF" : 1,
      						"flaggedButPassed" : 0,
      						"flaggedInProgress" : 0,
      						"memUsage" : 0,
      						"memLimit" : 33554432,
      						"children" : [
      							{
      								"type" : "IXSCAN",
      								"works" : 1,
      								"yields" : 0,
      								"unyields" : 0,
      								"invalidates" : 0,
      								"advanced" : 0,
      								"needTime" : 0,
      								"needFetch" : 0,
      								"isEOF" : 1,
      								"keyPattern" : "{ a: 1.0 }",
      								"boundsVerbose" : "field #0['a']: (0.0, inf.0]",
      								"isMultiKey" : 0,
      								"yieldMovedCursor" : 0,
      								"dupsTested" : 0,
      								"dupsDropped" : 0,
      								"seenInvalidated" : 0,
      								"matchTested" : 0,
      								"keysExamined" : 0,
      								"children" : [ ]
      							},
      							{
      								"type" : "IXSCAN",
      								"works" : 0,
      								"yields" : 0,
      								"unyields" : 0,
      								"invalidates" : 0,
      								"advanced" : 0,
      								"needTime" : 0,
      								"needFetch" : 0,
      								"isEOF" : 0,
      								"keyPattern" : "{}",
      								"boundsVerbose" : "field #0['b']: (0.0, inf.0]",
      								"isMultiKey" : 0,
      								"yieldMovedCursor" : 0,
      								"dupsTested" : 0,
      								"dupsDropped" : 0,
      								"seenInvalidated" : 0,
      								"matchTested" : 0,
      								"keysExamined" : 0,
      								"children" : [ ]
      							}
      						]
      					}
      				]
      			}
      		},
      		"feedback" : {
      
      		},
      		"filterSet" : false
      	},
      	{
      		"details" : {
      			"solution" : "(index-tagged expression tree: tree=Node\n---Leaf { a: 1.0 }, pos: 0\n---Leaf \n)"
      		},
      		"reason" : {
      			"score" : 1.0002,
      			"stats" : {
      				"type" : "SORT",
      				"works" : 1,
      				"yields" : 0,
      				"unyields" : 0,
      				"invalidates" : 0,
      				"advanced" : 0,
      				"needTime" : 0,
      				"needFetch" : 0,
      				"isEOF" : 0,
      				"forcedFetches" : 0,
      				"memUsage" : 0,
      				"memLimit" : 33554432,
      				"children" : [
      					{
      						"type" : "KEEP_MUTATIONS",
      						"works" : 0,
      						"yields" : 0,
      						"unyields" : 0,
      						"invalidates" : 0,
      						"advanced" : 0,
      						"needTime" : 0,
      						"needFetch" : 0,
      						"isEOF" : 0,
      						"children" : [
      							{
      								"type" : "FETCH",
      								"works" : 0,
      								"yields" : 0,
      								"unyields" : 0,
      								"invalidates" : 0,
      								"advanced" : 0,
      								"needTime" : 0,
      								"needFetch" : 0,
      								"isEOF" : 0,
      								"alreadyHasObj" : 0,
      								"forcedFetches" : 0,
      								"matchTested" : 0,
      								"children" : [
      									{
      										"type" : "IXSCAN",
      										"works" : 0,
      										"yields" : 0,
      										"unyields" : 0,
      										"invalidates" : 0,
      										"advanced" : 0,
      										"needTime" : 0,
      										"needFetch" : 0,
      										"isEOF" : 0,
      										"keyPattern" : "{}",
      										"boundsVerbose" : "field #0['a']: (0.0, inf.0]",
      										"isMultiKey" : 0,
      										"yieldMovedCursor" : 0,
      										"dupsTested" : 0,
      										"dupsDropped" : 0,
      										"seenInvalidated" : 0,
      										"matchTested" : 0,
      										"keysExamined" : 0,
      										"children" : [ ]
      									}
      								]
      							}
      						]
      					}
      				]
      			}
      		},
      		"feedback" : {
      
      		},
      		"filterSet" : false
      	}
      ]
      >
      

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            rassi J Rassi
            Votes:
            1 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: