Query Optimization

Identify Problematic Queries in Application Code

// Prisma — enable query logging
const db = new PrismaClient({
  log: [
    { level: 'query', emit: 'event' },
    { level: 'warn', emit: 'stdout' },
  ],
});

// Log all queries in development
db.$on('query', (e) => {
  if (e.duration > 100) {
    console.warn(`Slow query (${e.duration}ms):`, e.query);
  }
});

Step 2 — Read EXPLAIN ANALYZE Output

-- Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 20;

Reading the Plan — Key Signals

Example: Identifying a Missing Index

-- Bad plan: Seq Scan on 10M row table
Seq Scan on orders  (cost=0.00..285432.00 rows=1234 width=8)
                    (actual time=0.052..2847.123 rows=1234 loops=1)
  Filter: (user_id = 42 AND status = 'pending')
  Rows Removed by Filter: 9998766

-- Fix: composite index
CREATE INDEX CONCURRENTLY idx_orders_user_status
  ON orders(user_id, status)
  WHERE status IN ('pending', 'processing');  -- Partial index reduces size

-- After index: Index Scan
Index Scan using idx_orders_user_status on orders
  (cost=0.56..12.34 rows=8 width=8)
  (actual time=0.023..0.041 rows=8 loops=1)
  Index Cond: (user_id = 42 AND status = 'pending')

Step 3 — Index Strategy Selection

Index Type Decision Framework

Composite Index Column Order Rule

-- Selectivity rule: most selective first, then range/sort
-- Query: WHERE user_id = ? AND status = ? ORDER BY created_at DESC

-- Correct order: equality columns first, range/sort column last
CREATE INDEX idx_orders_composite ON orders(user_id, status, created_at DESC);

-- Wrong: range column in the middle breaks index use for rest
-- CREATE INDEX bad_idx ON orders(user_id, created_at, status);

Covering Index (Index-Only Scans)

-- Include all selected columns to avoid heap fetches
CREATE INDEX idx_users_email_covering
  ON users(email)
  INCLUDE (id, name, created_at);

-- Query becomes Index Only Scan — zero heap access
SELECT id, name, created_at FROM users WHERE email = '[email protected]';

Step 4 — N+1 Detection & Resolution

Detecting N+1 in ORM Code

// BAD — N+1: 1 query for users + N queries for each user's posts
const users = await db.user.findMany({ take: 100 });
for (const user of users) {
  const posts = await db.post.findMany({ where: { userId: user.id } });
  // 101 total queries
}

// GOOD — Eager loading with Prisma include
const users = await db.user.findMany({
  take: 100,
  include: {
    posts: {
      select: { id: true, title: true, publishedAt: true },
      where: { published: true },
      orderBy: { publishedAt: 'desc' },
      take: 5,
    },
  },
});
// 2 queries total: users + a JOIN or batched post fetch

Detecting N+1 with Query Counter

// middleware/query-counter.ts — development only
let queryCount = 0;
db.$use(async (params, next) => {
  queryCount++;
  const result = await next(params);
  return result;
});

// In tests
beforeEach(() => { queryCount = 0; });
afterEach(() => {
  if (queryCount > 10) {
    console.warn(`Potential N+1: ${queryCount} queries in this test`);
  }
});

Step 5 — Connection Pooling & Prevention

PgBouncer / Prisma Accelerate Setup

// prisma/schema.prisma
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  // PgBouncer requires this for prepared statements
  directUrl = env("DIRECT_DATABASE_URL")
}

# .env
DATABASE_URL=postgresql://user:pass@pgbouncer-host:6432/mydb?pgbouncer=true
DIRECT_DATABASE_URL=postgresql://user:pass@postgres-host:5432/mydb

Query Plan Regression in CI

-- Store baseline plan for critical queries
-- Compare in CI to catch plan regressions
EXPLAIN (FORMAT JSON, ANALYZE)
SELECT ...
-- Parse JSON output, fail if "Seq Scan" appears on tables > 10k rows

Quality Criteria

1. No Seq Scan on tables > 10k rows — All filter columns have indexes.
2. EXPLAIN ANALYZE run before optimizing — No blind index creation; plan confirms the bottleneck.
3. N+1 patterns eliminated in ORM — Eager loading or DataLoader used for relationship traversal.
4. Composite index column order matches query — Equality columns before range/sort columns.
5. pg_stat_statements enabled — Slow query visibility in production.
6. ANALYZE run after bulk inserts — Planner statistics kept current for large data loads.
7. Connection pool configured — PgBouncer or application pool prevents connection storms.
8. Partial indexes for soft deletes — WHERE deleted_at IS NULL on most queries; index matches.

Anti-Patterns

• Adding an index on every column — Index maintenance overhead slows writes; each index adds ~10-30% overhead per INSERT/UPDATE. Index only what's queried.
• Creating indexes inside transactions — CREATE INDEX CONCURRENTLY cannot run inside a transaction; causes error or falls back to locking.
• SELECT * in production queries — Fetches columns not needed; defeats covering indexes; increases network transfer.
• ORM .findAll() without pagination — Loading entire tables into memory when the user sees 20 rows.
• LIKE '%search%' on large text columns — Leading wildcard prevents B-tree use; use full-text search with GIN index and tsvector instead.
• Ignoring row count estimates in EXPLAIN — A plan with rows=1 actual rows=50000 is a statistics problem that will return to different bad plans after VACUUM.
• Not using EXPLAIN ANALYZE after adding an index — The planner might still choose a Seq Scan if statistics are stale or the table is small enough.