Query rewriter

SQL Query Rewriter

Reshape a slow query into a faster equivalent that returns identical rows. Shape beats tuning when the query does avoidable work - but a rewrite that changes results is worse than a slow query, so equivalence is proven, never assumed. This skill owns the structural rewrite only: index choice belongs to index-advisor, and plan diagnosis belongs to sql-query-optimizer.

Inputs to collect

1. The query text and its EXPLAIN (ANALYZE) output - ANALYZE if the query can be run safely, plain EXPLAIN otherwise (label conclusions from estimates as provisional).
2. Approximate row counts of each table involved, to spot Cartesian blowups.
3. The existing indexes on the touched tables - a rewrite often assumes a supporting index; confirm it exists or hand off to index-advisor.
4. What the caller actually consumes: which columns, whether order matters, whether duplicates are possible.
5. A way to run both versions against the same data for the equivalence diff.

Workflow

1. Confirm the shape is the problem. Read the EXPLAIN. Only rewrite if the plan shows a structural cost driver: actual rows far exceeding any single table (Cartesian product), a subquery re-executed per outer row (SubPlan with high loop counts), a Sort/Limit after scanning a huge OFFSET, or an OR that forces a seq scan. If the plan is already optimal, stop - modern Postgres unnests many subqueries itself.
2. Fix accidental cross joins first. A FROM listing N tables with a missing or incomplete join predicate multiplies row counts. Write one explicit JOIN ... ON per table relationship. If actual rows dwarf every source table, suspect a dropped ON clause.
3. Decorrelate per-row subqueries. A scalar subquery in SELECT/WHERE that references the outer row runs once per row. Rewrite it as a JOIN to a grouped derived table; in Postgres use a LATERAL join for per-row top-N. Replace WHERE EXISTS chains with a single JOIN where multiplicity allows. Keep EXISTS over IN when the inner set is large - EXISTS short-circuits and NOT IN returns wrong results on NULLs.
4. Split OR across different columns. WHERE a = 1 OR b = 2 can use neither column cleanly. Rewrite as UNION of two single-predicate queries (UNION ALL plus dedup only if duplicates are possible). An OR on the same column collapses to IN (...).
5. Project explicit columns. Replace SELECT * with the exact columns the caller uses. This shrinks I/O and lets an existing covering index answer the query without a heap fetch.
6. Convert deep OFFSET to keyset pagination. OFFSET 100000 LIMIT 20 scans and throws away 100k rows and degrades per page. Rewrite as keyset: WHERE (created_at, id) < (:last_ts, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20. Keep OFFSET only for small, bounded sets where a jump-to-page-N UI is required.
7. Prove equivalence. Run the original and the rewrite against the same data and diff the full output, including ordering and row count. Re-check EXPLAIN to confirm the structural cost driver is gone.

Worked example: decorrelating a per-row subquery

Bad - latest order date per customer via a correlated scalar subquery:

SELECT c.id, c.name,
       (SELECT MAX(o.created_at) FROM orders o
        WHERE o.customer_id = c.id) AS last_order
FROM customers c
WHERE c.region = 'EMEA';

EXPLAIN shows the cost driver: SubPlan 1 under the customers scan with loops=48213 - the orders aggregate re-executes once per EMEA customer, ~48k index probes.

Good - aggregate once, join once:

SELECT c.id, c.name, o.last_order
FROM customers c
LEFT JOIN (
  SELECT customer_id, MAX(created_at) AS last_order
  FROM orders
  GROUP BY customer_id
) o ON o.customer_id = c.id
WHERE c.region = 'EMEA';

EXPLAIN now shows one HashAggregate over orders and a single Hash Left Join - the per-row loop is gone. The LEFT JOIN (not INNER) preserves customers with no orders, matching the original's NULL behavior; the equivalence diff confirms identical rows. If only a few customers are EMEA, the LATERAL variant (LEFT JOIN LATERAL (SELECT MAX(...) FROM orders o WHERE o.customer_id = c.id) ...) probes per row but only for the filtered set - pick whichever EXPLAIN proves cheaper on the real data.

Deliverable

Produce the rewritten query plus a rewrite note containing: the structural cost driver quoted from the original EXPLAIN, the before/after plan comparison showing that driver removed, the equivalence evidence (row-count and full-output diff, including NULL and ordering behavior), and any index the rewrite assumes - with a hand-off to index-advisor if it does not exist.

Quality bar

• Output is identical to the original on the same data - same rows, same order, same NULL handling.
• Every rewrite removes a cost driver visible in EXPLAIN, not a suspected one.
• The rewrite is justified by the plan, not by folklore; if EXPLAIN does not change, the rewrite is not done.
• Every LEFT-vs-INNER, DISTINCT, and UNION-vs-UNION ALL choice is explicitly justified against multiplicity and NULL behavior.

Do NOT

• Do NOT recommend creating or reordering an index - that is index-advisor's job. State that a rewrite assumes the supporting index exists, and hand off if it does not.
• Do NOT rewrite a query the planner already optimizes; check EXPLAIN before refactoring.
• Do NOT trade correctness for speed - never ship a rewrite whose output has not been diffed against the original.
• Do NOT use NOT IN with a subquery that can yield NULLs; use NOT EXISTS.
• Do NOT convert an outer join to an inner join (or EXISTS to JOIN) without checking whether unmatched rows or duplicate multiplicity change the result.