Query Performance – Explain MongoDB Plan – Split elapsed time for ExecutionStats

I have created an explanation plan for a query and am somewhat confused with the schedules of the individual phases and their aggregation at the total elapsed time.

The following command was run to generate the statistics. Verbosity mode has been set to execution statistics mode.

expStats = db.testdb.explain("executionStats")

expStats.aggregate((
    {"$match": {
        "Id": {"$in": ("AAA", "BBB")},
        "testType":"TEST",
        }
    },
    {"$group": {
        "_id": {"Id":"$Id", "testType": "$testType", "testSubType":"$testSubType"},
        "count": {"$sum": 1}
        }
        }
    ))

A summary of the execution statistics is as follows:

"executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 6355196,
    "executionTimeMillis" : 17575,
    "totalKeysExamined" : 6355196,
    "totalDocsExamined" : 0,
    "executionStages" : {
        "stage" : "PROJECTION_COVERED",
        "nReturned" : 6355196,
        "executionTimeMillisEstimate" : 3464,
        "works" : 6355197,
        "advanced" : 6355196,
        "needTime" : 0,
        "needYield" : 0,
        "saveState" : 50390,
        "restoreState" : 50390,
        "isEOF" : 1,
        "transformBy" : {
            "Id" : 1,
            "testSubType" : 1,
            "testType" : 1,
            "_id" : 0
        },
        "inputStage" : {
            "stage" : "IXSCAN",
            "nReturned" : 6355196,
            "executionTimeMillisEstimate" : 3251,
            "works" : 6355197,
            "advanced" : 6355196,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 50390,
            "restoreState" : 50390,
            "isEOF" : 1,
            "keyPattern" : {
                        "Id" : 1,
                        "testType" : 1,
                        "eventTimestamp" : -1,
                        "testSubType" : 1
                },
                "indexName" : "clientEvents",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                        "Id" : ( ),
                        "testType" : ( ),
                        "eventTimestamp" : ( ),
                        "testSubType" : ( )
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "keysExamined" : 6355196,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    }
},
{
"$group" : {
    "_id" : {
            "Id" : "$Id",
            "testType" : "$testType",
            "testSubType" : "$testSubType"
    },
    "count" : {
            "$sum" : {
                    "$const" : 1
            }
    }
}
}

The total execution time = "executeTimeMillis": 17575.

LEVEL 1 (PROJECTION_COVERED) estimated execution time = "executeTimeMillisEstimate": 3464.

LEVEL 2 (IXSCAN) estimated execution time = "executeTimeMillisEstimate": 3251.

So 6715 ms for the 2 stages.

Where was the remaining execution time of 10860 ms spent? I can't find out and the execution statistics don't tell me either

This is Mongodb version 4.2, which runs on Linux

Conventions for formatting the elapsed time?

I have an elapsed time (to the nearest millisecond) and would like to format it for human consumption.

For example:

  • 500 ms = 500 ms
  • 1500 ms = 1.5 s
  • 90000ms = 1m30s (or 90s?)
  • 2790000ms = 46m30s

…etc.

Are there any UX policies for the different breaks? When do I switch from milliseconds to split seconds? From seconds to minutes?

Actually, I have two types of elapsed times: the first ones are usually in the order of seconds (it took ~ 4 seconds for something to go); The seconds are on the order of ten minutes (the total time was ~ 45 minutes, not more than 90 minutes). Does that make a difference?

Suppose British English.

sql – calculation of elapsed time

The following code compares recv.rcpt_dtim (a datetime type) to the current date / time. It calculates an elapsed time, formatted in hours and minutes as follows: "04:22". It took me a while to make it work, which it is, but it just seems sloppy. Does anyone have any tips for tidying up?

TRIM (((((CURRENT YEAR TO SECOND - recv.rcpt_dtim) :: INTERVAL SECOND (9) to
SECOND) / 3600) :: VARCHAR (12) || & # 39:;; & # 39 || CASE IF (MOD (MOD ((CURRENT YEAR TO
MINUTE - recv.rcpt_dtim) :: INTERVAL MINUTE (9) to
MINUTE) :: VARCHAR (12) :: INT, 60), 60)) <10 THEN "0" ELSE "" END ||
(MOD (MOD (((NEWS YEAR TO MINUTE - recv.rcpt_dtim) :: INTERVAL MINUTE (9)
to MINUTE) :: VARCHAR (12) :: INT, 60), 60)) :: VARCHAR (12))

Google Sheets specifies how the elapsed time is calculated between two cells that contain the date and time and are displayed in days and hours

Try the following:

= Split time (days (B2, A2) & "day" & time (B2) - time (A2), ",")

days allows you to figure out the difference between two days value gives you the duration between two times as you need to extract them from your cells. I would use then Splits To put the duration in a separate column, since formats, as far as I know, can only be applied to a single cell

you could just use it too = Days and subtract the end date from the beginning to get the hours, but it will not take days.