New in MongoDB 4.4: Hidden Index

MongoDB 4.4 Hidden Index

MongoDB 4.4 Hidden IndexWe already presented a previous article on the index types available on MongoDB. MongoDB 4.4 was released a few months ago and introduced a new interesting feature: the hidden index.

This feature is also available on Percona Server for MongoDB 4.4. In this article, we’ll see what it is.

What is a Hidden Index

A hidden index is simply a regular index that is not visible to the query planner. When evaluating the execution plans, MongoDB ignores such kind of indexes.

Building an index on MongoDB is quite expensive, in particular for large collections or when you don’t have enough available memory. Disabling indexes is useful for testing different execution plans without dropping any index for real. You can hide or unhide an index at any time at no cost for the database.

How it Works

We use a sample “restaurants” collection having documents like the following:

> db.restaurants.find().skip(100).limit(1).pretty()
{
    "_id" : ObjectId("5f9fec91271d598d55737eb1"),
    "address" : {
        "building" : "842",
        "coord" : [
            -73.97063700000001,
            40.751495
        ],
        "street" : "2 Avenue",
        "zipcode" : "10017"
    },
    "borough" : "Manhattan",
    "cuisine" : "American ",
    "grades" : [
        {
            "date" : ISODate("2014-07-22T00:00:00Z"),
            "grade" : "A",
            "score" : 6
        },
        {
            "date" : ISODate("2013-05-28T00:00:00Z"),
            "grade" : "A",
            "score" : 2
        },
        {
            "date" : ISODate("2012-05-29T00:00:00Z"),
            "grade" : "A",
            "score" : 8
        },
        {
            "date" : ISODate("2012-01-05T00:00:00Z"),
            "grade" : "A",
            "score" : 9
        },
        {
            "date" : ISODate("2011-08-10T00:00:00Z"),
            "grade" : "B",
            "score" : 24
        }
    ],
    "name" : "Keats Restaurant",
    "restaurant_id" : "40365288"
}

the only index available is _id:

> db.restaurants.getIndexes()
[ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_" }

We can create a couple of additional indexes.

> db.restaurants.createIndex( { borough: 1 } )
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}
> db.restaurants.createIndex( { cuisine: 1 , borough: 1 } )
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 2,
    "numIndexesAfter" : 3,
    "ok" : 1
}

Run a simple query to find all the Italian restaurants in Manhattan and let’s see the winning plan.

> db.restaurants.find( { cuisine:"Italian", borough:"Manhattan"} ).explain()
{
...
...
"winningPlan" : {
    "stage" : "FETCH",
    "inputStage" : {
        "stage" : "IXSCAN",
        "keyPattern" : {
            "cuisine" : 1,
            "borough" : 1
        },
        "indexName" : "cuisine_1_borough_1",
        "isMultiKey" : false,
        "multiKeyPaths" : {
            "cuisine" : [ ],
           "borough" : [ ]
        },
        "isUnique" : false,
        "isSparse" : false,
        "isPartial" : false,
        "indexVersion" : 2,
        "direction" : "forward",
        "indexBounds" : {
            "cuisine" : [
                "[\"Italian\", \"Italian\"]"
            ],
            "borough" : [
                "[\"Manhattan\", \"Manhattan\"]"
            ]
        }
    }
},
...

The { cuisine: 1, borough: 1 } is used. Let’s hide it and execute the query again.

To hide an index you just need to run hideIndex() method.

> db.restaurants.hideIndex( { cuisine: 1, borough: 1 } )
{
    "ok" : 0,
    "errmsg" : "not authorized on restaurants to execute command { collMod: \"restaurants\", index: { keyPattern: { cuisine: 1.0, borough: 1.0 }, hidden: true }, lsid: { id: UUID(\"25d9c931-3dff-49bb-8889-fff3c85c5506\") }, $db: \"restaurants\" }",
    "code" : 13,
    "codeName" : "Unauthorized"
}

Ops, if you get this error it means you need to set setFeatureCompatibilityVersion: “4.4”. This is a requirement in order to be able to hide indexes. So, remember to set the parameter.

> use admin
switched to db admin
> db.adminCommand( { setFeatureCompatibilityVersion: "4.4" } )
{ "ok" : 1 }
> use restaurants
switched to db restaurants
> db.restaurants.hideIndex( { cuisine: 1, borough: 1 } )
{ "hidden_old" : false, "hidden_new" : true, "ok" : 1 }

Let’s see the indexes from the collection:

> db.restaurants.getIndexes()
[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_"
    },
    {
        "v" : 2,
        "key" : {
            "borough" : 1
        },
        "name" : "borough_1"
    },
    {
        "v" : 2,
        "key" : {
            "cuisine" : 1,
            "borough" : 1
        },
        "name" : "cuisine_1_borough_1",
        "hidden" : true
    }
]

We can notice the index has “hidden” : true. Now try again the query explain:

> db.restaurants.find( { cuisine:"Italian", borough:"Manhattan"} ).explain()
{
...
...
"winningPlan" : {
    "stage" : "FETCH",
    "filter" : {
        "cuisine" : {  
            "$eq" : "Italian"
        }
    },
    "inputStage" : {
        "stage" : "IXSCAN",
        "keyPattern" : {
            "borough" : 1
        },
        "indexName" : "borough_1",
        "isMultiKey" : false,
        "multiKeyPaths" : {
            "borough" : [ ]
        },
        "isUnique" : false,
        "isSparse" : false,
        "isPartial" : false,
        "indexVersion" : 2,
        "direction" : "forward",
        "indexBounds" : {
            "borough" : [
                "[\"Manhattan\", \"Manhattan\"]"
            ]
        }
    }
},
"rejectedPlans" : []
...
...

We can see that MongoDB uses the other index { borough: 1 }. The hidden one is actually invisible. It is neither considered in the rejected plans. To make it visible again we need to use the unhideIndex() method:

> db.restaurants.unhideIndex( { cuisine: 1, borough: 1 } )
{ "hidden_old" : true, "hidden_new" : false, "ok" : 1 }

Notes on Hidden Indexes

  • You cannot hide the _id index.
  • During the writes, MongoDB maintains the hidden indexes the same as any regular index.
  • The hidden indexes are immediately available after unhidden.
  • A unique index provides uniqueness constraint even when hidden.
  • A TLL index drops documents even when hidden.

Conclusion

The new hidden index in MongoDB 4.4 is a useful and really easy-to-use feature. It provides the capability to run tests against index usability and to evaluate alternative execution plans, without the need of dropping and recreating indexes.


by Corrado Pandiani via Percona Database Performance Blog

Comments

Popular posts from this blog