0

My query with $and, $or operators is performing poorly. Even though I've built a compound index on all three fields that are specified in my query criteria, the query does a complete collection scan. I have tried both simple find and aggregation and the Winning plan for both is COLLSCAN.

Simple find

Query:

db.test.find({ $or: [ {id1: "900004"}, {relatedid: "900004"}], $and: [ { isValid: "Y" } ] } )

Execution stats:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "XXX",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "$or" : [ 
                        {
                            "Id1" : {
                                "$eq" : "900004"
                            }
                        }, 
                        {
                            "relatedID" : {
                                "$eq" : "900004"
                            }
                        }
                    ]
                }, 
                {
                    "isValid" : {
                        "$eq" : "Y"
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "$and" : [ 
                    {
                        "$or" : [ 
                            {
                                "Id1" : {
                                    "$eq" : "900004"
                                }
                            }, 
                            {
                                "relatedID" : {
                                    "$eq" : "900004"
                                }
                            }
                        ]
                    }, 
                    {
                        "isValid" : {
                            "$eq" : "Y"
                        }
                    }
                ]
            },
            "direction" : "forward"
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 61,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 100006,
        "executionStages" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "$and" : [ 
                    {
                        "$or" : [ 
                            {
                                "Id1" : {
                                    "$eq" : "900004"
                                }
                            }, 
                            {
                                "relatedID" : {
                                    "$eq" : "900004"
                                }
                            }
                        ]
                    }, 
                    {
                        "isValid" : {
                            "$eq" : "Y"
                        }
                    }
                ]
            },
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 60,
            "works" : 100008,
            "advanced" : 1,
            "needTime" : 100006,
            "needYield" : 0,
            "saveState" : 781,
            "restoreState" : 781,
            "isEOF" : 1,
            "invalidates" : 0,
            "direction" : "forward",
            "docsExamined" : 100006
        }
    },
    "serverInfo" : {
        "host" : "XXXX",
        "port" : ,
        "version" : "4.0.9",
        "gitVersion" : "XXX"
    },
    "ok" : 1.0
}

Aggregation

Query:

db.test.aggregate( [{ "$match":{ "$and": [ {"isValid": "Y"}, { "$or": [ {"Id1": "900004"}, {"relatedID": "900004"} ]}] }} ] )

Execution Stats:

{
    "stages" : [ 
        {
            "$cursor" : {
                "query" : {
                    "$and" : [ 
                        {
                            "isValid" : "Y"
                        }, 
                        {
                            "$or" : [ 
                                {
                                    "Id1" : "900004"
                                }, 
                                {
                                    "relatedID" : "900004"
                                }
                            ]
                        }
                    ]
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "XXXXX",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "$and" : [ 
                            {
                                "$or" : [ 
                                    {
                                        "Id1" : {
                                            "$eq" : "900004"
                                        }
                                    }, 
                                    {
                                        "relatedID" : {
                                            "$eq" : "900004"
                                        }
                                    }
                                ]
                            }, 
                            {
                                "isValid" : {
                                    "$eq" : "Y"
                                }
                            }
                        ]
                    },
                    "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                            "$and" : [ 
                                {
                                    "$or" : [ 
                                        {
                                            "Id" : {
                                                "$eq" : "900004"
                                            }
                                        }, 
                                        {
                                            "relatedID" : {
                                                "$eq" : "900004"
                                            }
                                        }
                                    ]
                                }, 
                                {
                                    "isValid" : {
                                        "$eq" : "Y"
                                    }
                                }
                            ]
                        },
                        "direction" : "forward"
                    },
                    "rejectedPlans" : []
                }
            }
        }
    ],
    "ok" : 1.0
}

How can I get a fast a query for my results?

  • This has been rectified and the query has been tuned. Below were the issues with the query 1. $or operator being used to search values in multiple fields 2. $and operator search value was on all the documents in the collection so in this case, it has to do complete collection scan I built text index on all fields text fields in the collection: db.ctmMappings.createIndex({"$**":"text"}) Then the below query worked just fine db.test.find({ "$text": {"$search": "900004"}}) This query worked more efficiently than the previous ones – Anban Jun 7 at 5:37
  • Update your comments in the answer and accept it as an answer. – Mani Jun 7 at 21:58
0

This has been rectified and the query has been tuned. Below were the issues with the query

  1. $or operator being used to search values in multiple fields, which was not really efficient

  2. $and operator search value was on all the documents in the collection so in this case, it has to do complete collection scan. Inefficient field chosen as index!

Fix applied:

I built text index on all fields text fields in the collection:

db.ctmMappings.createIndex({"$**":"text"})

Then reconstructed the query to below:

db.test.find({ "$text": {"$search": "900004"}})

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.