Array index - slow performance

Working with Data
,
1

We are trying to fetch data out of 100 million records. The filter attributes are in different arrays. We created separate indexes on both arrays. We used multiple stages to filter the records. But when wee checking the execution plan, both match stages are combined and the index is chosen. Its taking more than 20 seconds to get the final result set(only 23 records). Below the execution plan
‘’‘{‘queryPlanner’: {‘indexFilterSet’: False,
‘namespace’: ‘dap-ods.bookings’,
‘optimizedPipeline’: True,
‘parsedQuery’: {’$and’: [{‘contacts.value’: {‘$eq’: ‘11133 3336-8878’}},
{‘segments.departureAirportCode’: {‘$eq’: ‘SEA’}},
{‘segments.departureDateTimeStnLocal’: {‘$lte’: datetime.datetime(2023, 10, 14, 0, 0)}},
{‘segments.departureDateTimeStnLocal’: {‘$gte’: datetime.datetime(2023, 10, 13, 0, 0)}}]},
‘planCacheKey’: ‘6820AF83’,
‘plannerVersion’: 1,
‘queryHash’: ‘D0CBB6D1’,
‘rejectedPlans’: [{‘inputStage’: {‘filter’: {‘$and’: [{‘contacts.value’: {‘$eq’: ‘11133 3336-8878’}},
{‘segments.departureAirportCode’: {‘$eq’: ‘SEA’}},
{‘segments.departureDateTimeStnLocal’: {‘$gte’: datetime.datetime(2023, 10, 13, 0, 0)}}]},
‘inputStage’: {‘direction’: ‘forward’,
‘indexBounds’: {‘segments.departureDateTimeStnLocal’: [‘[new ’
‘Date(-9223372036854775808), ’
‘new ’
‘Date(1697241600000)]’]},
‘indexName’: ‘segments.departureDateTimeStnLocal_1’,
‘indexVersion’: 2,
‘isMultiKey’: True,
‘isPartial’: False,
‘isSparse’: False,
‘isUnique’: False,
‘keyPattern’: {‘segments.departureDateTimeStnLocal’: 1},
‘multiKeyPaths’: {‘segments.departureDateTimeStnLocal’: [‘segments’]},
‘stage’: ‘IXSCAN’},
‘stage’: ‘FETCH’},
‘stage’: ‘PROJECTION_SIMPLE’,
‘transformBy’: {’_id’: True,
‘businessKey’: True,
‘passengersInfo’: True}},
{‘inputStage’: {‘filter’: {’$and’: [{‘contacts.value’: {‘$eq’: ‘1111333336 8878’}},
{‘segments.departureAirportCode’: {‘$eq’: ‘SEA’}},
{‘segments.departureDateTimeStnLocal’: {‘$lte’: datetime.datetime(2023, 10, 14, 0, 0)}}]},
‘inputStage’: {‘direction’: ‘forward’,
‘indexBounds’: {‘segments.departureDateTimeStnLocal’: [‘[new ’
‘Date(1697155200000), ’
‘new ’
‘Date(9223372036854775807)]’]},
‘indexName’: ‘segments.departureDateTimeStnLocal_1’,
‘indexVersion’: 2,
‘isMultiKey’: True,
‘isPartial’: False,
‘isSparse’: False,
‘isUnique’: False,
‘keyPattern’: {‘segments.departureDateTimeStnLocal’: 1},
‘multiKeyPaths’: {‘segments.departureDateTimeStnLocal’: [‘segments’]},
‘stage’: ‘IXSCAN’},
‘stage’: ‘FETCH’},
‘stage’: ‘PROJECTION_SIMPLE’,
‘transformBy’: {’_id’: True,
‘businessKey’: True,
‘passengersInfo’: True}},
{‘inputStage’: {‘filter’: {’$and’: [{‘contacts.value’: {‘$eq’: ‘11133 3336-8878’}},
{‘segments.departureAirportCode’: {‘$eq’: ‘SEA’}},
{‘segments.departureDateTimeStnLocal’: {‘$gte’: datetime.datetime(2023, 10, 13, 0, 0)}}]},
‘inputStage’: {‘direction’: ‘forward’,
‘indexBounds’: {‘segments.departureAirportCode’: [‘[MinKey, ’
‘MaxKey]’],
‘segments.departureDateTimeStnLocal’: [’[new ’
‘Date(-9223372036854775808), ’
‘new ’
‘Date(1697241600000)]’]},
‘indexName’: ‘seg_departureDate_depAirport_1’,
‘indexVersion’: 2,
‘isMultiKey’: True,
‘isPartial’: False,
‘isSparse’: False,
‘isUnique’: False,
‘keyPattern’: {‘segments.departureAirportCode’: 1,
‘segments.departureDateTimeStnLocal’: 1},
‘multiKeyPaths’: {‘segments.departureAirportCode’: [‘segments’],
‘segments.departureDateTimeStnLocal’: [‘segments’]},
‘stage’: ‘IXSCAN’},
‘stage’: ‘FETCH’},
‘stage’: ‘PROJECTION_SIMPLE’,
‘transformBy’: {’_id’: True,
‘businessKey’: True,
‘passengersInfo’: True}},
{‘inputStage’: {‘filter’: {‘$and’: [{‘contacts.value’: {‘$eq’: ‘11133 3336-8878’}},
{‘segments.departureAirportCode’: {‘$eq’: ‘SEA’}},
{‘segments.departureDateTimeStnLocal’: {‘$lte’: datetime.datetime(2023, 10, 14, 0, 0)}}]},
‘inputStage’: {‘direction’: ‘forward’,
‘indexBounds’: {‘segments.departureAirportCode’: [‘[MinKey, ’
‘MaxKey]’],
‘segments.departureDateTimeStnLocal’: [’[new ’
‘Date(1697155200000), ’
‘new ’
‘Date(9223372036854775807)]’]},
‘indexName’: ‘seg_departureDate_depAirport_1’,
‘indexVersion’: 2,
‘isMultiKey’: True,
‘isPartial’: False,
‘isSparse’: False,
‘isUnique’: False,
‘keyPattern’: {‘segments.departureAirportCode’: 1,
‘segments.departureDateTimeStnLocal’: 1},
‘multiKeyPaths’: {‘segments.departureAirportCode’: [‘segments’],
‘segments.departureDateTimeStnLocal’: [‘segments’]},
‘stage’: ‘IXSCAN’},
‘stage’: ‘FETCH’},
‘stage’: ‘PROJECTION_SIMPLE’,
‘transformBy’: {’_id’: True,
‘businessKey’: True,
‘passengersInfo’: True}}],
‘winningPlan’: {‘inputStage’: {‘filter’: {‘$and’: [{‘segments.departureAirportCode’: {‘$eq’: ‘SEA’}},
{‘segments.departureDateTimeStnLocal’: {‘$lte’: datetime.datetime(2023, 10, 14, 0, 0)}},
{‘segments.departureDateTimeStnLocal’: {‘$gte’: datetime.datetime(2023, 10, 13, 0, 0)}}]},
‘inputStage’: {‘direction’: ‘forward’,
‘indexBounds’: {‘contacts.value’: [‘[“11133 3336-8878”, “11133 3336-8878”]’]},
‘indexName’: ‘contacts_1’,
‘indexVersion’: 2,
‘isMultiKey’: True,
‘isPartial’: False,
‘isSparse’: False,
‘isUnique’: False,
‘keyPattern’: {‘contacts.value’: 1},
‘multiKeyPaths’: {‘contacts.value’: [‘contacts’]},
‘stage’: ‘IXSCAN’},
‘stage’: ‘FETCH’},
‘stage’: ‘PROJECTION_SIMPLE’,
‘transformBy’: {‘_id’: True,
‘businessKey’: True,
‘passengersInfo’: True}}},‘’’

2

Please read Formatting code and log snippets in posts and reformat accordingly. We cannot really read your explain plan.

It would be nice to see the original query.