Partial and Sparse index in MongoDB

Partial Index

Partial index only index the documents in a collection that meet a specified filter expression. By indexing a subset of the documents in a collection, partial indexes have lower storage requirements and reduced performance costs for index creation and maintenance.

Explaining partial index in MongoDB

Create partial indexes using createIndex and partialFilterExpression

To create a partial index, use the db.collection.createIndex() method with the partialFilterExpression option. The partialFilterExpression option accepts a document that specifies the filter condition using:

  • equality expressions (i.e. field: value or using the $eq operator)
  • $exists
  • $gt, $gte, $lt, $lte
  • $type
  • $and operator at the top-level only

Creating a Partial Index

For example, the following operation creates an index that indexes only the documents with a rating field greater than 4.

db.articles.createIndex(
   { author: 1 },
   { partialFilterExpression: { rating: { $gt: 4 } } }
)

Testing partial index

Inserting Data

db.articles.insert([
	{author: "yami code", rating: 5, name: "article 1"},
	{author: "yami code", rating: 8, name: "article 2"},
	{author: "john doe", rating: 5, name: "article 3"},
	{author: "john doe", rating: 3, name: "article 4"},
])

Explaining result of partial index using explain and find

Example of Using partial index
db.articles.explain().find( { author: "yami code", rating: { $gt: 4 } } )
db.articles.explain().find( { author: "Yami Code", rating:  8 } )
Result

As showen in the result bellow MongoDB did use the index created in both scenarios, so in general MongoDB will not ony use the index when the condition is the same as defined in createIndex but also when the condition matches somehow the created one. In our case a rating that is equal to 8 is by default greater than 4.


	...
	"executionStages" : {
            "stage" : "FETCH",
            ...
            "inputStage" :{
                "stage" : "IXSCAN"
                ...
           	}
    }
Example of not using partial index
db.articles.explain().find( { author: "Yami Code", rating: { $lt: 8 } } )
db.articles.explain().find( { author: "Yami Code" } )
Result

In those cases if MongoDB uses the partial index it will lose the data where the rating is less than 4.


	{
		...
		"stage" : "COLLSCAN"
	}

Sparse Index

Sparse indexes only contain entries for documents that have the indexed field, even if the index field contains a null value. The index skips over any document that is missing the indexed field. The index is “sparse” because it does not include all documents of a collection. By contrast, non-sparse indexes contain all documents in a collection, storing null values for those documents that do not contain the indexed field.

You can combine the sparse index option with the unique index option to prevent inserting documents that have duplicate values for the indexed field(s) and skip indexing documents that lack the indexed field(s).

Creating a Sparse Index using createIndex and sparse

db.articles.createIndex( { name: 1 }, { sparse: true } );

Partial indexes represent a superset of the functionality offered by sparse indexes and should be preferred over sparse indexes.

Using Partial Index to create Sparse Index

Sparse indexes select documents to index solely based on the existence of the indexed field, or for compound indexes, the existence of the indexed fields. Partial indexes determine the index entries based on the specified filter. The filter can include fields other than the index keys and can specify conditions other than just an existence check. For example, a partial index can implement the same behavior as a sparse index:

db.collection.createIndex(
   { name: 1 },
   { partialFilterExpression: { name: { $exists: true } } }
)