Loading...

Type: Bug
Resolution: Done
Priority: Major - P3
Fix Version/s: None
Affects Version/s: 6.0.5
Component/s: None
Labels:
None

Operating System:
ALL

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

Details

Description

Attachments

Activity

People

Dates