Sparse index defined with partialFilterExpression is not used

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Done
    • Priority: Critical - P2
    • None
    • Affects Version/s: 3.2.8
    • Component/s: Index Maintenance
    • None
    • ALL
    • Hide

      1. Define an index which has a partialFilterExpression that checks the absence of a property with. {{ field: null }}
      2. Get the query plan a simple query that should use the index
      -> Notice that the index is not used.
      3. Attempt to do the same query but hint the index this time
      -> Notice that the index is now used.

      Expected: The index should be used at all times without hinting.

      Show
      1. Define an index which has a partialFilterExpression that checks the absence of a property with. {{ field: null }} 2. Get the query plan a simple query that should use the index -> Notice that the index is not used. 3. Attempt to do the same query but hint the index this time -> Notice that the index is now used. Expected: The index should be used at all times without hinting.
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      I have the following index defined on my collection:

                 {
               "v" : 1,
               "key" : {
                   "userId" : 1,
                   "groupId": 1,
                   "state": 1
               },
               "name" : "userId_1",
               "ns" : "app.messages",
               "partialFilterExpression" : {
                   "deletedAt" : null
               }
           },

      The winning plan for a simple query db.messages.find({ userId: 1 }) is:

             	"queryPlanner" : {
       		"plannerVersion" : 1,
       		"namespace" : "app.messages",
       		"indexFilterSet" : false,
       		"parsedQuery" : {
       			"userId" : {
       				"$eq" : "1"
       			}
       		},
       		"winningPlan" : {
       			"stage" : "COLLSCAN",
       			"filter" : {
       				"userId" : {
       					"$eq" : "1"
       				}
       			},
       			"direction" : "forward"
       		},
       		"rejectedPlans" : [ ]
       	},

      Hinting the index to the query makes it use it as expected:
      db.messages.find({ userId: 1 }).hint('userId_1')

            		"winningPlan" : {
       			"stage" : "FETCH",
       			"filter" : {
       				"userId" : {
       					"$eq" : "1"
       				}
       			},
       			"inputStage" : {
       				"stage" : "IXSCAN",
       				"keyPattern" : {
       					"userId" : 1,
       					"groupId" : 1,
       					"state" : 1
       				},
       				"indexName" : "userId_1_groupId_1_state_1",
       				"isMultiKey" : false,
       				"isUnique" : false,
       				"isSparse" : false,
       				"isPartial" : true,
       				"indexVersion" : 1,
       				"direction" : "forward",
       				"indexBounds" : {
       					"userId" : [
       						"[MinKey, MaxKey]"
       					],
       					"groupId" : [
       						"[MinKey, MaxKey]"
       					],
       					"state" : [
       						"[MinKey, MaxKey]"
       					]
       				}
       			}
       		},

      I've tried defining partialFilterExpression using $type or $gt but it won't work. deletedAt is a Date field that only exists for soft deleted documents documents

            Assignee:
            David Storch
            Reporter:
            Xavier Del Castillo
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: