[SERVER-31122] Count by regex match on index is not a covered query Created: 18/Sep/17  Updated: 09/Oct/17  Resolved: 18/Sep/17

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.4.7
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Alex Hu Assignee: Kelsey Schubert
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-27822 Count plans sometimes don't need a FETCH Closed
Operating System: ALL
Steps To Reproduce:

Use case

    use db1
    db.foo.insert({"keyword": "abc"})
    db.foo.insert({"keyword": "acd"})
    db.foo.createIndex({"keyword":1})
    db.bar.createIndex(index)
    db.foo.explain("executionStats").count({"keyword":/a/i})

Execution stats

    "executionStats" : {
        "nReturned" : 0,
        "totalKeysExamined" : 2,
        "totalDocsExamined" : 2
    }

The confusion is:

Why examine the documents even if the index has covered the querying field?

What we found is:

There is still a FETCH stage is used. And I think this stage can be optimized by removing.

Details:

  {
        "nReturned" : 0,
        "totalKeysExamined" : 2,
        "totalDocsExamined" : 2,
        "executionStages" : {
            "stage" : "COUNT",
            "nReturned" : 0,
            "nCounted" : 2,
            "inputStage" : {
                "stage" : "FETCH",
                "nReturned" : 2,
                "docsExamined" : 2,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 2,
                    "indexName" : "keyword_1",
                    "keysExamined" : 2
                }
            }
        }
    }

Participants:

 Description   

When all of the querying fields are defined in the index, there is no need to fetch documents for a count() query by regex match on that index.



 Comments   
Comment by Kelsey Schubert [ 18/Sep/17 ]

Hi huyingming,

Thank you for the detailed report. This issue has been corrected by SERVER-27822 and will be included in MongoDB 3.6. See below for an example of the output run against our latest development release:

(3.5.13) > db.foo.explain("executionStats").count({"keyword":/a/i})
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "db1.foo",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"keyword" : {
				"$regex" : "a",
				"$options" : "i"
			}
		},
		"winningPlan" : {
			"stage" : "COUNT",
			"inputStage" : {
				"stage" : "IXSCAN",
				"filter" : {
					"keyword" : {
						"$regex" : "a",
						"$options" : "i"
					}
				},
				"keyPattern" : {
					"keyword" : 1
				},
				"indexName" : "keyword_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"keyword" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"keyword" : [
						"[\"\", {})",
						"[/a/i, /a/i]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 0,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 2,
		"totalDocsExamined" : 0,
		"executionStages" : {
			"stage" : "COUNT",
			"nReturned" : 0,
			"executionTimeMillisEstimate" : 0,
			"works" : 3,
			"advanced" : 0,
			"needTime" : 2,
			"needYield" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"invalidates" : 0,
			"nCounted" : 2,
			"nSkipped" : 0,
			"inputStage" : {
				"stage" : "IXSCAN",
				"filter" : {
					"keyword" : {
						"$regex" : "a",
						"$options" : "i"
					}
				},
				"nReturned" : 2,
				"executionTimeMillisEstimate" : 0,
				"works" : 3,
				"advanced" : 2,
				"needTime" : 0,
				"needYield" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"invalidates" : 0,
				"keyPattern" : {
					"keyword" : 1
				},
				"indexName" : "keyword_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"keyword" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"keyword" : [
						"[\"\", {})",
						"[/a/i, /a/i]"
					]
				},
				"keysExamined" : 2,
				"seeks" : 1,
				"dupsTested" : 0,
				"dupsDropped" : 0,
				"seenInvalidated" : 0
			}
		}
	}
}

Kind regards,
Kelsey

Comment by Asya Kamsky [ 18/Sep/17 ]

Note that this case was fixed for 3.5.9 (see below on 3.5.9) likely as part of SERVER-27822. This example was not quite the same as SERVER-29967 as that one only applies to doing filtering on FETCH when an index is multikey" and this index isn't multikey.

(3.5.13) > db.foo.explain("executionStats").count({"keyword":/a/i})
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "db1.foo",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"keyword" : {
				"$regex" : "a",
				"$options" : "i"
			}
		},
		"winningPlan" : {
			"stage" : "COUNT",
			"inputStage" : {
				"stage" : "IXSCAN",
				"filter" : {
					"keyword" : {
						"$regex" : "a",
						"$options" : "i"
					}
				},
				"keyPattern" : {
					"keyword" : 1
				},
				"indexName" : "keyword_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"keyword" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"keyword" : [
						"[\"\", {})",
						"[/a/i, /a/i]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 0,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 2,
		"totalDocsExamined" : 0,
		"executionStages" : {
			"stage" : "COUNT",
			"nReturned" : 0,
			"executionTimeMillisEstimate" : 0,
			"works" : 3,
			"advanced" : 0,
			"needTime" : 2,
			"needYield" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"invalidates" : 0,
			"nCounted" : 2,
			"nSkipped" : 0,
			"inputStage" : {
				"stage" : "IXSCAN",
				"filter" : {
					"keyword" : {
						"$regex" : "a",
						"$options" : "i"
					}
				},
				"nReturned" : 2,
				"executionTimeMillisEstimate" : 0,
				"works" : 3,
				"advanced" : 2,
				"needTime" : 0,
				"needYield" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"invalidates" : 0,
				"keyPattern" : {
					"keyword" : 1
				},
				"indexName" : "keyword_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"keyword" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"keyword" : [
						"[\"\", {})",
						"[/a/i, /a/i]"
					]
				},
				"keysExamined" : 2,
				"seeks" : 1,
				"dupsTested" : 0,
				"dupsDropped" : 0,
				"seenInvalidated" : 0
			}
		}
	},
	"serverInfo" : {
		"host" : "Asyas-MacBook-Pro.local",
		"port" : 27017,
		"version" : "3.5.13",
		"gitVersion" : "52bbaa007cd84631d6da811d9a05b59f2dfad4f3"
	},
	"ok" : 1
}

Generated at Thu Feb 08 04:26:03 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.