Slow query. Replicaset

We are using a mongoDB replicaset and facing the issue about slow query. Actually if i run below query on terminal or Compass, query is very fast. But if the query comes from application, so slow.
I set profiling level to 1 and slowms=100 and i saw the query and query was so slow because missing index.
And i create a compound index, so our query got so fast like 150ms.
When I fill in the relevant blanks from the application and search, it keeps me waiting for at least 10 minutes.

db.historydata.find({“topic.sistemID”:2, “topic.header.MesajALan”: 177, “topic.header.MesajId”:1071, “timestamp”:{$lte: 1684334263}}).sort({-1}).limit(1)

Then I came across something like this in the logs.

“replanReason”:“cached plan was less efficient than expected: expected trial execution to take 2 works but it took at least 20 works”,“cursorExhausted”:true,

do you have any idea?

  1. Can you please show us the explain plan from the query when it is slow? This will give us an idea of what is happening in the query. You can append .explain(“executionStats”)
  2. What was the index that you created? Did you follow the The ESR (Equality, Sort, Range) Rule?

Hi @tapiocaPENGUIN

i analyzed it wrong i’m sorry. Query is using $bitsAllSet. When i search on the internet, i found this comment

Queries cannot use indexes for the $bitsAllSet portion of a query, although the other portions of a query can use indexes, if applicable.

I create a new compound index that doesn’t use that column. So my problem is solved.

Thank you very much. Sorry for westing your time :frowning:

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.