Measure the index utilization for all collections in a database before/after an operation

measureIndexUsage.js

/**
 * measureIndexUsage(block)
 *
 * Measure the index utilization before/after a block is run. The block can contain
 * one to many operations, however the index utilization will only measure the collections
 * associated with the current `db` instance.
 *
 * NOTE: this script does not "isolate" the operation(s) run so if running in a busy
 * environment the results may represent other operations index utilization.
 *
 * @sample
 * MEASURE_INDEX_USAGE(function() {
 * ///////////////////////////////////////////////////
 * db.users.aggregate([
 *   { $match: { name: "Alex" } },
 *   { $lookup: {
 *       from: "orders",
 *       localField: "orders",
 *       foreignField: "order_id",
 *       as: "orders"
 *   }}
 * ]);
 * ////////////////////////////////////////////////////
 * });
 *
 * The output will be an object with the collection names as keys and the indexes
 * with counters (number of times used) before/after the block was executed.
 *
 * {
 *    "orders" : {
 *        "order_id_1" : "1 (14 total)"
 *    },
 *    "users" : {
 *        "name_1_orders_1" : "1 (19 total)"
 *    }
 * }
 *
 * @created 2020-03-20
 * @author  Alex Bevilacqua <[email protected]>
 */
function MEASURE_INDEX_USAGE(block) {
  var statsCmd = [ { $indexStats: { } } ];
  // measure index usage
  var statsBefore = {};
  db.getCollectionNames().forEach(function(c) {
      statsBefore[c] = {};
      db.getCollection(c).aggregate(statsCmd).forEach(function(d) {
          statsBefore[c][d.name] = d.accesses.ops * 1.0;
      });
  });

  block();

  // measure index usage again
  var stats = {};
  db.getCollectionNames().forEach(function(c) {
      stats[c] = {};
      db.getCollection(c).aggregate(statsCmd).forEach(function(d) {
          if (!statsBefore[c].hasOwnProperty(d.name)) {
              stats[c][d.name] = d.accesses.ops;
          } else if (statsBefore[c][d.name] != d.accesses.ops) {
              stats[c][d.name] = (d.accesses.ops - statsBefore[c][d.name]) + " (" + d.accesses.ops + " total)";
          }
      });
  });

  printjson(stats);
}

For example:

use test_reportOnIndexUsage;

function setup() {
    db.users.drop();
    db.orders.drop();
    db.users.insertMany([
    { name: "Alex", email: "[email protected]", orders: [ 1, 2, 3, 4, 5 ] },
    { name: "Bob", email: "[email protected]", orders: [ 1, 3, 4, 7 ] },
    { name: "Frank", email: "[email protected]", orders: [ 8 ] }
    ]);
    db.users.createIndex({ name: 1, orders: 1 })
    db.orders.insertMany([
    { order_id: 1, product: "Thing 1" },
    { order_id: 2, product: "Thing 2" },
    { order_id: 3, product: "Thing 3" },
    { order_id: 4, product: "Thing 4" },
    { order_id: 5, product: "Thing 5" },
    { order_id: 6, product: "Thing 6" },
    { order_id: 7, product: "Thing 7" },
    { order_id: 8, product: "Thing 8" },
    { order_id: 9, product: "Thing 9" }
    ]);
    db.orders.createIndex({ order_id: 1 });
}

function simpleLookup() {
    db.users.aggregate([
    { $match: { name: "Alex" } },
    { $lookup: {
        from: "orders",
        localField: "orders",
        foreignField: "order_id",
        as: "orders"
    }}
    ]);
}

setup();

MEASURE_INDEX_USAGE(function() {
///////////////////////////////////////////////////
    simpleLookup();
////////////////////////////////////////////////////
});