sql-query-optimization

SQL Query Optimization Skill

Step 1 — Reproduce the Slow Query

• Capture the exact query (including parameter values if possible).
• Note the current execution time (p95 from APM, or run it manually 3–5 times).
• Confirm the database engine (PostgreSQL, MySQL, SQLite, etc.) and version.

Step 2 — Run EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;  -- paste the full query here

EXPLAIN FORMAT=JSON
SELECT ...;

Step 3 — Identify the Bottleneck

Seq Scan

ANALYZE <table>

Hash Join

Buffers: shared hit

Buffers: shared read

Bitmap Heap Scan

Step 4 — Fix

-- B-tree for equality and range
CREATE INDEX CONCURRENTLY ON orders(user_id);

-- Partial index for common filter
CREATE INDEX CONCURRENTLY ON orders(created_at)
  WHERE status = 'pending';

-- Covering index to eliminate heap fetch
CREATE INDEX CONCURRENTLY ON orders(user_id)
  INCLUDE (status, total_amount);

-- Expression index for function-wrapped column
CREATE INDEX CONCURRENTLY ON users(lower(email));

CONCURRENTLY

• Replace correlated subqueries with

  JOIN

  or

  EXISTS

  .
• Replace

  SELECT *

  with specific columns needed.
• Replace

  OFFSET

  pagination with keyset pagination:
  sql

  -- Instead of: LIMIT 20 OFFSET 10000
  WHERE id > :last_seen_id ORDER BY id LIMIT 20

• Replace

  IN (SELECT ...)

  with

  EXISTS (SELECT 1 FROM ...)

  for large subqueries.

ANALYZE orders;           -- single table
ANALYZE;                  -- all tables (run as superuser)

Step 5 — Verify Improvement

EXPLAIN (ANALYZE, BUFFERS)

• Confirm the plan uses the new index.
• Compare actual execution time to the baseline.
• Check that row estimates are now accurate.

Step 6 — Document

-- Supports: orders list by user, filtered on status (POST /api/users/:id/orders)
CREATE INDEX CONCURRENTLY ON orders(user_id, status);