Skip to main content

SQL Debugging and Query Plans

Reliable SQL is developed through observable invariants, not by repeatedly editing one large statement until the output looks plausible. Check row counts, key uniqueness, missingness, and totals at every grain-changing step.

Prerequisites​

Complete the preceding SQL lessons and be able to express transformations with joins, aggregates, CTEs, windows, and set operations.

Learning goals​

  • Localize errors by testing one relational stage at a time.
  • Compare expected and actual cardinality after joins and filters.
  • Read scans, filters, joins, aggregates, and estimated cardinality in a plan.
  • Distinguish EXPLAIN from EXPLAIN ANALYZE.

A debugging checklist​

  1. Restate the desired output grain and candidate key.
  2. Reduce to a small deterministic fixture containing duplicates, NULL, unmatched keys, and ties.
  3. Run each CTE independently.
  4. Record row count and distinct-key count before and after every join.
  5. Reconcile additive measures against a trusted lower-grain total.
  6. Add deterministic ordering only for presentation or order-sensitive calculations.
  7. Inspect the plan after correctness is established.

Worked example​

The following Standard SQL query stays at customer grain. COUNT(o.order_id) counts matches, while SUM ignores Ben's missing amount. The HAVING clause removes Dia's zero-order group.

SELECT c.customer_id,
       COUNT(o.order_id) AS order_rows,
       SUM(o.amount) AS known_amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
HAVING COUNT(o.order_id) > 0
ORDER BY c.customer_id;
customer_idorder_rowsknown_amount
12100.00
2225.00
3180.00

EXPLAIN is implementation-specific even though the query is portable. In DuckDB 1.5.6 this plan contains sequential scans feeding a hash join. The exact drawing, estimates, and optimizer choices are not a stable cross-version API.

EXPLAIN
SELECT o.order_id, c.customer_name
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'shipped';

Read the plan from its inputs toward the result. SEQ_SCAN reads the small fixture tables, the status filter reduces order rows, and HASH_JOIN matches customer identifiers. Estimated cardinality is a planner prediction, not an observed fact.

DuckDB-specific: EXPLAIN ANALYZE executes the query and reports actual operator cardinalities and cumulative wall-clock time. Do not use it for a statement with side effects merely to “preview” work, and do not treat timings from this tiny fixture as a production benchmark.

Limitations and common mistakes​

  • A fast wrong query is still wrong; validate semantics before tuning.
  • EXPLAIN does not execute the query, so it cannot report actual cardinality or runtime.
  • EXPLAIN ANALYZE does execute it, and cache state, hardware, concurrency, and data distribution affect timings.
  • Optimizer plans may change across DuckDB versions even when results remain identical.
  • Adding DISTINCT or filters until counts look right can conceal a faulty join.

Practice​

Deliberately join orders to order_items, compare COUNT(*) with COUNT(DISTINCT order_id), and locate the order responsible for fanout. Then aggregate items first and verify both counts agree.

Next steps​

You can now reason from relations to trustworthy analytical SQL. Continue with the next Data Engineering batch on files, formats, and local analytical tools when it is published, and revisit Relational Data Models whenever a query's grain is unclear.

Primary sources​

Checked 2026-10-14: DuckDB query profiling, DuckDB EXPLAIN guide, and PostgreSQL plan inspection.