[SERVER-1576] $or does not use indexes when combined with sort() Created: 05/Aug/10  Updated: 07/Mar/14  Resolved: 06/Aug/10

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Performance, Querying
Affects Version/s: 1.6.0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Niall O'Higgins Assignee: Aaron Staple
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Debian Lenny 4.0, amd64


Operating System: Linux
Participants:

 Description   

// First of all, my indexes:

db.notes.getIndexes() [

{
"name" : "id",
"ns" : "qix.notes",
"key" :

{ "_id" : 1 }

},
{
"ns" : "qix.notes",
"name" : "shared_to_1",
"key" :

{ "shared_to" : 1 }

},
{
"ns" : "qix.notes",
"name" : "tags_1",
"key" :

{ "tags" : 1 }

},
{
"ns" : "qix.notes",
"name" : "owner_account_id_1",
"key" :

{ "owner_account_id" : 1 }

},
{
"ns" : "qix.notes",
"name" : "location_2d",
"key" :

{ "location" : "2d" }

}
]

// No sorting - $or is fast

> db.notes.find({$or : [

{owner_account_id : ObjectId("0000000000000000000c7e98")}

,

{shared_to : ObjectId("0000000000000000000c7e98")}

] }).explain()
{
"clauses" : [
{
"cursor" : "BtreeCursor owner_account_id_1",
"nscanned" : 255,
"nscannedObjects" : 255,
"n" : 255,
"millis" : 0,
"indexBounds" :

{ "owner_account_id" : [ [ ObjectId("0000000000000000000c7e98"), ObjectId("0000000000000000000c7e98") ] ] }

,
"allPlans" : [
{
"cursor" : "BtreeCursor owner_account_id_1",
"indexBounds" :

{ "owner_account_id" : [ [ ObjectId("0000000000000000000c7e98"), ObjectId("0000000000000000000c7e98") ] ] }

}
]
},
{
"cursor" : "BtreeCursor shared_to_1",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0,
"indexBounds" :

{ "shared_to" : [ [ ObjectId("0000000000000000000c7e98"), ObjectId("0000000000000000000c7e98") ] ] }

,
"allPlans" : [
{
"cursor" : "BtreeCursor shared_to_1",
"indexBounds" :

{ "shared_to" : [ [ ObjectId("0000000000000000000c7e98"), ObjectId("0000000000000000000c7e98") ] ] }

}
]
}
],
"nscanned" : 255,
"nscanneObjects" : 255,
"n" : 255,
"millis" : 0
}

// Sort + limit, $or is slow:

> db.notes.find({$or : [

{owner_account_id : ObjectId("0000000000000000000c7e98")}

,

{shared_to : ObjectId("0000000000000000000c7e98")}

] }).limit(20).sort(

{created_at:1}

).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 822547,
"nscannedObjects" : 822547,
"n" : 20,
"scanAndOrder" : true,
"millis" : 1505,
"indexBounds" : {

}
}



 Comments   
Comment by Aaron Staple [ 06/Aug/10 ]

duplicate of SERVER-1205

Generated at Thu Feb 08 02:57:25 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.