0

Below is the query that I'm using. 

{
    $match: {
      $or: [
        { a: 1, b: 'P', c: null },
        { a: 1, b: 'R', c: 'P' },
        { a: 0, b: 'P', c: null },
        { a: 0, b: 'R', c: 'Q' },
      ],
    },
}

Below is the index that I'm currently using

{
    "a" : 1,
    "b" : 1,
    "c" : 1,
    "createdAt" : 1
}

I've tried all indexes. I get below result when I do explain("ExecutionStats") even though I put limit(10), it examines all keys.

"executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 27040,
                    "executionTimeMillis" : 3123,
                    "totalKeysExamined" : 27042,
                    "totalDocsExamined" : 53791,

}

I'm sorting on createdAt at the end of the query. I've also created an index on createdAt but still no success.

Viral Raval
  • 13
  • 1
  • 5
  • Do you have anything else in your `$match` stage? I've inserted 10 records, indexed and when execute `.explain` it applies correctly indexes. – Valijon May 25 '20 at 10:53
  • How many results do you expect. If there are really 27040 results then it will take time to collect and deliver those results. 3 seconds may be a reasonable response time depending on your cluster and hardware configuration. – Joe Drumgoole May 25 '20 at 11:05
  • No, there is nothing else in $match stage. When I do explain("executionStats") it shows that an index scan was performed, but what it is scanning so many indexes. There are 27k documents in a collection but as you can see in executionStats it examined 53k documents. – Viral Raval May 25 '20 at 11:06
  • `limit` without sort isn't really meaningful. If you have a sort stage that isnt handled implicitly by the index, all documents will need to be read into memory for sorting. The explain plan should indicate what is happening with each branch of the `$or`. Can we see the entire explain output? – Joe May 25 '20 at 22:14

0 Answers0