parse.ts

/**
 * Parsing Postgres `EXPLAIN ANALYZE` output.
 *
 * The text format is not documented as a grammar and has accumulated quirks
 * across two decades of releases, so this is deliberately tolerant: anything it
 * cannot interpret is preserved verbatim on the node rather than dropped. A
 * plan that renders with one unrecognised line is far more useful than an error
 * message, and someone pasting a plan at 2am does not want a parser being
 * principled at them.
 */

export interface PlanNode {
  /** e.g. "Seq Scan", "Hash Join", "Index Scan" */
  operation: string;
  /** Table or index the node reads, when it names one. */
  relation?: string;
  /** Index used, for index scans. */
  indexName?: string;

  /** Planner's guesses. */
  startupCost?: number;
  totalCost?: number;
  planRows?: number;
  width?: number;

  /** What actually happened. Absent unless ANALYZE was used. */
  actualStartupTime?: number;
  actualTotalTime?: number;
  actualRows?: number;
  loops?: number;

  /** Extra detail lines belonging to this node, verbatim. */
  details: string[];
  children: PlanNode[];
  /** Indentation depth, used to rebuild the tree. */
  depth: number;
}

export interface ParsedPlan {
  root: PlanNode | null;
  /** Trailing lines such as "Planning Time:" / "Execution Time:". */
  planningTime?: number;
  executionTime?: number;
  /** True when the plan carries real measurements rather than estimates only. */
  analyzed: boolean;
  /** Lines the parser could not place. Surfaced rather than silently dropped. */
  unparsed: string[];
}

/**
 * Split a plan line into indentation, descriptor, and the cost/actual tail.
 *
 * Deliberately loose about the descriptor rather than trying to enumerate
 * operation names. Postgres writes things like `Index Scan using
 * customers_pkey on customers` and `Parallel Bitmap Heap Scan on orders`, and a
 * character class tight enough to be "correct" fails the moment an identifier
 * contains an underscore — which every index name does.
 */
const NODE_LINE = /^(\s*)(?:->\s+)?([A-Z][^(]*?)\s*(\(cost=.*|\(actual .*)?$/;
const USING = /\s+using\s+(\S+)/i;
const ON = /\s+on\s+(\S+)/i;
const COST = /\(cost=([\d.]+)\.\.([\d.]+)\s+rows=(\d+)\s+width=(\d+)\)/;
const ACTUAL = /\(actual time=([\d.]+)\.\.([\d.]+)\s+rows=([\d.]+)\s+loops=(\d+)\)/;
const ACTUAL_NEVER = /\(never executed\)/;

/** Operations that read a relation directly. */
const SCAN_OPERATIONS = /^(Seq Scan|Index Scan|Index Only Scan|Bitmap Heap Scan|Tid Scan|Sample Scan|Foreign Scan)/;

function number(value: string | undefined): number | undefined {
  if (value === undefined) return undefined;
  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : undefined;
}

/**
 * Turn raw EXPLAIN text into a tree.
 *
 * Indentation carries the structure: Postgres indents each child two spaces
 * further than its parent and prefixes it with `->`. Rather than trusting a
 * fixed step, the depths are compared relatively, because parallel and CTE
 * subtrees do not always step uniformly.
 */
export function parsePlan(text: string): ParsedPlan {
  const lines = text.split('\n');
  const nodes: PlanNode[] = [];
  const unparsed: string[] = [];

  let planningTime: number | undefined;
  let executionTime: number | undefined;
  let current: PlanNode | null = null;

  for (const raw of lines) {
    const line = raw.replace(/\r$/, '');
    if (line.trim() === '') continue;

    const planning = /^\s*Planning Time:\s*([\d.]+)\s*ms/.exec(line);
    if (planning) {
      planningTime = number(planning[1]);
      continue;
    }

    const execution = /^\s*Execution Time:\s*([\d.]+)\s*ms/.exec(line);
    if (execution) {
      executionTime = number(execution[1]);
      continue;
    }

    // Noise psql adds around the plan.
    if (/^\s*(QUERY PLAN|-+|\(\d+ rows?\))\s*$/.test(line)) continue;

    const match = NODE_LINE.exec(line);
    const looksLikeNode = match && (line.includes('->') || nodes.length === 0 || line.includes('(cost='));

    if (looksLikeNode && match) {
      const [, indent, descriptor, tail = ''] = match;
      const cost = COST.exec(tail);
      const actual = ACTUAL.exec(tail);

      // Peel the `using <index>` and `on <relation>` clauses off, leaving the
      // bare operation name behind.
      const using = USING.exec(descriptor);
      const on = ON.exec(descriptor);
      const operation = descriptor
        .replace(USING, '')
        .replace(ON, '')
        .trim();

      const node: PlanNode = {
        operation,
        relation: on?.[1],
        indexName: using?.[1],
        startupCost: number(cost?.[1]),
        totalCost: number(cost?.[2]),
        planRows: number(cost?.[3]),
        width: number(cost?.[4]),
        actualStartupTime: number(actual?.[1]),
        actualTotalTime: number(actual?.[2]),
        actualRows: number(actual?.[3]),
        // "never executed" is a real outcome, not a missing measurement.
        loops: ACTUAL_NEVER.test(tail) ? 0 : number(actual?.[4]),
        details: [],
        children: [],
        depth: indent.length,
      };

      nodes.push(node);
      current = node;
      continue;
    }

    // A detail line belonging to the node above it, e.g. "Filter: (age > 30)".
    if (current && /^\s+\S/.test(line)) {
      const detail = line.trim();
      current.details.push(detail);

      continue;
    }

    unparsed.push(line);
  }

  return {
    root: buildTree(nodes),
    planningTime,
    executionTime,
    analyzed: nodes.some((node) => node.actualTotalTime !== undefined),
    unparsed,
  };
}

/** Rebuild parent/child relationships from indentation depth. */
function buildTree(nodes: PlanNode[]): PlanNode | null {
  if (nodes.length === 0) return null;

  const root = nodes[0];
  const stack: PlanNode[] = [root];

  for (const node of nodes.slice(1)) {
    // Walk back out to the nearest shallower node — that is this node's parent.
    while (stack.length > 1 && node.depth <= stack[stack.length - 1].depth) {
      stack.pop();
    }
    stack[stack.length - 1].children.push(node);
    stack.push(node);
  }

  return root;
}

/** Every node in the tree, depth-first. */
export function flatten(node: PlanNode | null): PlanNode[] {
  if (!node) return [];
  return [node, ...node.children.flatMap(flatten)];
}

/**
 * Time spent in this node alone, excluding its children.
 *
 * The number people actually want. `actual total time` is cumulative and
 * per-loop, so a nested loop's inner node showing 0.05ms can be the most
 * expensive thing in the plan once multiplied by 40,000 iterations — which is
 * exactly the case a reader most needs pointed out and least easily spots.
 */
export function exclusiveTime(node: PlanNode): number | undefined {
  if (node.actualTotalTime === undefined) return undefined;

  const own = node.actualTotalTime * (node.loops ?? 1);
  const childrenTime = node.children.reduce((sum, child) => {
    const childTotal = child.actualTotalTime;
    if (childTotal === undefined) return sum;
    return sum + childTotal * (child.loops ?? 1);
  }, 0);

  return Math.max(0, own - childrenTime);
}

/** Total wall time the plan reports, preferring the explicit figure. */
export function totalTime(plan: ParsedPlan): number | undefined {
  if (plan.executionTime !== undefined) return plan.executionTime;
  if (!plan.root?.actualTotalTime) return undefined;
  return plan.root.actualTotalTime * (plan.root.loops ?? 1);
}

export function isScan(node: PlanNode): boolean {
  return SCAN_OPERATIONS.test(node.operation);
}