benchmark.ts

/**
 * Benchmark: raw scans vs materialized summaries vs partition pruning.
 *
 * What this proves
 * ----------------
 * 1) Hive pruning — filtering season = N should do less work than scanning
 *    every facts/season=YYYY folder.
 * 2) Materialization — championship standings from
 *    summaries/driver_season_summary.parquet should beat a live
 *    GROUP BY over facts results Parquet (same answer, less work).
 *
 * How to read the numbers
 * -----------------------
 * - Times are wall-clock medians over several runs (after a warm-up).
 * - On a small laptop lake (a few seasons) the gap may be small in ms —
 *   the pattern is what matters; at warehouse scale the same idea saves
 *   serious money/latency.
 * - speedup = raw_ms / summary_ms (higher is better for the summary).
 *
 * Prerequisites
 * -------------
 *   npm run ingest -- --seasons 2024,2025
 *   npm run query -- queries/materialized_aggregates.sql
 *   npm run benchmark
 */
import * as fs from 'fs';
import * as duckdb from 'duckdb';
import {
  DATA_DIR,
  dimsPath,
  factGlob,
  sqlPath,
  summaryPath,
} from './paths';

const SEASON = Number(process.env.BENCH_SEASON ?? 2025);
const RUNS = Number(process.env.BENCH_RUNS ?? 7);

function openConnection() {
  // In-memory: avoid locking analytics.duckdb used by `npm run start`
  const db = new duckdb.Database(':memory:');
  return { db, conn: db.connect() };
}

function all(conn: duckdb.Connection, sql: string): Promise<duckdb.TableData> {
  return new Promise((resolve, reject) => {
    conn.all(sql, (err, rows) => {
      if (err) reject(err);
      else resolve(rows ?? []);
    });
  });
}

function median(values: number[]): number {
  const sorted = [...values].sort((a, b) => a - b);
  const mid = Math.floor(sorted.length / 2);
  if (sorted.length % 2 === 0) {
    return (sorted[mid - 1] + sorted[mid]) / 2;
  }
  return sorted[mid];
}

async function timedMedian(
  label: string,
  fn: () => Promise<unknown>,
  runs = RUNS,
): Promise<number> {
  // Warm-up (OS cache / DuckDB parse) — not counted
  await fn();

  const samples: number[] = [];
  for (let i = 0; i < runs; i++) {
    const t0 = performance.now();
    await fn();
    samples.push(performance.now() - t0);
  }
  const med = median(samples);
  const min = Math.min(...samples);
  const max = Math.max(...samples);
  console.log(
    `${label}: median ${med.toFixed(2)} ms  (min ${min.toFixed(2)}, max ${max.toFixed(2)}, n=${runs})`,
  );
  return med;
}

function printSpeedup(rawMs: number, fastMs: number, label: string) {
  if (fastMs <= 0) return;
  const ratio = rawMs / fastMs;
  console.log(
    `  → ${label}: ${ratio.toFixed(2)}× ${ratio >= 1 ? 'faster' : 'slower'} than raw`,
  );
}

async function main() {
  const resultsGlob = sqlPath(factGlob('results'));
  const drivers = sqlPath(dimsPath('drivers'));
  const constructors = sqlPath(dimsPath('constructors'));
  const driverSummary = sqlPath(summaryPath('driver_season_summary'));

  console.log('Pitwall benchmark');
  console.log(`Lake:    ${DATA_DIR}`);
  console.log(`Season:  ${SEASON}  (override with BENCH_SEASON=2024)`);
  console.log(`Runs:    ${RUNS} timed samples + 1 warm-up each`);
  console.log(`Results: ${resultsGlob}`);
  console.log(`Summary: ${driverSummary}`);
  console.log('');

  if (!fs.existsSync(summaryPath('driver_season_summary'))) {
    console.error('Missing driver_season_summary.parquet');
    console.error('Build it first:');
    console.error('  npm run query -- queries/materialized_aggregates.sql');
    process.exit(1);
  }

  const { db, conn } = openConnection();

  try {
    // Sanity: lake readable
    const probe = await all(
      conn,
      `SELECT CAST(COUNT(*) AS INTEGER) AS n
       FROM read_parquet('${resultsGlob}', hive_partitioning := true)`,
    );
    console.log(`Fact results rows (all seasons): ${probe[0]?.n}`);
    console.log('');

    // -------------------------------------------------------------------------
    // 1) Partition pruning
    // -------------------------------------------------------------------------
    console.log('— Partition pruning (count laps-worth of results rows) —');
    const fullCountMs = await timedMedian('full scan  COUNT(*) all seasons', () =>
      all(
        conn,
        `SELECT CAST(COUNT(*) AS INTEGER) AS n
         FROM read_parquet('${resultsGlob}', hive_partitioning := true)`,
      ),
    );
    const prunedCountMs = await timedMedian(
      `pruned scan COUNT(*) season=${SEASON}`,
      () =>
        all(
          conn,
          `SELECT CAST(COUNT(*) AS INTEGER) AS n
           FROM read_parquet('${resultsGlob}', hive_partitioning := true)
           WHERE season = ${SEASON}`,
        ),
    );
    printSpeedup(fullCountMs, prunedCountMs, 'pruning');
    console.log('');

    // -------------------------------------------------------------------------
    // 2) Standings: raw GROUP BY vs materialized summary
    // -------------------------------------------------------------------------
    console.log(`— Standings season=${SEASON} (raw GROUP BY vs summary) —`);

    const rawSql = `
      SELECT
        d.driver_id,
        d.family_name,
        c.constructor_name,
        CAST(SUM(r.points) AS DOUBLE) AS points,
        CAST(SUM(CASE WHEN r.finish_position = 1 THEN 1 ELSE 0 END) AS INTEGER) AS wins,
        CAST(SUM(CASE WHEN r.finish_position BETWEEN 1 AND 3 THEN 1 ELSE 0 END) AS INTEGER) AS podiums,
        CAST(COUNT(*) AS INTEGER) AS races
      FROM read_parquet('${resultsGlob}', hive_partitioning := true) r
      JOIN read_parquet('${drivers}') d USING (driver_id)
      JOIN read_parquet('${constructors}') c USING (constructor_id)
      WHERE r.season = ${SEASON}
      GROUP BY d.driver_id, d.family_name, c.constructor_name
      ORDER BY points DESC
    `;

    const summarySql = `
      SELECT
        d.driver_id,
        d.family_name,
        c.constructor_name,
        CAST(s.total_points AS DOUBLE) AS points,
        CAST(s.wins AS INTEGER) AS wins,
        CAST(s.podiums AS INTEGER) AS podiums,
        CAST(s.races AS INTEGER) AS races
      FROM read_parquet('${driverSummary}') s
      JOIN read_parquet('${drivers}') d USING (driver_id)
      JOIN read_parquet('${constructors}') c
        ON c.constructor_id = s.constructor_id
      WHERE s.season = ${SEASON}
      ORDER BY points DESC
    `;

    const rawRows = await all(conn, rawSql);
    const summaryRows = await all(conn, summarySql);
    console.log(
      `Row counts — raw: ${rawRows.length}, summary: ${summaryRows.length}`,
    );

    // Spot-check: top driver points should match
    const rawTop = rawRows[0] as Record<string, unknown> | undefined;
    const sumTop = summaryRows[0] as Record<string, unknown> | undefined;
    if (rawTop && sumTop) {
      const sameDriver = String(rawTop.driver_id) === String(sumTop.driver_id);
      const samePoints = Number(rawTop.points) === Number(sumTop.points);
      console.log(
        `Correctness — top driver match: ${sameDriver && samePoints ? 'OK' : 'MISMATCH'} ` +
          `(raw ${rawTop.driver_id}/${rawTop.points}, summary ${sumTop.driver_id}/${sumTop.points})`,
      );
    }
    console.log('');

    const rawMs = await timedMedian('raw       GROUP BY results + dims', () =>
      all(conn, rawSql),
    );
    const summaryMs = await timedMedian(
      'summary   read driver_season_summary',
      () => all(conn, summarySql),
    );
    printSpeedup(rawMs, summaryMs, 'materialization');
    console.log('');

    // -------------------------------------------------------------------------
    // Takeaway
    // -------------------------------------------------------------------------
    console.log('Takeaway');
    console.log(
      '  • Pruning skips unused season folders — layout is part of the index.',
    );
    console.log(
      '  • Summaries store the standings answer; APIs should read them when fresh.',
    );
    console.log(
      '  • Rebuild after ingest: npm run query -- queries/materialized_aggregates.sql',
    );
    console.log(
      '  • Standings API uses summary when present (source: "summary" | "raw").',
    );
  } catch (err) {
    console.error('Benchmark failed.');
    console.error(String(err));
    console.error(`Tried results glob: ${resultsGlob}`);
    console.error('Run ingest + materialized_aggregates first.');
    process.exit(1);
  } finally {
    conn.close();
    db.close();
  }
}

main();