Query taking long time to run

I have my collection with less than 10.000 documents and Im running the following query:

Sale.aggregate([
            {
                $match: {
                    seller,
                    createdAt: {
                        $gte: finalUnix
                    }
                }
            }
        ])

Seller and createdAt are string and they are already indexes of the collection. This simple query is taking more than 10 seconds to be executed. I have less than 10.000 documents and this query returns less than 1.000 documents.

Why is it taking so long?

Running a query only with $match (matching a string, index too) takes more than 10 seconds in MongoDB Compass too.

@foco_radiante, you can tell the details of the indexes you have created on the collection.

You can run the explain() method to generate a Query Plan for the aggregation query. The plan will show details about the index(es) used for the query.

{
   "explainVersion":"1",
   "queryPlanner":{
      "namespace":"test.testsales",
      "indexFilterSet":false,
      "parsedQuery":{
         "$and":[
            {
               "contract":{
                  "$eq":"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7"
               }
            },
            {
               "createdAt":{
                  "$gte":"1654100284"
               }
            }
         ]
      },
      "maxIndexedOrSolutionsReached":false,
      "maxIndexedAndSolutionsReached":false,
      "maxScansToExplodeReached":false,
      "winningPlan":{
         "stage":"FETCH",
         "filter":{
            "createdAt":{
               "$gte":"1654100284"
            }
         },
         "inputStage":{
            "stage":"IXSCAN",
            "keyPattern":{
               "contract":1
            },
            "indexName":"contract_1",
            "isMultiKey":false,
            "multiKeyPaths":{
               "contract":[
                  
               ]
            },
            "isUnique":false,
            "isSparse":false,
            "isPartial":false,
            "indexVersion":2,
            "direction":"forward",
            "indexBounds":{
               "contract":[
                  "[\"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7\", \"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7\"]"
               ]
            }
         }
      },
      "rejectedPlans":[
         {
            "stage":"FETCH",
            "filter":{
               "contract":{
                  "$eq":"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7"
               }
            },
            "inputStage":{
               "stage":"IXSCAN",
               "keyPattern":{
                  "createdAt":1
               },
               "indexName":"createdAt_1",
               "isMultiKey":false,
               "multiKeyPaths":{
                  "createdAt":[
                     
                  ]
               },
               "isUnique":false,
               "isSparse":false,
               "isPartial":false,
               "indexVersion":2,
               "direction":"forward",
               "indexBounds":{
                  "createdAt":[
                     "[\"1654100284\", {})"
                  ]
               }
            }
         },
         {
            "stage":"FETCH",
            "inputStage":{
               "stage":"IXSCAN",
               "keyPattern":{
                  "contract":1,
                  "createdAt":1
               },
               "indexName":"contract_1_createdAt_1",
               "isMultiKey":false,
               "multiKeyPaths":{
                  "contract":[
                     
                  ],
                  "createdAt":[
                     
                  ]
               },
               "isUnique":false,
               "isSparse":false,
               "isPartial":false,
               "indexVersion":2,
               "direction":"forward",
               "indexBounds":{
                  "contract":[
                     "[\"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7\", \"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7\"]"
                  ],
                  "createdAt":[
                     "[\"1654100284\", {})"
                  ]
               }
            }
         }
      ]
   },
   "executionStats":{
      "executionSuccess":true,
      "nReturned":270,
      "executionTimeMillis":1,
      "totalKeysExamined":270,
      "totalDocsExamined":270,
      "executionStages":{
         "stage":"FETCH",
         "filter":{
            "createdAt":{
               "$gte":"1654100284"
            }
         },
         "nReturned":270,
         "executionTimeMillisEstimate":1,
         "works":271,
         "advanced":270,
         "needTime":0,
         "needYield":0,
         "saveState":0,
         "restoreState":0,
         "isEOF":1,
         "docsExamined":270,
         "alreadyHasObj":0,
         "inputStage":{
            "stage":"IXSCAN",
            "nReturned":270,
            "executionTimeMillisEstimate":0,
            "works":271,
            "advanced":270,
            "needTime":0,
            "needYield":0,
            "saveState":0,
            "restoreState":0,
            "isEOF":1,
            "keyPattern":{
               "contract":1
            },
            "indexName":"contract_1",
            "isMultiKey":false,
            "multiKeyPaths":{
               "contract":[
                  
               ]
            },
            "isUnique":false,
            "isSparse":false,
            "isPartial":false,
            "indexVersion":2,
            "direction":"forward",
            "indexBounds":{
               "contract":[
                  "[\"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7\", \"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7\"]"
               ]
            },
            "keysExamined":270,
            "seeks":1,
            "dupsTested":0,
            "dupsDropped":0
         }
      },
      "allPlansExecution":[
         {
            "nReturned":101,
            "executionTimeMillisEstimate":1,
            "totalKeysExamined":101,
            "totalDocsExamined":101,
            "executionStages":{
               "stage":"FETCH",
               "filter":{
                  "createdAt":{
                     "$gte":"1654100284"
                  }
               },
               "nReturned":101,
               "executionTimeMillisEstimate":1,
               "works":101,
               "advanced":101,
               "needTime":0,
               "needYield":0,
               "saveState":0,
               "restoreState":0,
               "isEOF":0,
               "docsExamined":101,
               "alreadyHasObj":0,
               "inputStage":{
                  "stage":"IXSCAN",
                  "nReturned":101,
                  "executionTimeMillisEstimate":0,
                  "works":101,
                  "advanced":101,
                  "needTime":0,
                  "needYield":0,
                  "saveState":0,
                  "restoreState":0,
                  "isEOF":0,
                  "keyPattern":{
                     "contract":1
                  },
                  "indexName":"contract_1",
                  "isMultiKey":false,
                  "multiKeyPaths":{
                     "contract":[
                        
                     ]
                  },
                  "isUnique":false,
                  "isSparse":false,
                  "isPartial":false,
                  "indexVersion":2,
                  "direction":"forward",
                  "indexBounds":{
                     "contract":[
                        "[\"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7\", \"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7\"]"
                     ]
                  },
                  "keysExamined":101,
                  "seeks":1,
                  "dupsTested":0,
                  "dupsDropped":0
               }
            }
         },
         {
            "nReturned":1,
            "executionTimeMillisEstimate":0,
            "totalKeysExamined":101,
            "totalDocsExamined":101,
            "executionStages":{
               "stage":"FETCH",
               "filter":{
                  "contract":{
                     "$eq":"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7"
                  }
               },
               "nReturned":1,
               "executionTimeMillisEstimate":0,
               "works":101,
               "advanced":1,
               "needTime":100,
               "needYield":0,
               "saveState":0,
               "restoreState":0,
               "isEOF":0,
               "docsExamined":101,
               "alreadyHasObj":0,
               "inputStage":{
                  "stage":"IXSCAN",
                  "nReturned":101,
                  "executionTimeMillisEstimate":0,
                  "works":101,
                  "advanced":101,
                  "needTime":0,
                  "needYield":0,
                  "saveState":0,
                  "restoreState":0,
                  "isEOF":0,
                  "keyPattern":{
                     "createdAt":1
                  },
                  "indexName":"createdAt_1",
                  "isMultiKey":false,
                  "multiKeyPaths":{
                     "createdAt":[
                        
                     ]
                  },
                  "isUnique":false,
                  "isSparse":false,
                  "isPartial":false,
                  "indexVersion":2,
                  "direction":"forward",
                  "indexBounds":{
                     "createdAt":[
                        "[\"1654100284\", {})"
                     ]
                  },
                  "keysExamined":101,
                  "seeks":1,
                  "dupsTested":0,
                  "dupsDropped":0
               }
            }
         },
         {
            "nReturned":101,
            "executionTimeMillisEstimate":0,
            "totalKeysExamined":101,
            "totalDocsExamined":101,
            "executionStages":{
               "stage":"FETCH",
               "nReturned":101,
               "executionTimeMillisEstimate":0,
               "works":101,
               "advanced":101,
               "needTime":0,
               "needYield":0,
               "saveState":0,
               "restoreState":0,
               "isEOF":0,
               "docsExamined":101,
               "alreadyHasObj":0,
               "inputStage":{
                  "stage":"IXSCAN",
                  "nReturned":101,
                  "executionTimeMillisEstimate":0,
                  "works":101,
                  "advanced":101,
                  "needTime":0,
                  "needYield":0,
                  "saveState":0,
                  "restoreState":0,
                  "isEOF":0,
                  "keyPattern":{
                     "contract":1,
                     "createdAt":1
                  },
                  "indexName":"contract_1_createdAt_1",
                  "isMultiKey":false,
                  "multiKeyPaths":{
                     "contract":[
                        
                     ],
                     "createdAt":[
                        
                     ]
                  },
                  "isUnique":false,
                  "isSparse":false,
                  "isPartial":false,
                  "indexVersion":2,
                  "direction":"forward",
                  "indexBounds":{
                     "contract":[
                        "[\"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7\", \"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7\"]"
                     ],
                     "createdAt":[
                        "[\"1654100284\", {})"
                     ]
                  },
                  "keysExamined":101,
                  "seeks":1,
                  "dupsTested":0,
                  "dupsDropped":0
               }
            }
         }
      ]
   },
   "command":{
      "find":"testsales",
      "filter":{
         "contract":"0x248139afb8d3a2e16154fbe4fb528a3a214fd8e7",
         "createdAt":{
            "$gte":"1654100284"
         }
      },
      "projection":{
         
      },
      "$db":"test"
   },
   "serverParameters":{
      "internalQueryFacetBufferSizeBytes":104857600,
      "internalQueryFacetMaxOutputDocSizeBytes":104857600,
      "internalLookupStageIntermediateDocumentMaxSizeBytes":16793600,
      "internalDocumentSourceGroupMaxMemoryBytes":104857600,
      "internalQueryMaxBlockingSortMemoryUsageBytes":33554432,
      "internalQueryProhibitBlockingMergeOnMongoS":0,
      "internalQueryMaxAddToSetBytes":104857600,
      "internalDocumentSourceSetWindowFieldsMaxMemoryBytes":104857600
   },
   "ok":1,
   "$clusterTime":{
      "clusterTime":{
         "$timestamp":"7105048794433060909"
      },
      "signature":{
         "hash":"JMRPzVCd1dQHuvNBaZ4AI2Tm2Q0=",
         "keyId":{
            "low":3,
            "high":1645032659,
            "unsigned":false
         }
      }
   },
   "operationTime":{
      "$timestamp":"7105048794433060909"
   }
}

You see it is scanning 270 docs, but its taking like 10 seconds to return me the results.

The "winningPlan" has a "stage":"IXSCAN" and is applying the "indexName":"contract_1". And the execution time is shown here:

   "executionStats":{
      "executionSuccess":true,
      "nReturned":270,
      "executionTimeMillis":1,

It looks like you have two indexes (see below), and you dont need those two. You can delete the "contract_1" index and try your query again:

"indexName":"contract_1_createdAt_1",
"indexName":"contract_1",

Note that for the query to apply the index, you need to use the indexed fields in your query filter - in this case the contract and the createdAt.

See the topic on Prefixes to understand why you don’t need to those two indexes:

2 Likes

What if I have one query using one index and other query using other index?

I mean, I have one query using the contract index and other query using the createdAt index.

As mentioned

You do not have

You have a compoud index with createdAt as a secondary key.

1 Like