0

We have a mongo collection with about 10 million records. There are two indices: expires_date and processing_state. One of the queries accesses data between two expires_date (gte and lte) and processing_state = 'PROCESSED'.

The explain plan for the query shows two plans, one with an index scan on expires_date and the other on the processing_state, but the winning plan is shown to be expires_date.

When we run the query, it takes a lot of time (almost forever) but if we remove the processing_state field it completes almost instantaneously.

Also, the processing_state field has almost all the 10 million records as PROCESSED. Only a handful of records can be in PENDING or FAILED state. If we specify the processing_state other than PROCESSED query runs fast.

This behaviour is consistent even if the provided date range has 0 records.

From, the behaviour it seems the query is doing an index scan on processing_state instead of expires_date, which is againt what explain plan suggests. Any reasons why it should happen.

mickeymoon
  • 4,820
  • 5
  • 31
  • 56
  • It helps if you post your queries too. And, run the `db.collection.getIndexes()` command and list the indexes (other than the `_id`). – prasad_ May 21 '20 at 05:04
  • Please post the the results of `.explain("executionStats")`, a sample document and the query. – Joe Drumgoole May 21 '20 at 09:04

0 Answers0