[SERVER-12905] {$exists:false} performance regression Created: 26/Feb/14  Updated: 10/Dec/14  Resolved: 26/Feb/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.0-rc0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Ben Becker Assignee: Samantha Ritter (Inactive)
Resolution: Duplicate Votes: 0
Labels: performance
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-12557 $exists could use index Closed
Related
Operating System: ALL
Steps To Reproduce:

See QA-401

Participants:

 Description   

The following query takes ~70us in v2.4.9, and ~722us in v2.6.0rc0:

db.regressions.find({$query:{_id: {$exists: false }}, $orderby:{_id:1}});

v2.4.9 explain output:

> db.regressions.find({$query:{_id: {$exists: false }}, $orderby:{_id:1}, $explain:1}).pretty();
{
        "cursor" : "BtreeCursor _id_",
        "isMultiKey" : false,
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
                "_id" : [
                        [
                                null,
                                null
                        ]
                ]
        },
        "allPlans" : [
                {
                        "cursor" : "BtreeCursor _id_",
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "indexBounds" : {
                                "_id" : [
                                        [
                                                null,
                                                null
                                        ]
                                ]
                        }
                }
        ],
        "oldPlan" : {
                "cursor" : "BtreeCursor _id_",
                "indexBounds" : {
                        "_id" : [
                                [
                                        null,
                                        null
                                ]
                        ]
                }
        },
        "server" : "zzyzx:27017"
}

v2.6.0rc0 explain output:

> db.regressions.find({$query:{_id: {$exists: false }}, $orderby:{_id:1}, $explain:1}).pretty();
{
        "cursor" : "BtreeCursor _id_",
        "isMultiKey" : false,
        "n" : 0,
        "nscannedObjects" : 1442,
        "nscanned" : 1442,
        "nscannedObjectsAllPlans" : 1540,
        "nscannedAllPlans" : 1540,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 11,
        "nChunkSkips" : 0,
        "millis" : 4,
        "indexBounds" : {
                "_id" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ]
        },
        "allPlans" : [
                {
                        "cursor" : "BtreeCursor _id_",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 1442,
                        "nscanned" : 1442,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                                "_id" : [
                                        [
                                                {
                                                        "$minElement" : 1
                                                },
                                                {
                                                        "$maxElement" : 1
                                                }
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BasicCursor",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 98,
                        "nscanned" : 98,
                        "scanAndOrder" : true,
                        "indexOnly" : false,
                        "nChunkSkips" : 0
                }
        ],
        "server" : "zzyzx:27017",
        "filterSet" : false,
        "stats" : {
                "type" : "FETCH",
                "works" : 1443,
                "yields" : 11,
                "unyields" : 11,
                "invalidates" : 0,
                "advanced" : 0,
                "needTime" : 1442,
                "needFetch" : 0,
                "isEOF" : 1,
                "alreadyHasObj" : 0,
                "forcedFetches" : 0,
                "matchTested" : 0,
                "children" : [
                        {
                                "type" : "IXSCAN",
                                "works" : 1442,
                                "yields" : 11,
                                "unyields" : 11,
                                "invalidates" : 0,
                                "advanced" : 1442,
                                "needTime" : 0,
                                "needFetch" : 0,
                                "isEOF" : 1,
                                "keyPattern" : "{ _id: 1 }",
                                "boundsVerbose" : "field #0['_id']: [MinKey, MaxKey]",
                                "isMultiKey" : 0,
                                "yieldMovedCursor" : 0,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0,
                                "matchTested" : 0,
                                "keysExamined" : 1442,
                                "children" : [ ]
                        }
                ]
        }
}

QA-401 results:

> db.regressions.find({$query:{"query":{$not:/where/}}, $orderby:{deltaRatio:-1}}).limit(1).pretty();
{
        "_id" : ObjectId("530d751fd8ed4fb55a7bda24"),
        "deltaRatio" : "9.99",
        "deltaUs" : 650.17,
        "v249Avg" : "72.33",
        "v249Results" : [
                72,
                73,
                74,
                71,
                72,
                72
        ],
        "rc0Avg" : "722.50",
        "rc0Results" : [
                715,
                767,
                704,
                709,
                708,
                732
        ],
        "testId" : "GeneratedTests::GeneratedQuery659[Compound2]",
        "query" : "{ query: { _id: { $exists: false } }, orderby: { _id: 1 } }",
        "proj" : "{ NONE: 1 }",
        "index" : "Compound2",
        "rc0Count" : 0,
        "v249Count" : 0,
        "countsMatch" : true
}

Of course, it's worth noting that this specific query is unlikely to ever be used, given that it sorts by a field which does not exist.


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