[SERVER-12673] index bounds incorrect on compound index with simple query Created: 10/Feb/14  Updated: 10/Dec/14  Resolved: 20/Feb/14

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 2.5.5
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Antoine Girbal Assignee: David Storch
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-12639 Performance regression with RegEx Closed
Related
related to SERVER-12825 Don't generate index intersection pla... Closed
is related to SERVER-12795 Penalize ixisect during plan scoring ... Closed
Operating System: ALL
Participants:

 Description   

The following simple query on 'id' with range on 'date' does not properly bound the query on 'date'. In contrast, v2.4 was doing this properly.

Version 2.5.5:

> db.test.find({ id: "1537F3BDA01A95E403BFE4E0783B3D2BC2E2984FD492A21109468E939A39BB97", date: { $gte: new Date(1391212800000), $lte: new Date(1392076800000) } }).explain()
{ 
        "cursor" : "Complex Plan",
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "server" : "MongoDBLinux-3:27017",
        "stats" : {
                "type" : "FETCH",
                "works" : 2,
                "yields" : 0,
                "unyields" : 0,
                "invalidates" : 0,
                "advanced" : 0,
                "needTime" : 0,
                "needFetch" : 0,
                "isEOF" : 1,
                "alreadyHasObj" : 0,
                "forcedFetches" : 0,
                "matchTested" : 0,
                "children" : [
                        {
                                "type" : "KEEP_MUTATIONS",
                                "works" : 1,
                                "yields" : 0,
                                "unyields" : 0,
                                "invalidates" : 0,
                                "advanced" : 0,
                                "needTime" : 0,
                                "needFetch" : 0,
                                "isEOF" : 1,
                                "children" : [
                                        {
                                                "type" : "AND_HASH",
                                                "works" : 1,
                                                "yields" : 0,
                                                "unyields" : 0,
                                                "invalidates" : 0,
                                                "advanced" : 0,
                                                "needTime" : 0,
                                                "needFetch" : 0,
                                                "isEOF" : 1,
                                                "flaggedButPassed" : 0,
                                                "flaggedInProgress" : 0,
                                                "children" : [
                                                        {
                                                                "type" : "IXSCAN",
                                                                "works" : 1,
                                                                "yields" : 0,
                                                                "unyields" : 0,
                                                                "invalidates" : 0,
                                                                "advanced" : 0,
                                                                "needTime" : 0,
                                                                "needFetch" : 0,
                                                                "isEOF" : 1,
                                                                "keyPattern" : "{ date: 1.0 }",
                                                                "bounds" : {
                                                                        "date" : [
                                                                                [
                                                                                        ISODate("2014-02-01T00:00:00Z"),
                                                                                        ISODate("2014-02-11T00:00:00Z")
                                                                                ]
                                                                        ]
                                                                },
                                                                "isMultiKey" : 0,
                                                                "yieldMovedCursor" : 0,
                                                                "dupsTested" : 0,
                                                                "dupsDropped" : 0,
                                                                "seenInvalidated" : 0,
                                                                "matchTested" : 0,
                                                                "keysExamined" : 0,
                                                                "children" : [ ]
                                                        },
                                                        {
                                                                "type" : "IXSCAN",
                                                                "works" : 0,
                                                                "yields" : 0,
                                                                "unyields" : 0,
                                                                "invalidates" : 0,
                                                                "advanced" : 0,
                                                                "needTime" : 0,
                                                                "needFetch" : 0,
                                                                "isEOF" : 0,
                                                                "keyPattern" : "{ id: 1, date: 1 }",
                                                                "bounds" : {
                                                                        "id" : [
                                                                                [
                                                                                        "1537F3BDA01A95E403BFE4E0783B3D2BC2E2984FD492A21109468E939A39BB97",
                                                                                        "1537F3BDA01A95E403BFE4E0783B3D2BC2E2984FD492A21109468E939A39BB97"
                                                                                ]
                                                                        ],
                                                                        "date" : [
                                                                                [
                                                                                        {
                                                                                                "$minElement" : 1
                                                                                        },
                                                                                        {
                                                                                                "$maxElement" : 1
                                                                                        }
                                                                                ]
                                                                        ]
                                                                },
                                                                "isMultiKey" : 0,
                                                                "yieldMovedCursor" : 0,
                                                                "dupsTested" : 0,
                                                                "dupsDropped" : 0,
                                                                "seenInvalidated" : 0,
                                                                "matchTested" : 0,
                                                                "keysExamined" : 0,
                                                                "children" : [ ]
                                                        }
                                                ]
                                        }
                                ]
                        }
                ]
        }
} 

version 2.4:

> db.test.find({ id: "1537F3BDA01A95E403BFE4E0783B3D2BC2E2984FD492A21109468E939A39BB97", date: { $gte: new Date(1391212800000), $lte: new Date(1392076800000) } }).explain()
{ 
        "cursor" : "BtreeCursor id_1_date_1",
        "isMultiKey" : false,
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 3,
        "indexBounds" : {
                "id" : [
                        [
                                "1537F3BDA01A95E403BFE4E0783B3D2BC2E2984FD492A21109468E939A39BB97",
                                "1537F3BDA01A95E403BFE4E0783B3D2BC2E2984FD492A21109468E939A39BB97"
                        ]
                ],
                "date" : [
                        [
                                ISODate("2014-02-01T00:00:00Z"),
                                ISODate("2014-02-11T00:00:00Z")
                        ]
                ]
        },
        "server" : "MongoDBLinux-3:27017"
}



 Comments   
Comment by David Storch [ 20/Feb/14 ]

In cases where all plans are scored equally, 2.5.5 would choose the index intersection plan. This is the same issue as that reported in SERVER-12639. As described in that ticket, this has now been fixed in the nightlies.

Further related work is being done as part of SERVER-12795.

Comment by Antoine Girbal [ 12/Feb/14 ]

        "indexSizes" : {
                "_id_" : 4716366480,
                "date_1" : 4610135712,
                "id_1_date_1" : 9776582816
        },

Comment by Daniel Pasette (Inactive) [ 11/Feb/14 ]

can you include the indexes on the collection?

Generated at Thu Feb 08 03:29:13 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.