My queries are using available indexes differently in stage vs prod environments

Working with Data
1

Hi All,

I have two environments STAGE and PROD and the indexes in both environments are same, But while firing a query the indexes are not loading diffrently , due to that facing severe issue. Could any tell me what to do in this case.

Thanks

Eswar

2

Hi @eswar_sunny you’ll need to provide more details for us to help.

What do you mean the indexes are not loading differently? How are you verifying this?

If you’re seeing different indexes being used for the same query in STAGE and PROD, then you could have similar indexes that can be used for a given query. I would suggest auditing your indexes to make sure you only have the indexes necessary for the queries you run. Note that if there is a difference in amount of data and a difference in the data distribution between your STAGE and PROD systems different indexes may be used which could lead to slow queries.

What is the severe issue you’re facing. Read queries are slow? Write queries are slow? Without this piece of information anything would be a guess. Trying to apply any type of suggestion at this point could make things worse.

Other things we would need to know is what is the actual query/queries that are having problems. What index is being used and what index do you expect to be used.

3

Hi Doug,

Thanks for your response.
As my indexes are loading differently my read queries are getting socket timeout.
And also in stage if i execute the query and observe my query planner it is scanning on one index and the same query if i execute on prod and observe query planner it is scanning diffrent index.
Note: indexes are same in both prod and stage

Thanks
Eswar

4

I am using below aggregates, Could you please suggest what are the compound indexes that i can create

{
$match: {"crOn":{
    '$gte': ISODate("2019-12-10 15:33:47.000Z"),
    '$lt': ISODate("2019-12-20 15:33:47.000Z")
} }
},
{
$match: { $and: [{ $or: [
  { 
    $and : [ 
             {"quote.quoteTeam" : { "$elemMatch": {
"Id": { $in:[1,2]},
"role": { $in:["A",
 "B",
 "C"]},
"active":1
}},
              "type" : { $in:[1,2,3,5,7]}
                                }
                         
                              ]
                   },
{ 
    $and : [ 
             {"quote.partnerExtn.ptnrrBeGeoId":{ $in:[1,2]},
             "type" : { $in:[3,5,7]}
              
                                }
                         
                              ]
                   }

 
] }
]}
},
// Dynamic FIlter for status
  {
   $match: {"quote.qteStatus":{ $nin:[-1,17]} }
   },

Thanks
Eswar

5

What are the indexes presently you have created on the collection? Which ones are being utilized in prod and in the stage, respectively (as per the query plan)?

In general, aggregation queries utilize indexes in $match stage, when the stage occurs early (definitely, as first stage) in the pipeline. Your aggregation query has the $match stage; is the stage at the start of the pipeline or down? This detail is required for study.

Also, a sample structure of the document will help study the query; consider posting one.