index usage while performing sort operation
瀏覽次數：333 次
跳到第一則未讀訊息
Valentin Kuznetsov
2018年4月23日 上午11:03:042018/4/23
收件者：mongodb-user
Hi,
So, I do have dataset.name index, but I didn't use it in my query specs, instead I query using another index, and only ask to sort my record by dataset.name where I do have an index. Is it a bug or a feature on MongoDB?
I'm not sure if I found a bug, but it seems to me that index on my data is not used when I ask to fetch data using sort operation. Here is a query:
db.cache.find({"qhash":"7b32b92becab9b5de06fa8ac85011133", "das.record":1}).sort({"dataset.name":1}).explain(){ "queryPlanner" : { "plannerVersion" : 1, "namespace" : "das.cache", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "das.record" : { "$eq" : 1 } }, { "qhash" : { "$eq" : "7b32b92becab9b5de06fa8ac85011133" } } ] }, "winningPlan" : { "stage" : "SORT", "sortPattern" : { "dataset.name" : 1 }, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "inputStage" : { "stage" : "FETCH", "filter" : { "qhash" : { "$eq" : "7b32b92becab9b5de06fa8ac85011133" } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "das.record" : 1 }, "indexName" : "das.record_1", "isMultiKey" : false, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "das.record" : [ "[1.0, 1.0]" ] } } } } }, "rejectedPlans" : [ { "stage" : "SORT", "sortPattern" : { "dataset.name" : 1 }, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "inputStage" : { "stage" : "FETCH", "filter" : { "das.record" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "qhash" : 1 }, "indexName" : "qhash_1", "isMultiKey" : false, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "qhash" : [ "[\"7b32b92becab9b5de06fa8ac85011133\", \"7b32b92becab9b5de06fa8ac85011133\"]" ] } } } } }, { "stage" : "SORT", "sortPattern" : { "dataset.name" : 1 }, "inputStage" : { "stage" : "KEEP_MUTATIONS", "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "inputStage" : { "stage" : "FETCH", "inputStage" : { "stage" : "AND_SORTED", "inputStages" : [ { "stage" : "IXSCAN", "keyPattern" : { "qhash" : 1 }, "indexName" : "qhash_1", "isMultiKey" : false, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "qhash" : [ "[\"7b32b92becab9b5de06fa8ac85011133\", \"7b32b92becab9b5de06fa8ac85011133\"]" ] } }, { "stage" : "IXSCAN", "keyPattern" : { "das.record" : 1 }, "indexName" : "das.record_1", "isMultiKey" : false, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "das.record" : [ "[1.0, 1.0]" ] } } ] } } } } }, { "stage" : "SORT", "sortPattern" : { "dataset.name" : 1 }, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "inputStage" : { "stage" : "FETCH", "filter" : { "$and" : [ { "das.record" : { "$eq" : 1 } }, { "qhash" : { "$eq" : "7b32b92becab9b5de06fa8ac85011133" } } ] }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "dataset.name" : 1 }, "indexName" : "dataset.name_1", "isMultiKey" : true, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "dataset.name" : [ "[MinKey, MaxKey]" ] } } } } } ] }, "serverInfo" : { "host" : "vkair", "port" : 8230, "version" : "3.6.3", "gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5" }, "ok" : 1}>> db.cache.find({"qhash":"7b32b92becab9b5de06fa8ac85011133", "das.record":1}).sort({"dataset.name":1}).count()69936>> db.cache.find({"qhash":"7b32b92becab9b5de06fa8ac85011133", "das.record":1}).sort({"dataset.name":1})Error: error: { "ok" : 0, "errmsg" : "Executor error during find command: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.", "code" : 96, "codeName" : "OperationFailed"}
> db.cache.getIndexes()[ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "das.cache" }, { "v" : 2, "key" : { "qhash" : 1 }, "name" : "qhash_1", "ns" : "das.cache", "background" : true }, { "v" : 2, "key" : { "das.expire" : 1 }, "name" : "das.expire_1", "ns" : "das.cache", "background" : true }, { "v" : 2, "key" : { "das.record" : 1 }, "name" : "das.record_1", "ns" : "das.cache", "background" : true }, { "v" : 2, "key" : { "dataset.name" : 1 }, "name" : "dataset.name_1", "ns" : "das.cache", "background" : true }, { "v" : 2, "key" : { "file.name" : 1 }, "name" : "file.name_1", "ns" : "das.cache", "background" : true }]So, I do have dataset.name index, but I didn't use it in my query specs, instead I query using another index, and only ask to sort my record by dataset.name where I do have an index. Is it a bug or a feature on MongoDB?
Thanks,
Valentin.
Valentin Kuznetsov
2018年4月23日 上午11:09:452018/4/23
收件者：mongodb-user
I should probably add that I have:
MongoDB shell version v3.6.3
MongoDB server version: 3.6.3
Valentin Kuznetsov
2018年4月23日 下午2:37:432018/4/23
收件者：mongodb-user
It seems to me it is a bug: https://jira.mongodb.org/browse/SERVER-10472
Wan Bachtiar
2018年5月1日 晚上9:46:162018/5/1
收件者：mongodb-user
I’ve answered this question on StackOverflow: Failing to get results with sort query
Regards,
Wan.
回覆所有人
回覆作者
轉寄
0 則新訊息