🧪Query patterns and debugging

Bisect the multiset

Debug SQL like distributed systems: reproduce on a tiny schema, compare row counts at each CTE stage, then add locks and plans when timing is “random.” Multisets lie, duplicates before an aggregate are the usual villain.

SQL

Stage counts inside CTEs

WITH filtered AS (
  SELECT * FROM orders WHERE status = 'OPEN'
),
joined AS (
  SELECT f.order_id, c.segment
  FROM filtered f
  JOIN customers c USING (customer_id)
)
SELECT 'filtered' AS stage, COUNT(*) FROM filtered
UNION ALL
SELECT 'joined', COUNT(*) FROM joined;

✓Illustrative result set

stage	count
filtered	1200
joined	1180

👃Smells

What to check first.

Smell	Check
Row multiplication after a join	Fan-out: broken 1:1 assumption or missing key in ON.
Off-by-one time filters	Half-open ranges [start, end); normalize time zones before compare.

Production shells

Iterative debugging on shared prod with destructive tries.
ORM logs missing bind parameters, cannot replay the plan.
Stopping at the first plan without estimated vs actual row comparison.

Fixtures and plans

Build a checklist: keys, NULLs, duplicates, time zones.
Use EXPLAIN (ANALYZE, BUFFERS) equivalents; snapshot CSV fixtures for CI.
Pair with lock graphs when CPU is idle but latency spikes.

Tickets

A ten-row schema that reproduces the bug beats paragraphs of prose, attach it to the ticket like you would a failing unit test.

TL;DR

Debug SQL by freezing inputs (parameters, isolation, session settings), capturing the plan and row counts at each stage, and shrinking to a minimal repro. Common fixes: wrong join grain, NULL logic, missing index for filter/sort, stale statistics, or an ORM-generated N+1. Treat suspicious results as data model bugs until proven otherwise.