my query is :{ “EQUIPMENT.PARAMS.NAME”: /动力方式/ } and i had built a regular index on EQUIPMENT.PARAMS.NAME
the IXSCAN stage returned 744044 documents but the FETCH stage only returned 24265 documents ,the IXSCAN costed about 6s and the FETCH costed about 40s which exceeded my expect . it seemed that the IXSCAN stage did not filter enough documents
but when i queried { “EQUIPMENT.PARAMS.NAME”: /^动力方式/ } the IXSCAN stage only returned 24256 documents and fetch stage returned 24256 documents too , and the cost of
IXSCAN was only 68ms and the FETCH cost was 194ms
why the two similar ‘like’ queries performed totally different and how could i optimize the query { “EQUIPMENT.PARAMS.NAME”: /动力方式/ } ?
thanks in advance
The data structure is like this:
{
"_id": {
"$oid": "6858c4991bae3f6d12a7121f"
},
"ACCEPT_CODE": "SL2021110355-FD",
"APPLICATION_COMPANY": "化纤有限公司",
"APPROVAL_DATE": "2021-11-22",
"APPROVER": "皓",
"AUDITOR": "黎",
"AUDIT_DATE": "2021-11-21",
"BUSINESS_TYPE": "机电类",
"CERTIFICATE_URL": null,
"CHECKER": "魏",
"CHECK_DATE": "2021-11-21",
"DEPARTMENT": "安全事业部",
"END_DATE": "2021-11-02",
"ENGLISH_CERTIFICATE_URL": null,
"ENGLISH_ORIGINAL_RECORD_URL": null,
"ENGLISH_REPORT_URL": null,
"ENTRY_DATE": "2021-11-20",
"EQUIPMENT": {
"EQUIPMENT_CODE": "5110103412020G9514",
"EQUIPMENT_ID": "100053846",
"FACTORY_CODE": "020402F7186",
"FIRST_CATALOG_CODE": "5000",
"FIRST_CATALOG_NAME": "专用",
"INTERNAL_CODE": "未编",
"MAINTENANCE_COMPANY": null,
"MANUFACTURE_COMPANY": "有限公司",
"MODEL": " CPC型4.0t",
"NAME": "叉车",
"PARAMS": [
{
"CODE": "P50001002",
"NAME": "动力方式",
"SORTING": 100,
"VALUE": "内燃机"
},
{
"CODE": "S50001008",
"NAME": "改造日期",
"SORTING": 80,
"VALUE": "—"
},
{
"CODE": "S50001002",
"NAME": "防爆使用场所",
"SORTING": 10,
"VALUE": "-"
},
{
"CODE": "S50001003",
"NAME": "温度组别",
"SORTING": 20,
"VALUE": "-"
},
{
"CODE": "S50001004",
"NAME": "空载最大起升高度",
"SORTING": 30,
"VALUE": "4000"
},
{
"CODE": "S50001005",
"NAME": "传动方式",
"SORTING": 40,
"VALUE": "机械传动"
},
{
"CODE": "S50001001",
"NAME": "改造许可证编号",
"SORTING": 0,
"VALUE": "-"
},
{
"CODE": "S500003",
"NAME": "使用区域",
"SORTING": 90,
"VALUE": "仅限于工厂厂区"
},
{
"CODE": "S50001007",
"NAME": "改造单位名称",
"SORTING": 70,
"VALUE": "-"
},
{
"CODE": "P50002007",
"NAME": "车牌编号",
"SORTING": 5,
"VALUE": "场内浙F-83865"
},
{
"CODE": "S5000036",
"NAME": "驱动方式",
"SORTING": 0,
"VALUE": "前驱"
},
{
"CODE": "P50001001",
"NAME": "燃料类型",
"SORTING": 50,
"VALUE": "5000100102"
},
{
"CODE": "P50002004",
"NAME": "额定载重量(kg\\人)",
"SORTING": 20,
"VALUE": "4000"
},
{
"CODE": "P50002002",
"NAME": "车架编号",
"SORTING": 60,
"VALUE": "020402F7186"
},
{
"CODE": "P50002005",
"NAME": "空车重量(kg)",
"SORTING": 40,
"VALUE": "6300"
},
{
"CODE": "P50002003",
"NAME": "发动机（电机）编号",
"SORTING": 70,
"VALUE": "4C6-85U32☆Q200473081G☆"
},
{
"CODE": "S500032",
"NAME": "整车防爆标志",
"SORTING": 32,
"VALUE": "-"
},
{
"CODE": "S500031",
"NAME": "防爆车辆级别",
"SORTING": 0,
"VALUE": "-"
},
{
"CODE": "P50002008",
"NAME": "运行速度（最高时速）(km/h)",
"SORTING": 80,
"VALUE": "18.4"
},
{
"CODE": "P50002001",
"NAME": "厂牌型号",
"SORTING": 200,
"VALUE": "CPC型4.0t"
},
{
"CODE": "S500036",
"NAME": "是否防爆",
"SORTING": 0,
"VALUE": "否"
}
],
"SECOND_CATALOG_CODE": "5100",
"SECOND_CATALOG_NAME": "工业车辆",
"THIRD_CATALOG_CODE": "5110",
"THIRD_CATALOG_NAME": "[5110]叉车",
"USING_COMPANY": "化纤有限公司",
"USING_COMPANY_CODE": "91330400MA2B85HP0Y"
},
"KEYBOARDER": "马",
"MAIN_SURVEYOR": "魏",
"ORIGINAL_RECORD_URL": "/upload/2021/11/02/5000/SL2021110355-FD/4657144/CJD20214770-4657144-JL-1.pdf",
"PARTICIPANT": "何",
"REPORT_CODE": "CJD20214770",
"REPORT_ID": "O4657144",
"REPORT_URL": "/upload/2021/11/02/5000/SL2021110355-FD/4657144/CJD20214770-4657144-BG-1-SEAL0.pdf",
"SEGMENT_REPORTS": [
{
"AUDITOR": "陈",
"AUDIT_DATE": "2021-11-21",
"CHECKER": "魏",
"CHECK_DATE": "2021-11-21",
"ENTRY_DATE": "2021-11-20",
"KEYBOARDER": "马",
"NAME": "基本信息",
"REPORT_ID": "O4657144",
"SEGMENT_REPORT_ID": "O4657145",
"SORTING": 1000,
"SURVEYOR": "魏"
},
{
"AUDITOR": "陈",
"AUDIT_DATE": "2021-11-21",
"CHECKER": "魏",
"CHECK_DATE": "2021-11-21",
"ENTRY_DATE": "2021-11-20",
"KEYBOARDER": "马",
"NAME": "内容",
"REPORT_ID": "O4657144",
"SEGMENT_REPORT_ID": "O4657146",
"SORTING": 2000,
"SURVEYOR": "魏"
},
{
"AUDITOR": "陈",
"AUDIT_DATE": "2021-11-21",
"CHECKER": "魏",
"CHECK_DATE": "2021-11-21",
"ENTRY_DATE": "2021-11-20",
"KEYBOARDER": "马",
"NAME": "建议",
"REPORT_ID": "O4657144",
"SEGMENT_REPORT_ID": "O4657147",
"SORTING": 99997,
"SURVEYOR": "魏"
}
],
"START_DATE": "2021-11-02",
"TASK_CODE": "SL2021110355-FD-001",
"TEMPLATE": "报告"
}
the executionStats of the query { “EQUIPMENT.PARAMS.NAME”: /动力方式/ } are:
{
"explainVersion": "1",
"queryPlanner": {
"namespace": "zjtj.zjtj_report_info",
"indexFilterSet": false,
"parsedQuery": {
"EQUIPMENT.PARAMS.NAME": {
"$regex": "动力方式"
}
},
"queryHash": "30109082",
"planCacheKey": "5679EDF7",
"optimizationTimeMillis": 9394,
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"winningPlan": {
"stage": "FETCH",
"filter": {
"EQUIPMENT.PARAMS.NAME": {
"$regex": "动力方式"
}
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"EQUIPMENT.PARAMS.NAME": 1
},
"indexName": "EQUIPMENT.PARAMS.NAME_1",
"isMultiKey": true,
"multiKeyPaths": {
"EQUIPMENT.PARAMS.NAME": [
"EQUIPMENT.PARAMS"
]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"EQUIPMENT.PARAMS.NAME": [
"[\"\", {})",
"[/动力方式/, /动力方式/]"
]
}
}
},
"rejectedPlans": [
{
"stage": "FETCH",
"filter": {
"EQUIPMENT.PARAMS.NAME": {
"$regex": "动力方式"
}
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"EQUIPMENT.PARAMS.NAME": 1,
"EQUIPMENT.PARAMS.VALUE": 1
},
"indexName": "EQUIPMENT.PARAMS.NAME_1_EQUIPMENT.PARAMS.VALUE_1",
"isMultiKey": true,
"multiKeyPaths": {
"EQUIPMENT.PARAMS.NAME": [
"EQUIPMENT.PARAMS"
],
"EQUIPMENT.PARAMS.VALUE": [
"EQUIPMENT.PARAMS"
]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"EQUIPMENT.PARAMS.NAME": [
"[\"\", {})",
"[/动力方式/, /动力方式/]"
],
"EQUIPMENT.PARAMS.VALUE": [
"[MinKey, MaxKey]"
]
}
}
}
]
},
"executionStats": {
"executionSuccess": true,
"nReturned": 24265,
"executionTimeMillis": 108413,
"totalKeysExamined": 35441052,
"totalDocsExamined": 744044,
"executionStages": {
"stage": "FETCH",
"filter": {
"EQUIPMENT.PARAMS.NAME": {
"$regex": "动力方式"
}
},
"nReturned": 24265,
"executionTimeMillisEstimate": 53299,
"works": 35441053,
"advanced": 24265,
"needTime": 35416787,
"needYield": 0,
"saveState": 37571,
"restoreState": 37571,
"isEOF": 1,
"docsExamined": 744044,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 744044,
"executionTimeMillisEstimate": 9843,
"works": 35441053,
"advanced": 744044,
"needTime": 34697008,
"needYield": 0,
"saveState": 37571,
"restoreState": 37571,
"isEOF": 1,
"keyPattern": {
"EQUIPMENT.PARAMS.NAME": 1
},
"indexName": "EQUIPMENT.PARAMS.NAME_1",
"isMultiKey": true,
"multiKeyPaths": {
"EQUIPMENT.PARAMS.NAME": [
"EQUIPMENT.PARAMS"
]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"EQUIPMENT.PARAMS.NAME": [
"[\"\", {})",
"[/动力方式/, /动力方式/]"
]
},
"keysExamined": 35441052,
"seeks": 1,
"dupsTested": 35441052,
"dupsDropped": 34697008
}
}
},
"command": {
"find": "zjtj_report_info",
"filter": { "EQUIPMENT.PARAMS.NAME": {} },
"skip": 0,
"limit": 0,
"maxTimeMS": 60000,
"$db": "zjtj"
},
"serverInfo": {
"host": "mongodb",
"port": 27017,
"version": "7.0.21",
"gitVersion": "a47b62aff2bae1914085c3ef1d90fc099acf000c"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
"internalQueryFrameworkControl": "forceClassicEngine"
},
"ok": 1
}
the executeStats of query { “EQUIPMENT.PARAMS.NAME”: /^动力方式/ } are:
{
"explainVersion": "1",
"queryPlanner": {
"namespace": "zjtj.zjtj_report_info",
"indexFilterSet": false,
"parsedQuery": {
"EQUIPMENT.PARAMS.NAME": {
"$regex": "^动力方式"
}
},
"queryHash": "30109082",
"planCacheKey": "5679EDF7",
"optimizationTimeMillis": 8,
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"winningPlan": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"EQUIPMENT.PARAMS.NAME": 1
},
"indexName": "EQUIPMENT.PARAMS.NAME_1",
"isMultiKey": true,
"multiKeyPaths": {
"EQUIPMENT.PARAMS.NAME": [
"EQUIPMENT.PARAMS"
]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"EQUIPMENT.PARAMS.NAME": [
"[\"动力方式\", \"动力方弐\")",
"[/^动力方式/, /^动力方式/]"
]
}
}
},
"rejectedPlans": [
{
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"EQUIPMENT.PARAMS.NAME": 1,
"EQUIPMENT.PARAMS.VALUE": 1
},
"indexName": "EQUIPMENT.PARAMS.NAME_1_EQUIPMENT.PARAMS.VALUE_1",
"isMultiKey": true,
"multiKeyPaths": {
"EQUIPMENT.PARAMS.NAME": [
"EQUIPMENT.PARAMS"
],
"EQUIPMENT.PARAMS.VALUE": [
"EQUIPMENT.PARAMS"
]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"EQUIPMENT.PARAMS.NAME": [
"[\"动力方式\", \"动力方弐\")",
"[/^动力方式/, /^动力方式/]"
],
"EQUIPMENT.PARAMS.VALUE": [
"[MinKey, MaxKey]"
]
}
}
}
]
},
"executionStats": {
"executionSuccess": true,
"nReturned": 24265,
"executionTimeMillis": 414,
"totalKeysExamined": 24266,
"totalDocsExamined": 24265,
"executionStages": {
"stage": "FETCH",
"nReturned": 24265,
"executionTimeMillisEstimate": 322,
"works": 24267,
"advanced": 24265,
"needTime": 1,
"needYield": 0,
"saveState": 28,
"restoreState": 28,
"isEOF": 1,
"docsExamined": 24265,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 24265,
"executionTimeMillisEstimate": 43,
"works": 24267,
"advanced": 24265,
"needTime": 1,
"needYield": 0,
"saveState": 28,
"restoreState": 28,
"isEOF": 1,
"keyPattern": {
"EQUIPMENT.PARAMS.NAME": 1
},
"indexName": "EQUIPMENT.PARAMS.NAME_1",
"isMultiKey": true,
"multiKeyPaths": {
"EQUIPMENT.PARAMS.NAME": [
"EQUIPMENT.PARAMS"
]
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"EQUIPMENT.PARAMS.NAME": [
"[\"动力方式\", \"动力方弐\")",
"[/^动力方式/, /^动力方式/]"
]
},
"keysExamined": 24266,
"seeks": 2,
"dupsTested": 24265,
"dupsDropped": 0
}
}
},
"command": {
"find": "zjtj_report_info",
"filter": { "EQUIPMENT.PARAMS.NAME": {} },
"skip": 0,
"limit": 0,
"maxTimeMS": 60000,
"$db": "zjtj"
},
"serverInfo": {
"host": "mongodb",
"port": 27017,
"version": "7.0.21",
"gitVersion": "a47b62aff2bae1914085c3ef1d90fc099acf000c"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
"internalQueryFrameworkControl": "forceClassicEngine"
},
"ok": 1
}