$in queries do not use clustered index

XMLWordPrintableJSON

    • Query Optimization
    • Fully Compatible
    • v6.3, v6.0, v5.0
    • QO 2023-03-20, QO 2023-04-03
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None

      A query that uses $in on _id does not plan against a clustered index if one exists. If no suitable secondary index exists, we fall back to a collection scan.

      This affects queries directly on time-series buckets collections.

      test> db.createCollection('clustered', {clusteredIndex: {key: {_id: 1}, unique: true}})
test> db.clustered.find({_id: {$in: [0,1,2,3]}}).explain()
{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'test.clustered',
    indexFilterSet: false,
    parsedQuery: { _id: { '$in': [ 0, 1, 2, 3 ] } },
    queryHash: '0D7870DC',
    planCacheKey: '0D7870DC',
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'COLLSCAN',
      filter: { _id: { '$in': [ 0, 1, 2, 3 ] } },
      direction: 'forward'
    },
    rejectedPlans: []
  },

            Assignee:
            Hana Pearlman
            Reporter:
            Louis Williams
            Votes:
            2 Vote for this issue
            Watchers:
            22 Start watching this issue

              Created:
              Updated:
              Resolved: