Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-29326

count query with null criterion can't be covered by non-sparse index

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.2.6, 3.2.12
    • Component/s: Querying
    • None
    • ALL
    • Hide
      • created the non-sparse index _ {shopId:1, missingSince:1}

        _

        db.offer.createIndex({shopId:1, missingSince:1})
      • insert the following documents:
        db.offer.insert({ "_id" : 1, "v" : 1 });
        db.offer.insert({ "_id" : 2, "v" : 1 });
        db.offer.insert({ "_id" : 3, "v" : 1 });
        db.offer.insert({ "_id" : 4, "shopId" : 1, "v" : 1 });
        db.offer.insert({ "_id" : 5, "shopId" : 1, "v" : 1 });
        db.offer.insert({ "_id" : 6, "shopId" : 1, "v" : 1 });
        db.offer.insert({ "_id" : 7, "shopId" : 1, "v" : 1 });
        db.offer.insert({ "_id" : 8, "shopId" : 1, "v" : 1 });
        db.offer.insert({ "_id" : 9, "shopId" : 1, "missingSince" : null, "v" : 1 });
        db.offer.insert({ "_id" : 10, "shopId" : 1, "missingSince" : null, "v" : 1 });
        db.offer.insert({ "_id" : 11, "shopId" : 1, "missingSince" : null, "v" : 1 });
        db.offer.insert({ "_id" : 12, "shopId" : 1, "missingSince" : null, "v" : 1 });
        db.offer.insert({ "_id" : 13, "shopId" : 1, "missingSince" : null, "v" : 1 });
        db.offer.insert({ "_id" : 14, "shopId" : 1, "missingSince" : null, "v" : 1 });
        db.offer.insert({ "_id" : 15, "shopId" : 1, "missingSince" : null, "v" : 1 });
        db.offer.insert({ "_id" : 16, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 17, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 18, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 19, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 20, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 21, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 22, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 23, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 24, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 25, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 26, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 27, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
        db.offer.insert({ "_id" : 28, "shopId" : 1, "missingSince" : ISODate("2017-05-22T07:52:40.831Z"), "v" : 1 });
      • execute explain(true) for the following query:
        db.offer.explain(true).count({"shopId":1, "missingSince":null})
      • the execution stats shows that 12 documents needed to be examined, meaning that the query was not covered by the index
        {
            "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.offer",
                "indexFilterSet" : false,
                "parsedQuery" : {
                    "$and" : [
                        {
                            "missingSince" : {
                                "$eq" : null
                            }
                        },
                        {
                            "shopId" : {
                                "$eq" : 1
                            }
                        }
                    ]
                },
                "winningPlan" : {
                    "stage" : "COUNT",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "missingSince" : {
                                "$eq" : null
                            }
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "shopId" : 1,
                                "missingSince" : 1
                            },
                            "indexName" : "shopId_1_missingSince_1",
                            "isMultiKey" : false,
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "shopId" : [
                                    "[1.0, 1.0]"
                                ],
                                "missingSince" : [
                                    "[null, null]"
                                ]
                            }
                        }
                    }
                },
                "rejectedPlans" : [ ]
            },
            "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 0,
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 12,
                "totalDocsExamined" : 12,
                "executionStages" : {
                    "stage" : "COUNT",
                    "nReturned" : 0,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 13,
                    "advanced" : 0,
                    "needTime" : 12,
                    "needYield" : 0,
                    "saveState" : 0,
                    "restoreState" : 0,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "nCounted" : 12,
                    "nSkipped" : 0,
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "missingSince" : {
                                "$eq" : null
                            }
                        },
                        "nReturned" : 12,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 13,
                        "advanced" : 12,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "docsExamined" : 12,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 12,
                            "executionTimeMillisEstimate" : 0,
                            "works" : 13,
                            "advanced" : 12,
                            "needTime" : 0,
                            "needYield" : 0,
                            "saveState" : 0,
                            "restoreState" : 0,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "keyPattern" : {
                                "shopId" : 1,
                                "missingSince" : 1
                            },
                            "indexName" : "shopId_1_missingSince_1",
                            "isMultiKey" : false,
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "shopId" : [
                                    "[1.0, 1.0]"
                                ],
                                "missingSince" : [
                                    "[null, null]"
                                ]
                            },
                            "keysExamined" : 12,
                            "dupsTested" : 0,
                            "dupsDropped" : 0,
                            "seenInvalidated" : 0
                        }
                    }
                },
                "allPlansExecution" : [ ]
            },
            "serverInfo" : {
                "host" : "Kays MacBook Pro",
                "port" : 27017,
                "version" : "3.2.6",
                "gitVersion" : "05552b562c7a0b3143a729aaa0838e558dc49b25"
            },
            "ok" : 1
        }
      Show
      created the non-sparse index _ {shopId:1, missingSince:1} _ db.offer.createIndex({shopId:1, missingSince:1}) insert the following documents: db.offer.insert({ "_id" : 1, "v" : 1 }); db.offer.insert({ "_id" : 2, "v" : 1 }); db.offer.insert({ "_id" : 3, "v" : 1 }); db.offer.insert({ "_id" : 4, "shopId" : 1, "v" : 1 }); db.offer.insert({ "_id" : 5, "shopId" : 1, "v" : 1 }); db.offer.insert({ "_id" : 6, "shopId" : 1, "v" : 1 }); db.offer.insert({ "_id" : 7, "shopId" : 1, "v" : 1 }); db.offer.insert({ "_id" : 8, "shopId" : 1, "v" : 1 }); db.offer.insert({ "_id" : 9, "shopId" : 1, "missingSince" : null , "v" : 1 }); db.offer.insert({ "_id" : 10, "shopId" : 1, "missingSince" : null , "v" : 1 }); db.offer.insert({ "_id" : 11, "shopId" : 1, "missingSince" : null , "v" : 1 }); db.offer.insert({ "_id" : 12, "shopId" : 1, "missingSince" : null , "v" : 1 }); db.offer.insert({ "_id" : 13, "shopId" : 1, "missingSince" : null , "v" : 1 }); db.offer.insert({ "_id" : 14, "shopId" : 1, "missingSince" : null , "v" : 1 }); db.offer.insert({ "_id" : 15, "shopId" : 1, "missingSince" : null , "v" : 1 }); db.offer.insert({ "_id" : 16, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 17, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 18, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 19, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 20, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 21, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 22, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 23, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 24, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 25, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 26, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 27, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); db.offer.insert({ "_id" : 28, "shopId" : 1, "missingSince" : ISODate( "2017-05-22T07:52:40.831Z" ), "v" : 1 }); execute explain(true) for the following query: db.offer.explain( true ).count({ "shopId" :1, "missingSince" : null }) the execution stats shows that 12 documents needed to be examined, meaning that the query was not covered by the index { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.offer" , "indexFilterSet" : false , "parsedQuery" : { "$and" : [ { "missingSince" : { "$eq" : null } }, { "shopId" : { "$eq" : 1 } } ] }, "winningPlan" : { "stage" : "COUNT" , "inputStage" : { "stage" : "FETCH" , "filter" : { "missingSince" : { "$eq" : null } }, "inputStage" : { "stage" : "IXSCAN" , "keyPattern" : { "shopId" : 1, "missingSince" : 1 }, "indexName" : "shopId_1_missingSince_1" , "isMultiKey" : false , "isUnique" : false , "isSparse" : false , "isPartial" : false , "indexVersion" : 1, "direction" : "forward" , "indexBounds" : { "shopId" : [ "[1.0, 1.0]" ], "missingSince" : [ "[ null , null ]" ] } } } }, "rejectedPlans" : [ ] }, "executionStats" : { "executionSuccess" : true , "nReturned" : 0, "executionTimeMillis" : 0, "totalKeysExamined" : 12, "totalDocsExamined" : 12, "executionStages" : { "stage" : "COUNT" , "nReturned" : 0, "executionTimeMillisEstimate" : 0, "works" : 13, "advanced" : 0, "needTime" : 12, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "nCounted" : 12, "nSkipped" : 0, "inputStage" : { "stage" : "FETCH" , "filter" : { "missingSince" : { "$eq" : null } }, "nReturned" : 12, "executionTimeMillisEstimate" : 0, "works" : 13, "advanced" : 12, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 12, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN" , "nReturned" : 12, "executionTimeMillisEstimate" : 0, "works" : 13, "advanced" : 12, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "shopId" : 1, "missingSince" : 1 }, "indexName" : "shopId_1_missingSince_1" , "isMultiKey" : false , "isUnique" : false , "isSparse" : false , "isPartial" : false , "indexVersion" : 1, "direction" : "forward" , "indexBounds" : { "shopId" : [ "[1.0, 1.0]" ], "missingSince" : [ "[ null , null ]" ] }, "keysExamined" : 12, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 } } }, "allPlansExecution" : [ ] }, "serverInfo" : { "host" : "Kays MacBook Pro" , "port" : 27017, "version" : "3.2.6" , "gitVersion" : "05552b562c7a0b3143a729aaa0838e558dc49b25" }, "ok" : 1 }

      Count is unable to cover queries which are using null as criterion even though an appropriate non-sparse index exists.

      The problem is that such a query slows down the whole database because lot of data needs to be read from disk (75-100 MB/sec while the query is running). The used index in production is about 5 GB on each mongodb node. The total size of all indexes in production is 32 GB and fit perfectly in RAM since each node has 128 GB RAM.

      I broke down the problem to a minimalistic, unsharded setup. I inserted the following types of documents:

      1. 3 documents without both fields shopId and missingSince
      2. 5 documents with field shopId:1 without field missingSince
      3. 7 documents with field shopId:1 and missingSince:null
      4. 13 documents with field shopId:1 and missingSince:ISODate("2017-05-22T07:52:40.831Z")

      I created the non-sparse index {shopId:1, missingSince:1}. The execution plan of the query count({"shopId":1, "missingSince":null}) indicated "totalDocsExamined":12 which means that 12 documents had to be fetched. These must be the 5 documents of point 2 plus the 7 documents of point 3. All these 12 documents should be in the index with shopId:1, missingSince:null, thus satisfying the query.

            Assignee:
            mark.agarunov Mark Agarunov
            Reporter:
            kay.agahd@idealo.de Kay Agahd
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved: