Accidental Full Table Scan — The Query That Brought Down Production

Database CPU: 100% (sustained)
pg_stat_activity: 1 query, state "active", duration 4+ minutes
pg_locks: multiple queries waiting on "relation" lock
Application: HTTP 504 timeouts on all endpoints
Alert: "Database connection pool exhausted"
 
Root cause query:
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-14'

-- Index exists:
CREATE INDEX idx_orders_created_at ON orders(created_at);
 
-- WRONG: DATE() wraps the column — index is ignored, full scan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-14';
-- Seq Scan on orders (rows=50000000, actual time=42187ms)
-- Filter: (date(created_at) = '2026-03-14')
-- Rows Removed by Filter: 49998753
 
-- CORRECT: Range query on the indexed column directly
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at >= '2026-03-14 00:00:00'
  AND created_at  < '2026-03-15 00:00:00';
-- Index Scan using idx_orders_created_at on orders
-- (rows=1247, actual time=2.1ms)
 
-- Other common function-wrapping traps:
-- WRONG:
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
SELECT * FROM products WHERE UPPER(sku) = 'ABC-123';
SELECT * FROM logs WHERE TO_CHAR(event_time, 'YYYY-MM') = '2026-03';
 
-- CORRECT: Use a functional index that matches the query exactly
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Now the functional index is used

-- Column definition: users.phone VARCHAR(20)
-- Index: CREATE INDEX idx_users_phone ON users(phone);
 
-- WRONG: Integer parameter, VARCHAR column — PostgreSQL casts the column
SELECT * FROM users WHERE phone = 1234567890;
-- Seq Scan: PostgreSQL cannot use the index because it must cast every row
 
-- CORRECT: Match the column type exactly
SELECT * FROM users WHERE phone = '1234567890';
-- Index Scan: types match, index is used

// The same trap in Node.js application code
 
// WRONG: req.user.id is a number, but user_id column is UUID (string)
const sessions = await db.query(
  'SELECT * FROM sessions WHERE user_id = $1',
  [req.user.id]  // number being passed for a string column
)
 
// CORRECT: Ensure the parameter type matches the column type
const sessions = await db.query(
  'SELECT * FROM sessions WHERE user_id = $1',
  [String(req.user.id)]
)
 
// With Prisma — this is handled automatically because the schema defines types
// But raw SQL queries require manual type management
const sessions = await prisma.$queryRaw`
  SELECT * FROM sessions WHERE user_id = ${req.user.id}::text
`

// WRONG: Optional parameter, undefined produces full scan
async function getOrdersByUser(userId?: string) {
  // If userId is undefined, this becomes: WHERE user_id = NULL
  // PostgreSQL reads every row to find NULL matches (finds none)
  const result = await db.query(
    'SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC',
    [userId]
  )
  return result.rows
}
 
// CORRECT: Validate required parameters before querying
async function getOrdersByUser(userId: string) {
  if (!userId || typeof userId !== 'string') {
    throw new Error('userId is required and must be a string')
  }
 
  const result = await db.query(
    'SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 100',
    [userId]
  )
  return result.rows
}
 
// Dynamic WHERE clauses require extra care
function buildOrdersQuery(
  filters: { userId?: string; status?: string; startDate?: string }
) {
  const conditions: string[] = []
  const params: unknown[] = []
  let paramIndex = 1
 
  if (filters.userId) {
    conditions.push(`user_id = $${paramIndex++}`)
    params.push(filters.userId)
  }
  if (filters.status) {
    conditions.push(`status = $${paramIndex++}`)
    params.push(filters.status)
  }
  if (filters.startDate) {
    conditions.push(`created_at >= $${paramIndex++}`)
    params.push(filters.startDate)
  }
 
  // Require at least one filter on large tables
  if (conditions.length === 0) {
    throw new Error('At least one filter is required when querying orders')
  }
 
  const where = conditions.join(' AND ')
  return { sql: `SELECT * FROM orders WHERE ${where} LIMIT 100`, params }
}

-- Index exists on user_id but not on notes
-- WRONG: OR forces full scan because 'notes' is not indexed
SELECT * FROM orders
WHERE user_id = '123'
   OR notes LIKE '%refund%';
-- PostgreSQL must evaluate EVERY row because of the OR with notes
 
-- CORRECT option 1: UNION (each branch can use its own index)
SELECT * FROM orders WHERE user_id = '123'
UNION
SELECT * FROM orders WHERE to_tsvector('english', notes) @@ to_tsquery('refund');
 
-- CORRECT option 2: Add a GIN index for full-text search on notes
CREATE INDEX idx_orders_notes_fts ON orders USING GIN (to_tsvector('english', notes));
SELECT * FROM orders
WHERE user_id = '123'
   OR to_tsvector('english', notes) @@ to_tsquery('english', 'refund');

-- Run EXPLAIN ANALYZE before deploying
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-14';
 
-- Reading the output:
-- "Seq Scan"           = full table scan (investigate this)
-- "Index Scan"         = good, index is being used
-- "Index Only Scan"    = best, reads only the index
-- "Rows Removed by Filter: 49998753" = 50M rows read, only 1247 returned
-- "Execution Time: 42187.3 ms"       = 42 seconds, unacceptable
 
-- After fix:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE created_at >= '2026-03-14' AND created_at < '2026-03-15';
 
-- "Index Scan using idx_orders_created_at"
-- "Rows Removed by Filter: 0"
-- "Execution Time: 2.1 ms"  -- 20,000x faster

-- Enable slow query logging in postgresql.conf
-- log_min_duration_statement = 500  -- log queries slower than 500ms
-- log_statement = 'none'
 
-- Find top slow queries using pg_stat_statements
SELECT
  left(query, 120) AS query,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(rows / calls::numeric, 0) AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

// Node.js: automatic slow query logging via pg pool
import { Pool } from 'pg'
 
const pool = new Pool({ connectionString: process.env.DATABASE_URL })
 
// Wrap all queries with timing
const originalQuery = pool.query.bind(pool)
pool.query = async function (...args: any[]) {
  const start = Date.now()
  const query = typeof args[0] === 'string' ? args[0] : args[0]?.text ?? 'unknown'
 
  try {
    const result = await originalQuery(...args)
    const duration = Date.now() - start
 
    if (duration > 500) {
      console.warn({
        query: query.substring(0, 200),
        duration_ms: duration,
        rows: result.rowCount,
      }, 'Slow query detected')
    }
 
    return result
  } catch (err) {
    const duration = Date.now() - start
    console.error({ query: query.substring(0, 200), duration_ms: duration, err }, 'Query failed')
    throw err
  }
} as any