Details
-
Bug
-
Resolution: Done
-
Major - P3
-
None
-
6.0.5
-
None
-
None
-
ALL
Description
my server is 6.0.5 cluster.
cluster has two shards.now i use $lookup for two tables.when i want to analyze explain for lookup subpipeline.but all output is 0.
totalDocsExamined: Long("0"),
totalKeysExamined: Long("0"),
collectionScans: Long("0"),
indexesUsed: [],
[direct: mongos] datacenter> db.order.aggregate([{$match:{createTime: {$gt:ISODate("2023-08-03T00:00:00")}}},{$lookup:{from:"terminal",localField:"waybillNo",foreignField:"waybillNo",as:"output"}},{$match:{output:{$ne:[]}}}]).itcount() |
151
|
[direct: mongos] datacenter>
|
[direct: mongos] datacenter> db.order.aggregate([{$match:{createTime: {$gt:ISODate("2023-08-03T00:00:00")}}},{$lookup:{from:"terminal",localField:"waybillNo",foreignField:"waybillNo",as:"output"}},{$match:{output:{$ne:[]}}}],{'explain':"executionStats"}) |
{
|
serverInfo: {
|
host: 'xxxx', |
port: 31051, |
version: '6.0.5', |
gitVersion: 'c9a99c120371d4d4c52cbb15dac34a36ce8d3b1d' |
},
|
serverParameters: {
|
internalQueryFacetBufferSizeBytes: 104857600, |
internalQueryFacetMaxOutputDocSizeBytes: 104857600, |
internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600, |
internalDocumentSourceGroupMaxMemoryBytes: 104857600, |
internalQueryMaxBlockingSortMemoryUsageBytes: 104857600, |
internalQueryProhibitBlockingMergeOnMongoS: 0, |
internalQueryMaxAddToSetBytes: 104857600, |
internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600 |
},
|
mergeType: 'mongos', |
splitPipeline: {
|
shardsPart: [
|
{
|
'$match': { createTime: { '$gt': ISODate("2023-08-03T00:00:00.000Z") } } |
},
|
{
|
'$lookup': { |
from: 'terminal', |
as: 'output', |
localField: 'waybillNo', |
foreignField: 'waybillNo' |
}
|
},
|
{
|
'$match': { output: { '$not': { '$eq': [] } } } |
}
|
],
|
mergerPart: [
|
{
|
'$mergeCursors': { |
lsid: {
|
id: new UUID("a33d7596-2c08-475c-ab61-b188446698f2"), |
uid: Binary(Buffer.from("3b408cb48548b5037822c10eb0976b3cbf2cee3bf9c708796bf03941fbecd80f", "hex"), 0) |
},
|
compareWholeSortKey: false, |
tailableMode: 'normal', |
nss: 'datacenter.order', |
allowPartialResults: false, |
recordRemoteOpWaitTime: false |
}
|
}
|
]
|
},
|
shards: {
|
shard1: {
|
host: 'host1:31001', |
stages: [
|
{
|
'$cursor': { |
queryPlanner: {
|
namespace: 'datacenter.order', |
indexFilterSet: false, |
parsedQuery: {
|
createTime: { '$gt': ISODate("2023-08-03T00:00:00.000Z") } |
},
|
queryHash: 'BFA14242', |
planCacheKey: '5D24761B', |
maxIndexedOrSolutionsReached: false, |
maxIndexedAndSolutionsReached: false, |
maxScansToExplodeReached: false, |
winningPlan: {
|
stage: 'SHARDING_FILTER', |
inputStage: {
|
stage: 'FETCH', |
inputStage: {
|
stage: 'IXSCAN', |
keyPattern: { createTime: 1 }, |
indexName: 'createTime_1', |
isMultiKey: false, |
multiKeyPaths: { createTime: [] },
|
isUnique: false, |
isSparse: false, |
isPartial: false, |
indexVersion: 2, |
direction: 'forward', |
indexBounds: {
|
createTime: [
|
'(new Date(1691020800000), new Date(9223372036854775807)]' |
]
|
}
|
}
|
}
|
},
|
rejectedPlans: []
|
},
|
executionStats: {
|
executionSuccess: true, |
nReturned: 317, |
executionTimeMillis: 196, |
totalKeysExamined: 317, |
totalDocsExamined: 317, |
executionStages: {
|
stage: 'SHARDING_FILTER', |
nReturned: 317, |
executionTimeMillisEstimate: 0, |
works: 318, |
advanced: 317, |
needTime: 0, |
needYield: 0, |
saveState: 1, |
restoreState: 1, |
isEOF: 1, |
chunkSkips: 0, |
inputStage: {
|
stage: 'FETCH', |
nReturned: 317, |
executionTimeMillisEstimate: 0, |
works: 318, |
advanced: 317, |
needTime: 0, |
needYield: 0, |
saveState: 1, |
restoreState: 1, |
isEOF: 1, |
docsExamined: 317, |
alreadyHasObj: 0, |
inputStage: {
|
stage: 'IXSCAN', |
nReturned: 317, |
executionTimeMillisEstimate: 0, |
works: 318, |
advanced: 317, |
needTime: 0, |
needYield: 0, |
saveState: 1, |
restoreState: 1, |
isEOF: 1, |
keyPattern: { createTime: 1 }, |
indexName: 'createTime_1', |
isMultiKey: false, |
multiKeyPaths: { createTime: [] },
|
isUnique: false, |
isSparse: false, |
isPartial: false, |
indexVersion: 2, |
direction: 'forward', |
indexBounds: {
|
createTime: [
|
'(new Date(1691020800000), new Date(9223372036854775807)]' |
]
|
},
|
keysExamined: 317, |
seeks: 1, |
dupsTested: 0, |
dupsDropped: 0 |
}
|
}
|
}
|
}
|
},
|
nReturned: Long("317"), |
executionTimeMillisEstimate: Long("0") |
},
|
{
|
'$lookup': { |
from: 'terminal', |
as: 'output', |
localField: 'waybillNo', |
foreignField: 'waybillNo' |
},
|
totalDocsExamined: Long("0"), |
totalKeysExamined: Long("0"), |
collectionScans: Long("0"), |
indexesUsed: [],
|
nReturned: Long("317"), |
executionTimeMillisEstimate: Long("195") |
},
|
{
|
'$match': { output: { '$not': { '$eq': [] } } }, |
nReturned: Long("0"), |
executionTimeMillisEstimate: Long("195") |
}
|
]
|
},
|
shard2: {
|
host: 'host2:31002', |
stages: [
|
{
|
'$cursor': { |
queryPlanner: {
|
namespace: 'datacenter.order', |
indexFilterSet: false, |
parsedQuery: {
|
createTime: { '$gt': ISODate("2023-08-03T00:00:00.000Z") } |
},
|
queryHash: 'BFA14242', |
planCacheKey: '5D24761B', |
maxIndexedOrSolutionsReached: false, |
maxIndexedAndSolutionsReached: false, |
maxScansToExplodeReached: false, |
winningPlan: {
|
stage: 'SHARDING_FILTER', |
inputStage: {
|
stage: 'FETCH', |
inputStage: {
|
stage: 'IXSCAN', |
keyPattern: { createTime: 1 }, |
indexName: 'createTime_1', |
isMultiKey: false, |
multiKeyPaths: { createTime: [] },
|
isUnique: false, |
isSparse: false, |
isPartial: false, |
indexVersion: 2, |
direction: 'forward', |
indexBounds: {
|
createTime: [
|
'(new Date(1691020800000), new Date(9223372036854775807)]' |
]
|
}
|
}
|
}
|
},
|
rejectedPlans: []
|
},
|
executionStats: {
|
executionSuccess: true, |
nReturned: 736, |
executionTimeMillis: 420, |
totalKeysExamined: 736, |
totalDocsExamined: 736, |
executionStages: {
|
stage: 'SHARDING_FILTER', |
nReturned: 736, |
executionTimeMillisEstimate: 7, |
works: 737, |
advanced: 736, |
needTime: 0, |
needYield: 0, |
saveState: 1, |
restoreState: 1, |
isEOF: 1, |
chunkSkips: 0, |
inputStage: {
|
stage: 'FETCH', |
nReturned: 736, |
executionTimeMillisEstimate: 5, |
works: 737, |
advanced: 736, |
needTime: 0, |
needYield: 0, |
saveState: 1, |
restoreState: 1, |
isEOF: 1, |
docsExamined: 736, |
alreadyHasObj: 0, |
inputStage: {
|
stage: 'IXSCAN', |
nReturned: 736, |
executionTimeMillisEstimate: 4, |
works: 737, |
advanced: 736, |
needTime: 0, |
needYield: 0, |
saveState: 1, |
restoreState: 1, |
isEOF: 1, |
keyPattern: { createTime: 1 }, |
indexName: 'createTime_1', |
isMultiKey: false, |
multiKeyPaths: { createTime: [] },
|
isUnique: false, |
isSparse: false, |
isPartial: false, |
indexVersion: 2, |
direction: 'forward', |
indexBounds: {
|
createTime: [
|
'(new Date(1691020800000), new Date(9223372036854775807)]' |
]
|
},
|
keysExamined: 736, |
seeks: 1, |
dupsTested: 0, |
dupsDropped: 0 |
}
|
}
|
}
|
}
|
},
|
nReturned: Long("736"), |
executionTimeMillisEstimate: Long("7") |
},
|
{
|
'$lookup': { |
from: 'terminal', |
as: 'output', |
localField: 'waybillNo', |
foreignField: 'waybillNo' |
},
|
totalDocsExamined: Long("0"), |
totalKeysExamined: Long("0"), |
collectionScans: Long("0"), |
indexesUsed: [],
|
nReturned: Long("736"), |
executionTimeMillisEstimate: Long("420") |
},
|
{
|
'$match': { output: { '$not': { '$eq': [] } } }, |
nReturned: Long("0"), |
executionTimeMillisEstimate: Long("420") |
}
|
]
|
}
|
},
|
command: {
|
aggregate: 'order', |
pipeline: [
|
{
|
'$match': { createTime: { '$gt': ISODate("2023-08-03T00:00:00.000Z") } } |
},
|
{
|
'$lookup': { |
from: 'terminal', |
localField: 'waybillNo', |
foreignField: 'waybillNo', |
as: 'output' |
}
|
},
|
{ '$match': { output: { '$ne': [] } } } |
],
|
cursor: {}
|
},
|
ok: 1, |
'$clusterTime': { |
clusterTime: Timestamp({ t: 1691112367, i: 1 }), |
signature: {
|
hash: Binary(Buffer.from("2460b999b760d306c2ce2357b7ed59596a224ca6", "hex"), 0), |
keyId: Long("7225447387848769561") |
}
|
},
|
operationTime: Timestamp({ t: 1691112367, i: 1 }) |
}
|
|