PLAN

Duplicate plan subtree

The query plan computes the same subtree more than once.

Outcome: SQL plans
Scope: Judged per SQL query
Usual fix: A change in the job code
CI gate: No budget flag: gate it with --fail-on-introduced against a baseline

An example finding

Where you see it

Dashboard

The PLAN tag marks it in the action region of the dashboard, among the widgets that rank a fix. How to read it in the findings guide.

Agent (MCP)

An assistant can read this entry with the list_detectors tool and its background with get_finding_documentation. The call and the response below come from a real run of the MCP server.

Call

{
  "tool": "list_detectors",
  "arguments": {
    "type": "duplicatePlanSubtree"
  }
}

Response

{
  "count": 1,
  "detectors": [
    {
      "type": "duplicatePlanSubtree",
      "name": "duplicate plan subtree",
      "tag": "PLAN",
      "tagSharedWith": [
        "smallFiles",
        "overBroadcast",
        "underBroadcast",
        "pythonUdf",
        "nestedLoopJoin",
        "mergeRewrite",
        "unprunedScan",
        "joinKeyCast"
      ],
      "family": "plan",
      "pitch": "The query plan computes the same subtree more than once.",
      "region": "action",
      "scope": "sql",
      "fixEffort": "code",
      "thresholdSummary": "the same physical plan subtree executed more than once",
      "detectors": [
        {
          "detector": "duplicatePlanSubtree",
          "version": 2,
          "thresholds": {
            "minSubtreeSize": 3,
            "minOccurrences": 2,
            "stageFloorPct": 0.005
          }
        }
      ],
      "pages": {
        "catalog": "docs/detectors/duplicatePlanSubtree.html",
        "guide": "docs/user-guide/understanding-findings.html#plan"
      },
      "tuning": {
        "kind": "page",
        "anchor": "#bottleneck-duplicate-plan-subtree",
        "path": "docs/tuning-reference/bottleneck-duplicate-plan-subtree.html#bottleneck-duplicate-plan-subtree"
      },
      "gate": null
    }
  ]
}

get_finding_documentation call

{
  "tool": "get_finding_documentation",
  "arguments": {
    "type": "duplicatePlanSubtree"
  }
}

Its response, with each Markdown body replaced by its length

{
  "type": "duplicatePlanSubtree",
  "name": "Duplicate Plan Subtree",
  "detectionDoc": {
    "tag": "PLAN",
    "title": "Plan advisor",
    "content": "<7876 characters of Markdown>"
  },
  "tuningDoc": {
    "anchor": "#bottleneck-duplicate-plan-subtree",
    "title": "Duplicate Plan Subtree",
    "content": "<5567 characters of Markdown>"
  }
}

CI (CLI)

List this entry from the command line. The output below is a real run of the command. Budgets and exit codes are in the CI guide.

sparkforensics-analyze detectors --type duplicatePlanSubtree --format md

# Detectors (1)

## SQL plans

| Tag | Type | Scope | Fix | Gate | What it finds |
| --- | --- | --- | --- | --- | --- |
| PLAN | `duplicatePlanSubtree` | sql | code |  | The query plan computes the same subtree more than once. |

Report only this finding type for a run with --type:

sparkforensics-analyze <event-log> --type duplicatePlanSubtree --impact critical,warning,info

It has no budget flag. Fail a build on a new one by comparing against a baseline with --fail-on-introduced.

Airflow

The operator has no budget parameter for it. Use fail_on_introduced with a baseline.

check_spark_job = SparkForensicsOperator(
    task_id="check_spark_job",
    log_source=...,
    backend=SubprocessAnalyzeHook(),
    baseline_log_source=...,
    fail_on_introduced="critical",
)

The operator works per run, and its summary counts findings per impact band. See the operator for its parameters.

How it is measured

Thresholds

The same physical plan subtree executed more than once.

duplicatePlanSubtree version 2

Threshold	Default
`minSubtreeSize`	3
`minOccurrences`	2
`stageFloorPct`	0.005

Each rule's impact bands and fixed fallbacks are in the threshold tables.

Override a default per run with --thresholds; see Tuning detector thresholds.

Docs

This finding has its own tuning page: Spark tuning reference.
Findings guide: PLAN in Understanding findings.
Shares the PLAN tag with: small files, oversized broadcast join, missed broadcast join, row-at-a-time Python UDF, nested loop join, MERGE rewrite, unpruned partitioned scan, cast on a join key.