Aggregate query is taking long-time to fetch results from huge collection

Working with Data
#1

I’m working with MongoDB 4.4.11 Enterprise edition for a pricing application, we have a collection which contains 36.5 million documents with 90 fields, average document size of 2.2 KB and storage size of 12.6 GB with 7 indexes around size of 7 GB.
Use case:
Dropdown’s from the application UI for the selected criteria has to be populated from this collection (as it contains Master catalog data). Also this
collection is having combination of all the pricing availability data across the system, so in order to show VALID combiantions for the users to select we need
dropdowns to be populated from this collection based on different filters dynamically based on user selection.

Everytime we execute the query the response time is differing though the indexes are getting picked up, Please note the below query contains all the filters,
but there is a chance these filter criteria varies dynamically based on user selection (i.e. user can only apply accessTypeName,aspeedNumValue,countryId,btProductName (or) accessTypeName,countryId,btProductName (or) accessTypeName,popName,countryId,btProductName)

We are facing the slowness in producing the resultsets, first hit as you see in below took 168 secs followed by 1.2 seconds and 0.2 seconds, Could someone
help what are we missing here ?

I have attached the following details
Query
Indexes
Explain Plans
Document model

query:
db.btProductAccessMapping.explain(“executionStats”).aggregate([{
“$match”: {
“$and”: [{
“accessTypeName”: “Ethernet”
},
{
“aspeedNumValue”: 1000000
},
{
“popName”: “Frankfurt Genfer Straße”
},
{
“countryId”: “DE”
},
{
“btProductName”: “BT iVPN”
}
]
}
},
{
“$group”: {
“_id”: {
“supplierName”: “$supplierName”,
“supplierId”: “$supplierId”
}
}
},
{
“$project”: {
“_id”: 0,
“supplierName”: “$_id.supplierName”,
“supplierId”: “$_id.supplierId”
}
}, {
“$sort”: {
“supplierName”: 1
}
}
])

Please refere to the attached execution plan below in details:

Execution 1:
nReturned: 10,
executionTimeMillisEstimate: 168293

Execution 2:
nReturned: 10,
executionTimeMillisEstimate: 1209

Execution 3:
nReturned: 10,
executionTimeMillisEstimate: 250

Please refer to the attachment for more detials.

Questionnaries:
Are we missing any indexes ? In our usecase its dymanic filtering ?
Should we create Mview with only the required fields ?
I have tried creating Mview by grouping the required combinations and nesting the dataset, but while nesting it is exceeding the mongodb bsob storage limit ?
if we re-create same collection as flattened with only limited fields(lets say only 50 fields) will yield better results ?

#2

Index list:
[
{ v: 2, key: { _id: 1 }, name: ‘id’ },
{
v: 2,
key: {
accessTypeName: 1,
cpeAccessType: 1,
supportResilientPop: 1,
btProductName: 1,
btProductAvailabilityStatus: 1,
ipv6Enabled: 1,
ethernetPhaseAttribute: 1,
aspeedValue: 1,
aspeedUom: 1,
pspeedValue: 1,
pspeedUom: 1,
aspeedUpValue: 1,
aspeedUpUom: 1,
serviceVariant: 1,
supplierId: 1,
supplierProductId: 1,
interfaceId: 1,
framingId: 1,
connectorId: 1,
acat: 1,
countryId: 1
},
name: ‘accessTypeName_1_cpeAccessType_1_supportResilientPop_1_btProductName_1_btProductAvailabilityStatus_1_ipv6Enabled_1_ethernetPhaseAttribute_1_aspeedValue_1_aspeedUom_1_pspeedValue_1_pspeedUom_1_aspeedUpValue_1_aspeedUpUom_1_serviceVariant_1_supplierId_1_supplierProductId_1_interfaceId_1_framingId_1_connectorId_1_acat_1_countryId_1’,
background: true
},
{
v: 2,
key: {
countryId: 1,
btProductId: 1,
popId: 1,
platformId: 1,
platformName: 1,
accessTypeId: 1,
accessTypeName: 1,
supplierId: 1,
supplierProductId: 1,
aspeedValue: 1,
aspeedUom: 1,
aspeedUpValue: 1,
aspeedUpUom: 1,
pspeedValue: 1,
pspeedUom: 1,
pspeedUpValue: 1,
pspeedUpUom: 1,
portTypeId: 1,
lmpId: 1
},
name: ‘countryId_1_btProductId_1_popId_1_platformId_1_platformName_1_accessTypeId_1_accessTypeName_1_supplierId_1_supplierProductId_1_aspeedValue_1_aspeedUom_1_aspeedUpValue_1_aspeedUpUom_1_pspeedValue_1_pspeedUom_1_pspeedUpValue_1_pspeedUpUom_1_portTypeId_1_lmpId_1’
},
{
v: 2,
key: {
countryId: 1,
countryName: 1,
btProductName: 1,
btProductDisplayName: 1,
supplierName: 1,
supplierProductName: 1,
accessTypeName: 1,
lmpName: 1,
lmpConfigurationName: 1,
interfaceName: 1,
framingName: 1,
connectorName: 1,
popName: 1,
popTypeName: 1
},
name: ‘USSQuickQuotingFilters’
},
{
v: 2,
key: {
countryName: 1,
btProductDisplayName: 1,
platformName: 1,
supplierName: 1,
supplierProductName: 1,
accessTypeGroup: 1,
accessTypeName: 1,
ethernetPhaseAttribute: 1,
popName: 1,
popTypeName: 1,
lmpName: 1,
lmpConfigurationName: 1,
interfaceName: 1,
framingName: 1,
connectorName: 1,
portAvailabilityStatus: 1,
accessAvailabilityStatus: 1,
orderingStatus: 1,
productAvailability: 1,
physicalLayer: 1,
deliveryMedium: 1,
faultrepair24x7Name: 1,
status: 1,
aspeedNumValue: 1,
aspeedValue: 1,
aspeedUpNumValue: 1,
aspeedUpValue: 1,
pspeedNumValue: 1,
pspeedValue: 1,
pspeedUpNumValue: 1,
pspeedUpValue: 1,
_id: 1
},
name: ‘master filter 1’
},
{
v: 2,
key: {
countryName: 1,
btProductDisplayName: 1,
btProductAbbr: 1,
serviceVariant: 1,
cpeAccessType: 1,
supportResilientPop: 1,
wanIpAddressType: 1,
ipv6Enabled: 1,
acat: 1,
spm: 1,
accessLeadTime: 1,
accessLeadTimeStatus: 1,
pspeedLeadTime: 1,
cpeLeadTime: 1,
cpeLeadTimeStatus: 1,
minGuaranteedSpeedDown: 1,
minGuaranteedSpeedUp: 1,
cityName: 1,
standardFrameSize: 1,
timeToDeliver: 1,
supplierJitter: 1,
supplierLatency: 1,
cuPairs: 1,
contentionRatio: 1,
serviceLeadTimeCpe: 1,
serLeadtimestatCpe: 1,
serviceLeadTimeNocpe: 1,
serLeadtimestatNocpe: 1,
serviceId: 1,
exception: 1,
comments: 1,
_id: 1
},
name: ‘master filter 2’
},
{
v: 2,
key: {
accessTypeName: 1,
btProductName: 1,
supplierName: 1,
supplierProductName: 1,
portTypeId: 1,
popTypeId: 1,
aspeedNumValue: 1,
pspeedNumValue: 1,
aspeedUpNumValue: 1,
pspeedUpNumValue: 1,
countryId: 1
},
name: ‘Staffscreenfilter’
}
]

#3

Explain Plans:

execution 1:

{
stages: [
{
‘$cursor’: {
queryPlanner: {
plannerVersion: 1,
namespace: ‘btProductAvailabilityDB_staging.btProductAccessMapping’,
indexFilterSet: false,
parsedQuery: {
‘$and’: [
{
accessTypeName: {
‘$eq’: ‘Ethernet’
}
},
{
aspeedNumValue: {
‘$eq’: 1000000
}
},
{
btProductName: {
‘$eq’: ‘BT iVPN’
}
},
{
countryId: {
‘$eq’: ‘DE’
}
},
{
popName: {
‘$eq’: ‘Frankfurt Genfer Straße’
}
}
]
},
queryHash: ‘26F32A64’,
planCacheKey: ‘29241033’,
winningPlan: {
stage: ‘PROJECTION_SIMPLE’,
transformBy: {
supplierId: 1,
supplierName: 1,
_id: 0
},
inputStage: {
stage: ‘FETCH’,
filter: {
aspeedNumValue: {
‘$eq’: 1000000
}
},
inputStage: {
stage: ‘IXSCAN’,
keyPattern: {
countryId: 1,
countryName: 1,
btProductName: 1,
btProductDisplayName: 1,
supplierName: 1,
supplierProductName: 1,
accessTypeName: 1,
lmpName: 1,
lmpConfigurationName: 1,
interfaceName: 1,
framingName: 1,
connectorName: 1,
popName: 1,
popTypeName: 1
},
indexName: ‘USSQuickQuotingFilters’,
isMultiKey: false,
multiKeyPaths: {
countryId: ,
countryName: ,
btProductName: ,
btProductDisplayName: ,
supplierName: ,
supplierProductName: ,
accessTypeName: ,
lmpName: ,
lmpConfigurationName: ,
interfaceName: ,
framingName: ,
connectorName: ,
popName: ,
popTypeName:
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: ‘forward’,
indexBounds: {
countryId: [
‘[“DE”, “DE”]’
],
countryName: [
‘[MinKey, MaxKey]’
],
btProductName: [
‘[“BT iVPN”, “BT iVPN”]’
],
btProductDisplayName: [
‘[MinKey, MaxKey]’
],
supplierName: [
‘[MinKey, MaxKey]’
],
supplierProductName: [
‘[MinKey, MaxKey]’
],
accessTypeName: [
‘[“Ethernet”, “Ethernet”]’
],
lmpName: [
‘[MinKey, MaxKey]’
],
lmpConfigurationName: [
‘[MinKey, MaxKey]’
],
interfaceName: [
‘[MinKey, MaxKey]’
],
framingName: [
‘[MinKey, MaxKey]’
],
connectorName: [
‘[MinKey, MaxKey]’
],
popName: [
‘[“Frankfurt Genfer Straße”, “Frankfurt Genfer Straße”]’
],
popTypeName: [
‘[MinKey, MaxKey]’
]
}
}
}
},
rejectedPlans: [
{
stage: ‘PROJECTION_SIMPLE’,
transformBy: {
supplierId: 1,
supplierName: 1,
_id: 0
},
inputStage: {
stage: ‘FETCH’,
filter: {
‘$and’: [
{
aspeedNumValue: {
‘$eq’: 1000000
}
},
{
popName: {
‘$eq’: ‘Frankfurt Genfer Straße’
}
}
]
},
inputStage: {
stage: ‘IXSCAN’,
keyPattern: {
accessTypeName: 1,
cpeAccessType: 1,
supportResilientPop: 1,
btProductName: 1,
btProductAvailabilityStatus: 1,
ipv6Enabled: 1,
ethernetPhaseAttribute: 1,
aspeedValue: 1,
aspeedUom: 1,
pspeedValue: 1,
pspeedUom: 1,
aspeedUpValue: 1,
aspeedUpUom: 1,
serviceVariant: 1,
supplierId: 1,
supplierProductId: 1,
interfaceId: 1,
framingId: 1,
connectorId: 1,
acat: 1,
countryId: 1
},
indexName: ‘accessTypeName_1_cpeAccessType_1_supportResilientPop_1_btProductName_1_btProductAvailabilityStatus_1_ipv6Enabled_1_ethernetPhaseAttribute_1_aspeedValue_1_aspeedUom_1_pspeedValue_1_pspeedUom_1_aspeedUpValue_1_aspeedUpUom_1_serviceVariant_1_supplierId_1_supplierProductId_1_interfaceId_1_framingId_1_connectorId_1_acat_1_countryId_1’,
isMultiKey: false,
multiKeyPaths: {
accessTypeName: ,
cpeAccessType: ,
supportResilientPop: ,
btProductName: ,
btProductAvailabilityStatus: ,
ipv6Enabled: ,
ethernetPhaseAttribute: ,
aspeedValue: ,
aspeedUom: ,
pspeedValue: ,
pspeedUom: ,
aspeedUpValue: ,
aspeedUpUom: ,
serviceVariant: ,
supplierId: ,
supplierProductId: ,
interfaceId: ,
framingId: ,
connectorId: ,
acat: ,
countryId:
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: ‘forward’,
indexBounds: {
accessTypeName: [
‘[“Ethernet”, “Ethernet”]’
],
cpeAccessType: [
‘[MinKey, MaxKey]’
],
supportResilientPop: [
‘[MinKey, MaxKey]’
],
btProductName: [
‘[“BT iVPN”, “BT iVPN”]’
],
btProductAvailabilityStatus: [
‘[MinKey, MaxKey]’
],
ipv6Enabled: [
‘[MinKey, MaxKey]’
],
ethernetPhaseAttribute: [
‘[MinKey, MaxKey]’
],
aspeedValue: [
‘[MinKey, MaxKey]’
],
aspeedUom: [
‘[MinKey, MaxKey]’
],
pspeedValue: [
‘[MinKey, MaxKey]’
],
pspeedUom: [
‘[MinKey, MaxKey]’
],
aspeedUpValue: [
‘[MinKey, MaxKey]’
],
aspeedUpUom: [
‘[MinKey, MaxKey]’
],
serviceVariant: [
‘[MinKey, MaxKey]’
],
supplierId: [
‘[MinKey, MaxKey]’
],
supplierProductId: [
‘[MinKey, MaxKey]’
],
interfaceId: [
‘[MinKey, MaxKey]’
],
framingId: [
‘[MinKey, MaxKey]’
],
connectorId: [
‘[MinKey, MaxKey]’
],
acat: [
‘[MinKey, MaxKey]’
],
countryId: [
‘[“DE”, “DE”]’
]
}
}
}
},
{
stage: ‘PROJECTION_SIMPLE’,
transformBy: {
supplierId: 1,
supplierName: 1,
_id: 0
},
inputStage: {
stage: ‘FETCH’,
filter: {
‘$and’: [
{
aspeedNumValue: {
‘$eq’: 1000000
}
},
{
btProductName: {
‘$eq’: ‘BT iVPN’
}
},
{
popName: {
‘$eq’: ‘Frankfurt Genfer Straße’
}
}
]
},
inputStage: {
stage: ‘IXSCAN’,
keyPattern: {
countryId: 1,
btProductId: 1,
popId: 1,
platformId: 1,
platformName: 1,
accessTypeId: 1,
accessTypeName: 1,
supplierId: 1,
supplierProductId: 1,
aspeedValue: 1,
aspeedUom: 1,
aspeedUpValue: 1,
aspeedUpUom: 1,
pspeedValue: 1,
pspeedUom: 1,
pspeedUpValue: 1,
pspeedUpUom: 1,
portTypeId: 1,
lmpId: 1
},
indexName: ‘countryId_1_btProductId_1_popId_1_platformId_1_platformName_1_accessTypeId_1_accessTypeName_1_supplierId_1_supplierProductId_1_aspeedValue_1_aspeedUom_1_aspeedUpValue_1_aspeedUpUom_1_pspeedValue_1_pspeedUom_1_pspeedUpValue_1_pspeedUpUom_1_portTypeId_1_lmpId_1’,
isMultiKey: false,
multiKeyPaths: {
countryId: ,
btProductId: ,
popId: ,
platformId: ,
platformName: ,
accessTypeId: ,
accessTypeName: ,
supplierId: ,
supplierProductId: ,
aspeedValue: ,
aspeedUom: ,
aspeedUpValue: ,
aspeedUpUom: ,
pspeedValue: ,
pspeedUom: ,
pspeedUpValue: ,
pspeedUpUom: ,
portTypeId: ,
lmpId:
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: ‘forward’,
indexBounds: {
countryId: [
‘[“DE”, “DE”]’
],
btProductId: [
‘[MinKey, MaxKey]’
],
popId: [
‘[MinKey, MaxKey]’
],
platformId: [
‘[MinKey, MaxKey]’
],
platformName: [
‘[MinKey, MaxKey]’
],
accessTypeId: [
‘[MinKey, MaxKey]’
],
accessTypeName: [
‘[“Ethernet”, “Ethernet”]’
],
supplierId: [
‘[MinKey, MaxKey]’
],
supplierProductId: [
‘[MinKey, MaxKey]’
],
aspeedValue: [
‘[MinKey, MaxKey]’
],
aspeedUom: [
‘[MinKey, MaxKey]’
],
aspeedUpValue: [
‘[MinKey, MaxKey]’
],
aspeedUpUom: [
‘[MinKey, MaxKey]’
],
pspeedValue: [
‘[MinKey, MaxKey]’
],
pspeedUom: [
‘[MinKey, MaxKey]’
],
pspeedUpValue: [
‘[MinKey, MaxKey]’
],
pspeedUpUom: [
‘[MinKey, MaxKey]’
],
portTypeId: [
‘[MinKey, MaxKey]’
],
lmpId: [
‘[MinKey, MaxKey]’
]
}
}
}
},
{
stage: ‘PROJECTION_SIMPLE’,
transformBy: {
supplierId: 1,
supplierName: 1,
_id: 0
},
inputStage: {
stage: ‘FETCH’,
filter: {
popName: {
‘$eq’: ‘Frankfurt Genfer Straße’
}
},
inputStage: {
stage: ‘IXSCAN’,
keyPattern: {
accessTypeName: 1,
btProductName: 1,
supplierName: 1,
supplierProductName: 1,
portTypeId: 1,
popTypeId: 1,
aspeedNumValue: 1,
pspeedNumValue: 1,
aspeedUpNumValue: 1,
pspeedUpNumValue: 1,
countryId: 1
},
indexName: ‘Staffscreenfilter’,
isMultiKey: false,
multiKeyPaths: {
accessTypeName: ,
btProductName: ,
supplierName: ,
supplierProductName: ,
portTypeId: ,
popTypeId: ,
aspeedNumValue: ,
pspeedNumValue: ,
aspeedUpNumValue: ,
pspeedUpNumValue: ,
countryId:
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: ‘forward’,
indexBounds: {
accessTypeName: [
‘[“Ethernet”, “Ethernet”]’
],
btProductName: [
‘[“BT iVPN”, “BT iVPN”]’
],
supplierName: [
‘[MinKey, MaxKey]’
],
supplierProductName: [
‘[MinKey, MaxKey]’
],
portTypeId: [
‘[MinKey, MaxKey]’
],
popTypeId: [
‘[MinKey, MaxKey]’
],
aspeedNumValue: [
‘[1000000, 1000000]’
],
pspeedNumValue: [
‘[MinKey, MaxKey]’
],
aspeedUpNumValue: [
‘[MinKey, MaxKey]’
],
pspeedUpNumValue: [
‘[MinKey, MaxKey]’
],
countryId: [
‘[“DE”, “DE”]’
]
}
}
}
}
]
},
executionStats: {
executionSuccess: true,
nReturned: 2026,
executionTimeMillis: 247060,
totalKeysExamined: 27604,
totalDocsExamined: 26952,
executionStages: {
stage: ‘PROJECTION_SIMPLE’,
nReturned: 2026,
executionTimeMillisEstimate: 191238,
works: 27604,
advanced: 2026,
needTime: 25577,
needYield: 0,
saveState: 10031,
restoreState: 10031,
isEOF: 1,
transformBy: {
supplierId: 1,
supplierName: 1,
_id: 0
},
inputStage: {
stage: ‘FETCH’,
filter: {
aspeedNumValue: {
‘$eq’: 1000000
}
},
nReturned: 2026,
executionTimeMillisEstimate: 191228,
works: 27604,
advanced: 2026,
needTime: 25577,
needYield: 0,
saveState: 10031,
restoreState: 10031,
isEOF: 1,
docsExamined: 26952,
alreadyHasObj: 0,
inputStage: {
stage: ‘IXSCAN’,
nReturned: 26952,
executionTimeMillisEstimate: 505,
works: 27604,
advanced: 26952,
needTime: 651,
needYield: 0,
saveState: 10031,
restoreState: 10031,
isEOF: 1,
keyPattern: {
countryId: 1,
countryName: 1,
btProductName: 1,
btProductDisplayName: 1,
supplierName: 1,
supplierProductName: 1,
accessTypeName: 1,
lmpName: 1,
lmpConfigurationName: 1,
interfaceName: 1,
framingName: 1,
connectorName: 1,
popName: 1,
popTypeName: 1
},
indexName: ‘USSQuickQuotingFilters’,
isMultiKey: false,
multiKeyPaths: {
countryId: ,
countryName: ,
btProductName: ,
btProductDisplayName: ,
supplierName: ,
supplierProductName: ,
accessTypeName: ,
lmpName: ,
lmpConfigurationName: ,
interfaceName: ,
framingName: ,
connectorName: ,
popName: ,
popTypeName:
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: ‘forward’,
indexBounds: {
countryId: [
‘[“DE”, “DE”]’
],
countryName: [
‘[MinKey, MaxKey]’
],
btProductName: [
‘[“BT iVPN”, “BT iVPN”]’
],
btProductDisplayName: [
‘[MinKey, MaxKey]’
],
supplierName: [
‘[MinKey, MaxKey]’
],
supplierProductName: [
‘[MinKey, MaxKey]’
],
accessTypeName: [
‘[“Ethernet”, “Ethernet”]’
],
lmpName: [
‘[MinKey, MaxKey]’
],
lmpConfigurationName: [
‘[MinKey, MaxKey]’
],
interfaceName: [
‘[MinKey, MaxKey]’
],
framingName: [
‘[MinKey, MaxKey]’
],
connectorName: [
‘[MinKey, MaxKey]’
],
popName: [
‘[“Frankfurt Genfer Straße”, “Frankfurt Genfer Straße”]’
],
popTypeName: [
‘[MinKey, MaxKey]’
]
},
keysExamined: 27604,
seeks: 652,
dupsTested: 0,
dupsDropped: 0
}
}
}
}
},
nReturned: 2026,
executionTimeMillisEstimate: 168293
},
{
‘$group’: {
_id: {
supplierName: ‘$supplierName’,
supplierId: ‘$supplierId’
}
},
nReturned: 10,
executionTimeMillisEstimate: 168293
},
{
‘$project’: {
supplierName: ‘$_id.supplierName’,
supplierId: ‘$_id.supplierId’,
_id: false
},
nReturned: 10,
executionTimeMillisEstimate: 168293
},
{
‘$sort’: {
sortKey: {
supplierName: 1
}
},
nReturned: 10,
executionTimeMillisEstimate: 168293
}
],
serverInfo: {
host: ‘blp03537258’,
port: 61901,
version: ‘4.4.11’,
gitVersion: ‘b7530cacde8432d2f22ed506f258ff9c3b45c5e9’
},
ok: 1,
‘$clusterTime’: {
clusterTime: Timestamp({ t: 1677649759, i: 1 }),
signature: {
hash: Binary(Buffer.from(“89d0fc7a6e2eacb74e6448beef1c45c055fb4c63”, “hex”), 0),
keyId: 7163629481175810000
}
},
operationTime: Timestamp({ t: 1677649759, i: 1 })
}

#4

execution 2:

db.btProductAccessMapping.aggregate([{
“$match”: {
“$and”: [{
“accessTypeName”: “Ethernet”
},
{
“aspeedNumValue”: 1000000
},
{
“popName”: “Frankfurt Genfer Straße”
},
{
“countryId”: “DE”
},
{
“btProductName”: “BT iVPN”
}
]
}
},
{
“$group”: {
“_id”: {
“supplierName”: “$supplierName”,
“supplierId”: “$supplierId”
}
}
},
{
“$project”: {
“_id”: 0,
“supplierName”: “$_id.supplierName”,
“supplierId”: “$_id.supplierId”
}
}, {
“$sort”: {
“supplierName”: 1
}
}
]).explain(“executionStats”)
{
stages: [
{
‘$cursor’: {
queryPlanner: {
plannerVersion: 1,
namespace: ‘btProductAvailabilityDB_staging.btProductAccessMapping’,
indexFilterSet: false,
parsedQuery: {
‘$and’: [
{
accessTypeName: {
‘$eq’: ‘Ethernet’
}
},
{
aspeedNumValue: {
‘$eq’: 1000000
}
},
{
btProductName: {
‘$eq’: ‘BT iVPN’
}
},
{
countryId: {
‘$eq’: ‘DE’
}
},
{
popName: {
‘$eq’: ‘Frankfurt Genfer Straße’
}
}
]
},
queryHash: ‘26F32A64’,
planCacheKey: ‘29241033’,
winningPlan: {
stage: ‘PROJECTION_SIMPLE’,
transformBy: {
supplierId: 1,
supplierName: 1,
_id: 0
},
inputStage: {
stage: ‘FETCH’,
filter: {
aspeedNumValue: {
‘$eq’: 1000000
}
},
inputStage: {
stage: ‘IXSCAN’,
keyPattern: {
countryId: 1,
countryName: 1,
btProductName: 1,
btProductDisplayName: 1,
supplierName: 1,
supplierProductName: 1,
accessTypeName: 1,
lmpName: 1,
lmpConfigurationName: 1,
interfaceName: 1,
framingName: 1,
connectorName: 1,
popName: 1,
popTypeName: 1
},
indexName: ‘USSQuickQuotingFilters’,
isMultiKey: false,
multiKeyPaths: {
countryId: ,
countryName: ,
btProductName: ,
btProductDisplayName: ,
supplierName: ,
supplierProductName: ,
accessTypeName: ,
lmpName: ,
lmpConfigurationName: ,
interfaceName: ,
framingName: ,
connectorName: ,
popName: ,
popTypeName:
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: ‘forward’,
indexBounds: {
countryId: [
‘[“DE”, “DE”]’
],
countryName: [
‘[MinKey, MaxKey]’
],
btProductName: [
‘[“BT iVPN”, “BT iVPN”]’
],
btProductDisplayName: [
‘[MinKey, MaxKey]’
],
supplierName: [
‘[MinKey, MaxKey]’
],
supplierProductName: [
‘[MinKey, MaxKey]’
],
accessTypeName: [
‘[“Ethernet”, “Ethernet”]’
],
lmpName: [
‘[MinKey, MaxKey]’
],
lmpConfigurationName: [
‘[MinKey, MaxKey]’
],
interfaceName: [
‘[MinKey, MaxKey]’
],
framingName: [
‘[MinKey, MaxKey]’
],
connectorName: [
‘[MinKey, MaxKey]’
],
popName: [
‘[“Frankfurt Genfer Straße”, “Frankfurt Genfer Straße”]’
],
popTypeName: [
‘[MinKey, MaxKey]’
]
}
}
}
},
rejectedPlans: [
{
stage: ‘PROJECTION_SIMPLE’,
transformBy: {
supplierId: 1,
supplierName: 1,
_id: 0
},
inputStage: {
stage: ‘FETCH’,
filter: {
‘$and’: [
{
aspeedNumValue: {
‘$eq’: 1000000
}
},
{
popName: {
‘$eq’: ‘Frankfurt Genfer Straße’
}
}
]
},
inputStage: {
stage: ‘IXSCAN’,
keyPattern: {
accessTypeName: 1,
cpeAccessType: 1,
supportResilientPop: 1,
btProductName: 1,
btProductAvailabilityStatus: 1,
ipv6Enabled: 1,
ethernetPhaseAttribute: 1,
aspeedValue: 1,
aspeedUom: 1,
pspeedValue: 1,
pspeedUom: 1,
aspeedUpValue: 1,
aspeedUpUom: 1,
serviceVariant: 1,
supplierId: 1,
supplierProductId: 1,
interfaceId: 1,
framingId: 1,
connectorId: 1,
acat: 1,
countryId: 1
},
indexName: ‘accessTypeName_1_cpeAccessType_1_supportResilientPop_1_btProductName_1_btProductAvailabilityStatus_1_ipv6Enabled_1_ethernetPhaseAttribute_1_aspeedValue_1_aspeedUom_1_pspeedValue_1_pspeedUom_1_aspeedUpValue_1_aspeedUpUom_1_serviceVariant_1_supplierId_1_supplierProductId_1_interfaceId_1_framingId_1_connectorId_1_acat_1_countryId_1’,
isMultiKey: false,
multiKeyPaths: {
accessTypeName: ,
cpeAccessType: ,
supportResilientPop: ,
btProductName: ,
btProductAvailabilityStatus: ,
ipv6Enabled: ,
ethernetPhaseAttribute: ,
aspeedValue: ,
aspeedUom: ,
pspeedValue: ,
pspeedUom: ,
aspeedUpValue: ,
aspeedUpUom: ,
serviceVariant: ,
supplierId: ,
supplierProductId: ,
interfaceId: ,
framingId: ,
connectorId: ,
acat: ,
countryId:
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: ‘forward’,
indexBounds: {
accessTypeName: [
‘[“Ethernet”, “Ethernet”]’
],
cpeAccessType: [
‘[MinKey, MaxKey]’
],
supportResilientPop: [
‘[MinKey, MaxKey]’
],
btProductName: [
‘[“BT iVPN”, “BT iVPN”]’
],
btProductAvailabilityStatus: [
‘[MinKey, MaxKey]’
],
ipv6Enabled: [
‘[MinKey, MaxKey]’
],
ethernetPhaseAttribute: [
‘[MinKey, MaxKey]’
],
aspeedValue: [
‘[MinKey, MaxKey]’
],
aspeedUom: [
‘[MinKey, MaxKey]’
],
pspeedValue: [
‘[MinKey, MaxKey]’
],
pspeedUom: [
‘[MinKey, MaxKey]’
],
aspeedUpValue: [
‘[MinKey, MaxKey]’
],
aspeedUpUom: [
‘[MinKey, MaxKey]’
],
serviceVariant: [
‘[MinKey, MaxKey]’
],
supplierId: [
‘[MinKey, MaxKey]’
],
supplierProductId: [
‘[MinKey, MaxKey]’
],
interfaceId: [
‘[MinKey, MaxKey]’
],
framingId: [
‘[MinKey, MaxKey]’
],
connectorId: [
‘[MinKey, MaxKey]’
],
acat: [
‘[MinKey, MaxKey]’
],
countryId: [
‘[“DE”, “DE”]’
]
}
}
}
},
{
stage: ‘PROJECTION_SIMPLE’,
transformBy: {
supplierId: 1,
supplierName: 1,
_id: 0
},
inputStage: {
stage: ‘FETCH’,
filter: {
‘$and’: [
{
aspeedNumValue: {
‘$eq’: 1000000
}
},
{
btProductName: {
‘$eq’: ‘BT iVPN’
}
},
{
popName: {
‘$eq’: ‘Frankfurt Genfer Straße’
}
}
]
},
inputStage: {
stage: ‘IXSCAN’,
keyPattern: {
countryId: 1,
btProductId: 1,
popId: 1,
platformId: 1,
platformName: 1,
accessTypeId: 1,
accessTypeName: 1,
supplierId: 1,
supplierProductId: 1,
aspeedValue: 1,
aspeedUom: 1,
aspeedUpValue: 1,
aspeedUpUom: 1,
pspeedValue: 1,
pspeedUom: 1,
pspeedUpValue: 1,
pspeedUpUom: 1,
portTypeId: 1,
lmpId: 1
},
indexName: ‘countryId_1_btProductId_1_popId_1_platformId_1_platformName_1_accessTypeId_1_accessTypeName_1_supplierId_1_supplierProductId_1_aspeedValue_1_aspeedUom_1_aspeedUpValue_1_aspeedUpUom_1_pspeedValue_1_pspeedUom_1_pspeedUpValue_1_pspeedUpUom_1_portTypeId_1_lmpId_1’,
isMultiKey: false,
multiKeyPaths: {
countryId: ,
btProductId: ,
popId: ,
platformId: ,
platformName: ,
accessTypeId: ,
accessTypeName: ,
supplierId: ,
supplierProductId: ,
aspeedValue: ,
aspeedUom: ,
aspeedUpValue: ,
aspeedUpUom: ,
pspeedValue: ,
pspeedUom: ,
pspeedUpValue: ,
pspeedUpUom: ,
portTypeId: ,
lmpId:
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: ‘forward’,
indexBounds: {
countryId: [
‘[“DE”, “DE”]’
],
btProductId: [
‘[MinKey, MaxKey]’
],
popId: [
‘[MinKey, MaxKey]’
],
platformId: [
‘[MinKey, MaxKey]’
],
platformName: [
‘[MinKey, MaxKey]’
],
accessTypeId: [
‘[MinKey, MaxKey]’
],
accessTypeName: [
‘[“Ethernet”, “Ethernet”]’
],
supplierId: [
‘[MinKey, MaxKey]’
],
supplierProductId: [
‘[MinKey, MaxKey]’
],
aspeedValue: [
‘[MinKey, MaxKey]’
],
aspeedUom: [
‘[MinKey, MaxKey]’
],
aspeedUpValue: [
‘[MinKey, MaxKey]’
],
aspeedUpUom: [
‘[MinKey, MaxKey]’
],
pspeedValue: [
‘[MinKey, MaxKey]’
],
pspeedUom: [
‘[MinKey, MaxKey]’
],
pspeedUpValue: [
‘[MinKey, MaxKey]’
],
pspeedUpUom: [
‘[MinKey, MaxKey]’
],
portTypeId: [
‘[MinKey, MaxKey]’
],
lmpId: [
‘[MinKey, MaxKey]’
]
}
}
}
},
{
stage: ‘PROJECTION_SIMPLE’,
transformBy: {
supplierId: 1,
supplierName: 1,
_id: 0
},
inputStage: {
stage: ‘FETCH’,
filter: {
popName: {
‘$eq’: ‘Frankfurt Genfer Straße’
}
},
inputStage: {
stage: ‘IXSCAN’,
keyPattern: {
accessTypeName: 1,
btProductName: 1,
supplierName: 1,
supplierProductName: 1,
portTypeId: 1,
popTypeId: 1,
aspeedNumValue: 1,
pspeedNumValue: 1,
aspeedUpNumValue: 1,
pspeedUpNumValue: 1,
countryId: 1
},
indexName: ‘Staffscreenfilter’,
isMultiKey: false,
multiKeyPaths: {
accessTypeName: ,
btProductName: ,
supplierName: ,
supplierProductName: ,
portTypeId: ,
popTypeId: ,
aspeedNumValue: ,
pspeedNumValue: ,
aspeedUpNumValue: ,
pspeedUpNumValue: ,
countryId:
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: ‘forward’,
indexBounds: {
accessTypeName: [
‘[“Ethernet”, “Ethernet”]’
],
btProductName: [
‘[“BT iVPN”, “BT iVPN”]’
],
supplierName: [
‘[MinKey, MaxKey]’
],
supplierProductName: [
‘[MinKey, MaxKey]’
],
portTypeId: [
‘[MinKey, MaxKey]’
],
popTypeId: [
‘[MinKey, MaxKey]’
],
aspeedNumValue: [
‘[1000000, 1000000]’
],
pspeedNumValue: [
‘[MinKey, MaxKey]’
],
aspeedUpNumValue: [
‘[MinKey, MaxKey]’
],
pspeedUpNumValue: [
‘[MinKey, MaxKey]’
],
countryId: [
‘[“DE”, “DE”]’
]
}
}
}
}
]
},
executionStats: {
executionSuccess: true,
nReturned: 2026,
executionTimeMillis: 1922,
totalKeysExamined: 27604,
totalDocsExamined: 26952,
executionStages: {
stage: ‘PROJECTION_SIMPLE’,
nReturned: 2026,
executionTimeMillisEstimate: 1411,
works: 27604,
advanced: 2026,
needTime: 25577,
needYield: 0,
saveState: 125,
restoreState: 125,
isEOF: 1,
transformBy: {
supplierId: 1,
supplierName: 1,
_id: 0
},
inputStage: {
stage: ‘FETCH’,
filter: {
aspeedNumValue: {
‘$eq’: 1000000
}
},
nReturned: 2026,
executionTimeMillisEstimate: 1411,
works: 27604,
advanced: 2026,
needTime: 25577,
needYield: 0,
saveState: 125,
restoreState: 125,
isEOF: 1,
docsExamined: 26952,
alreadyHasObj: 0,
inputStage: {
stage: ‘IXSCAN’,
nReturned: 26952,
executionTimeMillisEstimate: 88,
works: 27604,
advanced: 26952,
needTime: 651,
needYield: 0,
saveState: 125,
restoreState: 125,
isEOF: 1,
keyPattern: {
countryId: 1,
countryName: 1,
btProductName: 1,
btProductDisplayName: 1,
supplierName: 1,
supplierProductName: 1,
accessTypeName: 1,
lmpName: 1,
lmpConfigurationName: 1,
interfaceName: 1,
framingName: 1,
connectorName: 1,
popName: 1,
popTypeName: 1
},
indexName: ‘USSQuickQuotingFilters’,
isMultiKey: false,
multiKeyPaths: {
countryId: ,
countryName: ,
btProductName: ,
btProductDisplayName: ,
supplierName: ,
supplierProductName: ,
accessTypeName: ,
lmpName: ,
lmpConfigurationName: ,
interfaceName: ,
framingName: ,
connectorName: ,
popName: ,
popTypeName:
},
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: ‘forward’,
indexBounds: {
countryId: [
‘[“DE”, “DE”]’
],
countryName: [
‘[MinKey, MaxKey]’
],
btProductName: [
‘[“BT iVPN”, “BT iVPN”]’
],
btProductDisplayName: [
‘[MinKey, MaxKey]’
],
supplierName: [
‘[MinKey, MaxKey]’
],
supplierProductName: [
‘[MinKey, MaxKey]’
],
accessTypeName: [
‘[“Ethernet”, “Ethernet”]’
],
lmpName: [
‘[MinKey, MaxKey]’
],
lmpConfigurationName: [
‘[MinKey, MaxKey]’
],
interfaceName: [
‘[MinKey, MaxKey]’
],
framingName: [
‘[MinKey, MaxKey]’
],
connectorName: [
‘[MinKey, MaxKey]’
],
popName: [
‘[“Frankfurt Genfer Straße”, “Frankfurt Genfer Straße”]’
],
popTypeName: [
‘[MinKey, MaxKey]’
]
},
keysExamined: 27604,
seeks: 652,
dupsTested: 0,
dupsDropped: 0
}
}
}
}
},
nReturned: 2026,
executionTimeMillisEstimate: 1209
},
{
‘$group’: {
_id: {
supplierName: ‘$supplierName’,
supplierId: ‘$supplierId’
}
},
nReturned: 10,
executionTimeMillisEstimate: 1209
},
{
‘$project’: {
supplierName: ‘$_id.supplierName’,
supplierId: ‘$_id.supplierId’,
_id: false
},
nReturned: 10,
executionTimeMillisEstimate: 1209
},
{
‘$sort’: {
sortKey: {
supplierName: 1
}
},
nReturned: 10,
executionTimeMillisEstimate: 1209
}
],
serverInfo: {
host: ‘blp03537258’,
port: 61901,
version: ‘4.4.11’,
gitVersion: ‘b7530cacde8432d2f22ed506f258ff9c3b45c5e9’
},
ok: 1,
‘$clusterTime’: {
clusterTime: Timestamp({ t: 1677650309, i: 1 }),
signature: {
hash: Binary(Buffer.from(“2a91e8cdae5cce2b058b76d29d370d3ec487472d”, “hex”), 0),
keyId: 7163629481175810000
}
},
operationTime: Timestamp({ t: 1677650309, i: 1 })
}

#5

Document model:
{
“_id”: {
“$oid”: “63e3485d2e6a2c71dd04ee29”
},
“popId”: “081-00012”,
“popName”: “Tokyo 2”,
“popTypeId”: 1,
“popTypeName”: “GPOP”,
“legacyCountryId”: 13,
“countryName”: “Japan”,
“countryId”: “JP”,
“cityName”: “Tokyo”,
“btProductId”: 76,
“btProductName”: “BT MPLS”,
“btProductDisplayName”: “IP Connect global (EDCA)”,
“btProductAbbr”: “MPLS”,
“btProductAvailCd”: 1,
“btProductAvailabilityStatus”: “Standard”,
“specialBidcomments”: “NA”,
“platformId”: 3,
“platformName”: “MPLS - harmonized”,
“supplierId”: 4,
“supplierName”: “Colt”,
“supplierProductId”: 694652,
“supplierProductName”: “Colt Ethernet Hub and Spoke P2A”,
“accessTypeGroup”: “Ethernet”,
“accessTypeId”: 2,
“accessTypeName”: “Ethernet”,
“cpeAccessType”: “1 Gbps (Ethernet)”,
“pspeedCode”: 2254,
“pspeedValue”: “6”,
“pspeedUom”: “Mbps”,
“pspeedNumValue”: 6000,
“pspeedUpCode”: 2254,
“pspeedUpValue”: “6”,
“pspeedUpUom”: “Mbps”,
“pspeedUpNumValue”: 6000,
“pspeedStatusCode”: 1,
“portAvailabilityStatus”: “Available”,
“aspeedCode”: 7017,
“aspeedValue”: “60”,
“aspeedUom”: “Mbps”,
“aspeedNumValue”: 60000,
“aspeedStatusCode”: 1,
“accessAvailabilityStatus”: “Available”,
“orderingStatus”: “Standard”,
“aspeedUpCode”: 7017,
“aspeedUpValue”: “60”,
“aspeedUpUom”: “Mbps”,
“aspeedUpNumValue”: 60000,
“interfaceId”: 13,
“interfaceName”: “1000Base-LX”,
“framingId”: 2,
“framingName”: “None (Clear)”,
“connectorId”: 6,
“connectorName”: “SC series”,
“accessLeadTime”: null,
“accessLeadTimeStatus”: null,
“pspeedLeadTime”: 14,
“cpeLeadTime”: null,
“cpeLeadTimeStatus”: null,
“serviceLeadTimeCpe”: “34”,
“serLeadtimestatCpe”: “Estimate”,
“serviceLeadTimeNocpe”: “34”,
“serLeadtimestatNocpe”: “Estimate”,
“supportResilientPop”: 0,
“serviceId”: null,
“portTypeId”: 23504,
“btPackageId”: -1,
“btPackageName”: null,
“serviceVariant”: “Premium”,
“deliveryMode”: “Fibre”,
“ipv6Enabled”: true,
“ethernetPhaseId”: 24,
“ethernetPhaseAttribute”: “2b”,
“customerLocationTypeId”: 4,
“customerLocationTypeName”: “Off Net”,
“faultrepair24x7Id”: 2,
“faultrepair24x7Name”: “included”,
“lmpId”: 2261,
“lmpName”: “Colt”,
“lmpConfigurationName”: “Colt(Off Net)”,
“acat”: “AC1+”,
“spm”: “Amber”,
“productAvailability”: “Off Net”,
“standardFrameSize”: 1600,
“status”: “LIVE”,
“physicalLayer”: “TBC”,
“deliveryMedium”: “TBC”
}