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

Query planner prefers suboptimal index to using index intersection

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.0.1
    • Component/s: Querying
    • None
    • ALL

      find() with the $all keyword is slow even on indexed fields. The query planner chooses a plan requiring examining many extra documents, even though it could use an index intersection. It seems that the collection scan is the first to return the 101 entries, but the executionTimeMillisEstimate is much higher (2060 vs 10) than for the AND_SORTED query, which should be the fastest. I'm not sure why the collection scan is beating the AND_SORTED query, given the radically different executionTimeMillisEstimate.

      Unable to find source-code formatter for language: json. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
      {
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "shangrila.materials",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			"$and" : [
      				{
      					"elements" : {
      						"$eq" : "Li"
      					}
      				},
      				{
      					"elements" : {
      						"$eq" : "F"
      					}
      				},
      				{
      					"elements" : {
      						"$eq" : "Mn"
      					}
      				}
      			]
      		},
      		"winningPlan" : {
      			"stage" : "FETCH",
      			"filter" : {
      				"$and" : [
      					{
      						"elements" : {
      							"$eq" : "F"
      						}
      					},
      					{
      						"elements" : {
      							"$eq" : "Mn"
      						}
      					}
      				]
      			},
      			"inputStage" : {
      				"stage" : "IXSCAN",
      				"keyPattern" : {
      					"elements" : 1
      				},
      				"indexName" : "elements_1",
      				"isMultiKey" : true,
      				"direction" : "forward",
      				"indexBounds" : {
      					"elements" : [
      						"[\"Li\", \"Li\"]"
      					]
      				}
      			}
      		},
      		"rejectedPlans" : [
      			{
      				"stage" : "FETCH",
      				"filter" : {
      					"$and" : [
      						{
      							"elements" : {
      								"$eq" : "F"
      							}
      						},
      						{
      							"elements" : {
      								"$eq" : "Mn"
      							}
      						}
      					]
      				},
      				"inputStage" : {
      					"stage" : "IXSCAN",
      					"keyPattern" : {
      						"elements" : 1,
      						"nelements" : 1
      					},
      					"indexName" : "elements_1_nelements_1",
      					"isMultiKey" : true,
      					"direction" : "forward",
      					"indexBounds" : {
      						"elements" : [
      							"[\"Li\", \"Li\"]"
      						],
      						"nelements" : [
      							"[MinKey, MaxKey]"
      						]
      					}
      				}
      			},
      			{
      				"stage" : "FETCH",
      				"filter" : {
      					"$and" : [
      						{
      							"elements" : {
      								"$eq" : "Li"
      							}
      						},
      						{
      							"elements" : {
      								"$eq" : "F"
      							}
      						},
      						{
      							"elements" : {
      								"$eq" : "Mn"
      							}
      						}
      					]
      				},
      				"inputStage" : {
      					"stage" : "AND_SORTED",
      					"inputStages" : [
      						{
      							"stage" : "IXSCAN",
      							"keyPattern" : {
      								"elements" : 1
      							},
      							"indexName" : "elements_1",
      							"isMultiKey" : true,
      							"direction" : "forward",
      							"indexBounds" : {
      								"elements" : [
      									"[\"Li\", \"Li\"]"
      								]
      							}
      						},
      						{
      							"stage" : "IXSCAN",
      							"keyPattern" : {
      								"elements" : 1
      							},
      							"indexName" : "elements_1",
      							"isMultiKey" : true,
      							"direction" : "forward",
      							"indexBounds" : {
      								"elements" : [
      									"[\"F\", \"F\"]"
      								]
      							}
      						},
      						{
      							"stage" : "IXSCAN",
      							"keyPattern" : {
      								"elements" : 1
      							},
      							"indexName" : "elements_1",
      							"isMultiKey" : true,
      							"direction" : "forward",
      							"indexBounds" : {
      								"elements" : [
      									"[\"Mn\", \"Mn\"]"
      								]
      							}
      						}
      					]
      				}
      			}
      		]
      	},
      	"executionStats" : {
      		"executionSuccess" : true,
      		"nReturned" : 1297,
      		"executionTimeMillis" : 77874,
      		"totalKeysExamined" : 59901,
      		"totalDocsExamined" : 59901,
      		"executionStages" : {
      			"stage" : "FETCH",
      			"filter" : {
      				"$and" : [
      					{
      						"elements" : {
      							"$eq" : "F"
      						}
      					},
      					{
      						"elements" : {
      							"$eq" : "Mn"
      						}
      					}
      				]
      			},
      			"nReturned" : 1297,
      			"executionTimeMillisEstimate" : 74860,
      			"works" : 59902,
      			"advanced" : 1297,
      			"needTime" : 58604,
      			"needFetch" : 0,
      			"saveState" : 3878,
      			"restoreState" : 3878,
      			"isEOF" : 1,
      			"invalidates" : 0,
      			"docsExamined" : 59901,
      			"alreadyHasObj" : 0,
      			"inputStage" : {
      				"stage" : "IXSCAN",
      				"nReturned" : 59901,
      				"executionTimeMillisEstimate" : 220,
      				"works" : 59902,
      				"advanced" : 59901,
      				"needTime" : 0,
      				"needFetch" : 0,
      				"saveState" : 3878,
      				"restoreState" : 3878,
      				"isEOF" : 1,
      				"invalidates" : 0,
      				"keyPattern" : {
      					"elements" : 1
      				},
      				"indexName" : "elements_1",
      				"isMultiKey" : true,
      				"direction" : "forward",
      				"indexBounds" : {
      					"elements" : [
      						"[\"Li\", \"Li\"]"
      					]
      				},
      				"keysExamined" : 59901,
      				"dupsTested" : 59901,
      				"dupsDropped" : 0,
      				"seenInvalidated" : 0,
      				"matchTested" : 0
      			}
      		},
      		"allPlansExecution" : [
      			{
      				"nReturned" : 88,
      				"executionTimeMillisEstimate" : 2471,
      				"totalKeysExamined" : 3985,
      				"totalDocsExamined" : 3985,
      				"executionStages" : {
      					"stage" : "FETCH",
      					"filter" : {
      						"$and" : [
      							{
      								"elements" : {
      									"$eq" : "F"
      								}
      							},
      							{
      								"elements" : {
      									"$eq" : "Mn"
      								}
      							}
      						]
      					},
      					"nReturned" : 88,
      					"executionTimeMillisEstimate" : 2471,
      					"works" : 3985,
      					"advanced" : 88,
      					"needTime" : 3897,
      					"needFetch" : 0,
      					"saveState" : 3878,
      					"restoreState" : 3878,
      					"isEOF" : 0,
      					"invalidates" : 0,
      					"docsExamined" : 3985,
      					"alreadyHasObj" : 0,
      					"inputStage" : {
      						"stage" : "IXSCAN",
      						"nReturned" : 3985,
      						"executionTimeMillisEstimate" : 0,
      						"works" : 3985,
      						"advanced" : 3985,
      						"needTime" : 0,
      						"needFetch" : 0,
      						"saveState" : 3878,
      						"restoreState" : 3878,
      						"isEOF" : 0,
      						"invalidates" : 0,
      						"keyPattern" : {
      							"elements" : 1,
      							"nelements" : 1
      						},
      						"indexName" : "elements_1_nelements_1",
      						"isMultiKey" : true,
      						"direction" : "forward",
      						"indexBounds" : {
      							"elements" : [
      								"[\"Li\", \"Li\"]"
      							],
      							"nelements" : [
      								"[MinKey, MaxKey]"
      							]
      						},
      						"keysExamined" : 3985,
      						"dupsTested" : 3985,
      						"dupsDropped" : 0,
      						"seenInvalidated" : 0,
      						"matchTested" : 0
      					}
      				}
      			},
      			{
      				"nReturned" : 62,
      				"executionTimeMillisEstimate" : 10,
      				"totalKeysExamined" : 3985,
      				"totalDocsExamined" : 62,
      				"executionStages" : {
      					"stage" : "FETCH",
      					"filter" : {
      						"$and" : [
      							{
      								"elements" : {
      									"$eq" : "Li"
      								}
      							},
      							{
      								"elements" : {
      									"$eq" : "F"
      								}
      							},
      							{
      								"elements" : {
      									"$eq" : "Mn"
      								}
      							}
      						]
      					},
      					"nReturned" : 62,
      					"executionTimeMillisEstimate" : 10,
      					"works" : 3985,
      					"advanced" : 62,
      					"needTime" : 3923,
      					"needFetch" : 0,
      					"saveState" : 3878,
      					"restoreState" : 3878,
      					"isEOF" : 0,
      					"invalidates" : 0,
      					"docsExamined" : 62,
      					"alreadyHasObj" : 21,
      					"inputStage" : {
      						"stage" : "AND_SORTED",
      						"nReturned" : 62,
      						"executionTimeMillisEstimate" : 10,
      						"works" : 3985,
      						"advanced" : 62,
      						"needTime" : 3923,
      						"needFetch" : 0,
      						"saveState" : 3878,
      						"restoreState" : 3878,
      						"isEOF" : 0,
      						"invalidates" : 0,
      						"flagged" : 0,
      						"matchTested" : 0,
      						"failedAnd_0" : 347,
      						"failedAnd_1" : 278,
      						"failedAnd_2" : 109,
      						"inputStages" : [
      							{
      								"stage" : "IXSCAN",
      								"nReturned" : 2215,
      								"executionTimeMillisEstimate" : 0,
      								"works" : 2215,
      								"advanced" : 2215,
      								"needTime" : 0,
      								"needFetch" : 0,
      								"saveState" : 3878,
      								"restoreState" : 3878,
      								"isEOF" : 0,
      								"invalidates" : 0,
      								"keyPattern" : {
      									"elements" : 1
      								},
      								"indexName" : "elements_1",
      								"isMultiKey" : true,
      								"direction" : "forward",
      								"indexBounds" : {
      									"elements" : [
      										"[\"Li\", \"Li\"]"
      									]
      								},
      								"keysExamined" : 2215,
      								"dupsTested" : 2215,
      								"dupsDropped" : 0,
      								"seenInvalidated" : 0,
      								"matchTested" : 0
      							},
      							{
      								"stage" : "IXSCAN",
      								"nReturned" : 1083,
      								"executionTimeMillisEstimate" : 10,
      								"works" : 1083,
      								"advanced" : 1083,
      								"needTime" : 0,
      								"needFetch" : 0,
      								"saveState" : 3878,
      								"restoreState" : 3878,
      								"isEOF" : 0,
      								"invalidates" : 0,
      								"keyPattern" : {
      									"elements" : 1
      								},
      								"indexName" : "elements_1",
      								"isMultiKey" : true,
      								"direction" : "forward",
      								"indexBounds" : {
      									"elements" : [
      										"[\"F\", \"F\"]"
      									]
      								},
      								"keysExamined" : 1083,
      								"dupsTested" : 1083,
      								"dupsDropped" : 0,
      								"seenInvalidated" : 0,
      								"matchTested" : 0
      							},
      							{
      								"stage" : "IXSCAN",
      								"nReturned" : 687,
      								"executionTimeMillisEstimate" : 0,
      								"works" : 687,
      								"advanced" : 687,
      								"needTime" : 0,
      								"needFetch" : 0,
      								"saveState" : 3878,
      								"restoreState" : 3878,
      								"isEOF" : 0,
      								"invalidates" : 0,
      								"keyPattern" : {
      									"elements" : 1
      								},
      								"indexName" : "elements_1",
      								"isMultiKey" : true,
      								"direction" : "forward",
      								"indexBounds" : {
      									"elements" : [
      										"[\"Mn\", \"Mn\"]"
      									]
      								},
      								"keysExamined" : 687,
      								"dupsTested" : 687,
      								"dupsDropped" : 0,
      								"seenInvalidated" : 0,
      								"matchTested" : 0
      							}
      						]
      					}
      				}
      			},
      			{
      				"nReturned" : 101,
      				"executionTimeMillisEstimate" : 2060,
      				"totalKeysExamined" : 3985,
      				"totalDocsExamined" : 3985,
      				"executionStages" : {
      					"stage" : "FETCH",
      					"filter" : {
      						"$and" : [
      							{
      								"elements" : {
      									"$eq" : "F"
      								}
      							},
      							{
      								"elements" : {
      									"$eq" : "Mn"
      								}
      							}
      						]
      					},
      					"nReturned" : 101,
      					"executionTimeMillisEstimate" : 2060,
      					"works" : 3985,
      					"advanced" : 101,
      					"needTime" : 3884,
      					"needFetch" : 0,
      					"saveState" : 233,
      					"restoreState" : 233,
      					"isEOF" : 0,
      					"invalidates" : 0,
      					"docsExamined" : 3985,
      					"alreadyHasObj" : 0,
      					"inputStage" : {
      						"stage" : "IXSCAN",
      						"nReturned" : 3985,
      						"executionTimeMillisEstimate" : 10,
      						"works" : 3985,
      						"advanced" : 3985,
      						"needTime" : 0,
      						"needFetch" : 0,
      						"saveState" : 233,
      						"restoreState" : 233,
      						"isEOF" : 0,
      						"invalidates" : 0,
      						"keyPattern" : {
      							"elements" : 1
      						},
      						"indexName" : "elements_1",
      						"isMultiKey" : true,
      						"direction" : "forward",
      						"indexBounds" : {
      							"elements" : [
      								"[\"Li\", \"Li\"]"
      							]
      						},
      						"keysExamined" : 3985,
      						"dupsTested" : 3985,
      						"dupsDropped" : 0,
      						"seenInvalidated" : 0,
      						"matchTested" : 0
      					}
      				}
      			}
      		]
      	},
      	"serverInfo" : {
      		"host" : "xxxx",
      		"port" : 27017,
      		"version" : "3.0.1",
      		"gitVersion" : "534b5a3f9d10f00cd27737fbcd951032248b5952"
      	},
      	"ok" : 1
      }
      

        1. test_mongo.out
          45 kB
          William Richards
        2. test_mongo.py
          0.7 kB
          William Richards

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            wrichard William Richards
            Votes:
            0 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: