Why is performance worse when matching an index within an $or clause?

Working with Data
,
1

Been recently reading the docs regarding the $or operator, and when testing matching indexes on the $or clause vs don’t doing it… And so far the results are that NOT matching an index within $or clause makes my queries to be faster, whereas using an index match within $or clauses makes the queries to be >4 seconds slower in comparison.

Having this index:

{date: 1, listField: 1}

First, taking into account the following $match clause without matching indexes within $or:

db.my_collection.aggregate([
    {
        "$match": {
            "date": ...,
            "$and": [
		        {"$or": [
		            {"listField": "field1_a"}, 
		            {"listField": "field1_b"}
		        ]}, 
		        {"$or": [
		            {"listField": "field2_a"}, 
		            {"listField": "field2_b"}
		        ]}
		    ],
        }}
])

The resulting winning plan for this is:

		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"listField" : {
					"$in" : [ "field2_a", "field2_b" ]
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"date" : 1,
					"listField" : 1
				},
				"indexName" : "date_1_listField_1",
				"isMultiKey" : true,
				"multiKeyPaths" : {
					"date" : [ ],
					"listField" : [ "listField" ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"date" : [ "[new Date(...), new Date(...))" ],
					"listField" : [ "[\"field1_a\", \"field1_a\"]", "[\"field2_b\", \"field2_b\"]" ]
				}
			}
		}

And now, matching indexes within the $or clause:

db.my_collection.aggregate([
    {
        "$match": {
            "date": ...,
            "$and": [
		        {"$or": [
		            {"date": ..., "listField": "field1_a"}, 
		            {"date": ..., "listField": "field1_b"}
		        ]}, 
		        {"$or": [
		            {"date": ..., "listField": "field2_a"}, 
		            {"date": ..., "listField": "field2_b"}
		        ]}
		    ],
        }}
])

The resulting winning plan is the following:

		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"$or" : [
					{
						"$and" : [
							{
								"date" : {
									"$lt" : ISODate("...")
								}
							},
							{
								"date" : {
									"$gte" : ISODate("...")
								}
							},
							{
								"listField" : {
									"$eq" : "field2_a"
								}
							}
						]
					},
					{
						"$and" : [
							{
								"date" : {
									"$lt" : ISODate("...")
								}
							},
							{
								"date" : {
									"$gte" : ISODate("...")
								}
							},
							{
								"listField" : {
									"$eq" : "field2_b"
								}
							}
						]
					}
				]
			},
			"inputStage" : {
				"stage" : "OR",
				"inputStages" : [
					{
						"stage" : "IXSCAN",
						"keyPattern" : {
							"date" : 1,
							"listField" : 1
						},
						"indexName" : "date_1_listField_1",
						"isMultiKey" : true,
						"multiKeyPaths" : {
							"date" : [ ],
							"listField" : [ "listField" ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"date" : [ "[new Date(...), new Date(...))" ],
							"listField" : [ "[\"field1_a\", \"field1_b\"]" ]
						}
					},
					{
						"stage" : "IXSCAN",
						"keyPattern" : {
							"date" : 1,
							"listField" : 1
						},
						"indexName" : "date_1_listField_1",
						"isMultiKey" : true,
						"multiKeyPaths" : {
							"date" : [ ],
							"listField" : [ "listField" ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"date" : [ "[new Date(...), new Date(...))" ],
							"listField" : [ "[\"field1_a\", \"field1_b\"]" ]
						}
					}
				]
			}
		}

Regarding the output of this last winning plan I’ve got two questions:

  1. Why within the first FETCH, the listField is only matching field2 values and not on field1 values too?
  2. Why on the inputStage no field2 values are considered and indexBounds are only within field1 values?
2

Hi Edgar,

Can you share the following information please?

  • The version of MongoDB you are using
  • The output from explain() using allPlansExecution mode

Ronan

3

Thanks for replying @Ronan_Merrick !

I also forgot to include a later field in the index which is a boolean approvedDoc. My bad, including it now in the following plans. The index is: date: 1, listField: 1, approvedDoc: 1.

  • The version I’m using is 5.0.6 via Docker.
  • The output from explain() using allPlansExecution mode is the following:

Not maching indexes:

{
	"explainVersion" : "1",
	"queryPlanner" : {
		"namespace" : "my_db.my_collection",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"date" : {
						"$lt" : ISODate("...")
					}
				},
				{
					"date" : {
						"$gte" : ISODate("...")
					}
				},
				{
					"listField" : {
						"$in" : [ "field1_b", "field1_a" ]
					}
				},
				{
					"listField" : {
						"$in" : [ "field2_b", "field2_a" ]
					}
				}
			]
		},
		"optimizedPipeline" : true,
		"maxIndexedOrSolutionsReached" : false,
		"maxIndexedAndSolutionsReached" : false,
		"maxScansToExplodeReached" : false,
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"listField" : {
					"$in" : [ "field2_b", "field2_a" ]
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"date" : 1,
					"listField" : 1,
					"approvedDoc" : 1
				},
				"indexName" : "date_1_listField_1_approvedDoc_1",
				"isMultiKey" : true,
				"multiKeyPaths" : {
					"date" : [ ],
					"listField" : [ "listField" ],
					"approvedDoc" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"date" : [ "[new Date(...), new Date(...))" ],
					"listField" : [ "[\"field1_b\", \"field1_b\"]", "[\"field1_a\", \"field1_a\"]" ],
					"approvedDoc" : [ "[MinKey, MaxKey]" ]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 297825,
		"executionTimeMillis" : 3497,
		"totalKeysExamined" : 383780,
		"totalDocsExamined" : 383756,
		"executionStages" : {
			"stage" : "FETCH",
			"filter" : {
				"listField" : {
					"$in" : [ "field2_b", "field2_a" ]
				}
			},
			"nReturned" : 297825,
			"executionTimeMillisEstimate" : 2195,
			"works" : 383780,
			"advanced" : 297825,
			"needTime" : 85954,
			"needYield" : 0,
			"saveState" : 431,
			"restoreState" : 431,
			"isEOF" : 1,
			"docsExamined" : 383756,
			"alreadyHasObj" : 0,
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : 383756,
				"executionTimeMillisEstimate" : 317,
				"works" : 383780,
				"advanced" : 383756,
				"needTime" : 23,
				"needYield" : 0,
				"saveState" : 431,
				"restoreState" : 431,
				"isEOF" : 1,
				"keyPattern" : {
					"date" : 1,
					"listField" : 1,
					"approvedDoc" : 1
				},
				"indexName" : "date_1_listField_1_approvedDoc_1",
				"isMultiKey" : true,
				"multiKeyPaths" : {
					"date" : [ ],
					"listField" : [ "listField" ],
					"approvedDoc" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"date" : [ "[new Date(...), new Date(...))" ],
					"listField" : [ "[\"field1_b\", \"field1_b\"]", "[\"field1_a\", \"field1_a\"]" ],
					"approvedDoc" : [ "[MinKey, MaxKey]" ]
				},
				"keysExamined" : 383780,
				"seeks" : 24,
				"dupsTested" : 383756,
				"dupsDropped" : 0
			}
		},
		"allPlansExecution" : [ ]
	},
	"command" : {
		"aggregate" : "my_collection",
		"pipeline" : [
			{
				"$match" : {
					"date" : {
						"$gte" : ISODate("..."),
						"$lt" : ISODate("...")
					},
					"$and" : [
						{
							"$or" : [
								{
									"listField" : "field1_a"
								},
								{
									"listField" : "field1_b"
								}
							]
						},
						{
							"$or" : [
								{
									"listField" : "field2_a"
								},
								{
									"listField" : "field2_b"
								}
							]
						}
					]
				}
			}
		],
		"cursor" : {
			
		},
		"$db" : "my_db"
	},
	"serverInfo" : {
		"port" : 27017,
		"version" : "5.0.6"
	},
	"serverParameters" : {
		"internalQueryFacetBufferSizeBytes" : 104857600,
		"internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
		"internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
		"internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
		"internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
		"internalQueryProhibitBlockingMergeOnMongoS" : 0,
		"internalQueryMaxAddToSetBytes" : 104857600,
		"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600
	},
	"ok" : 1
}

Matching indexes within $or:

{
	"explainVersion" : "1",
	"queryPlanner" : {
		"namespace" : "my_db.my_collection",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"$or" : [
						{
							"$and" : [
								{
									"listField" : {
										"$eq" : "field1_a"
									}
								},
								{
									"date" : {
										"$lt" : ISODate("...")
									}
								},
								{
									"date" : {
										"$gte" : ISODate("...")
									}
								}
							]
						},
						{
							"$and" : [
								{
									"listField" : {
										"$eq" : "field1_b"
									}
								},
								{
									"date" : {
										"$lt" : ISODate("...")
									}
								},
								{
									"date" : {
										"$gte" : ISODate("...")
									}
								}
							]
						}
					]
				},
				{
					"$or" : [
						{
							"$and" : [
								{
									"listField" : {
										"$eq" : "field2_a"
									}
								},
								{
									"date" : {
										"$lt" : ISODate("...")
									}
								},
								{
									"date" : {
										"$gte" : ISODate("...")
									}
								}
							]
						},
						{
							"$and" : [
								{
									"listField" : {
										"$eq" : "field2_b"
									}
								},
								{
									"date" : {
										"$lt" : ISODate("...")
									}
								},
								{
									"date" : {
										"$gte" : ISODate("...")
									}
								}
							]
						}
					]
				},
				{
					"date" : {
						"$lt" : ISODate("...")
					}
				},
				{
					"date" : {
						"$gte" : ISODate("...")
					}
				}
			]
		},
		"optimizedPipeline" : true,
		"maxIndexedOrSolutionsReached" : false,
		"maxIndexedAndSolutionsReached" : false,
		"maxScansToExplodeReached" : false,
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"$or" : [
					{
						"$and" : [
							{
								"date" : {
									"$lt" : ISODate("...")
								}
							},
							{
								"date" : {
									"$gte" : ISODate("...")
								}
							},
							{
								"listField" : {
									"$eq" : "field2_a"
								}
							}
						]
					},
					{
						"$and" : [
							{
								"date" : {
									"$lt" : ISODate("...")
								}
							},
							{
								"date" : {
									"$gte" : ISODate("...")
								}
							},
							{
								"listField" : {
									"$eq" : "field2_b"
								}
							}
						]
					}
				]
			},
			"inputStage" : {
				"stage" : "OR",
				"inputStages" : [
					{
						"stage" : "IXSCAN",
						"keyPattern" : {
							"date" : 1,
							"listField" : 1,
							"approvedDoc" : 1
						},
						"indexName" : "date_1_listField_1_approvedDoc_1",
						"isMultiKey" : true,
						"multiKeyPaths" : {
							"date" : [ ],
							"listField" : [ "listField" ],
							"approvedDoc" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"date" : [ "[new Date(...), new Date(...))" ],
							"listField" : [ "[\"field1_a\", \"field1_a\"]" ],
							"approvedDoc" : [ "[MinKey, MaxKey]" ]
						}
					},
					{
						"stage" : "IXSCAN",
						"keyPattern" : {
							"date" : 1,
							"listField" : 1,
							"approvedDoc" : 1
						},
						"indexName" : "date_1_listField_1_approvedDoc_1",
						"isMultiKey" : true,
						"multiKeyPaths" : {
							"date" : [ ],
							"listField" : [ "listField" ],
							"approvedDoc" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"date" : [ "[new Date(...), new Date(...))" ],
							"listField" : [ "[\"field1_b\", \"field1_b\"]" ],
							"approvedDoc" : [ "[MinKey, MaxKey]" ]
						}
					}
				]
			}
		},
		"rejectedPlans" : [
			{
				"stage" : "FETCH",
				"filter" : {
					"$or" : [
						{
							"$and" : [
								{
									"date" : {
										"$lt" : ISODate("...")
									}
								},
								{
									"date" : {
										"$gte" : ISODate("...")
									}
								},
								{
									"listField" : {
										"$eq" : "field1_a"
									}
								}
							]
						},
						{
							"$and" : [
								{
									"date" : {
										"$lt" : ISODate("...")
									}
								},
								{
									"date" : {
										"$gte" : ISODate("...")
									}
								},
								{
									"listField" : {
										"$eq" : "field1_b"
									}
								}
							]
						}
					]
				},
				"inputStage" : {
					"stage" : "OR",
					"inputStages" : [
						{
							"stage" : "IXSCAN",
							"keyPattern" : {
								"date" : 1,
								"listField" : 1,
								"approvedDoc" : 1
							},
							"indexName" : "date_1_listField_1_approvedDoc_1",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"date" : [ ],
								"listField" : [ "listField" ],
								"approvedDoc" : [ ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"date" : [ "[new Date(...), new Date(...))" ],
								"listField" : [ "[\"field2_a\", \"field2_a\"]" ],
								"approvedDoc" : [ "[MinKey, MaxKey]" ]
							}
						},
						{
							"stage" : "IXSCAN",
							"keyPattern" : {
								"date" : 1,
								"listField" : 1,
								"approvedDoc" : 1
							},
							"indexName" : "date_1_listField_1_approvedDoc_1",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"date" : [ ],
								"listField" : [ "listField" ],
								"approvedDoc" : [ ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"date" : [ "[new Date(...), new Date(...))" ],
								"listField" : [ "[\"field2_b\", \"field2_b\"]" ],
								"approvedDoc" : [ "[MinKey, MaxKey]" ]
							}
						}
					]
				}
			},
			{
				"stage" : "FETCH",
				"filter" : {
					"$and" : [
						{
							"$or" : [
								{
									"$and" : [
										{
											"date" : {
												"$lt" : ISODate("...")
											}
										},
										{
											"date" : {
												"$gte" : ISODate("...")
											}
										},
										{
											"listField" : {
												"$eq" : "field1_a"
											}
										}
									]
								},
								{
									"$and" : [
										{
											"date" : {
												"$lt" : ISODate("...")
											}
										},
										{
											"date" : {
												"$gte" : ISODate("...")
											}
										},
										{
											"listField" : {
												"$eq" : "field1_b"
											}
										}
									]
								}
							]
						},
						{
							"$or" : [
								{
									"$and" : [
										{
											"date" : {
												"$lt" : ISODate("...")
											}
										},
										{
											"date" : {
												"$gte" : ISODate("...")
											}
										},
										{
											"listField" : {
												"$eq" : "field2_a"
											}
										}
									]
								},
								{
									"$and" : [
										{
											"date" : {
												"$lt" : ISODate("...")
											}
										},
										{
											"date" : {
												"$gte" : ISODate("...")
											}
										},
										{
											"listField" : {
												"$eq" : "field2_b"
											}
										}
									]
								}
							]
						}
					]
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"date" : 1,
						"listField" : 1,
						"approvedDoc" : 1
					},
					"indexName" : "date_1_listField_1_approvedDoc_1",
					"isMultiKey" : true,
					"multiKeyPaths" : {
						"date" : [ ],
						"listField" : [ "listField" ],
						"approvedDoc" : [ ]
					},
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : false,
					"indexVersion" : 2,
					"direction" : "forward",
					"indexBounds" : {
						"date" : [ "[new Date(...), new Date(...))" ],
						"listField" : [ "[MinKey, MaxKey]" ],
						"approvedDoc" : [ "[MinKey, MaxKey]" ]
					}
				}
			}
		]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 297825,
		"executionTimeMillis" : 7228,
		"totalKeysExamined" : 383788,
		"totalDocsExamined" : 383756,
		"executionStages" : {
			"stage" : "FETCH",
			"filter" : {
				"$or" : [
					{
						"$and" : [
							{
								"date" : {
									"$lt" : ISODate("...")
								}
							},
							{
								"date" : {
									"$gte" : ISODate("...")
								}
							},
							{
								"listField" : {
									"$eq" : "field2_a"
								}
							}
						]
					},
					{
						"$and" : [
							{
								"date" : {
									"$lt" : ISODate("...")
								}
							},
							{
								"date" : {
									"$gte" : ISODate("...")
								}
							},
							{
								"listField" : {
									"$eq" : "field2_b"
								}
							}
						]
					}
				]
			},
			"nReturned" : 297825,
			"executionTimeMillisEstimate" : 5667,
			"works" : 383788,
			"advanced" : 297825,
			"needTime" : 85962,
			"needYield" : 0,
			"saveState" : 549,
			"restoreState" : 549,
			"isEOF" : 1,
			"docsExamined" : 383756,
			"alreadyHasObj" : 0,
			"inputStage" : {
				"stage" : "OR",
				"nReturned" : 383756,
				"executionTimeMillisEstimate" : 489,
				"works" : 383788,
				"advanced" : 383756,
				"needTime" : 31,
				"needYield" : 0,
				"saveState" : 549,
				"restoreState" : 549,
				"isEOF" : 1,
				"dupsTested" : 383756,
				"dupsDropped" : 0,
				"inputStages" : [
					{
						"stage" : "IXSCAN",
						"nReturned" : 150432,
						"executionTimeMillisEstimate" : 218,
						"works" : 150448,
						"advanced" : 150432,
						"needTime" : 15,
						"needYield" : 0,
						"saveState" : 549,
						"restoreState" : 549,
						"isEOF" : 1,
						"keyPattern" : {
							"date" : 1,
							"listField" : 1,
							"approvedDoc" : 1
						},
						"indexName" : "date_1_listField_1_approvedDoc_1",
						"isMultiKey" : true,
						"multiKeyPaths" : {
							"date" : [ ],
							"listField" : [ "listField" ],
							"approvedDoc" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"date" : [ "[new Date(...), new Date(...))" ],
							"listField" : [ "[\"field1_a\", \"field1_a\"]" ],
							"approvedDoc" : [ "[MinKey, MaxKey]" ]
						},
						"keysExamined" : 150448,
						"seeks" : 16,
						"dupsTested" : 150432,
						"dupsDropped" : 0,
						"indexDef" : {
							"indexName" : "date_1_listField_1_approvedDoc_1",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"date" : [ ],
								"listField" : [ "listField" ],
								"approvedDoc" : [ ]
							},
							"keyPattern" : {
								"date" : 1,
								"listField" : 1,
								"approvedDoc" : 1
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"direction" : "forward"
						}
					},
					{
						"stage" : "IXSCAN",
						"nReturned" : 233324,
						"executionTimeMillisEstimate" : 228,
						"works" : 233340,
						"advanced" : 233324,
						"needTime" : 15,
						"needYield" : 0,
						"saveState" : 549,
						"restoreState" : 549,
						"isEOF" : 1,
						"keyPattern" : {
							"date" : 1,
							"listField" : 1,
							"approvedDoc" : 1
						},
						"indexName" : "date_1_listField_1_approvedDoc_1",
						"isMultiKey" : true,
						"multiKeyPaths" : {
							"date" : [ ],
							"listField" : [ "listField" ],
							"approvedDoc" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"date" : [ "[new Date(...), new Date(...))" ],
							"listField" : [ "[\"field1_b\", \"field1_b\"]" ],
							"approvedDoc" : [ "[MinKey, MaxKey]" ]
						},
						"keysExamined" : 233340,
						"seeks" : 16,
						"dupsTested" : 233324,
						"dupsDropped" : 0,
						"indexDef" : {
							"indexName" : "date_1_listField_1_approvedDoc_1",
							"isMultiKey" : true,
							"multiKeyPaths" : {
								"date" : [ ],
								"listField" : [ "listField" ],
								"approvedDoc" : [ ]
							},
							"keyPattern" : {
								"date" : 1,
								"listField" : 1,
								"approvedDoc" : 1
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"direction" : "forward"
						}
					}
				]
			}
		},
		"allPlansExecution" : [
			{
				"nReturned" : 101,
				"executionTimeMillisEstimate" : 101,
				"totalKeysExamined" : 258,
				"totalDocsExamined" : 258,
				"executionStages" : {
					"stage" : "FETCH",
					"filter" : {
						"$or" : [
							{
								"$and" : [
									{
										"date" : {
											"$lt" : ISODate("...")
										}
									},
									{
										"date" : {
											"$gte" : ISODate("...")
										}
									},
									{
										"listField" : {
											"$eq" : "field2_a"
										}
									}
								]
							},
							{
								"$and" : [
									{
										"date" : {
											"$lt" : ISODate("...")
										}
									},
									{
										"date" : {
											"$gte" : ISODate("...")
										}
									},
									{
										"listField" : {
											"$eq" : "field2_b"
										}
									}
								]
							}
						]
					},
					"nReturned" : 101,
					"executionTimeMillisEstimate" : 101,
					"works" : 258,
					"advanced" : 101,
					"needTime" : 157,
					"needYield" : 0,
					"saveState" : 19,
					"restoreState" : 19,
					"isEOF" : 0,
					"docsExamined" : 258,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "OR",
						"nReturned" : 258,
						"executionTimeMillisEstimate" : 0,
						"works" : 258,
						"advanced" : 258,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 19,
						"restoreState" : 19,
						"isEOF" : 0,
						"dupsTested" : 258,
						"dupsDropped" : 0,
						"inputStages" : [
							{
								"stage" : "IXSCAN",
								"nReturned" : 258,
								"executionTimeMillisEstimate" : 0,
								"works" : 258,
								"advanced" : 258,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 19,
								"restoreState" : 19,
								"isEOF" : 0,
								"keyPattern" : {
									"date" : 1,
									"listField" : 1,
									"approvedDoc" : 1
								},
								"indexName" : "date_1_listField_1_approvedDoc_1",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"date" : [ ],
									"listField" : [ "listField" ],
									"approvedDoc" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"date" : [ "[new Date(...), new Date(...))" ],
									"listField" : [ "[\"field1_a\", \"field1_a\"]" ],
									"approvedDoc" : [ "[MinKey, MaxKey]" ]
								},
								"keysExamined" : 258,
								"seeks" : 1,
								"dupsTested" : 258,
								"dupsDropped" : 0
							},
							{
								"stage" : "IXSCAN",
								"nReturned" : 0,
								"executionTimeMillisEstimate" : 0,
								"works" : 0,
								"advanced" : 0,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 19,
								"restoreState" : 19,
								"isEOF" : 0,
								"keyPattern" : {
									"date" : 1,
									"listField" : 1,
									"approvedDoc" : 1
								},
								"indexName" : "date_1_listField_1_approvedDoc_1",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"date" : [ ],
									"listField" : [ "listField" ],
									"approvedDoc" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"date" : [ "[new Date(...), new Date(...))" ],
									"listField" : [ "[\"field1_b\", \"field1_b\"]" ],
									"approvedDoc" : [ "[MinKey, MaxKey]" ]
								},
								"keysExamined" : 0,
								"seeks" : 0,
								"dupsTested" : 0,
								"dupsDropped" : 0
							}
						]
					}
				}
			},
			{
				"nReturned" : 92,
				"executionTimeMillisEstimate" : 191,
				"totalKeysExamined" : 258,
				"totalDocsExamined" : 258,
				"executionStages" : {
					"stage" : "FETCH",
					"filter" : {
						"$or" : [
							{
								"$and" : [
									{
										"date" : {
											"$lt" : ISODate("...")
										}
									},
									{
										"date" : {
											"$gte" : ISODate("...")
										}
									},
									{
										"listField" : {
											"$eq" : "field1_a"
										}
									}
								]
							},
							{
								"$and" : [
									{
										"date" : {
											"$lt" : ISODate("...")
										}
									},
									{
										"date" : {
											"$gte" : ISODate("...")
										}
									},
									{
										"listField" : {
											"$eq" : "field1_b"
										}
									}
								]
							}
						]
					},
					"nReturned" : 92,
					"executionTimeMillisEstimate" : 191,
					"works" : 258,
					"advanced" : 92,
					"needTime" : 166,
					"needYield" : 0,
					"saveState" : 549,
					"restoreState" : 549,
					"isEOF" : 0,
					"docsExamined" : 258,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "OR",
						"nReturned" : 258,
						"executionTimeMillisEstimate" : 0,
						"works" : 258,
						"advanced" : 258,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 549,
						"restoreState" : 549,
						"isEOF" : 0,
						"dupsTested" : 258,
						"dupsDropped" : 0,
						"inputStages" : [
							{
								"stage" : "IXSCAN",
								"nReturned" : 258,
								"executionTimeMillisEstimate" : 0,
								"works" : 258,
								"advanced" : 258,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 549,
								"restoreState" : 549,
								"isEOF" : 0,
								"keyPattern" : {
									"date" : 1,
									"listField" : 1,
									"approvedDoc" : 1
								},
								"indexName" : "date_1_listField_1_approvedDoc_1",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"date" : [ ],
									"listField" : [ "listField" ],
									"approvedDoc" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"date" : [ "[new Date(...), new Date(...))" ],
									"listField" : [ "[\"field2_a\", \"field2_a\"]" ],
									"approvedDoc" : [ "[MinKey, MaxKey]" ]
								},
								"keysExamined" : 258,
								"seeks" : 1,
								"dupsTested" : 258,
								"dupsDropped" : 0
							},
							{
								"stage" : "IXSCAN",
								"nReturned" : 0,
								"executionTimeMillisEstimate" : 0,
								"works" : 0,
								"advanced" : 0,
								"needTime" : 0,
								"needYield" : 0,
								"saveState" : 549,
								"restoreState" : 549,
								"isEOF" : 0,
								"keyPattern" : {
									"date" : 1,
									"listField" : 1,
									"approvedDoc" : 1
								},
								"indexName" : "date_1_listField_1_approvedDoc_1",
								"isMultiKey" : true,
								"multiKeyPaths" : {
									"date" : [ ],
									"listField" : [ "listField" ],
									"approvedDoc" : [ ]
								},
								"isUnique" : false,
								"isSparse" : false,
								"isPartial" : false,
								"indexVersion" : 2,
								"direction" : "forward",
								"indexBounds" : {
									"date" : [ "[new Date(...), new Date(...))" ],
									"listField" : [ "[\"field2_b\", \"field2_b\"]" ],
									"approvedDoc" : [ "[MinKey, MaxKey]" ]
								},
								"keysExamined" : 0,
								"seeks" : 0,
								"dupsTested" : 0,
								"dupsDropped" : 0
							}
						]
					}
				}
			},
			{
				"nReturned" : 24,
				"executionTimeMillisEstimate" : 77,
				"totalKeysExamined" : 258,
				"totalDocsExamined" : 258,
				"executionStages" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"$or" : [
									{
										"$and" : [
											{
												"date" : {
													"$lt" : ISODate("...")
												}
											},
											{
												"date" : {
													"$gte" : ISODate("...")
												}
											},
											{
												"listField" : {
													"$eq" : "field1_a"
												}
											}
										]
									},
									{
										"$and" : [
											{
												"date" : {
													"$lt" : ISODate("...")
												}
											},
											{
												"date" : {
													"$gte" : ISODate("...")
												}
											},
											{
												"listField" : {
													"$eq" : "field1_b"
												}
											}
										]
									}
								]
							},
							{
								"$or" : [
									{
										"$and" : [
											{
												"date" : {
													"$lt" : ISODate("...")
												}
											},
											{
												"date" : {
													"$gte" : ISODate("...")
												}
											},
											{
												"listField" : {
													"$eq" : "field2_a"
												}
											}
										]
									},
									{
										"$and" : [
											{
												"date" : {
													"$lt" : ISODate("...")
												}
											},
											{
												"date" : {
													"$gte" : ISODate("...")
												}
											},
											{
												"listField" : {
													"$eq" : "field2_b"
												}
											}
										]
									}
								]
							}
						]
					},
					"nReturned" : 24,
					"executionTimeMillisEstimate" : 77,
					"works" : 258,
					"advanced" : 24,
					"needTime" : 234,
					"needYield" : 0,
					"saveState" : 549,
					"restoreState" : 549,
					"isEOF" : 0,
					"docsExamined" : 258,
					"alreadyHasObj" : 0,
					"inputStage" : {
						"stage" : "IXSCAN",
						"nReturned" : 258,
						"executionTimeMillisEstimate" : 0,
						"works" : 258,
						"advanced" : 258,
						"needTime" : 0,
						"needYield" : 0,
						"saveState" : 549,
						"restoreState" : 549,
						"isEOF" : 0,
						"keyPattern" : {
							"date" : 1,
							"listField" : 1,
							"approvedDoc" : 1
						},
						"indexName" : "date_1_listField_1_approvedDoc_1",
						"isMultiKey" : true,
						"multiKeyPaths" : {
							"date" : [ ],
							"listField" : [ "listField" ],
							"approvedDoc" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"date" : [ "[new Date(...), new Date(...))" ],
							"listField" : [ "[MinKey, MaxKey]" ],
							"approvedDoc" : [ "[MinKey, MaxKey]" ]
						},
						"keysExamined" : 258,
						"seeks" : 1,
						"dupsTested" : 258,
						"dupsDropped" : 0
					}
				}
			}
		]
	},
	"command" : {
		"aggregate" : "my_collection",
		"pipeline" : [
			{
				"$match" : {
					"date" : {
						"$gte" : ISODate("..."),
						"$lt" : ISODate("...")
					},
					"$and" : [
						{
							"$or" : [
								{
									"date" : {
										"$gte" : ISODate("..."),
										"$lt" : ISODate("...")
									},
									"listField" : "field1_a"
								},
								{
									"date" : {
										"$gte" : ISODate("..."),
										"$lt" : ISODate("...")
									},
									"listField" : "field1_b"
								}
							]
						},
						{
							"$or" : [
								{
									"date" : {
										"$gte" : ISODate("..."),
										"$lt" : ISODate("...")
									},
									"listField" : "field2_a"
								},
								{
									"date" : {
										"$gte" : ISODate("..."),
										"$lt" : ISODate("...")
									},
									"listField" : "field2_b"
								}
							]
						}
					]
				}
			}
		],
		"cursor" : {
			
		},
		"$db" : "my_db"
	},
	"serverInfo" : {
		"port" : 27017,
		"version" : "5.0.6"
	},
	"serverParameters" : {
		"internalQueryFacetBufferSizeBytes" : 104857600,
		"internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
		"internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
		"internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
		"internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
		"internalQueryProhibitBlockingMergeOnMongoS" : 0,
		"internalQueryMaxAddToSetBytes" : 104857600,
		"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600
	},
	"ok" : 1
}
4

Hi Edgar,

Thank you for providing this information. You appear to have obfuscated the date values in your query.

Are the different portions of the query applying the same filtering criteria to date or different criteria, i.e. are the $lt and $gte values the same in all cases?

Regards,

Ronan

5

Oh yes, sorry for that, thought they wouldn’t add value to the debugging.

And yes, they are indeed the same 9 days interval for all.

6

Hi @Ronan_Merrick , any more insights on this performance issue?

Thanks!

7

Hi Edgar,

Apologies for the delay in getting back to you.

I am looking into this.

Regards,

Ronan

8

Hi Edgar,

Thanks for your patience while I have been investigating.

I simplified the query slightly:

db.foo.find({$and: [{listField:{$in:["field1_a", "field1_b"]}},{listField:{$in:["field2_a", "field2_b"]}},{date:{$lt:ISODate("2023-02-11T14:41:35.803Z"),$gte:ISODate("2023-02-08T14:41:35.803Z")}}]}).explain(1)

I have done some testing with your query and it appears that we will always FETCH for one of the $and branch values when the multikey index is a compound one as in your case. If the multikey index is not a compound index, we consider an alternative plan that performs 2 index scans and an `AND_SORTED stage. However in my testing, this was not the winning plan and there is no way to force this. This suggests that in my testing anyway this plan didn’t perform better than scanning for one value and then FETCHing for the other and this may not be the reason the query is not performing as you expect.

I would like to note that the reason for the performance could be the index order. We recommend to follow the ESR rule when constructing your indexes, which places Equality fields first, then Sort fields and finally Range fields. In your query you are performing a range match on date but this is the first field in the index.

Please try to reverse the order of the listField and date fields in the index and let us know if this improves the performance e.g

 listField: 1, date: 1,approvedDoc: 1

Please let me know how you get on with this.

Regards,

Ronan

9

Thanks a lot for the investigation Ronan! Much appreciated.

I’ve just performed those changes in the indexes without much difference :frowning: … response times are mostly equal with no major advantage/disadvantage.

I’m worried that it may be the listField itself that’s causing the times. Tried to apply the attribute pattern without much luck (mainly since I can’t really append date prior a wildcard and ended up retrieving way more documents that I wanted to https://jira.mongodb.org/browse/SERVER-48570), I appended all the attributes related to each of the documents in a list such as key:value, and in order to retrieve all the documents all those have an empty string "" to match all documents when no attributes for lookup are specified.

Could this be a document issue more than a query one?

Also, in case this may be the bottleneck are parallel aggregations something plausible? In order to launch n parallel aggregations into MongoDB and merge cursors altogether.

10

Hi Eddy,

No problem at all.

I would suggest to read this blog post from one of our colleagues about the attribute pattern.

If I understand correctly, your problem is that you may not know the attribute names in advance and the considerations for Wildcard Indexes meant these weren’t a good fit for your use case. Please correct me if I misunderstand.

If this is the case, you could use a key/value convention as suggested in our colleague’s blog post, for example:

"specs": [
    { k: "volume", v: "500", u: "ml" },
    { k: "volume", v: "12", u: "ounces" }
]

Then you only need to index {"specs.k":1,"specs.v":1} in this case.

You would need to use $elemMatch to compound the bounds when matching on array items with this approach.

Let me know if you see any difference with this approach.

Regards,

Ronan

11

When applying the blog post you mentioned a few weeks ago I saw that wildcard indexes cannot be preceded or proceeded by any other field. With this I mean that applying the wildcard index I’m retrieving unbounded by date sets instead of going for a more specific subset given a date + listField criteria for example.

It would be great if wildcard indexes could be mixed by preceding fields such as a date so the working set is narrowed even more, which is an enhancement proposed in the ticket before.

Knowing that, I applied the attribute pattern in a different fashion making listField look like this - clarifying that field1_a = key1:value1, so that could be easily parsed into a key/value structure:

listField: [
   { fieldKey: "key1", fieldValue: "value1" },
   { fieldKey: "key2", fieldValue: "value2" },
   { fieldKey: "", fieldValue: ""} // so all documents can be retrieved
]

Having as index the previous ones, that means: { date: 1, listField: 1, approvedDoc: 1 }, or { listField: 1, date: 1, approvedDoc: 1 } following the ESR convention you mentioned.

Now, taking into account the previous query with the current approach:

db.foo.find(
    {
        $and: [
            { listField: { $in: ["key1:value1"] } },
            { date: { $lt:ISODate("2023-02-11T14:41:35.803Z"), $gte:ISODate("2023-02-08T14:41:35.803Z") } }]
    }).explain(1)

This will examine 150k index keys, having 1M documents. Time elapsed using explain: 2,5 seconds.

Now, using the attribute pattern with the changes mentioned previously and using $elemMatch:

db.foo.aggregate([
    {
        "$match": {
            "date": {"$gte": ISODate("2022-11-01T00:00:00Z"), "$lt": ISODate("2022-11-09T00:00:00Z")}, 
            "listField": {$elemMatch: {"fieldKey": "key1", "fieldValue": "value1"}}
        }
    }]).explain("executionStats")

This will examine 5.5M index keys, having 1M documents. Time elapsed using explain = 15 seconds.

One thing that also worries me about this approach is that I loose the 1:1 link between keys and values, because with $elemMatch if values happen to have duplicated values for key1 and key2, in case I’m looking only for key1 related values it may result in erroneous results.

Also, is explain the best way to determine if a query is better performant than another or is there any other better mechanism for local testing?