SQL

Purpose

Write SQL that the planner can execute efficiently, and read execution plans well enough to know why it did not.

When to Use

• Writing non-trivial queries: aggregations, window functions, recursive CTEs.
• Diagnosing a slow query.
• Designing indexes for a known access pattern.
• Choosing a transaction isolation level.
• Reviewing migrations for lock risk.

Capabilities

• Query authoring: joins, CTEs, window functions, lateral joins, upserts.
• Index design: composite ordering, covering indexes, partial indexes.
• Plan reading: EXPLAIN (ANALYZE, BUFFERS) and the shapes that signal trouble.
• Isolation levels and the anomalies each one permits.
• Safe migrations: concurrent index builds, backfills, lock avoidance.

Inputs

• The query, the schema, and the row counts of the tables involved.
• Existing indexes.
• The actual execution plan, not a guess about it.

Outputs

• A rewritten query, an index, or both — with a before/after plan.
• Migration statements that do not hold long locks.

Workflow

1. Get the plan — EXPLAIN (ANALYZE, BUFFERS). Never optimize a query you have not profiled.
2. Find the expensive node — Look for sequential scans on large tables, nested loops with high row counts, and estimates that diverge from actuals by an order of magnitude.
3. Fix the cause — Bad estimate means stale statistics. Sequential scan on a selective filter means a missing index. High row counts through a join means the filter is applied too late.
4. Index deliberately — Column order in a composite index is equality columns first, then the range or sort column.
5. Re-measure — Confirm with a fresh plan, and check that write throughput did not regress.

Best Practices

• An index on (a, b) serves queries filtering on a, and on a and b — but not on b alone.
• Wrapping an indexed column in a function (WHERE lower(email) = ...) disables the index unless the index is on the expression.
• SELECT * in application code prevents index-only scans and breaks when the schema changes.
• Never run an unbounded UPDATE or DELETE on a large table in one transaction — batch it.
• CREATE INDEX CONCURRENTLY in production; the plain form locks writes for the duration.
• Prefer keyset pagination (WHERE id > :last) over OFFSET — offset cost grows linearly with page depth.

Examples

Window function instead of a correlated subquery:

-- Latest order per customer, one pass.
SELECT customer_id, order_id, placed_at, total_cents
FROM (
  SELECT
    o.customer_id,
    o.id AS order_id,
    o.placed_at,
    o.total_cents,
    ROW_NUMBER() OVER (
      PARTITION BY o.customer_id
      ORDER BY o.placed_at DESC
    ) AS rn
  FROM orders o
  WHERE o.placed_at >= now() - interval '90 days'
) ranked
WHERE rn = 1;

Index matching the access pattern:

-- Query: WHERE tenant_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 50
CREATE INDEX CONCURRENTLY idx_tickets_tenant_open_recent
  ON tickets (tenant_id, status, created_at DESC)
  WHERE deleted_at IS NULL;

Notes

• The partial index above only covers live rows, keeping it small and hot in cache.
• READ COMMITTED (the default in PostgreSQL) permits non-repeatable reads. If a transaction reads a row, decides, and writes based on that decision, you need REPEATABLE READ plus retry logic, or SELECT ... FOR UPDATE.
• Statistics drift after bulk loads. Run ANALYZE before benchmarking anything.