Why doesn't mongodb pick the "best" index

Working with Data
1
  • I have the following query
db.MDocumentContent.find({
            "updatedTime": {
                "$lte": ISODate('2023-08-20T16:00:00.000Z')
            },
            "type": {
                "$nin": [
                    "default",
                    "customFigureSample"
                ]
            },
            "isClean": {
                "$in": [
                    null,
                    false
                ]
            },
            "_id": {
                "$gt": ObjectId("611a18d87c62f5002fb0809c")
            }
        }).sort({ _id: 1 }).limit(10);

Analyzing the query with explain reveals that the index used is id instead of _id_1_updatedTime_1_type_1_isClean_1.

{
    "queryPlanner" : {
        "plannerVersion" : 1.0,
        "namespace" : "mtv.MDocumentContent",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "updatedTime" : {
                        "$lte" : ISODate("2023-08-20T16:00:00.000+0000")
                    }
                },
                {
                    "_id" : {
                        "$gt" : ObjectId("611a18d87c62f5002fb0809c")
                    }
                },
                {
                    "isClean" : {
                        "$in" : [
                            null,
                            false
                        ]
                    }
                },
                {
                    "type" : {
                        "$not" : {
                            "$in" : [
                                "customFigureSample",
                                "default"
                            ]
                        }
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 10.0,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [
                        {
                            "updatedTime" : {
                                "$lte" : ISODate("2023-08-20T16:00:00.000+0000")
                            }
                        },
                        {
                            "isClean" : {
                                "$in" : [
                                    null,
                                    false
                                ]
                            }
                        },
                        {
                            "type" : {
                                "$not" : {
                                    "$in" : [
                                        "customFigureSample",
                                        "default"
                                    ]
                                }
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "_id" : 1.0
                    },
                    "indexName" : "_id_",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "_id" : [

                        ]
                    },
                    "isUnique" : true,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2.0,
                    "direction" : "forward",
                    "indexBounds" : {
                        "_id" : [
                            "(ObjectId('611a18d87c62f5002fb0809c'), ObjectId('ffffffffffffffffffffffff')]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "LIMIT",
                "limitAmount" : 10.0,
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "isClean" : {
                            "$in" : [
                                null,
                                false
                            ]
                        }
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "_id" : 1.0,
                            "updatedTime" : 1.0,
                            "type" : 1.0,
                            "isClean" : 1.0
                        },
                        "indexName" : "_id_1_updatedTime_1_type_1_isClean_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "_id" : [

                            ],
                            "updatedTime" : [

                            ],
                            "type" : [

                            ],
                            "isClean" : [

                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2.0,
                        "direction" : "forward",
                        "indexBounds" : {
                            "_id" : [
                                "(ObjectId('611a18d87c62f5002fb0809c'), ObjectId('ffffffffffffffffffffffff')]"
                            ],
                            "updatedTime" : [
                                "(true, new Date(1692547200000)]"
                            ],
                            "type" : [
                                "[MinKey, \"customFigureSample\")",
                                "(\"customFigureSample\", \"default\")",
                                "(\"default\", MaxKey]"
                            ],
                            "isClean" : [
                                "[undefined, undefined]",
                                "[null, null]",
                                "[false, false]"
                            ]
                        }
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 10.0,
        "executionTimeMillis" : 12.0,
        "totalKeysExamined" : 2256.0,
        "totalDocsExamined" : 2256.0,
        "totalKeysExaminedBySizeInBytes" : 42864.0,
        "totalDocsExaminedBySizeInBytes" : 3409047.0,
        "executionStages" : {
            "stage" : "LIMIT",
            "nReturned" : 10.0,
            "executionTimeMillisEstimate" : 1.0,
            "works" : 2257.0,
            "advanced" : 10.0,
            "needTime" : 2246.0,
            "needYield" : 0.0,
            "saveState" : 35.0,
            "restoreState" : 35.0,
            "isEOF" : 1.0,
            "limitAmount" : 10.0,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [
                        {
                            "updatedTime" : {
                                "$lte" : ISODate("2023-08-20T16:00:00.000+0000")
                            }
                        },
                        {
                            "isClean" : {
                                "$in" : [
                                    null,
                                    false
                                ]
                            }
                        },
                        {
                            "type" : {
                                "$not" : {
                                    "$in" : [
                                        "customFigureSample",
                                        "default"
                                    ]
                                }
                            }
                        }
                    ]
                },
                "nReturned" : 10.0,
                "executionTimeMillisEstimate" : 1.0,
                "works" : 2256.0,
                "advanced" : 10.0,
                "needTime" : 2246.0,
                "needYield" : 0.0,
                "saveState" : 35.0,
                "restoreState" : 35.0,
                "isEOF" : 0.0,
                "docsExamined" : 2256.0,
                "docsExaminedBySizeInBytes" : 3409047.0,
                "alreadyHasObj" : 0.0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 2256.0,
                    "executionTimeMillisEstimate" : 0.0,
                    "works" : 2256.0,
                    "advanced" : 2256.0,
                    "needTime" : 0.0,
                    "needYield" : 0.0,
                    "saveState" : 35.0,
                    "restoreState" : 35.0,
                    "isEOF" : 0.0,
                    "keyPattern" : {
                        "_id" : 1.0
                    },
                    "indexName" : "_id_",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "_id" : [

                        ]
                    },
                    "isUnique" : true,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2.0,
                    "direction" : "forward",
                    "indexBounds" : {
                        "_id" : [
                            "(ObjectId('611a18d87c62f5002fb0809c'), ObjectId('ffffffffffffffffffffffff')]"
                        ]
                    },
                    "keysExamined" : 2256.0,
                    "keysExaminedBySizeInBytes" : 42864.0,
                    "seeks" : 1.0,
                    "dupsTested" : 0.0,
                    "dupsDropped" : 0.0
                }
            }
        },
        "allPlansExecution" : [
            {
                "nReturned" : 10.0,
                "executionTimeMillisEstimate" : 1.0,
                "totalKeysExamined" : 2256.0,
                "totalDocsExamined" : 2256.0,
                "totalKeysExaminedBySizeInBytes" : 42864.0,
                "totalDocsExaminedBySizeInBytes" : 3409047.0,
                "executionStages" : {
                    "stage" : "LIMIT",
                    "nReturned" : 10.0,
                    "executionTimeMillisEstimate" : 1.0,
                    "works" : 2256.0,
                    "advanced" : 10.0,
                    "needTime" : 2246.0,
                    "needYield" : 0.0,
                    "saveState" : 35.0,
                    "restoreState" : 35.0,
                    "isEOF" : 1.0,
                    "limitAmount" : 10.0,
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "$and" : [
                                {
                                    "updatedTime" : {
                                        "$lte" : ISODate("2023-08-20T16:00:00.000+0000")
                                    }
                                },
                                {
                                    "isClean" : {
                                        "$in" : [
                                            null,
                                            false
                                        ]
                                    }
                                },
                                {
                                    "type" : {
                                        "$not" : {
                                            "$in" : [
                                                "customFigureSample",
                                                "default"
                                            ]
                                        }
                                    }
                                }
                            ]
                        },
                        "nReturned" : 10.0,
                        "executionTimeMillisEstimate" : 1.0,
                        "works" : 2256.0,
                        "advanced" : 10.0,
                        "needTime" : 2246.0,
                        "needYield" : 0.0,
                        "saveState" : 35.0,
                        "restoreState" : 35.0,
                        "isEOF" : 0.0,
                        "docsExamined" : 2256.0,
                        "docsExaminedBySizeInBytes" : 3409047.0,
                        "alreadyHasObj" : 0.0,
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 2256.0,
                            "executionTimeMillisEstimate" : 0.0,
                            "works" : 2256.0,
                            "advanced" : 2256.0,
                            "needTime" : 0.0,
                            "needYield" : 0.0,
                            "saveState" : 35.0,
                            "restoreState" : 35.0,
                            "isEOF" : 0.0,
                            "keyPattern" : {
                                "_id" : 1.0
                            },
                            "indexName" : "_id_",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "_id" : [

                                ]
                            },
                            "isUnique" : true,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2.0,
                            "direction" : "forward",
                            "indexBounds" : {
                                "_id" : [
                                    "(ObjectId('611a18d87c62f5002fb0809c'), ObjectId('ffffffffffffffffffffffff')]"
                                ]
                            },
                            "keysExamined" : 2256.0,
                            "keysExaminedBySizeInBytes" : 42864.0,
                            "seeks" : 1.0,
                            "dupsTested" : 0.0,
                            "dupsDropped" : 0.0
                        }
                    }
                }
            },
            {
                "nReturned" : 10.0,
                "executionTimeMillisEstimate" : 0.0,
                "totalKeysExamined" : 2256.0,
                "totalDocsExamined" : 10.0,
                "totalKeysExaminedBySizeInBytes" : 76694.0,
                "totalDocsExaminedBySizeInBytes" : 45016.0,
                "executionStages" : {
                    "stage" : "LIMIT",
                    "nReturned" : 10.0,
                    "executionTimeMillisEstimate" : 0.0,
                    "works" : 2256.0,
                    "advanced" : 10.0,
                    "needTime" : 2246.0,
                    "needYield" : 0.0,
                    "saveState" : 35.0,
                    "restoreState" : 35.0,
                    "isEOF" : 1.0,
                    "limitAmount" : 10.0,
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "isClean" : {
                                "$in" : [
                                    null,
                                    false
                                ]
                            }
                        },
                        "nReturned" : 10.0,
                        "executionTimeMillisEstimate" : 0.0,
                        "works" : 2256.0,
                        "advanced" : 10.0,
                        "needTime" : 2246.0,
                        "needYield" : 0.0,
                        "saveState" : 35.0,
                        "restoreState" : 35.0,
                        "isEOF" : 0.0,
                        "docsExamined" : 10.0,
                        "docsExaminedBySizeInBytes" : 45016.0,
                        "alreadyHasObj" : 0.0,
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 10.0,
                            "executionTimeMillisEstimate" : 0.0,
                            "works" : 2256.0,
                            "advanced" : 10.0,
                            "needTime" : 2246.0,
                            "needYield" : 0.0,
                            "saveState" : 35.0,
                            "restoreState" : 35.0,
                            "isEOF" : 0.0,
                            "keyPattern" : {
                                "_id" : 1.0,
                                "updatedTime" : 1.0,
                                "type" : 1.0,
                                "isClean" : 1.0
                            },
                            "indexName" : "_id_1_updatedTime_1_type_1_isClean_1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "_id" : [

                                ],
                                "updatedTime" : [

                                ],
                                "type" : [

                                ],
                                "isClean" : [

                                ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2.0,
                            "direction" : "forward",
                            "indexBounds" : {
                                "_id" : [
                                    "(ObjectId('611a18d87c62f5002fb0809c'), ObjectId('ffffffffffffffffffffffff')]"
                                ],
                                "updatedTime" : [
                                    "(true, new Date(1692547200000)]"
                                ],
                                "type" : [
                                    "[MinKey, \"customFigureSample\")",
                                    "(\"customFigureSample\", \"default\")",
                                    "(\"default\", MaxKey]"
                                ],
                                "isClean" : [
                                    "[undefined, undefined]",
                                    "[null, null]",
                                    "[false, false]"
                                ]
                            },
                            "keysExamined" : 2256.0,
                            "keysExaminedBySizeInBytes" : 76694.0,
                            "seeks" : 2247.0,
                            "dupsTested" : 0.0,
                            "dupsDropped" : 0.0
                        }
                    }
                }
            }
        ]
    },
    "ok" : 1.0,
    "$clusterTime" : {
        "clusterTime" : Timestamp(1708585127, 18),
        "signature" : {
            "hash" : BinData(0, "0YV3kUHkU1JPVWVekohkmjPCkdE="),
            "keyId" : NumberLong(7285838794148806712)
        }
    },
    "operationTime" : Timestamp(1708585127, 18)
}

Personally, I think _id_1_updatedTime_1_type_1_isClean_1 is the best index.
Does anyone know why this is

2

Hi @DIE_GO, welcome to the community!

I also believe that your index could have a better performance, to be sure, you can use the hint option on your search to force MongoDB to execute a query with the index specified in the hint field and check the metrics with .explain("executionStats") to validate ours assumption.

3

It’s fine to use hint to force the index _id_1_updatedTime_1_type_1_isClean_1, but I don’t understand why mongodb use id instead of _id_1_updatedTime_1_type_1_isClean_1 @ Artur_57972

4

This behavior might happen due to the plan cache, you can evict the plans cached with db.collection.getPlanCache().clear().

5

After following your instructions, it still doesn’t work

6

Can you provide the output of .explain("executionStats") for both cases? One is for the _id index, and the other is for the index that you created.

7

@Artur_57972 I’m sorry I’m late

use index id
explain query follow:

db.MDocumentContent.explain("executionStats").find({
            "updatedTime": {
                "$lte": ISODate('2023-08-20T16:00:00.000Z')
            },
            "type": {
                "$nin": [
                    "default",
                    "customFigureSample"
                ]
            },
            "isClean": {
                "$in": [
                    null,
                    false
                ]
            },
            "_id": {
                "$gt": ObjectId("6406d7187b897400478a6551")
            }
        }).sort({ _id: 1 }).limit(10).hint('_id_');

explain result follow:

{
    "queryPlanner" : {
        "plannerVersion" : 1.0,
        "namespace" : "mtv.MDocumentContent",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "updatedTime" : {
                        "$lte" : ISODate("2023-08-20T16:00:00.000+0000")
                    }
                },
                {
                    "_id" : {
                        "$gt" : ObjectId("6406d7187b897400478a6551")
                    }
                },
                {
                    "isClean" : {
                        "$in" : [
                            null,
                            false
                        ]
                    }
                },
                {
                    "type" : {
                        "$not" : {
                            "$in" : [
                                "customFigureSample",
                                "default"
                            ]
                        }
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 10.0,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [
                        {
                            "updatedTime" : {
                                "$lte" : ISODate("2023-08-20T16:00:00.000+0000")
                            }
                        },
                        {
                            "isClean" : {
                                "$in" : [
                                    null,
                                    false
                                ]
                            }
                        },
                        {
                            "type" : {
                                "$not" : {
                                    "$in" : [
                                        "customFigureSample",
                                        "default"
                                    ]
                                }
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "_id" : 1.0
                    },
                    "indexName" : "_id_",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "_id" : [

                        ]
                    },
                    "isUnique" : true,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2.0,
                    "direction" : "forward",
                    "indexBounds" : {
                        "_id" : [
                            "(ObjectId('6406d7187b897400478a6551'), ObjectId('ffffffffffffffffffffffff')]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [

        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 10.0,
        "executionTimeMillis" : 2534.0,
        "totalKeysExamined" : 251441.0,
        "totalDocsExamined" : 251441.0,
        "executionStages" : {
            "stage" : "LIMIT",
            "nReturned" : 10.0,
            "executionTimeMillisEstimate" : 423.0,
            "works" : 251442.0,
            "advanced" : 10.0,
            "needTime" : 251431.0,
            "needYield" : 0.0,
            "saveState" : 1964.0,
            "restoreState" : 1964.0,
            "isEOF" : 1.0,
            "invalidates" : 0.0,
            "limitAmount" : 10.0,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [
                        {
                            "updatedTime" : {
                                "$lte" : ISODate("2023-08-20T16:00:00.000+0000")
                            }
                        },
                        {
                            "isClean" : {
                                "$in" : [
                                    null,
                                    false
                                ]
                            }
                        },
                        {
                            "type" : {
                                "$not" : {
                                    "$in" : [
                                        "customFigureSample",
                                        "default"
                                    ]
                                }
                            }
                        }
                    ]
                },
                "nReturned" : 10.0,
                "executionTimeMillisEstimate" : 423.0,
                "works" : 251441.0,
                "advanced" : 10.0,
                "needTime" : 251431.0,
                "needYield" : 0.0,
                "saveState" : 1964.0,
                "restoreState" : 1964.0,
                "isEOF" : 0.0,
                "invalidates" : 0.0,
                "docsExamined" : 251441.0,
                "alreadyHasObj" : 0.0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 251441.0,
                    "executionTimeMillisEstimate" : 9.0,
                    "works" : 251441.0,
                    "advanced" : 251441.0,
                    "needTime" : 0.0,
                    "needYield" : 0.0,
                    "saveState" : 1964.0,
                    "restoreState" : 1964.0,
                    "isEOF" : 0.0,
                    "invalidates" : 0.0,
                    "keyPattern" : {
                        "_id" : 1.0
                    },
                    "indexName" : "_id_",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "_id" : [

                        ]
                    },
                    "isUnique" : true,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2.0,
                    "direction" : "forward",
                    "indexBounds" : {
                        "_id" : [
                            "(ObjectId('6406d7187b897400478a6551'), ObjectId('ffffffffffffffffffffffff')]"
                        ]
                    },
                    "keysExamined" : 251441.0,
                    "seeks" : 1.0,
                    "dupsTested" : 0.0,
                    "dupsDropped" : 0.0,
                    "seenInvalidated" : 0.0
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "mongodb-backup-sz",
        "port" : 27019.0,
        "version" : "4.0.19",
        "gitVersion" : "7e28f4296a04d858a2e3dd84a1e79c9ba59a9568"
    },
    "ok" : 1.0,
    "operationTime" : Timestamp(1709023432, 235),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1709023432, 235),
        "signature" : {
            "hash" : BinData(0, "8ZPdhpOrtVaZSWiYa4htzpGKfjQ="),
            "keyId" : NumberLong(7301617112424906756)
        }
    }
}

user index _id_1_updatedTime_1_type_1_isClean_1
explain query follow：

db.MDocumentContent.explain("executionStats").find({
            "updatedTime": {
                "$lte": ISODate('2023-08-20T16:00:00.000Z')
            },
            "type": {
                "$nin": [
                    "default",
                    "customFigureSample"
                ]
            },
            "isClean": {
                "$in": [
                    null,
                    false
                ]
            },
            "_id": {
                "$gt": ObjectId("6406d7187b897400478a6551")
            }
        }).sort({ _id: 1 }).limit(10).hint('_id_1_updatedTime_1_type_1_isClean_1');

explain result follow：

{
    "queryPlanner" : {
        "plannerVersion" : 1.0,
        "namespace" : "mtv.MDocumentContent",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "updatedTime" : {
                        "$lte" : ISODate("2023-08-20T16:00:00.000+0000")
                    }
                },
                {
                    "_id" : {
                        "$gt" : ObjectId("6406d7187b897400478a6551")
                    }
                },
                {
                    "isClean" : {
                        "$in" : [
                            null,
                            false
                        ]
                    }
                },
                {
                    "type" : {
                        "$not" : {
                            "$in" : [
                                "customFigureSample",
                                "default"
                            ]
                        }
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 10.0,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "isClean" : {
                        "$in" : [
                            null,
                            false
                        ]
                    }
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "_id" : 1.0,
                        "updatedTime" : 1.0,
                        "type" : 1.0,
                        "isClean" : 1.0
                    },
                    "indexName" : "_id_1_updatedTime_1_type_1_isClean_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "_id" : [

                        ],
                        "updatedTime" : [

                        ],
                        "type" : [

                        ],
                        "isClean" : [

                        ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2.0,
                    "direction" : "forward",
                    "indexBounds" : {
                        "_id" : [
                            "(ObjectId('6406d7187b897400478a6551'), ObjectId('ffffffffffffffffffffffff')]"
                        ],
                        "updatedTime" : [
                            "(true, new Date(1692547200000)]"
                        ],
                        "type" : [
                            "[MinKey, \"customFigureSample\")",
                            "(\"customFigureSample\", \"default\")",
                            "(\"default\", MaxKey]"
                        ],
                        "isClean" : [
                            "[null, null]",
                            "[false, false]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [

        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 10.0,
        "executionTimeMillis" : 377.0,
        "totalKeysExamined" : 251441.0,
        "totalDocsExamined" : 10.0,
        "executionStages" : {
            "stage" : "LIMIT",
            "nReturned" : 10.0,
            "executionTimeMillisEstimate" : 28.0,
            "works" : 251442.0,
            "advanced" : 10.0,
            "needTime" : 251431.0,
            "needYield" : 0.0,
            "saveState" : 1964.0,
            "restoreState" : 1964.0,
            "isEOF" : 1.0,
            "invalidates" : 0.0,
            "limitAmount" : 10.0,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "isClean" : {
                        "$in" : [
                            null,
                            false
                        ]
                    }
                },
                "nReturned" : 10.0,
                "executionTimeMillisEstimate" : 28.0,
                "works" : 251441.0,
                "advanced" : 10.0,
                "needTime" : 251431.0,
                "needYield" : 0.0,
                "saveState" : 1964.0,
                "restoreState" : 1964.0,
                "isEOF" : 0.0,
                "invalidates" : 0.0,
                "docsExamined" : 10.0,
                "alreadyHasObj" : 0.0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 10.0,
                    "executionTimeMillisEstimate" : 27.0,
                    "works" : 251441.0,
                    "advanced" : 10.0,
                    "needTime" : 251431.0,
                    "needYield" : 0.0,
                    "saveState" : 1964.0,
                    "restoreState" : 1964.0,
                    "isEOF" : 0.0,
                    "invalidates" : 0.0,
                    "keyPattern" : {
                        "_id" : 1.0,
                        "updatedTime" : 1.0,
                        "type" : 1.0,
                        "isClean" : 1.0
                    },
                    "indexName" : "_id_1_updatedTime_1_type_1_isClean_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "_id" : [

                        ],
                        "updatedTime" : [

                        ],
                        "type" : [

                        ],
                        "isClean" : [

                        ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2.0,
                    "direction" : "forward",
                    "indexBounds" : {
                        "_id" : [
                            "(ObjectId('6406d7187b897400478a6551'), ObjectId('ffffffffffffffffffffffff')]"
                        ],
                        "updatedTime" : [
                            "(true, new Date(1692547200000)]"
                        ],
                        "type" : [
                            "[MinKey, \"customFigureSample\")",
                            "(\"customFigureSample\", \"default\")",
                            "(\"default\", MaxKey]"
                        ],
                        "isClean" : [
                            "[null, null]",
                            "[false, false]"
                        ]
                    },
                    "keysExamined" : 251441.0,
                    "seeks" : 251432.0,
                    "dupsTested" : 0.0,
                    "dupsDropped" : 0.0,
                    "seenInvalidated" : 0.0
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "mongodb-backup-sz",
        "port" : 27019.0,
        "version" : "4.0.19",
        "gitVersion" : "7e28f4296a04d858a2e3dd84a1e79c9ba59a9568"
    },
    "ok" : 1.0,
    "operationTime" : Timestamp(1709023707, 485),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1709023707, 485),
        "signature" : {
            "hash" : BinData(0, "pIUBcyOoQxzzVgwEebfP6j1s39E="),
            "keyId" : NumberLong(7301617112424906756)
        }
    }
}
8

This is really strange, the second index is clearly the best one, but MongoDB insists on using the _id. From what I could see, you’re using MongoDB version 4.0.19, right? If that is true, try this same scenario in a newer version.

9

Yes, the current version is 4.0.19. Ok, I’ll try it with the latest version of mongodb

10

How many documents do you have in your collection?