[SERVER-62676] Add execution stats for $lookup pushed down into SBE Created: 14/Jan/22  Updated: 29/Oct/23  Resolved: 12/Apr/22

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 6.0.0-rc0

Type: Task Priority: Major - P3
Reporter: Eric Cox (Inactive) Assignee: Mohammad Dashti (Inactive)
Resolution: Fixed Votes: 0
Labels: sbe
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Documented
is documented by DOCS-15229 Investigate changes in SERVER-62676: ... Backlog
Problem/Incident
Backwards Compatibility: Fully Compatible
Sprint: QE 2022-04-04, QE 2022-04-18
Participants:
Linked BF Score: 184

 Description   

The owner of this ticket should evaluate the current set of exec stats that the classic DocumentSourceLookup exposes and determine if that set of metrics makes sense in sbe. 

If so we should support those metrics, or alternatively we should come up with a set of metrics that will be useful for the user when running queries with $lookup in sbe.



 Comments   
Comment by Githook User [ 11/Apr/22 ]

Author:

{'name': 'Mohammad Dashti', 'email': 'mdashti@gmail.com', 'username': 'mdashti'}

Message: SERVER-62676 Added execution stats for `$lookup` pushed down into SBE
Branch: master
https://github.com/mongodb/mongo/commit/bf35d99d2816e3815eb97308a287a5f6654f148e

Comment by Mohammad Dashti (Inactive) [ 05/Apr/22 ]

This script shows the current exec stats in both the classic DocumentSourceLookup and SBE:

(function() {
 
"use strict";
 
load("jstests/libs/sbe_util.js");      // For 'checkSBEEnabled()'.
load("jstests/libs/analyze_plan.js");  // For 'getAggPlanStages()' and 'hasRejectedPlans()'
 
const JoinAlgorithm = {
  Classic: 0,
  NLJ: 1,
  INLJ: 2,
};
 
///////////// Parameters
const enableSBE = true;
let joinAlgo = JoinAlgorithm.INLJ
 
///////////// Implementation
let dbName = 'explain_test';
 
const conn = MongoRunner.runMongod(enableSBE ? {
  setParameter: {featureFlagSBELookupPushdown: true, featureFlagSBELookupPushdownIndexJoin: true}
} : {});
 
try{
 
  assert.neq(null, conn, "mongod was unable to start up");
 
  var db = conn.getDB(dbName);
  assert.commandWorked(db.dropDatabase());
  db = conn.getDB(dbName);
 
  if (enableSBE && !checkSBEEnabled(db, ["featureFlagSBELookupPushdown"])) {
      jsTestLog("Skipping test because either the sbe lookup pushdown feature flag is disabled or" +
                " sbe itself is disabled");
      MongoRunner.stopMongod(conn);
      return;
  }
 
  assert.commandWorked(db.adminCommand({setParameter: 1, internalQueryForceClassicEngine: !enableSBE}));
 
  db.orders.insertMany( [
    { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
    { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
    { "_id" : 4, "item" : "almonds", "price" : 14, "quantity" : 2 },
    { "_id" : 5, "item" : "pecans", "price" : 22, "quantity" : 1 },
    { "_id" : 6, "item" : "cashews", "price" : 24, "quantity" : 1 },
  ] );
 
  db.inventory.insertMany( [
    { "_id" : 1, "sku" : "almonds", "description": "product 1", "instock" : 120 },
    { "_id" : 2, "sku" : "bread", "description": "product 2", "instock" : 80 },
    { "_id" : 3, "sku" : "cashews", "description": "product 3", "instock" : 60 },
    { "_id" : 4, "sku" : "pecans", "description": "product 4", "instock" : 70 },
    { "_id" : 5, "sku": "nuts", "description": "Incomplete" },
    { "_id" : 6 }
  ] )
 
  assert.commandWorked(db.inventory.dropIndexes());
  if(joinAlgo == JoinAlgorithm.NLJ) {
    assert.commandWorked(db.inventory.createIndex({'$**': 1}));
  } else if(joinAlgo == JoinAlgorithm.INLJ) {
    assert.commandWorked(db.inventory.createIndex({'sku': 1}));
  } else {
    // no index
  }
 
  let lookupExpRes = db.orders.explain('executionStats')
                              .aggregate( [
    {
      $lookup:
        {
          from: "inventory",
          localField: "item",
          foreignField: "sku",
          as: "inventory_docs"
        }
    }
  ] );
 
  function flip(dict){
    var ret = {};
    for(var key in dict){
      ret[dict[key]] = key;
    }
    return ret;
  }
 
  print("lookupExpRes (with SBE: " + (enableSBE?"enabled":"disabled") + " and joinAlgo: " + flip(JoinAlgorithm)[joinAlgo] + "):");
  printjson(lookupExpRes);
 
} finally {
  MongoRunner.stopMongod(conn);
}
 
print("Finished.");
 
}());

Currently, the classic DocumentSourceLookup exposes the following metrics:

  • totalDocsExamined: The total number of documents examined by the plan.
  • totalKeysExamined: The total number of index keys examined by the plan.
  • collectionScans: The number of collection scans that occur during execution. Note that more than one collection scan may happen during execution for $lookup execution.
  • indexesUsed: The names of each index used by the plan.

Whereas the LoopJoin in SBE (which handles nested loop join and index nested loop join algorithms) exposes the following metrics:

  • innerOpens: The number of times the inner side of the join (i.e., the foreign side) was opened or re-opened. It's usually the size of the local collection.
  • innerCloses: The number of times the inner side of the join was closed.

Next, will evaluate to what extent it's feasible to provide similar metrics available in the classic DocumentSourceLookup for the LoopJoin in SBE.

Generated at Thu Feb 08 05:55:46 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.