2

I have a query that seems to never return. When I run explain on that query, it shows me executionStats.executionTimeMillis of 27ms, and that the initial input-stage is IXSCAN that should return 4 objects only. I've confirmed that querying for the input-stage query returns only 4 results.

This is my query:

{"$or":[
    {"field1.key":{"$in":["name1","name2",/^prefix.*suffix$/]},"field2.key":"foobar"},
    {"field1.key":{"$in":["name1","name2",/^prefix.*suffix$/]},"field3.key":"foobar"}
]}

This is the explain({ verbose : "executionStats" }) output (sorry for the long paste):

{
    "queryPlanner" : {
        "mongosPlannerVersion" : 1,
        "winningPlan" : {
            "stage" : "SHARD_MERGE",
            "shards" : [ 
                {
                    "shardName" : "...",
                    "plannerVersion" : 1,
                    "indexFilterSet" : false,
                    "parsedQuery" : { ... },
                    "winningPlan" : {
                        "stage" : "SUBPLAN",
                        "inputStage" : {
                            "stage" : "OR",
                            "inputStages" : [ 
                                {
                                    "stage" : "FETCH",
                                    "filter" : {"field1.key":{"$in":["name1","name2",/^prefix.*suffix$/]},
                                    "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : { "field3.key" : 1.0 },
                                        "indexName" : "field3.key_1",
                                        "isMultiKey" : true,
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 1,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "field3.key" : [ "[\"foobar\", \"foobar\"]" ]
                                        }
                                    }
                                }, 
                                {
                                    "stage" : "FETCH",
                                    "filter" : {"field1.key":{"$in":["name1","name2",/^prefix.*suffix$/]},
                                    "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : { "field2.key" : 1.0 },
                                        "indexName" : "field2.key_1",
                                        "isMultiKey" : true,
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 1,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "field2.key" : [ "[\"foobar\", \"foobar\"]" ]
                                        }
                                    }
                                }
                            ]
                        }
                    },
                    "rejectedPlans" : []
                }, 
                ...
                // same plan for the 3 other shards
                ...
            ]
        }
    },
    "executionStats" : {
        "nReturned" : 0,
        "executionTimeMillis" : 27,
        "totalKeysExamined" : 4,
        "totalDocsExamined" : 4,
        "executionStages" : {
            "stage" : "SHARD_MERGE",
            "nReturned" : 0,
            "executionTimeMillis" : 27,
            "totalKeysExamined" : 4,
            "totalDocsExamined" : 4,
            "totalChildMillis" : NumberLong(63),
            ...
            // execution times for each shard
            ...
        },
        "allPlansExecution" : []
    },
    "ok" : 1.0
}

UPDATE

It seems that despite explain mentioning it uses "field2.key" for the first part of the $or and "field3.key" for the second part of the $or, when looking at db.currentOp().inprog it shows: "planSummary": "IXSCAN { field1.key: 1.0 }, IXSCAN { field3.key: 1.0 }" so it selected the wrong index for one of the $or parts, and thus making the query scan a huge number of documents.

  1. Any idea why explain gets the indexes right, but the query itself doesn't?
  2. How can we hint mongo to use the correct indexes, when using $or?
marmor
  • 27,641
  • 11
  • 107
  • 150
  • 1
    Do you see the query in `db.currentOp().inprog`? What does the `planSummary` field contain in the relevant op object there? – Meni May 09 '17 at 10:59
  • @Meni thanks! this helped me to better understand the issue – marmor May 09 '17 at 12:13
  • Could you find a solution? I think I've got the same problem here. The explain query returns almost instantly and the `winningPlan` is the correct index, but the query takes for ever to execute. The `db.currentOp().inprog` does not have the `planSummary` attribute. – Renan Ferreira Jul 25 '19 at 20:04

0 Answers0