Range query an indexed nested field has weird indexBounds

[aharbick]

Say I have a testdocuments collection with documents that look like:

{
name: "Test"
details: {
timestamp: 1319083200000,
type: "something"
}
}

And I've got indexes that look like:

> db.testdocuments.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"ns" : "testdocuments_production.testdocuments",
"name" : "_id_"
},
{
"v" : 1,
"key" : {
"details.timestamp" : 1
},
"ns" : "testdocuments_production.testdocuments",
"name" : "details.timestamp_1"
}
]

The query appears to ignore the $lte part of the spec and doesn't have
a constrained range. Here is the explain:

PRIMARY> db.testdocuments.find({'details.timestamp': {$gte:
1319083200000, $lte: 1319093200000}}).explain()
{
"cursor" : "BtreeCursor details.timestamp_1",
"nscanned" : 404081,
"nscannedObjects" : 404081,
"n" : 3494,
"millis" : 3063,
"nYields" : 455,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" : {
"details.timestamp" : [
[
1319083200000,
1.7976931348623157e+308
]
]
}
}

NOTE the "1.7976931348623157e+308" on the indexBounds. That is
essentially unbounded. Which is why the query scans 404081 documents
to only return 3494.

Also note that a similar index on a non-nested field does what you'd
expect (i.e. the second part of the indexBounds is the $lte part of
the query spec).

Is this right?

[Tyler Brock]

May I ask what version you are running?

I tried to replicate it running v2.0.1 and was able to see that the
BtreeCursor was using the provided bounds correctly using your
numbers.

db.testdocuments.insert({name: "Test", details: {timestamp:
1319083200000, type: "something"}})
db.testdocuments.ensureIndex({"details.timestamp": 1})

db.testdocuments.find({'details.timestamp': {$gte: 1319083200000,
$lte: 1319093200000}}).explain()
{
"cursor" : "BtreeCursor details.timestamp_1",

"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 1,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,

"indexOnly" : false,
"indexBounds" : {
"details.timestamp" : [
[
1319083200000,

1319093200000
]
]
}
}

Let me know.

-Tyler

[aharbick]

We're using version 2.0.1. FWIW, I dumbed down the example to
communicate it. The essence is essentially correct, but it's possible
that other details matter (e.g. there are other indexes, etc.)

Also... I noticed in your test... isMultiKey was false but in my it
was true. Could that make a difference and what would make that true?

Thanks,
Andy

[aharbick]

Ah I figured out where you example (which also worked for me) wasn't
exactly the same as mine. My "details" field was actually and ARRAY
of "sub documents" not a "sub document" itself. Try your example
again with this:

db.testdocuments.insert({name: "Test", details: [{timestamp:

1319083200000, type: "something"}, {timestamp: 1329083200000, type:
"something2"}]})

Further more if I insert only on item in the details array the
indexBounds also looks fine e.g.

db.testdocuments.insert({name: "Test", details: [{timestamp:
1319083200000, type: "something"}]})

Thanks for your help!

Andy

[Tyler Brock]

Hey Andy, wanted to follow up after getting some more information from
Aaron here @10gen. The behavior you are seeing is as designed, and is
new in 2.0.

"Because the index you are using is multikey, the server cannot
constrain the index on both upper and lower bounds. Instead, only one
of the bounds is chosen. This is so we can return correct matches in
cases like the following:

save {a:[1,10]}
query {a:{$gt:2,$lt:9}}

The document should be returned because both query criteria ($gt and
$lt) match a value in the a array, but if we were to use both upper
and lower index bounds we would not find this document using the
index.

Right now if you want to do an index scan with both upper and lower
bounds, you need to use a non multi key index."

I hope that answers you question.

Perhaps you could hint to the query optimizer to use a non-multikey
index. More info here: http://www.mongodb.org/display/DOCS/Optimization#Optimization-Hint

-Tyler

[Mathieu Poumeyrol]

You may also want to watch (and vote ?):

https://jira.mongodb.org/browse/SERVER-4180

> --
> You received this message because you are subscribed to the Google Groups "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
>