polardb/polardbx-sql/polardbx-plan-analysis

PolarDB-X Execution Plan Analysis

Analyze PolarDB-X execution plans (EXPLAIN / EXPLAIN COST / EXPLAIN ANALYZE output) and verify semantic equivalence between SQL and execution plans.

Scope

Applies to:

• Operator interpretation of EXPLAIN / EXPLAIN COST / EXPLAIN ANALYZE output
• EXPLAIN COST cost estimation (rowcount, cumulative cost) interpretation
• EXPLAIN ANALYZE runtime statistics (actual time, actual rowcount, instances) interpretation and bottleneck identification
• SQL-to-execution-plan semantic equivalence verification
• Foundation for phase/rule-level optimizer attribution analysis (operator interpretation capabilities)

Does NOT apply to:

• Optimizer rule tuning or phase-level attribution analysis
• SQL rewriting or query optimization suggestions
• Index recommendation or schema design (use sql-review or polardbx-sql)
• Non-PolarDB-X EXPLAIN output (MySQL, PostgreSQL, etc.)
• DN-level (storage node) internal execution details

Verification — confirm you are working with PolarDB-X EXPLAIN output:

SELECT version();          -- should contain "PolarDB-X" or "TDDL"
SHOW STORAGE;              -- PolarDB-X specific command, confirms distributed topology

Mode 1: Plan Interpretation

Workflow

1. Receive input — User pastes EXPLAIN output. If not provided, guide them to run EXPLAIN <sql> / EXPLAIN COST <sql> / EXPLAIN ANALYZE <sql>
2. Identify mode — Contains rowcount = -> EXPLAIN COST; contains actual time = -> EXPLAIN ANALYZE. See references/explain-cost-analyze.md for field definitions
3. Parse plan tree — One operator per line, indentation indicates parent-child relationship, operator name is the text before the first (
4. Separate metadata — Identify non-operator lines at the end of the plan tree (HitCache:, Source:, TemplateId:, etc.), process them separately as metadata
5. Classify operators — Match against references/operator-taxonomy.md into 17 categories (note: display names may differ from class names, see mapping table)
6. Interpret each operator — Explain bottom-up: name, category, function, key attributes. See corresponding reference files for operator attribute definitions
7. Cost/performance analysis (COST/ANALYZE mode) — rowcount deviation, actual time bottlenecks, instances, spill
8. Plan description — One paragraph summarizing data flow (scan -> output), stating plan type and key strategies
9. Metadata interpretation — Parse HitCache/Source/TemplateId/BaselineInfo fields, provide cache hit and plan source conclusions

Output Format

Output three sections (four when metadata is present): Raw Plan -> Plan Description -> Operator Analysis -> Plan Metadata (if applicable).

## Raw Plan
 
<Each line prefixed with line number, format: "line_number  operator_text", line numbers right-aligned>
 
## Plan Description
 
<One paragraph summarizing data flow, with L<line_number> annotations at key operators for reference>
 
## Operator Analysis
 
| # | Operator | Category | Description |
|---|----------|----------|-------------|
| 1 | Project | Project | Computes promo_revenue = 100 * $f0 / $f1. Input: #2 |
| 2 | HashAgg | Agg | Scalar aggregation sum($f0), sum($f1), final stage. Input: #3 |
| ... | ... | ... | ... |

Raw Plan Line Numbering Rules

• Lines are numbered sequentially starting from 1
• Line numbers are right-aligned with space padding (e.g., 1, 10)
• Two spaces follow the line number, then the original indentation and operator text
• Example:

`` 1 Exchange(distribution=single, collation=[]) 2 Project(promo_revenue="?0 * $f0 / $f1") 3 HashAgg($f0="sum($f0)", $f1="sum($f1)") ``

Plan Description Requirements

1. State the plan type at the beginning: Columnar / MPP / SMP
2. Trace data flow bottom-up, describe in actual flow order
3. Use specific column names and values: annotate shuffle partition count like shuffle by l_partkey to 96 partitions
4. Specify join roles: which side builds the hash table, whether partition-wise
5. Annotate key strategies: two-phase aggregation, broadcast small table, large table stays in place
6. Appropriate sentence breaks: 2-4 operation steps per sentence, separated by periods
7. Annotate key nodes with (L<line_number>) referencing the raw plan line number for quick lookup. Annotation targets include: Exchange/shuffle, join, aggregation, sort, and other key operators; not every operator needs annotation, only those helpful for understanding data flow

Operator Analysis Requirements

• Listed in original plan order (root to leaf)
• Append Input: #N at the end of the description to indicate data source line number
• Dual-input operators annotated as Input: #N, #M
• Leaf nodes have no input annotation

Example

Input plan:

Project(promo_revenue="?0 * $f0 / $f1")
  HashAgg($f0="sum($f0)", $f1="sum($f1)")
    Exchange(distribution=single, collation=[])
      PartialHashAgg($f0="sum($f0)", $f1="sum($f1)")
        Project($f0="...", $f1="l_extendedprice * ?4 - l_discount")
          HashJoin(condition="l_partkey = p_partkey", type="inner")
            OSSTableScan(tables="part_col_index[p1,...p96]", shardcount=96, partition=[remote])
            Exchange(distribution=hash[0]96, collation=[])
              OSSTableScan(tables="lineitem_col_index[p1,...p96]", shardcount=96, sql="... where l_shipdate ...")

Output:

## Raw Plan
 
1  Project(promo_revenue="?0 * $f0 / $f1")
2    HashAgg($f0="sum($f0)", $f1="sum($f1)")
3      Exchange(distribution=single, collation=[])
4        PartialHashAgg($f0="sum($f0)", $f1="sum($f1)")
5          Project($f0="...", $f1="l_extendedprice * ?4 - l_discount")
6            HashJoin(condition="l_partkey = p_partkey", type="inner")
7              OSSTableScan(tables="part_col_index[p1,...p96]", shardcount=96, partition=[remote])
8              Exchange(distribution=hash[0]96, collation=[])
9                OSSTableScan(tables="lineitem_col_index[p1,...p96]", shardcount=96, sql="... where l_shipdate ...")
 
## Plan Description
 
Columnar execution plan. Scans lineitem(L9) with l_shipdate filter, then shuffles by l_partkey to 96 partitions(L8), partition-wise hash join(L6) with part(L7). Projects to compute promo/total revenue, then two-phase aggregation (PartialHashAgg(L4) -> Exchange single(L3) -> HashAgg(L2)), finally Project(L1) outputs promo_revenue.
 
## Operator Analysis
 
| # | Operator | Category | Description |
|---|----------|----------|-------------|
| 1 | Project | Project | Computes promo_revenue = 100 * $f0 / $f1. Input: #2 |
| 2 | HashAgg | Agg | Scalar aggregation sum, final stage. Input: #3 |
| 3 | Exchange | Exchange | distribution=single, converges to single node. Input: #4 |
| 4 | PartialHashAgg | Agg | Scalar aggregation sum, first stage. Input: #5 |
| 5 | Project | Project | Computes promo revenue and total revenue. Input: #6 |
| 6 | HashJoin | Join | inner, l_partkey = p_partkey. Input: #7, #8 |
| 7 | OSSTableScan | TableScan | part_col_index, 96 shards |
| 8 | Exchange | Exchange | hash[0]96, lineitem shuffled by l_partkey. Input: #9 |
| 9 | OSSTableScan | TableScan | lineitem_col_index, 96 shards, l_shipdate filter |

More plan description examples:

• "Columnar execution plan. Filters region(L15) then broadcasts(L14), joins with nation(L13) then broadcasts(L12), customer joins the result(L11). Orders filtered by date, shuffled by o_custkey to 96 partitions(L8) to join customer result(L7), then shuffled by o_orderkey to 96 partitions(L5) to join lineitem(L4). Supplier broadcasts(L3), joins via suppkey + nationkey(L2), two-phase agg groups by n_name, sorts by revenue for output(L1)."
• "Columnar execution plan. Orders filtered by date(L7), partition-wise semi hash join with lineitem(L5). Two-phase agg(L3,L4) counts by o_orderpriority, sorts for output(L1)."
• "SMP execution plan. Orders and lineitem pushed down to DN for join and partial aggregation(L4,L5), Gather(L3) collects results, CN performs final HashAgg(L2), TopN(L1) returns top 10."

Plan Metadata Parsing

EXPLAIN output may include metadata lines at the end (HitCache, Source, TemplateId, etc.). These lines are not part of the operator tree, are not included in line numbering, and are interpreted separately in a ## Plan Metadata section after the operator analysis table.

See references/plan-metadata.md for field definitions, Source enum values, output format, and interpretation guidelines.

Mode 2: Equivalence Analysis

Workflow

1. Receive input — Obtain the SQL and corresponding EXPLAIN plan
2. Check each dimension — Verify bottom-up along the 14 dimensions defined in references/equivalence-analysis.md
3. Output conclusion — Per-dimension verdict; when not equivalent, specify location and reason

14 Analysis Dimensions

Output Format

## Equivalence Analysis
 
### Filter Conditions
- Result: Equivalent
- Explanation: ...
 
### JOIN Conditions & Type
- Result: Not equivalent
- Explanation: ...
 
## Conclusion
SQL and plan are [equivalent / not equivalent]. [When not equivalent, describe the issue]

Plan Visualization (Optional)

Only execute when the user explicitly requests "draw", "visualize", or similar. Do not draw by default.

See references/plan-visualization.md for details.

EXPLAIN Display Name -> Operator Class Mapping