|
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 })
|
}
|
|
|