Is sorting in MongoDB supposed to be very slow and memory consuming

Working with Data
1

Hi,

Im a lifelong MySQL/Postgres user and I decided to use MongoDB for a project recently.
I have a very simple and small timeseries collection( indexed on timestamp field ) with 100 000 documents total.
When i run a simple query to return all the documents sorted by timestamp, I get an error that the maximum memory limit is reached( the query requires 100Mb or so according to .explain() ). After I enabled allowDiskUese(), the query works, but it still takes 500ms or so to execute.

Is this performace expected and normal?

Im asking this because ive worked with MySQL databases and sorted tables with millions of rows and it usually takes 10-20 milliseconds to execute the query. Even if I throw in some joins, it’s still faster than simply sorting 100 000 rows in MongoDB.

So, I’m assuming that I’m doing something wrong. Im sorting on {timestamp: -1} and I have an index {timestamp: -1}

So, the data should already be indexed and also sorted( since time series collections are already sorted by default if i remember correctly )

2

what’s your sort query like? what’s output of explain?

3

The query is blank {}, sort is {timestamp: -1}
Explain Output:



{
    "explainVersion" : "1",
    "stages" : [
        {
            "$cursor" : {
                "queryPlanner" : {
                    "namespace" : "64356bd6bfd6bd1c5a5b688f_test.system.buckets.stockdatapoints",
                    "indexFilterSet" : false,
                    "parsedQuery" : {

                    },
                    "queryHash" : "17830885",
                    "planCacheKey" : "17830885",
                    "maxIndexedOrSolutionsReached" : false,
                    "maxIndexedAndSolutionsReached" : false,
                    "maxScansToExplodeReached" : false,
                    "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "direction" : "backward"
                    },
                    "rejectedPlans" : [

                    ]
                },
                "executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 26583.0,
                    "executionTimeMillis" : 483.0,
                    "totalKeysExamined" : 0.0,
                    "totalDocsExamined" : 26583.0,
                    "executionStages" : {
                        "stage" : "COLLSCAN",
                        "nReturned" : 26583.0,
                        "executionTimeMillisEstimate" : 4.0,
                        "works" : 26585.0,
                        "advanced" : 26583.0,
                        "needTime" : 1.0,
                        "needYield" : 0.0,
                        "saveState" : 33.0,
                        "restoreState" : 33.0,
                        "isEOF" : 1.0,
                        "direction" : "backward",
                        "docsExamined" : 26583.0
                    },
                    "allPlansExecution" : [

                    ]
                }
            },
            "nReturned" : NumberLong(26583),
            "executionTimeMillisEstimate" : NumberLong(30)
        },
        {
            "$match" : {
                "$expr" : {
                    "$lte" : [
                        {
                            "$subtract" : [
                                "$control.max.timestamp",
                                "$control.min.timestamp"
                            ]
                        },
                        {
                            "$const" : NumberLong(86400000)
                        }
                    ]
                }
            },
            "nReturned" : NumberLong(26583),
            "executionTimeMillisEstimate" : NumberLong(70)
        },
        {
            "$_internalUnpackBucket" : {
                "exclude" : [

                ],
                "timeField" : "timestamp",
                "metaField" : "meta",
                "bucketMaxSpanSeconds" : 86400.0,
                "assumeNoMixedSchemaData" : true,
                "includeMinTimeAsMetadata" : true
            },
            "nReturned" : NumberLong(163877),
            "executionTimeMillisEstimate" : NumberLong(238)
        },
        {
            "$_internalBoundedSort" : {
                "sortKey" : {
                    "timestamp" : -1.0
                },
                "bound" : {
                    "base" : "min",
                    "offsetSeconds" : NumberLong(86400)
                },
                "limit" : NumberLong(0)
            },
            "totalDataSizeSortedBytesEstimate" : NumberLong(149418079),
            "usedDisk" : false,
            "spills" : NumberLong(0),
            "nReturned" : NumberLong(163877),
            "executionTimeMillisEstimate" : NumberLong(401)
        }
    ],
    "serverInfo" : {
        "host" : "ac-dw7yihy-shard-00-01.mc6ycp2.mongodb.net",
        "port" : 27017.0,
        "version" : "6.0.6",
        "gitVersion" : "26b4851a412cc8b9b4a18cdb6cd0f9f642e06aa7"
    },
    "serverParameters" : {
        "internalQueryFacetBufferSizeBytes" : 104857600.0,
        "internalQueryFacetMaxOutputDocSizeBytes" : 104857600.0,
        "internalLookupStageIntermediateDocumentMaxSizeBytes" : 16793600.0,
        "internalDocumentSourceGroupMaxMemoryBytes" : 104857600.0,
        "internalQueryMaxBlockingSortMemoryUsageBytes" : 33554432.0,
        "internalQueryProhibitBlockingMergeOnMongoS" : 0.0,
        "internalQueryMaxAddToSetBytes" : 104857600.0,
        "internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600.0
    },
    "command" : {
        "aggregate" : "system.buckets.stockdatapoints",
        "pipeline" : [
            {
                "$_internalUnpackBucket" : {
                    "timeField" : "timestamp",
                    "metaField" : "meta",
                    "bucketMaxSpanSeconds" : 86400.0,
                    "assumeNoMixedSchemaData" : true,
                    "usesExtendedRange" : false
                }
            },
            {
                "$sort" : {
                    "timestamp" : -1.0
                }
            }
        ],
        "cursor" : {

        },
        "collation" : {

        }
    },
    "ok" : 1.0,
    "$clusterTime" : {
        "clusterTime" : Timestamp(1685651310, 1),
        "signature" : {
            "hash" : BinData(0, "v84A1OLjrUT4fEWuCOhH3HFz9ZQ="),
            "keyId" : NumberLong(7200384509919887361)
        }
    },
    "operationTime" : Timestamp(1685651310, 1)
}
4

From this section you can see it is doing a collection scan/full table scan.

Looks like the docs are already sorted in that order so there’s no sort stage. (maybe that’s one reason why time series is special).

I believe most time it spends is to read everything from disk. (26583 full rows data from disk, which can be very slow)