Azure CosmosDB for MongoDB not sorting using _id index? [closed]

Question

Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers.

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed last month.

Improve this question

Trying to figure this out, running a simple query like this

.find()
  .sort({ _id: 1 })
  .limit(100)
  .hint({ _id: 1 }).explain("executionStats");

And it's doing a full scan everytime

      executionTimeMillis: 329.892,
      executionStartAtTimeMillis: 0.038,
      totalDocsExamined: 204773,
      totalKeysExamined: 204773,

But when I run against a real mongo instance it uses the _id index to sort

executionTimeMillis: 0,
totalKeysExamined: 100,
totalDocsExamined: 100,

My collection in azure is not sharded or anything like that, is this just a limitation of the Cosmos Mongo emulation or is something up with my indexes. If so is there any documentation explaining that?

Edit: Here is the full explain results, I've tried with and without a filter just to see if it made a difference

{
  explainVersion: 2,
  command: "db.runCommand({explain: { 'find' : 'Collection', 'filter' : { }, 'sort' : { '_id' : 1 }, 'limit' : 100 }})",
  explainCommandPlanningTimeMillis: 0.198,
  explainCommandExecTimeMillis: 222.434,
  queryPlanner: {
    namespace: 'Database.Collection',
    winningPlan: {
      stage: 'LIMIT',
      estimatedTotalKeysExamined: 100,
      inputStage: {
        stage: 'COLLSCAN',
        estimatedTotalKeysExamined: 170402,
        inputStage: {
          stage: 'SORT',
          sortKeysCount: 1,
          estimatedTotalKeysExamined: 85201,
          inputStage: {
            stage: 'COLLSCAN',
            estimatedTotalKeysExamined: 85201
          }
        }
      }
    }
  },
  executionStats: {
    nReturned: 100,
    executionTimeMillis: 222.376,
    executionStartAtTimeMillis: 192.863,
    totalDocsExamined: 100,
    totalKeysExamined: 100,
    executionStages: {
      stage: 'LIMIT',
      nReturned: 100,
      executionTimeMillis: 222.376,
      executionStartAtTimeMillis: 192.863,
      totalDocsExamined: 100,
      totalKeysExamined: 100,
      numBlocksFromCache: 29381,
      numBlocksFromDisk: 11142,
      inputStage: {
        stage: 'COLLSCAN',
        nReturned: 100,
        executionTimeMillis: 222.365,
        executionStartAtTimeMillis: 192.861,
        totalDocsExamined: 100,
        totalKeysExamined: 100,
        numBlocksFromCache: 29381,
        numBlocksFromDisk: 11142,
        parallelWorkers: 2,
        inputStage: {
          stage: 'SORT',
          nReturned: 70,
          executionTimeMillis: 171.279,
          executionStartAtTimeMillis: 171.262,
          totalDocsExamined: 70,
          totalKeysExamined: 70,
          sortMethod: 'top-N heapsort',
          totalDataSizeSortedBytesEstimate: 189,
          numBlocksFromCache: 29381,
          numBlocksFromDisk: 11142,
          inputStage: {
            stage: 'COLLSCAN',
            nReturned: 68258,
            executionTimeMillis: 121.96000000000001,
            executionStartAtTimeMillis: 0.056,
            totalDocsExamined: 68258,
            totalKeysExamined: 68258,
            numBlocksFromCache: 29289,
            numBlocksFromDisk: 11142
          }
        }
      }
    }
  },
  ok: 1
}

it uses the index fine for me. What is the full output of the explain? (with anything senstive redacted?) — Martin Smith
– Martin Smith, Commented Oct 2 at 18:26
Are you using RU model or VCore? I am looking at RU model and see quite different results — Martin Smith
– Martin Smith, Commented Oct 2 at 18:59
Ah, I'm out then. I haven't used that. I don't see the issue on a collection provisioned with the RU approach but the back end is entirely different AFAIK — Martin Smith
– Martin Smith, Commented Oct 2 at 19:03

Nick · Accepted Answer · 2025-10-02 19:09:24Z

2

I think I may have found the culprit.

https://learn.microsoft.com/en-us/azure/cosmos-db/mongodb/vcore/limits

The relevant bit:

Sorting is done in memory and doesn't push down to the index.

answered Oct 2 at 19:09

Nick

9,2334 gold badges28 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Azure CosmosDB for MongoDB not sorting using _id index? [closed]

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related