Rewrites a structurally inefficient SQL query into a faster equivalent that returns identical results - correlated subqueries to joins or LATERAL, accidental cross joins to explicit ON predicates, OR-across-columns to UNION, SELECT * to projected columns, and deep OFFSET pagination to keyset. Use when a query is slow because of its shape rather than its indexes - EXPLAIN shows a Cartesian blowup, a per-row subquery, or OFFSET discarding tens of thousands of rows - and the indexes are already in place. Do NOT use when the fix is adding or reordering an index - use index-advisor instead; do NOT use to diagnose an unknown slow query from its plan - use sql-query-optimizer instead.
Click to play with sound.
---
name: SQL Query Rewriter
description: Rewrites a structurally inefficient SQL query into a faster equivalent that returns identical results - correlated subqueries to joins or LATERAL, accidental cross joins to explicit ON predicates, OR-across-columns to UNION, SELECT * to projected columns, and deep OFFSET pagination to keyset. Use when a query is slow because of its shape rather than its indexes - EXPLAIN shows a Cartesian blowup, a per-row subquery, or OFFSET discarding tens of thousands of rows - and the indexes are already in place. Do NOT use when the fix is adding or reordering an index - use index-advisor instead; do NOT use to diagnose an unknown slow query from its plan - use sql-query-optimizer instead.
---
# 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.