[SERVER-22393] Index not used for query on capped collection using reverse $natural sort order Created: 01/Feb/16  Updated: 03/Feb/16  Resolved: 01/Feb/16

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.4, 3.2.1
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Alfie Kirkpatrick Assignee: Kelsey Schubert
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

Setup and query

use test
db.createCollection("tcapped", {capped:true, size: 10000000})
db.tcapped.ensureIndex({num: 1})
db.tcapped.insert({num: 1})
db.tcapped.find({num: 1}).sort({$natural: -1}).explain()

Version 2.6.4 result

/* 0 */
{
    "cursor" : "BasicCursor",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1,
    "nscannedObjectsAllPlans" : 1,
    "nscannedAllPlans" : 1,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "server" : "alfie-gb:27017",
    "filterSet" : false,
    "stats" : {
        "type" : "COLLSCAN",
        "works" : 3,
        "yields" : 0,
        "unyields" : 0,
        "invalidates" : 0,
        "advanced" : 1,
        "needTime" : 1,
        "needFetch" : 0,
        "isEOF" : 1,
        "docsTested" : 1,
        "children" : []
    }
}

Version 3.2.1 result

/* 0 */
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.tcapped",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "num" : {
                "$eq" : 1
            }
        },
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "num" : {
                    "$eq" : 1
                }
            },
            "direction" : "backward"
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 1,
        "executionStages" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "num" : {
                    "$eq" : 1
                }
            },
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 0,
            "works" : 3,
            "advanced" : 1,
            "needTime" : 1,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "direction" : "backward",
            "docsExamined" : 1
        },
        "allPlansExecution" : []
    },
    "serverInfo" : {
        "host" : "alfie-gb",
        "port" : 27017,
        "version" : "3.2.1",
        "gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
    }
}

Participants:

 Description   

If sort({$natural: -1}) is specified on a capped collection query which would normally use an index scan, the query switches to a colscan. For a large collection this causes performance issues.

For our use case it's important to be able to query a capped collection using an index, limit the result count and iterate backwards based on insertion order, latest first.

Tested with 2.6.4 and 3.2.1 (WiredTiger storage engine).

I didn't test with a non-capped collection.



 Comments   
Comment by Kelsey Schubert [ 03/Feb/16 ]

Hi jugglingcats,

Using ObjectId as _id to sort the documents that are returned is the best approach to achieve what you are trying to do. In capped collections, the insertion order is the guaranteed to be the the $natural order. Therefore, sorting by ObjectId on the _id index will always give you the expected results.

If you are not using ObjectId as _id, the workaround would be to sort using on an insertion timestamp field rather than $natural.

Regards,
Thomas

Comment by Alfie Kirkpatrick [ 01/Feb/16 ]

Thanks for taking the time to respond with doc link and apologies for raising a false bug report.

It's a shame that this isn't supported. We worked around it by sorting on _id which is an ObjectId. I realise this may not be accurate in all cases, but it's good enough for us.

Is there an enhancement request open for this feature (that I could follow)?

Comment by Kelsey Schubert [ 01/Feb/16 ]

Hi Alfie,

Thanks for the report. Sorting by natural order forces a collection scan and will not use an index. This is expected behavior and is described in our documentation here.

Kind regards,
Thomas

Generated at Thu Feb 08 04:00:17 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.