The mongodb database aggregation query is slow in sorting, and the CPU and hard disk IO utilization are not used very much. Please ask the boss to point out the problem
aggregation code
[{
$match: {
SpcpProfileId: {
$in: [
42177743503074
]
}
}
}, {
$lookup: {
from: 'CampaignsReport',
'let': {
camnewId: '$SpcpCampaignId'
},
pipeline: [
{
$match: {
$expr: {
$and: [
{
$gte: [
'$reportTime',
1663603200
]
},
{
$eq: [
'$camId',
'$$camnewId'
]
}
]
}
}
},
{
$project: {
camId: 1,
adDetail: {
cost: 1
}
}
}
],
as: 'CampaignsReport_doc'
}
}, {
$project: {
SpcpCampaignId: 1,
SpcpName: 1,
cost: {
$sum: '$adDetail.cost'
}
}
}, {
$sort: {
cost: -1
}
}, {
$limit: 10
}]
explain(‘allPlansExecution’) result
{ stages:
[ { '$cursor':
{ query: { SpcpProfileId: { '$in': [ 42177743503074 ] } },
fields: { SpcpCampaignId: 1, SpcpName: 1, 'adDetail.cost': 1, _id: 1 },
queryPlanner:
{ plannerVersion: 1,
namespace: 'AmazonAds.SpCampaignsMongo',
indexFilterSet: false,
parsedQuery: { SpcpProfileId: { '$eq': 42177743503074 } },
winningPlan:
{ stage: 'FETCH',
inputStage:
{ stage: 'IXSCAN',
keyPattern: { SpcpProfileId: 1 },
indexName: 'SpcpProfileId_1',
isMultiKey: false,
multiKeyPaths: { SpcpProfileId: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { SpcpProfileId: [ '[42177743503074.0, 42177743503074.0]' ] } } },
rejectedPlans:
[ { stage: 'FETCH',
inputStage:
{ stage: 'IXSCAN',
keyPattern:
{ SpcpProfileId: 1,
SpcpPortfolioId: 1,
SpcpState: 1,
SpcpServingStatus: 1 },
indexName: 'SpcpProfileId_1_SpcpPortfolioId_1_SpcpState_1_SpcpServingStatus_1',
isMultiKey: false,
multiKeyPaths:
{ SpcpProfileId: [],
SpcpPortfolioId: [],
SpcpState: [],
SpcpServingStatus: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds:
{ SpcpProfileId: [ '[42177743503074.0, 42177743503074.0]' ],
SpcpPortfolioId: [ '[MinKey, MaxKey]' ],
SpcpState: [ '[MinKey, MaxKey]' ],
SpcpServingStatus: [ '[MinKey, MaxKey]' ] } } } ] },
executionStats:
{ executionSuccess: true,
nReturned: 1680,
executionTimeMillis: 7189,
totalKeysExamined: 1680,
totalDocsExamined: 1680,
executionStages:
{ stage: 'FETCH',
nReturned: 1680,
executionTimeMillisEstimate: 0,
works: 1681,
advanced: 1680,
needTime: 0,
needYield: 0,
saveState: 15,
restoreState: 15,
isEOF: 1,
invalidates: 0,
docsExamined: 1680,
alreadyHasObj: 0,
inputStage:
{ stage: 'IXSCAN',
nReturned: 1680,
executionTimeMillisEstimate: 0,
works: 1681,
advanced: 1680,
needTime: 0,
needYield: 0,
saveState: 15,
restoreState: 15,
isEOF: 1,
invalidates: 0,
keyPattern: { SpcpProfileId: 1 },
indexName: 'SpcpProfileId_1',
isMultiKey: false,
multiKeyPaths: { SpcpProfileId: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { SpcpProfileId: [ '[42177743503074.0, 42177743503074.0]' ] },
keysExamined: 1680,
seeks: 1,
dupsTested: 0,
dupsDropped: 0,
seenInvalidated: 0 } },
allPlansExecution:
[ { nReturned: 101,
executionTimeMillisEstimate: 0,
totalKeysExamined: 101,
totalDocsExamined: 101,
executionStages:
{ stage: 'FETCH',
nReturned: 101,
executionTimeMillisEstimate: 0,
works: 101,
advanced: 101,
needTime: 0,
needYield: 0,
saveState: 2,
restoreState: 1,
isEOF: 0,
invalidates: 0,
docsExamined: 101,
alreadyHasObj: 0,
inputStage:
{ stage: 'IXSCAN',
nReturned: 101,
executionTimeMillisEstimate: 0,
works: 101,
advanced: 101,
needTime: 0,
needYield: 0,
saveState: 2,
restoreState: 1,
isEOF: 0,
invalidates: 0,
keyPattern: { SpcpProfileId: 1 },
indexName: 'SpcpProfileId_1',
isMultiKey: false,
multiKeyPaths: { SpcpProfileId: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { SpcpProfileId: [ '[42177743503074.0, 42177743503074.0]' ] },
keysExamined: 101,
seeks: 1,
dupsTested: 0,
dupsDropped: 0,
seenInvalidated: 0 } } },
{ nReturned: 101,
executionTimeMillisEstimate: 0,
totalKeysExamined: 101,
totalDocsExamined: 101,
executionStages:
{ stage: 'FETCH',
nReturned: 101,
executionTimeMillisEstimate: 0,
works: 101,
advanced: 101,
needTime: 0,
needYield: 0,
saveState: 15,
restoreState: 15,
isEOF: 0,
invalidates: 0,
docsExamined: 101,
alreadyHasObj: 0,
inputStage:
{ stage: 'IXSCAN',
nReturned: 101,
executionTimeMillisEstimate: 0,
works: 101,
advanced: 101,
needTime: 0,
needYield: 0,
saveState: 15,
restoreState: 15,
isEOF: 0,
invalidates: 0,
keyPattern:
{ SpcpProfileId: 1,
SpcpPortfolioId: 1,
SpcpState: 1,
SpcpServingStatus: 1 },
indexName: 'SpcpProfileId_1_SpcpPortfolioId_1_SpcpState_1_SpcpServingStatus_1',
isMultiKey: false,
multiKeyPaths:
{ SpcpProfileId: [],
SpcpPortfolioId: [],
SpcpState: [],
SpcpServingStatus: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds:
{ SpcpProfileId: [ '[42177743503074.0, 42177743503074.0]' ],
SpcpPortfolioId: [ '[MinKey, MaxKey]' ],
SpcpState: [ '[MinKey, MaxKey]' ],
SpcpServingStatus: [ '[MinKey, MaxKey]' ] },
keysExamined: 101,
seeks: 1,
dupsTested: 0,
dupsDropped: 0,
seenInvalidated: 0 } } } ] } } },
{ '$lookup':
{ from: 'CampaignsReport',
as: 'CampaignsReport_doc',
let: { camnewId: '$SpcpCampaignId' },
pipeline:
[ { '$match':
{ '$expr':
{ '$and':
[ { '$gte': [ '$reportTime', 1663603200 ] },
{ '$eq': [ '$camId', '$$camnewId' ] } ] } } },
{ '$project': { camId: 1, adDetail: { cost: 1 } } } ] } },
{ '$project':
{ _id: true,
SpcpName: true,
SpcpCampaignId: true,
cost: { '$sum': [ '$adDetail.cost' ] } } },
{ '$sort': { sortKey: { cost: -1 }, limit: 10 } } ],
ok: 1 }