[SERVER-3156] query projection spec using 'true' instead of '1' prevents covered index query Created: 27/May/11  Updated: 12/Jul/16  Resolved: 20/Nov/13

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 1.8.1
Fix Version/s: 2.5.5

Type: Bug Priority: Minor - P4
Reporter: Sebastian Styka Assignee: Benety Goh
Resolution: Done Votes: 2
Labels: indexing, neweng, query_triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

ubuntu x64


Attachments: File server3156.js    
Issue Links:
Duplicate
is duplicated by SERVER-6854 query projection spec using 'true' in... Closed
is duplicated by SERVER-10956 Queries aren't covered when using 'tr... Closed
is duplicated by SERVER-5189 Retrieving fields from a covered inde... Closed
Participants:

 Description   

Update (aaron)

A query projection a:true should be equivalent to a:1, however specification of a true value prevents covered index queries apparently due to the following line:

            if ( ! e.isNumber() )
                _hasNonSimple = true;

Test

 
c = db.c;
c.drop();
 
c.save( { a:5 } );
assert.eq( { a:5 }, c.find( { a:5 }, { _id:0, a:1 } ).toArray()[ 0 ] );
assert.eq( { a:5 }, c.find( { a:5 }, { _id:0, a:true } ).toArray()[ 0 ] );
 
c.ensureIndex( { a:1 } );
assert.eq( { a:5 }, c.find( { a:5 }, { _id:0, a:1 } ).toArray()[ 0 ] );
assert( c.find( { a:5 }, { _id:0, a:1 } ).explain().indexOnly );
assert.eq( { a:5 }, c.find( {}, { _id:0, a:true } ).toArray()[ 0 ] );
 
// Fails because 'true' prevents doing a covered index query.
assert( c.find( { a:5 }, { _id:0, a:true } ).explain().indexOnly );

------------------------------------------------------------

I'm working on a collection consisting of elements like the one below:

{
"_id" : ObjectId("4ddec579b47cc1e6024d15a9"),
"CH_price" : 0,
"TR_price" : 312,
"TW_price" : null,
"city_code" : "BER",
"currency" : "EUR",
"max_chd" : 0,
"max_chd_age" : 0,
"max_pax" : 3,
"meal" : "BB",
"min_chd" : 0,
"min_chd_age" : 0,
"min_nights" : 0,
"min_pax" : 3,
"modified" : ISODate("2011-05-26T21:24:31.871Z"),
"original_room" : "DZ",
"rate_code" : "BER466_DZ",
"room" : "DR",
"source" : "FTI",
"tlc" : "BER",
"valid_from" : ISODate("2011-05-28T00:00:00Z"),
"valid_to" : ISODate("2011-05-29T00:00:00Z"),
"hotel_name" : "EUROSTARS BERLIN",
"category" : 5,
"city_name" : "Berlín",
"geo" : [
52.520870208740234,
13.389501571655273
],
"name" : "Standard",
"mh_code" : "BER000AJ",
"mhr_code" : "BER000AJ01",
"hotel_id" : "BER466_CITY"
}

which has following indexes defined:

{
"name" : "id",
"ns" : "xenia_pricing.mh_rate_prices",
"key" :

{ "_id" : 1 }

},
{
"_id" : ObjectId("4d81c0f20c42d00e2c1a4e18"),
"name" : "rate_code_1_min_nights_1_valid_from_1_valid_to_1",
"ns" : "xenia_pricing.mh_rate_prices",
"key" :

{ "rate_code" : 1, "min_nights" : 1, "valid_from" : 1, "valid_to" : 1 }

}

After submitting a sample query:

db.mh_rate_prices.find({ "rate_code" :

{ "$in" : ["BER466_DZ"] }

, "min_nights" :

{ "$lte" : 2 }

, "$or" : [{ "valid_from" : { "$lte" :

{ "$date" : 1309071377602 }

}, "valid_to" : { "$gte" :

{ "$date" : 1309071377602 }

} }, { "valid_from" : { "$lte" :

{ "$date" : 1309244177602 }

}, "valid_to" : { "$gte" :

{ "$date" : 1309244177602 }

} }, { "valid_from" : { "$gte" :

{ "$date" : 1309071377602 }

}, "valid_to" : { "$lte" :

{ "$date" : 1309244177602 }

} }] }).explain()

.explain() indicated that "indexOnly" : false, which suggests that the query was not covered by index:

{
"clauses" : [
{
"cursor" : "BtreeCursor valid_from_1_rate_code_1_min_nights_1_valid_to_1",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"valid_from" : [
[

{ "$minElement" : 1 }

,

{ "$date" : 1309071377602 }

]
],
"rate_code" : [
[
"BER466_DZ",
"BER466_DZ"
]
],
"min_nights" : [
[
-1.7976931348623157e+308,
2
]
],
"valid_to" : [
[

{ "$date" : 1309071377602 }

,

{ "$maxElement" : 1 }

]
]
}
},
{
"cursor" : "BtreeCursor valid_from_1_rate_code_1_min_nights_1_valid_to_1",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"valid_from" : [
[

{ "$date" : 1309071377602 }

,

{ "$date" : 1309244177602 }

]
],
"rate_code" : [
[
"BER466_DZ",
"BER466_DZ"
]
],
"min_nights" : [
[
-1.7976931348623157e+308,
2
]
],
"valid_to" : [
[

{ "$date" : 1309244177602 }

,

{ "$maxElement" : 1 }

]
]
}
},
{
"cursor" : "BtreeCursor rate_code_1_min_nights_1_valid_from_1_valid_to_1",
"nscanned" : 59,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 4,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"rate_code" : [
[
"BER466_DZ",
"BER466_DZ"
]
],
"min_nights" : [
[
-1.7976931348623157e+308,
2
]
],
"valid_from" : [
[

{ "$date" : 1309071377602 }

,

{ "$maxElement" : 1 }

]
],
"valid_to" : [
[

{ "$minElement" : 1 }

,

{ "$date" : 1309244177602 }

]
]
}
}
],
"nscanned" : 59,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 4
}

What is the reason of that behaviour and how should I define indexes to avoid long response time (up to 5 seconds for some queries)?



 Comments   
Comment by Githook User [ 20/Nov/13 ]

Author:

{u'username': u'benety', u'name': u'Benety Goh', u'email': u'benety@mongodb.com'}

Message: SERVER-3156 handle projection spec using boolean
Branch: master
https://github.com/mongodb/mongo/commit/2640c3e347e752b8819273e329c6d0e705b364b3

Comment by Mathieu Poumeyrol [ 14/Aug/13 ]

Just got bitten — badly — by this one.

Comment by Eliot Horowitz (Inactive) [ 19/Apr/12 ]

Yeah - we should change that

Comment by unbeknownst [ 18/Apr/12 ]

What? so you can select fields using either booleans or numbers but the behavior of the index changes depending on the type???

If I ran:

db.userevents.find({_id:ObjectId("4da41e0738710e13e4000001")}, {_id:true})

I get:

{ "_id" : ObjectId("4da41e0738710e13e4000001") }

If I ran:

db.userevents.find({_id:ObjectId("4da41e0738710e13e4000001")}, {_id:true, g:true})

I get:

{ "_id" : ObjectId("4da41e0738710e13e4000001"), "g" : ObjectId("4d650bb438710e13d4000001") }

So obviously the field selection works with booleans, which makes sense to me, as it does on other drivers, but it does not make sense than the type of the selector influences the decision on whether to use the index only to return data or not.
Are other drivers like this? Should we change in Python from using "True" to use "1" on the field selectors?

Comment by Eliot Horowitz (Inactive) [ 18/Apr/12 ]

needs to be:

{ _id : 1 }

Comment by unbeknownst [ 18/Apr/12 ]

Could you explain this case:

> db.userevents.find({_id:ObjectId("4da41e0738710e13e4000001")}, {_id:true}).explain()
{
"cursor" : "BtreeCursor id",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" :

{ "_id" : [ [ ObjectId("4da41e0738710e13e4000001"), ObjectId("4da41e0738710e13e4000001") ] ] }

}

"indexOnly" is false and "nscannedObjects" is 1, which means is not using the index to return the data?
Our "userevents" docs have 8 fields plus the _id.

I am using version "2.0.1".

Comment by Eliot Horowitz (Inactive) [ 27/May/11 ]

You don't have any projection in there, so the full documents will be returned, so indexOnly is false.

Notice in the 2nd case, nscanned=59 and nscannedObjects=0 which indicates it only had to use the index.

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