[SERVER-29326] count query with null criterion can't be covered by non-sparse index Created: 23/May/17  Updated: 21/Jun/17  Resolved: 23/May/17

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.2.6, 3.2.12
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Kay Agahd Assignee: Mark Agarunov
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-18861 Queries matching null value should be... Backlog
Operating System: ALL
Steps To Reproduce:
  • 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
    }

Participants:

 Description   

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.



 Comments   
Comment by Asya Kamsky [ 24/May/17 ]

You are correct that for count queries or other queries which don't need to return the indexed field that is either null or missing the FETCH is unnecessary and the ticket this one was marked a duplicate of (SERVER-18861) actually tracks improvements of current index use for null equality, I agree that the ticket description is not particularly clear about that. Its counterpart for $ne null queries is SERVER-27646.

We will try to clarify the description of these tickets to make it more clear which one tracks which work.

The main reason I brought up SERVER-12869 is that we have been seriously considering implementing this improvement and when we do, the work to improve performance of "old" indexes will be unnecessary, so we want to make sure that we schedule work that will improve things for the largest number of cases while staying relevant for multiple future releases.

Comment by Kay Agahd [ 24/May/17 ]

Thank you for jumping in asya, very much appreciated!
We are aware that {x:null} must match both documents. That's the reason why we query for null and not for either $type:10 or $exists:false. That's further the reason why we didn't understand why mongodb needs still to fetch the document because both types of document are indexed with {x:null} which satifies and covers the query. There is no need to fetch the document anymore!
So if you want to keep the current language semantics, you don't need to fix SERVER-12869 first to fix this issue (SERVER-29326) respectively SERVER-18861.

Comment by Asya Kamsky [ 24/May/17 ]

kay.agahd@idealo.de

There is a design issue underlying this. MongoDB query language semantics dictate that {x:null} must match both documents where x is not present, and ones where x is present and equal to null. On the other hand, in the indexes, the value null represents both, documents with field absent and documents with field set to null.

To resolve this will likely require work captured in SERVER-12869 which would be a major undertaking requiring a new index version and has to be scheduled against all the other large projects that the query team has in the backlog.

Regards,
Asya

Comment by Kay Agahd [ 24/May/17 ]

Thanks Mark.Agarunov but I'd rather say "has to do with" or "depends on" instead of "is duplicated" because this issue is for combined indexes and not single field indexes as SERVER-18861 .
It seems that mongodb does not put much effort to resolve this issue since it has been opened already 2 years ago and ist still in the backlog! Related issues were created already in Feb 2014.

If you are aware of such a critical bug you should at least update mongodb's documentation! Not only because it has taken us and probably others many hours to trace down this issue but also because if we knew that queries with null predicates never can be covered we would have chosen another schema design!
Thank you for your attention.

Comment by Mark Agarunov [ 23/May/17 ]

Hello kay.agahd@idealo.de,

Thank you for the report. Looking over the output you've provided, this behavior appears to be the same issue as described in SERVER-18861. I have closed this ticket as a duplicate, so please follow SERVER-18861 for any updates on this issue.

Thanks,
Mark

Generated at Thu Feb 08 04:20:31 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.