[SERVER-19598] Explicit rooted $and containing $or doesn't use index in 2.4 Created: 27/Jul/15  Updated: 29/Jul/15  Resolved: 29/Jul/15

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.4.13
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Kevin Pulo Assignee: Ramon Fernandez Marina
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Operating System: ALL
Steps To Reproduce:

db.andor.drop()
db.andor.ensureIndex( { a: 1, b: 1, c: 1 } )
db.andor.find( { $and: [ { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ] } ).explain()
db.andor.find( { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ).explain()

The query with the explict rooted $and will use a BasicCursor, whereas without it uses the index as expected.

Participants:

 Description   

In 2.4, a query of the form { $and: [ { $or: [ ... ] } ] } does not choose an available suitable index:

> db.version()
2.4.13
> db.andor.drop()
false
> db.andor.ensureIndex( { a: 1, b: 1, c: 1 } )
Cannot use commands write mode, degrading to compatibility mode
{ "ok" : 1 }
> db.andor.find( { $and: [ { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ] } ).explain()
{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
        },
        "server" : "genique:11111"
}
> db.andor.find( { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ).explain()
{
        "clauses" : [
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "nscannedObjectsAllPlans" : 0,
                        "nscannedAllPlans" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "b" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "c" : [
                                        [
                                                1,
                                                1
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "nscannedObjectsAllPlans" : 0,
                        "nscannedAllPlans" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "b" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "c" : [
                                        [
                                                2,
                                                2
                                        ]
                                ]
                        }
                }
        ],
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "millis" : 0,
        "server" : "genique:11111"
}

Whereas in 2.6, the right index is selected as expected:

> db.version()
2.6.10
> db.andor.drop()
true
> db.andor.ensureIndex( { a: 1, b: 1, c: 1 } )
{
        "createdCollectionAutomatically" : true,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.andor.find( { $and: [ { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ] } ).explain()
{
        "clauses" : [
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "b" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "c" : [
                                        [
                                                1,
                                                1
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "b" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "c" : [
                                        [
                                                2,
                                                2
                                        ]
                                ]
                        }
                }
        ],
        "cursor" : "QueryOptimizerCursor",
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "scanAndOrder" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "server" : "genique:27017",
        "filterSet" : false
}
> db.andor.find( { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ).explain()
{
        "clauses" : [
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "b" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "c" : [
                                        [
                                                1,
                                                1
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "b" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "c" : [
                                        [
                                                2,
                                                2
                                        ]
                                ]
                        }
                }
        ],
        "cursor" : "QueryOptimizerCursor",
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "scanAndOrder" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "server" : "genique:27017",
        "filterSet" : false
}



 Comments   
Comment by Ramon Fernandez Marina [ 29/Jul/15 ]

The query system was completely re-written for MongoDB 2.6 to address this and multiple other issues. As such, it is not backport this 2.6 fix to 2.4. Users running 2.4 who are affected by this issue should consider upgrading to MongoDB 3.0 at their earliest convenience.

Regards,
Ramón.

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