natural order with a filter on MongoDB capped collection

Question

I have a MongoDB capped collection with the following indexes:

[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "events.messageIn"
    },
    {
        "v" : 1,
        "key" : {
            "uuid" : 1,
            "ts" : -1
        },
        "name" : "uuid_1_ts_-1",
        "ns" : "events.messageIn",
        "background" : true
    }
]

As you can probably infer, this is a collection of event data. Because timestamps are always increasing, order {$natural: -1} should be equivalent to order {ts: -1}. I incidentally added an index on ts to be able to do efficient queries for time slices (e.g. db.messageIn.find({ts: {$gte: ISODate("2016-08-01")}})

However, I (perhaps naively) expected that the logical question "give me SOME_UUID's most recent messages" would be most efficiently answered by db.messageIn.find({uuid: SOME_UUID}).sort({$natural: -1}). As it is, that query is a collection scan, orders of magnitude slower than the plan for db.event.find({uuid: SOME_UUID}).sort({ts: -1}).

Why exactly is this? Speculatively, I'm assuming this is because MongoDB thinks that, once it's looking at the index, it won't be able to provide natural order; as a result, it regresses to a collection scan. Is there not some way for MongoDB to be cleverer about this query? For example, to use the analogy of a book's index:

uuid     ts                page
abcdef   2016-06-01T00:03  10
abcdef   2016-06-01T00:02  8
abcdef   2016-06-01T00:01  6
ghijkl   2016-06-01T00:03  9
mnopqr   2016-06-01T00:02  7
mnopqr   2016-06-01T00:01  5

Once filtering by uuid, you can easily enough order by page; there's no need to visit every page in order, check its uuid, and yield the page if it matches. Are MongoDB's "page" references somehow not orderable? Clearly my mental model is falling short somewhere.

Andriy Simonov Andriy Simonov · Accepted Answer · 2016-08-01T12:32:49

From my understanding of the documentation to support higher insertion throughput capped collection do not use indexes to return documents in the insertion order (or insertion backward order). Hence, when db.messageIn.find({uuid: SOME_UUID}).sort({$natural: -1}) is executed every document is examined one by one to preserve the insertion order and matching documents are filtered.

On the other hand, db.event.find({uuid: SOME_UUID}).sort({ts: -1}) is much faster because it uses the "uuid_1_ts_-1" index for both matching uuid and sorting. In MongoDB, sort operations can obtain the sort order by retrieving documents based on the ordering in an index (the documentation describes a case axectly like yours).

By the way, the "uuid_1_ts_-1" is not going to be used in queries like db.messageIn.find({ts: {$gte: ISODate("2016-08-01")}}) as it is a compound index and for a compound index, MongoDB can use only the index prefixes to support queries.

natural order with a filter on MongoDB capped collection

1 Answers