Code Summary: Partial Indexes

To create a partial index on the state field using a partialFilterExpression that only indexes documents with a population greater than or equal to 10,000, use the following command:

db.zips.createIndex(
  { state: 1 },
  { partialFilterExpression: { pop: { $gte: 10000 } } }
)

To test if the partial index is being used, run a query for documents with a state of California using the explain method. To return the most relevant results, use dot notation to return only the queryPlanner.winningPlan object, as shown in the following command:

db.zips.find({ state: "CA" }).explain().queryPlanner.winningPlan

Note that the partial index wasn’t used since there are documents in the collection with a state of California, that also have a population less than 10,000. As a result, the index was ineligible.

To test if the partial index is used on a different query, one that finds documents that have a population of less than or equal to 10,000, use the following command:

db.zips.find({ state: "CA", pop: { $gte: 10000 } }).explain().queryPlanner.winningPlan

Note that the partial index was used, since the requested documents match the partial filter expression.

To test the partial index one more time, run the following command to get the explain output and return just the queryPlanner.winningPlan object for a query that finds documents with a population of 12,000 or fewer:

db.zips.find({ state: "CA", pop: { $lte: 12000 } }).explain().queryPlanner.winningPlan

Note that the partial index wasn’t used since there are documents in the result set that have a population of less than 10,000.