All geek questions in one place

Why collect request documents?

I have a MongoDB document like this:

  • collection array objectId
  • one bool for draft
  • one bool to remove

Exsample:

"_id" : "55689be772ba931a30c87fd8",
"Draft" : false,
"Deleted" : false,
"productsId" : [
    ObjectId("55688d7a72ba931bf430edf5"),
    ObjectId("55688d7a72ba931bf430edf8"),
    ObjectId("55688d7a72ba931bf430edf0"),
    ObjectId("55688d7a72ba931bf430edee")
]

I have an index:

db.getCollection("mycolection").createIndex({ "Deleted": 1, "Draft": 1, "productsId": 1 }, { "name": "_deleted_draft_productsId" })

Why when executing this query:

db.mycolection.explain("executionStats").count({productsId: ObjectId('55688d7a72ba931bf430edf4'),Draft: { $ne: true }, Deleted: { $ne: true } })

I get these statistics with totalKeysExamined:687:

"executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 685,
        "executionTimeMillis" : 3,
        "totalKeysExamined" : 687,
        "totalDocsExamined" : 685,
        "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
                "$and" : [
                    {
                        "$nor" : [
                            {
                                "Deleted" : {
                                    "$eq" : true
                                }
                            }
                        ]
                    },
                    {
                        "$nor" : [
                            {
                                "Draft" : {
                                    "$eq" : true
                                }
                            }
                        ]
                    }
                ]
            },
            "nReturned" : 685,
            "executionTimeMillisEstimate" : 0,
            "works" : 687,
            "advanced" : 685,
            "needTime" : 1,
            "needYield" : 0,
            "saveState" : 7,
            "restoreState" : 7,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 685,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 685,
                "executionTimeMillisEstimate" : 0,
                "works" : 687,
                "advanced" : 685,
                "needTime" : 1,
                "needYield" : 0,
                "saveState" : 7,
                "restoreState" : 7,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "productsId" : 1,
                    "Deleted" : 1,
                    "Draft" : 1
                },
                "indexName" : "_productsId_deleted_draft",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "productsId" : [
                        "productsId"
                    ],
                    "Deleted" : [ ],
                    "Draft" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "productsId" : [
                        "[ObjectId('55688d7a72ba931bf430edf4'), ObjectId('55688d7a72ba931bf430edf4')]"
                    ],
                    "Deleted" : [
                        "[MinKey, true)",
                        "(true, MaxKey]"
                    ],
                    "Draft" : [
                        "[MinKey, true)",
                        "(true, MaxKey]"
                    ]
                },
                "keysExamined" : 687,
                "seeks" : 2,
                "dupsTested" : 685,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    }

but if I try to remove the tags draftand deletedbool as follows:

db.Redazionale.explain("executionStats").count({IdProdotti: ObjectId('55688d7a72ba931bf430edf4') })

I get these statistics using totalDocsExamined:0what I expected in the previous request

"executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 0,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 690,
        "totalDocsExamined" : 0,
        "executionStages" : {
            "stage" : "COUNT",
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 0,
            "works" : 690,
            "advanced" : 0,
            "needTime" : 689,
            "needYield" : 0,
            "saveState" : 5,
            "restoreState" : 5,
            "isEOF" : 1,
            "invalidates" : 0,
            "nCounted" : 689,
            "nSkipped" : 0,
            "inputStage" : {
                "stage" : "COUNT_SCAN",
                "nReturned" : 689,
                "executionTimeMillisEstimate" : 0,
                "works" : 690,
                "advanced" : 689,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 5,
                "restoreState" : 5,
                "isEOF" : 1,
                "invalidates" : 0,
                "keysExamined" : 690,
                "keyPattern" : {
                    "productsId" : 1,
                    "Deleted" : 1,
                    "Draft" : 1
                },
                "indexName" : "_productsId_deleted_draft",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "productsId" : [
                        "productsId"
                    ],
                    "Deleted" : [ ],
                    "Draft" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "indexBounds" : {
                    "startKey" : {
                        "productsId" : ObjectId("55688d7a72ba931bf430edf4"),
                        "Deleted" : MinKey,
                        "Draft" : MinKey
                    },
                    "startKeyInclusive" : true,
                    "endKey" : {
                        "productsId" : ObjectId("55688d7a72ba931bf430edf4"),
                        "Deleted" : MaxKey,
                        "Draft" : MaxKey
                    },
                    "endKeyInclusive" : true
                }
            }
        }
    }

Update

I uploaded a file dump to create a small part of the entire database. click here to download

and try these queries:

db.Redazionale.explain("executionStats").count({ IdProdotti: ObjectId('55688d7a72ba931bf430edf4'),Draft: { $ne: true }, Deleted: { $ne: true } })

db.Redazionale.explain("executionStats").count({ IdProdotti: ObjectId('55688d7a72ba931bf430edf4') })
+4
source share
1 answer

, Draft: { $ne: true } :

"Draft" : [
    "[MinKey, true)",
    "(true, MaxKey]"
]

3 :

           "indexBounds" : {
                "productsId" : [
                    "[ObjectId('55688d7a72ba931bf430edf4'), ObjectId('55688d7a72ba931bf430edf4')]"
                ],
                "Deleted" : [
                    "[MinKey, true)",
                    "(true, MaxKey]"
                ],
                "Draft" : [
                    "[MinKey, true)",
                    "(true, MaxKey]"
                ]
            },

, .

, productsId , . productsId _productsId_deleted_draft , .

2 3 COUNT_SCAN. .

db.mycolection.explain("executionStats").count({
    productsId: ObjectId('55688d7a72ba931bf430edf4'),
    Draft: false, 
    Deleted: false 
})

COUNT_SCAN, :

            "indexBounds" : {
                "startKey" : {
                    "productsId" : ObjectId("55688d7a72ba931bf430edf4"),
                    "Deleted" : false,
                    "Draft" : false
                },
                "startKeyInclusive" : true,
                "endKey" : {
                    "productsId" : ObjectId("55688d7a72ba931bf430edf4"),
                    "Deleted" : false,
                    "Draft" : false
                },
                "endKeyInclusive" : true
            }

, Draft: { $ne: true } Draft: false, Draft, . , . , .

0

Source: https://habr.com/ru/post/1684446/

More articles:


All Articles