Mongosh script for analyzing the idx 146 query (generated by Claude): // Mongosh script for the TPC-H idx 146 query that fails plan_stability_subjoin_cardinality_md.js // after SERVER-125579 makes the 2nd pipeline-form $lookup eligible. // // Usage: // 1. Start resmoke with pauseAfterPopulate so the TPC-H data loads and the mongod stays up: // buildscripts/resmoke.py run --suites=query_golden_join_optimization_plan_stability \ // --runAllFeatureFlagTests \ // --pauseAfterPopulate // 2. Connect mongosh to the paused mongod (typically port 20000 for resmoke). // mongosh --port 20000 // 3. Load this file inside mongosh: // load( "<path>/idx146.js" ) // 4. Use the helpers printed at the bottom. const TPCH_DB = "plan_stability_subjoin_cardinality_md" ; const tpch = db.getSiblingDB(TPCH_DB); // Verbatim from jstests/query_golden/test_inputs/plan_stability_pipelines_tpch_fuzzed.js, // entry with idx: 146. const idx146Pipeline = [ { $lookup: { from: "partsupp" , localField: "p_partkey" , foreignField: "ps_partkey" , pipeline: [ {$match: {$and: [{ps_comment: {$regex: new RegExp( "^ am" )}}]}}, ], as: "partsupp" , }, }, {$unwind: "$partsupp" }, { $lookup: { from: "lineitem" , localField: "partsupp.ps_partkey" , foreignField: "l_partkey" , pipeline: [ { $match: { $and: [ {l_receiptdate: {$gte: new Date( "1997-03-31T00:00:00Z" )}}, {l_linenumber: {$lt: 2}}, ], }, }, ], as: "lineitem" , }, }, {$unwind: "$lineitem" }, // { // $match: { // $nor: [ // { "lineitem.l_extendedprice" : {$gte: 1236.32}}, // {p_size: {$gt: 44}}, // { "lineitem.l_shipdate" : {$eq: new Date( "1998-06-20T00:00:00Z" )}}, // { "lineitem.l_partkey" : 12202}, // { "lineitem.l_shipdate" : {$lt: new Date( "1997-11-20T00:00:00Z" )}}, // ], // }, // }, ]; // Same pipeline, prefixed with a $_internalJoinHint stage that forces the join order // (partsupp ⋈ lineitem) ⋈ part — the plan shape that produces the all-dotted multi-predicate // case at the outer EMBEDDING node. Useful for demonstrating that the test reconstructor's // failure depends purely on plan shape, not on absorbed-filter handling. // // NodeId convention assumed: // 0 = part (leading collection) // 1 = partsupp (1st $lookup's foreign) // 2 = lineitem (2nd $lookup's foreign) // If the plan shape doesn't match the expectation, re-run with {level: 0, mode: "ALL" } (no node // constraint) and inspect the slotBasedPlan for the actual NodeId-to-collection mapping. const hintedIdx146Pipeline = [ { $_internalJoinHint: { perSubsetLevelMode: [ {level: NumberInt(0), mode: "CHEAPEST" , hint: {node: NumberInt(1)}}, {level: NumberInt(1), mode: "CHEAPEST" , hint: {node: NumberInt(2), isLeftChild: false }}, {level: NumberInt(2), mode: "CHEAPEST" , hint: {node: NumberInt(0), isLeftChild: false }}, ], }, }, ...idx146Pipeline, ]; function runIdx146() { return tpch.part.aggregate(idx146Pipeline).itcount(); } function explainIdx146(verbosity = "queryPlanner" ) { return tpch.part.explain(verbosity).aggregate(idx146Pipeline); } function runIdx146Hinted() { return tpch.part.aggregate(hintedIdx146Pipeline).itcount(); } function explainIdx146Hinted(verbosity = "queryPlanner" ) { return tpch.part.explain(verbosity).aggregate(hintedIdx146Pipeline); } // Walk the explain tree and invoke `callback(node, depth)` for every node whose stage // name contains EMBEDDING. function walkEmbeddingNodes(node, callback, depth = 0) { if (!node || typeof node !== "object" ) return ; if (node.stage && typeof node.stage === "string" && node.stage.includes( "EMBEDDING" )) { callback(node, depth); } for ( const k of Object .keys(node)) { const v = node[k]; if (Array.isArray(v)) v.forEach((c) => walkEmbeddingNodes(c, callback, depth + 1)); else if ( typeof v === "object" ) walkEmbeddingNodes(v, callback, depth + 1); } } // Print every EMBEDDING node's join predicates from the given explain result. Handles both // the SBE shape (stages[0].$cursor.queryPlanner.winningPlan.queryPlan) and a direct queryPlan. function printJoinPredicates(explainResult) { const root = explainResult?.stages?.[0]?.$cursor?.queryPlanner?.winningPlan?.queryPlan || explainResult?.queryPlanner?.winningPlan?.queryPlan || explainResult; let seen = 0; walkEmbeddingNodes(root, (node) => { seen += 1; const pad = " " ; print( "--- EMBEDDING node " + seen + " ---" ); print(pad + "stage: " + node.stage); print(pad + "planNodeId: " + node.planNodeId); print(pad + "leftEmbeddingField: " + node.leftEmbeddingField); print(pad + "rightEmbeddingField: " + node.rightEmbeddingField); print(pad + "joinPredicates: " + JSON.stringify(node.joinPredicates)); if (Array.isArray(node.inputStages)) { print(pad + "inputStages: " + node.inputStages.map((s) => s.stage + "(" + s.planNodeId + ")" ).join( ", " )); } }); if (seen === 0) print( "(no EMBEDDING nodes found — join optimization may not have fired)" ); } // Recursively build a one-line summary of the join tree from an explain plan node. // EMBEDDING joins render as `(left ⋈METHOD[predicates] right)` . Leaves render as the // collection name (from `nss` ). Wrapper stages (PROJECTION, FETCH, IXSCAN with a child) // are skipped through. function summarizeNode(node) { if (!node || typeof node !== "object" ) return "?" ; const stage = node.stage; if ( typeof stage === "string" && stage.includes( "EMBEDDING" )) { const method = stage.includes( "HASH" ) ? "HJ" : stage.includes( "INDEXED" ) ? "INLJ" : "NLJ" ; const left = summarizeNode(node.inputStages?.[0]); const right = summarizeNode(node.inputStages?.[1]); const preds = (node.joinPredicates || []).join( " ∧ " ); return `(${left} ⋈${method}[${preds}] ${right})` ; } // Leaf: COLLSCAN/IXSCAN/FETCH usually carries the namespace directly. if (node.nss) { return node.nss.split( "." ).pop(); } // Wrapper: PROJECTION_SIMPLE / PROJECTION_DEFAULT etc. descend through inputStage. if (node.inputStage) return summarizeNode(node.inputStage); if (Array.isArray(node.inputStages) && node.inputStages.length === 1) { return summarizeNode(node.inputStages[0]); } return `[${stage || "unknown" }]` ; } // Print a one-line shape summary of the plan tree, e.g. // ((partsupp ⋈HJ[ps_partkey = l_partkey] lineitem) ⋈HJ[partsupp.ps_partkey = p_partkey ∧ lineitem.l_partkey = p_partkey] part) function printShape(explainResult) { const root = explainResult?.stages?.[0]?.$cursor?.queryPlanner?.winningPlan?.queryPlan || explainResult?.queryPlanner?.winningPlan?.queryPlan || explainResult; print(summarizeNode(root)); } print( "=== idx146.js loaded ===" ); print( "db: " + TPCH_DB + " (collections: " + tpch.getCollectionNames().join( ", " ) + ")" ); print(""); print( "Helpers:" ); print( " runIdx146() run the query, return matching row count" ); print( " explainIdx146([verbosity]) get explain output ( default : queryPlanner)" ); print( " runIdx146Hinted() same as runIdx146(), with the hint-forced shape" ); print( " explainIdx146Hinted([verbosity]) hinted explain (force (partsupp ⋈ lineitem) ⋈ part)" ); print( " printJoinPredicates(explainResult) print every EMBEDDING node's predicates" ); print( " printShape(explainResult) one-line summary of the join tree shape" ); print( " idx146Pipeline the raw pipeline (mutable, for experiments)" ); print( " hintedIdx146Pipeline the pipeline with $_internalJoinHint prefix" ); print(""); print( "Quick start:" ); print( " const e = explainIdx146();" ); print( " printShape(e);" ); print( " printJoinPredicates(e);" ); print(""); print( "Demonstrate the multi-predicate issue is plan-shape-driven, not absorbed-filter-driven:" ); print( " printShape(explainIdx146Hinted());" ); print( " printJoinPredicates(explainIdx146Hinted());" );