PostgreSQL vs MySQL EXPLAIN Output Differences

PostgreSQL and MySQL both expose a JSON execution plan, but the two documents share almost no field names, use incompatible cost units, and disagree on where actual-versus-estimated numbers live — so any baseline tracker that stores them raw will compare noise. This runbook enumerates the exact structural differences between EXPLAIN (FORMAT JSON) in PostgreSQL and EXPLAIN FORMAT=JSON in MySQL, then gives a deterministic normalization that folds both into one canonical plan node you can hash, diff, and threshold. It is the engine-mapping layer that feeds Normalizing Query Plans for Cross-Engine Comparison and it depends on the cost-axis alignment defined in Cost Estimation Mapping Across PostgreSQL and MySQL.

Symptom Identification and Production Thresholds #

Storing engine-native plans and comparing them directly produces silent false negatives: the fields you diff on are either absent or measured on a different axis. Treat each of the following as a hard breach condition, not a guideline.

1. Buffer fields present in only one engine. PostgreSQL under BUFFERS emits Shared Hit Blocks, Shared Read Blocks, and Temp Written Blocks; MySQL EXPLAIN FORMAT=JSON carries zero buffer-accounting fields. A canonical schema that marks shared_hit_blocks as required will null out on 100% of MySQL captures. Any capture whose required-field null rate exceeds 0% must be quarantined, never joined to a baseline.
2. Cost-unit magnitude mismatch. An identical full scan of a 1,000,000-row table yields a PostgreSQL Total Cost near 18,334 (rows plus pages, at seq_page_cost = 1.0) versus a MySQL query_cost near 103,000 (row_evaluate_cost = 0.1 per row plus block reads) — a $5.6\times$ scale gap. Comparing raw cost numbers cross-engine with any tolerance under 460% is meaningless.
3. Cardinality fields do not align. PostgreSQL exposes one estimate (Plan Rows) and, under ANALYZE, one actual (Actual Rows). MySQL FORMAT=JSON exposes rows_examined_per_scan (pre-filter) and rows_produced_per_join (post-filter) but no actual at all. Any estimated-versus-actual ratio built by pairing PostgreSQL Actual Rows against MySQL rows_produced_per_join compares an actual to an estimate and will misfire above the $2.0\times$ divergence alarm.
4. Actual-time double counting. PostgreSQL Actual Total Time is per-node cumulative milliseconds already normalized per loop; MySQL EXPLAIN ANALYZE emits a text tree with actual time=0.05..12.4 rows=840 loops=6, where time is per-iteration. Summing the two the same way over-counts the MySQL inner side by its loops factor, inflating totals by $3\times$–$8\times$ on nested access.
5. Operator-vocabulary collision. PostgreSQL has a dedicated Merge Join node; MySQL 8 has none, offering only nested-loop and (since 8.0.18) hash join. A plan hash keyed on raw operator names collides across engines on more than 30% of join-bearing queries.

When two or more of these fire on the same fingerprint, stop comparing raw plans and route both captures through the normalizer below before any threshold evaluation.

Root Cause Analysis #

The divergence is not cosmetic — it stems from two optimizers with unrelated cost models and unrelated serializers. PostgreSQL descends from a page-fetch cost model where 1.0 unit equals one sequential 8 KB page read; every other constant (random_page_cost = 4.0, cpu_tuple_cost = 0.01) is expressed relative to that. MySQL’s optimizer costs in its own units anchored on io_block_read_cost = 1.0 and row_evaluate_cost = 0.1, and it accumulates cost as read_cost + eval_cost per table rather than as startup/total pairs per node. The two numbers are not the same currency, and no fixed exchange rate is exact — but a workload-calibrated scaling constant of roughly 5.6 maps a MySQL full-scan cost onto the PostgreSQL axis closely enough to threshold.

A PostgreSQL node is a recursive object rooted at plan[0]["Plan"], with children under Plans:

[
  {
    "Plan": {
      "Node Type": "Hash Join",
      "Total Cost": 18334.50,
      "Plan Rows": 42000,
      "Actual Rows": 41180,
      "Shared Hit Blocks": 9021,
      "Plans": [
        {"Node Type": "Seq Scan", "Relation Name": "orders",
         "Total Cost": 12010.00, "Plan Rows": 500000, "Actual Rows": 499213}
      ]
    }
  }
]

MySQL nests the same logical join under query_block, with per-table cost inside cost_info and joins expressed as a nested_loop array rather than typed nodes:

{
  "query_block": {
    "cost_info": {"query_cost": "103182.40"},
    "nested_loop": [
      {"table": {"table_name": "orders", "access_type": "ALL",
                 "rows_examined_per_scan": 500000, "rows_produced_per_join": 500000,
                 "filtered": "100.00",
                 "cost_info": {"read_cost": "51420.00", "eval_cost": "50000.00"}}}
    ]
  }
}

The generative fault lines are: cost currency (page-relative versus block-and-row), plan shape (typed recursive nodes versus a nested_loop/table array), cardinality semantics (single estimate/actual pair versus pre- and post-filter estimates), and instrumentation surface (rich BUFFERS I/O accounting versus none). You can align a query to a stable identity across these using Plan Hashing Algorithms for SQL Engines, but only after the fields are canonical.

Step-by-Step Remediation #

Normalize at capture time so the baseline store only ever holds canonical nodes. The reference implementation captures the PostgreSQL plan over asyncpg, walks both engine shapes, remaps operators, rescales the MySQL cost onto the PostgreSQL axis, and rejects any node missing a required field.

import json
from dataclasses import dataclass
from enum import Enum
from typing import Optional

import asyncpg
import structlog
from opentelemetry import trace

log = structlog.get_logger("normalize.explain")
tracer = trace.get_tracer("normalize.explain")

# PG Total Cost is in seq_page_cost units; a MySQL query_cost for an equivalent
# full scan runs ~5.6x larger. Divide MySQL cost to land on the PostgreSQL axis.
MYSQL_COST_SCALE = 1.0 / 5.6
REQUIRED_FIELDS = ("relation", "operator", "est_rows", "est_cost")


class CanonicalOp(str, Enum):
    SEQ_SCAN = "seq_scan"
    INDEX_SCAN = "index_scan"
    NESTED_LOOP = "nested_loop"
    HASH_JOIN = "hash_join"
    MERGE_JOIN = "merge_join"
    AGGREGATE = "aggregate"
    OTHER = "other"


PG_OP_MAP = {
    "Seq Scan": CanonicalOp.SEQ_SCAN, "Index Scan": CanonicalOp.INDEX_SCAN,
    "Index Only Scan": CanonicalOp.INDEX_SCAN, "Bitmap Heap Scan": CanonicalOp.INDEX_SCAN,
    "Nested Loop": CanonicalOp.NESTED_LOOP, "Hash Join": CanonicalOp.HASH_JOIN,
    "Merge Join": CanonicalOp.MERGE_JOIN, "Aggregate": CanonicalOp.AGGREGATE,
}
MYSQL_ACCESS_MAP = {
    "ALL": CanonicalOp.SEQ_SCAN, "index": CanonicalOp.INDEX_SCAN,
    "range": CanonicalOp.INDEX_SCAN, "ref": CanonicalOp.INDEX_SCAN,
    "eq_ref": CanonicalOp.INDEX_SCAN, "const": CanonicalOp.INDEX_SCAN,
}


@dataclass(frozen=True)
class CanonicalNode:
    engine: str
    relation: Optional[str]
    operator: CanonicalOp
    est_rows: int
    est_cost: float                      # normalized onto the PostgreSQL axis
    actual_rows: Optional[int] = None
    shared_hit_blocks: Optional[int] = None   # PostgreSQL-only, None elsewhere


def normalize_pg(plan_json: str) -> list[CanonicalNode]:
    root = json.loads(plan_json)[0]["Plan"]
    out: list[CanonicalNode] = []

    def walk(n: dict) -> None:
        out.append(CanonicalNode(
            engine="postgresql",
            relation=n.get("Relation Name"),
            operator=PG_OP_MAP.get(n["Node Type"], CanonicalOp.OTHER),
            est_rows=int(n.get("Plan Rows", 0)),
            est_cost=float(n.get("Total Cost", 0.0)),
            actual_rows=int(n["Actual Rows"]) if "Actual Rows" in n else None,
            shared_hit_blocks=n.get("Shared Hit Blocks"),
        ))
        for child in n.get("Plans", []):
            walk(child)

    walk(root)
    return out


def normalize_mysql(plan_json: str) -> list[CanonicalNode]:
    root = json.loads(plan_json)["query_block"]
    out: list[CanonicalNode] = []

    def emit(tbl: dict) -> None:
        ci = tbl.get("cost_info", {})
        raw = float(ci.get("read_cost", 0.0)) + float(ci.get("eval_cost", 0.0))
        out.append(CanonicalNode(
            engine="mysql",
            relation=tbl.get("table_name"),
            operator=MYSQL_ACCESS_MAP.get(tbl.get("access_type", ""), CanonicalOp.OTHER),
            est_rows=int(tbl.get("rows_examined_per_scan", 0)),
            est_cost=raw * MYSQL_COST_SCALE,   # rescale onto the PostgreSQL axis
        ))

    def walk(block: dict) -> None:
        if "table" in block:
            emit(block["table"])
        for item in block.get("nested_loop", []):
            walk(item)
        for key in ("ordering_operation", "grouping_operation",
                    "duplicates_removal", "query_block"):
            if key in block:
                walk(block[key])

    walk(root)
    return out


def reject_incomplete(nodes: list[CanonicalNode]) -> list[CanonicalNode]:
    for n in nodes:
        missing = [f for f in REQUIRED_FIELDS if getattr(n, f) in (None, "")]
        if missing:
            log.error("quarantine_node", engine=n.engine, relation=n.relation,
                      missing=missing)
            raise ValueError(f"canonical node missing {missing}")
    return nodes


async def capture_pg(pool: asyncpg.Pool, sql: str) -> list[CanonicalNode]:
    with tracer.start_as_current_span("capture_pg") as span:
        async with pool.acquire() as conn:
            await conn.execute("SET LOCAL statement_timeout = 800")
            raw = await conn.fetchval(f"EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) {sql}")
        span.set_attribute("db.system", "postgresql")
        nodes = reject_incomplete(normalize_pg(raw))
        log.info("captured", engine="postgresql", node_count=len(nodes))
        return nodes

The mapping is deterministic: the same plan text always yields the same canonical nodes, so the output is safe to hash and store. The field correspondences the mapper enforces are:

Running the capture against a two-table PostgreSQL join emits the canonical count and lets a downstream diff operate on uniform nodes:

2026-07-18T09:41:07Z [info] captured engine=postgresql node_count=3
2026-07-18T09:41:07Z [info] canonical_diff fingerprint=7c2b…e4 est_cost_pg=18334.5 est_cost_norm_mysql=18425.8 delta_pct=0.005

A normalized delta near 0.005 confirms the two engines agree once cost is on a common axis — the same query is not regressing, it is merely described in two dialects.

Verification Checklist #

• [ ] The MySQL cost scaling constant (1/5.6) has been recalibrated against your own full-scan benchmark on the current storage tier, not assumed.
• [ ] Every canonical node passes reject_incomplete; the quarantine rate for both engines is 0% over the last 100 captures.
• [ ] est_rows is sourced from Plan Rows and rows_examined_per_scan only — never from rows_produced_per_join.
• [ ] actual_rows is None for all MySQL FORMAT=JSON captures, and populated for PostgreSQL captures taken with ANALYZE.
• [ ] Operator maps cover every Node Type and access_type observed in the last week; unmapped values fall to other and are logged, not silently dropped.
• [ ] The canonical delta for a known-stable cross-engine query stays under 0.05 after normalization.
• [ ] shared_hit_blocks is marked optional in the baseline schema so MySQL rows are not rejected for lacking buffers.
• [ ] A round-trip test re-normalizes a stored plan and reproduces an identical canonical node set (determinism holds).

Compatibility and Engine-Specific Notes #

The canonical shape is engine-agnostic, but the source fields and their caveats differ sharply. Distributed SQL engines add a third dialect again — CockroachDB reports a unitless optimizer cost and per-node estimated row counts, while TiDB splits work across task tiers (root versus cop) with estRows/actRows columns.

Fold MySQL’s missing Merge Join into the hash-join operator class rather than inventing a node the optimizer never chose. On distributed engines, a single logical relation can span ranges on different nodes, so treat any cost scaling as per-range rather than per-table. Anchor the exchange rate itself in Cost Estimation Mapping Across PostgreSQL and MySQL, and hash the normalized output with Plan Hashing Algorithms for SQL Engines so a canonical node set has one stable identity regardless of source engine.

Related #

• ← Back to Normalizing Query Plans for Cross-Engine Comparison (parent topic)
• Align the cost currency: Cost Estimation Mapping Across PostgreSQL and MySQL
• Give the canonical node a stable identity: Plan Hashing Algorithms for SQL Engines
• Wider context: Automated EXPLAIN Capture & Storage Workflows

← Back to Normalizing Query Plans for Cross-Engine Comparison