01. Query Planner Logic

https://www.mongodb.com/docs/manual/core/query-plans/

mongoDB query planner는 사용 가능한 인덱스가 있는 경우 가장 효율적인 쿼리 계획을 선택하고 캐시함.

1. 느린 응답
   • query → disk → cache → return
2. 빠른 응답
   • query → cache → return

02. Query Plan 읽는 방법

https://www.mongodb.com/docs/manual/reference/method/cursor.explain/

cursor.explain()

**# use sample_restaurants
# db.restaurants.find(
		{borough: "Brooklyn"}
).explain('executionStats')**
{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'sample_restaurants.restaurants',
    indexFilterSet: false,
    parsedQuery: { borough: { '$eq': 'Brooklyn' } },
    queryHash: 'A1445303',
    planCacheKey: 'A1445303',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'COLLSCAN',
      filter: { borough: { '$eq': 'Brooklyn' } },
      direction: 'forward'
    },
    rejectedPlans: []
  },
  **executionStats**: {
    executionSuccess: true,
    nReturned: 6086,
    executionTimeMillis: 21,
    totalKeysExamined: 0,
    totalDocsExamined: 25359,
    executionStages: {
      stage: 'COLLSCAN',
      filter: { borough: { '$eq': 'Brooklyn' } },
      nReturned: 6086,
      executionTimeMillisEstimate: 1,
      works: 25361,
      advanced: 6086,
      needTime: 19274,
      needYield: 0,
      saveState: 25,
      restoreState: 25,
      isEOF: 1,
      direction: 'forward',
      docsExamined: 25359
    }
  },
  command: {
    find: 'restaurants',
    filter: { borough: 'Brooklyn' },
    '$db': 'sample_restaurants'
  },
  serverInfo: {
    host: 'ac-97tz3ja-shard-00-02.eumcwhs.mongodb.net',
    port: 27017,
    version: '6.0.5',
    gitVersion: 'c9a99c120371d4d4c52cbb15dac34a36ce8d3b1d'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 16793600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 33554432,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp({ t: 1682922695, i: 3 }),
    signature: {
      hash: Binary(Buffer.from("c018a35f5ae17ec7e843fa015709f8e41fd2bfa4", "hex"), 0),
      keyId: Long("7187814231566188546")
    }
  },
  operationTime: Timestamp({ t: 1682922695, i: 3 })
}

**# db.restaurants.find(
		{borough: "Brooklyn"},
		{name: 1, borough: 1}
).sort({name: 1}).explain('executionStats')**
{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'sample_restaurants.restaurants',
    indexFilterSet: false,
    parsedQuery: { borough: { '$eq': 'Brooklyn' } },
    queryHash: '803B6E0A',
    planCacheKey: '803B6E0A',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'SORT',
      sortPattern: { name: 1 },
      memLimit: 33554432,
      type: 'simple',
      inputStage: {
        stage: 'PROJECTION_SIMPLE',
        transformBy: { name: 1, borough: 1 },
        inputStage: {
          stage: 'COLLSCAN',
          filter: { borough: { '$eq': 'Brooklyn' } },
          direction: 'forward'
        }
      }
    },
    rejectedPlans: []
  },
  **executionStats**: {
    executionSuccess: true,
    **nReturned: 6086,**
    executionTimeMillis: 54,
    totalKeysExamined: 0,
    **totalDocsExamined: 25359,**
    executionStages: {
      stage: '**SORT**',
      nReturned: 6086,
      executionTimeMillisEstimate: 25,
      works: 31448,
      advanced: 6086,
      needTime: 25361,
      needYield: 0,
      saveState: 31,
      restoreState: 31,
      isEOF: 1,
      sortPattern: { name: 1 },
      memLimit: 33554432,
      type: 'simple',
      totalDataSizeSorted: 778083,
      usedDisk: false,
      spills: 0,
      inputStage: {
        stage: '**PROJECTION_SIMPLE**',
        nReturned: 6086,
        executionTimeMillisEstimate: 4,
        works: 25361,
        advanced: 6086,
        needTime: 19274,
        needYield: 0,
        saveState: 31,
        restoreState: 31,
        isEOF: 1,
        transformBy: { name: 1, borough: 1 },
        inputStage: {
          stage: '**COLLSCAN**',
          filter: { borough: { '$eq': 'Brooklyn' } },
          nReturned: 6086,
          executionTimeMillisEstimate: 3,
          works: 25361,
          advanced: 6086,
          needTime: 19274,
          needYield: 0,
          saveState: 31,
          restoreState: 31,
          isEOF: 1,
          direction: 'forward',
          docsExamined: 25359
        }
      }
    }
  },
	...
  operationTime: Timestamp({ t: 1682923408, i: 16 })
}

totalDocsExamined: 25359, nReturned: 6086

인덱스 생성 후 조회