[0.11.x] Very different query performance on ReactiveMongo vs MongoShell
42 views
Skip to first unread message
Edmondo Porcu
Dec 13, 2017, 6:35:21 PM12/13/17
to ReactiveMongo - http://reactivemongo.org
Hello,
I have an annoying use case of query performance I can't resolve:
- When I run the query from Studio 3t / MongoShell, it takes little to complete (except data transfer)
- When I run a query explain from Scala code, it takes about 2 minutes
- When I run the query from Scala code, it takes even longer.
I have no idea of what is wrong, any suggestion is welcome. Here the explain result:
{
queryPlanner: {
plannerVersion: BSONInteger(1),
namespace: "cerved.quickreportxml",
indexFilterSet: BSONBoolean(false),
parsedQuery: {
$and: [
0: {
reportIdentifier.companyIdentifier.vatCode: {
$eq: "01026940427"
}
},
1: {
createdAt: {
$gt: BSONLong(513206750567)
}
}
]
},
winningPlan: {
stage: "FETCH",
inputStage: {
stage: "IXSCAN",
keyPattern: {
reportIdentifier.companyIdentifier.vatCode: BSONInteger(1),
createdAt: BSONInteger(1)
},
indexName: "quickreport_createdAt_companyIdentifier",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "backward",
indexBounds: {
reportIdentifier.companyIdentifier.vatCode: [
0: "["01026940427", "01026940427"]"
],
createdAt: [
0: "[inf.0, 513206750567)"
]
}
}
},
rejectedPlans: [
0: {
stage: "FETCH",
inputStage: {
stage: "IXSCAN",
keyPattern: {
createdAt: BSONInteger(1),
reportIdentifier.companyIdentifier.vatCode: BSONInteger(1)
},
indexName: "createdAt_1_reportIdentifier.companyIdentifier.vatCode_1",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "backward",
indexBounds: {
createdAt: [
0: "[inf.0, 513206750567)"
],
reportIdentifier.companyIdentifier.vatCode: [
0: "["01026940427", "01026940427"]"
]
}
}
},
1: {
stage: "FETCH",
filter: {
reportIdentifier.companyIdentifier.vatCode: {
$eq: "01026940427"
}
},
inputStage: {
stage: "IXSCAN",
keyPattern: {
createdAt: BSONInteger(-1)
},
indexName: "quickreportxml_createdAt_descending",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "forward",
indexBounds: {
createdAt: [
0: "[inf.0, 513206750567)"
]
}
}
},
2: {
stage: "FETCH",
filter: {
reportIdentifier.companyIdentifier.vatCode: {
$eq: "01026940427"
}
},
inputStage: {
stage: "IXSCAN",
keyPattern: {
createdAt: BSONDouble(1.0)
},
indexName: "quickreportxml_createdAt",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "backward",
indexBounds: {
createdAt: [
0: "[inf.0, 513206750567)"
]
}
}
},
3: {
stage: "ENSURE_SORTED",
inputStage: {
stage: "OR",
inputStages: [
0: {
stage: "SORT",
sortPattern: {
createdAt: BSONInteger(-1)
},
limitAmount: BSONInteger(1),
inputStage: {
stage: "SORT_KEY_GENERATOR",
inputStage: {
stage: "FETCH",
filter: {
createdAt: {
$gt: BSONLong(513206750567)
}
},
inputStage: {
stage: "IXSCAN",
keyPattern: {
reportIdentifier.companyIdentifier.vatCode: BSONInteger(1)
},
indexName: "quickreportxml_companyidentifier",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "forward",
indexBounds: {
reportIdentifier.companyIdentifier.vatCode: [
0: "["01026940427", "01026940427"]"
]
}
}
}
}
},
1: {
stage: "SORT",
sortPattern: {
createdAt: BSONInteger(-1)
},
inputStage: {
stage: "SORT_KEY_GENERATOR",
inputStage: {
stage: "FETCH",
filter: {
createdAt: {
$gt: BSONLong(513206750567)
}
},
inputStage: {
stage: "IXSCAN",
keyPattern: {
reportIdentifier.companyIdentifier.vatCode: BSONInteger(1)
},
indexName: "quickreportxml_companyidentifier",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "forward",
indexBounds: {
reportIdentifier.companyIdentifier.vatCode: [
0: "["01026940427", "01026940427"]"
]
}
}
}
}
}
]
}
}
]
},
executionStats: {
executionSuccess: BSONBoolean(true),
nReturned: BSONInteger(32),
executionTimeMillis: BSONInteger(59),
totalKeysExamined: BSONInteger(32),
totalDocsExamined: BSONInteger(32),
executionStages: {
stage: "FETCH",
nReturned: BSONInteger(32),
executionTimeMillisEstimate: BSONInteger(60),
works: BSONInteger(33),
advanced: BSONInteger(32),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(1),
invalidates: BSONInteger(0),
docsExamined: BSONInteger(32),
alreadyHasObj: BSONInteger(0),
inputStage: {
stage: "IXSCAN",
nReturned: BSONInteger(32),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(33),
advanced: BSONInteger(32),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(1),
invalidates: BSONInteger(0),
keyPattern: {
reportIdentifier.companyIdentifier.vatCode: BSONInteger(1),
createdAt: BSONInteger(1)
},
indexName: "quickreport_createdAt_companyIdentifier",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "backward",
indexBounds: {
reportIdentifier.companyIdentifier.vatCode: [
0: "["01026940427", "01026940427"]"
],
createdAt: [
0: "[inf.0, 513206750567)"
]
},
keysExamined: BSONInteger(32),
dupsTested: BSONInteger(0),
dupsDropped: BSONInteger(0),
seenInvalidated: BSONInteger(0)
}
},
allPlansExecution: [
0: {
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
totalKeysExamined: BSONInteger(1),
totalDocsExamined: BSONInteger(0),
executionStages: {
stage: "FETCH",
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(0),
needTime: BSONInteger(1),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
docsExamined: BSONInteger(0),
alreadyHasObj: BSONInteger(0),
inputStage: {
stage: "IXSCAN",
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(0),
needTime: BSONInteger(1),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
keyPattern: {
createdAt: BSONInteger(1),
reportIdentifier.companyIdentifier.vatCode: BSONInteger(1)
},
indexName: "createdAt_1_reportIdentifier.companyIdentifier.vatCode_1",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "backward",
indexBounds: {
createdAt: [
0: "[inf.0, 513206750567)"
],
reportIdentifier.companyIdentifier.vatCode: [
0: "["01026940427", "01026940427"]"
]
},
keysExamined: BSONInteger(1),
dupsTested: BSONInteger(0),
dupsDropped: BSONInteger(0),
seenInvalidated: BSONInteger(0)
}
}
},
1: {
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
totalKeysExamined: BSONInteger(1),
totalDocsExamined: BSONInteger(1),
executionStages: {
stage: "FETCH",
filter: {
reportIdentifier.companyIdentifier.vatCode: {
$eq: "01026940427"
}
},
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(0),
needTime: BSONInteger(1),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
docsExamined: BSONInteger(1),
alreadyHasObj: BSONInteger(0),
inputStage: {
stage: "IXSCAN",
nReturned: BSONInteger(1),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(1),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
keyPattern: {
createdAt: BSONInteger(-1)
},
indexName: "quickreportxml_createdAt_descending",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "forward",
indexBounds: {
createdAt: [
0: "[inf.0, 513206750567)"
]
},
keysExamined: BSONInteger(1),
dupsTested: BSONInteger(0),
dupsDropped: BSONInteger(0),
seenInvalidated: BSONInteger(0)
}
}
},
2: {
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
totalKeysExamined: BSONInteger(1),
totalDocsExamined: BSONInteger(1),
executionStages: {
stage: "FETCH",
filter: {
reportIdentifier.companyIdentifier.vatCode: {
$eq: "01026940427"
}
},
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(0),
needTime: BSONInteger(1),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
docsExamined: BSONInteger(1),
alreadyHasObj: BSONInteger(0),
inputStage: {
stage: "IXSCAN",
nReturned: BSONInteger(1),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(1),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
keyPattern: {
createdAt: BSONDouble(1.0)
},
indexName: "quickreportxml_createdAt",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "backward",
indexBounds: {
createdAt: [
0: "[inf.0, 513206750567)"
]
},
keysExamined: BSONInteger(1),
dupsTested: BSONInteger(0),
dupsDropped: BSONInteger(0),
seenInvalidated: BSONInteger(0)
}
}
},
3: {
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
totalKeysExamined: BSONInteger(0),
totalDocsExamined: BSONInteger(0),
executionStages: {
stage: "ENSURE_SORTED",
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(0),
needTime: BSONInteger(1),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
nDropped: BSONInteger(0),
inputStage: {
stage: "OR",
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(0),
needTime: BSONInteger(1),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
dupsTested: BSONInteger(0),
dupsDropped: BSONInteger(0),
locsForgotten: BSONInteger(0),
inputStages: [
0: {
stage: "SORT",
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(0),
needTime: BSONInteger(1),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
sortPattern: {
createdAt: BSONInteger(-1)
},
memUsage: BSONInteger(0),
memLimit: BSONInteger(33554432),
limitAmount: BSONInteger(1),
inputStage: {
stage: "SORT_KEY_GENERATOR",
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(0),
needTime: BSONInteger(1),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
inputStage: {
stage: "FETCH",
filter: {
createdAt: {
$gt: BSONLong(513206750567)
}
},
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(0),
advanced: BSONInteger(0),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
docsExamined: BSONInteger(0),
alreadyHasObj: BSONInteger(0),
inputStage: {
stage: "IXSCAN",
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(0),
advanced: BSONInteger(0),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
keyPattern: {
reportIdentifier.companyIdentifier.vatCode: BSONInteger(1)
},
indexName: "quickreportxml_companyidentifier",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "forward",
indexBounds: {
reportIdentifier.companyIdentifier.vatCode: [
0: "["01026940427", "01026940427"]"
]
},
keysExamined: BSONInteger(0),
dupsTested: BSONInteger(0),
dupsDropped: BSONInteger(0),
seenInvalidated: BSONInteger(0)
}
}
}
},
1: {
stage: "SORT",
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(0),
advanced: BSONInteger(0),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
sortPattern: {
createdAt: BSONInteger(-1)
},
memUsage: BSONInteger(0),
memLimit: BSONInteger(33554432),
inputStage: {
stage: "SORT_KEY_GENERATOR",
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(0),
advanced: BSONInteger(0),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
inputStage: {
stage: "FETCH",
filter: {
createdAt: {
$gt: BSONLong(513206750567)
}
},
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(0),
advanced: BSONInteger(0),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
docsExamined: BSONInteger(0),
alreadyHasObj: BSONInteger(0),
inputStage: {
stage: "IXSCAN",
nReturned: BSONInteger(0),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(0),
advanced: BSONInteger(0),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(3),
restoreState: BSONInteger(3),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
keyPattern: {
reportIdentifier.companyIdentifier.vatCode: BSONInteger(1)
},
indexName: "quickreportxml_companyidentifier",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "forward",
indexBounds: {
reportIdentifier.companyIdentifier.vatCode: [
0: "["01026940427", "01026940427"]"
]
},
keysExamined: BSONInteger(0),
dupsTested: BSONInteger(0),
dupsDropped: BSONInteger(0),
seenInvalidated: BSONInteger(0)
}
}
}
}
]
}
}
},
4: {
nReturned: BSONInteger(1),
executionTimeMillisEstimate: BSONInteger(0),
totalKeysExamined: BSONInteger(1),
totalDocsExamined: BSONInteger(1),
executionStages: {
stage: "FETCH",
nReturned: BSONInteger(1),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(1),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(0),
restoreState: BSONInteger(0),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
docsExamined: BSONInteger(1),
alreadyHasObj: BSONInteger(0),
inputStage: {
stage: "IXSCAN",
nReturned: BSONInteger(1),
executionTimeMillisEstimate: BSONInteger(0),
works: BSONInteger(1),
advanced: BSONInteger(1),
needTime: BSONInteger(0),
needYield: BSONInteger(0),
saveState: BSONInteger(0),
restoreState: BSONInteger(0),
isEOF: BSONInteger(0),
invalidates: BSONInteger(0),
keyPattern: {
reportIdentifier.companyIdentifier.vatCode: BSONInteger(1),
createdAt: BSONInteger(1)
},
indexName: "quickreport_createdAt_companyIdentifier",
isMultiKey: BSONBoolean(false),
isUnique: BSONBoolean(false),
isSparse: BSONBoolean(false),
isPartial: BSONBoolean(false),
indexVersion: BSONInteger(1),
direction: "backward",
indexBounds: {
reportIdentifier.companyIdentifier.vatCode: [
0: "["01026940427", "01026940427"]"
],
createdAt: [
0: "[inf.0, 513206750567)"
]
},
keysExamined: BSONInteger(1),
dupsTested: BSONInteger(0),
dupsDropped: BSONInteger(0),
seenInvalidated: BSONInteger(0)
}
}
}
]
},
serverInfo: {
host: "ip-10-16-71-243.eu-west-1.compute.internal",
port: BSONInteger(27017),
version: "3.2.9",
gitVersion: "22ec9e93b40c85fc7cae7d56e7d6a02fd811088c"
}
}
However, when I log the execution of thies query
Edmondo Porcu
Dec 14, 2017, 2:36:03 AM12/14/17
to ReactiveMongo - http://reactivemongo.org
Interesting, in 0.12.8 the speed improvement is there. Although I still do not have the same performance of mongoshell it is obvious working much better
Reply all
Reply to author
Forward
0 new messages