database-query-optimizer

name	database-query-optimizer
description	Database query analysis, optimization, and best practices
type	domain
enforcement	suggest
priority	high

Database Query Optimizer Skill

This skill provides comprehensive guidance on database query optimization, including N+1 detection, index recommendations, and query performance analysis.

N+1 Query Detection

The Problem

// BAD: N+1 Query Problem
async function getUsersWithPosts() {
  const users = await db.users.findMany(); // 1 query
  for (const user of users) {
    user.posts = await db.posts.findMany({ // N queries
      where: { userId: user.id }
    });
  }
  return users;
}
// Total: 1 + N queries (if 100 users = 101 queries!)

// GOOD: Eager Loading (1 query with join)
async function getUsersWithPostsOptimized() {
  return db.users.findMany({
    include: { posts: true }
  });
}
// Total: 1 query with JOIN

Detection Patterns

Look for these patterns:

1. Loop fetching related records
2. .map() with await inside
3. Sequential dependent queries
4. Missing include or join in ORMs

// N+1 Pattern 1: Loop fetch
for (const order of orders) {
  order.customer = await getCustomer(order.customerId); // BAD
}

// N+1 Pattern 2: Map with async
const enrichedOrders = await Promise.all(
  orders.map(async (order) => ({
    ...order,
    customer: await getCustomer(order.customerId) // BAD (parallel but still N queries)
  }))
);

// Solution: Batch fetch
const customerIds = [...new Set(orders.map(o => o.customerId))];
const customers = await getCustomersByIds(customerIds);
const customerMap = new Map(customers.map(c => [c.id, c]));
const enrichedOrders = orders.map(order => ({
  ...order,
  customer: customerMap.get(order.customerId)
}));

Index Optimization

When to Add Indexes

1. WHERE clause columns - Frequently filtered columns
2. JOIN columns - Foreign keys and join conditions
3. ORDER BY columns - Sorted result sets
4. Unique constraints - Already indexed implicitly
5. Frequently grouped columns - GROUP BY operations

Index Types

-- B-Tree (default, most common)
CREATE INDEX idx_users_email ON users(email);

-- Composite (multi-column)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial (filtered)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Covering (includes all needed columns)
CREATE INDEX idx_orders_covering ON orders(user_id, status)
INCLUDE (total, created_at);

-- Full-text search
CREATE INDEX idx_posts_content ON posts USING gin(to_tsvector('english', content));

-- JSON path (PostgreSQL)
CREATE INDEX idx_data_name ON users((data->>'name'));

Index Analysis

-- PostgreSQL: Analyze query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[e-mail]';

-- Check index usage
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- Find missing indexes (unused)
SELECT
  schemaname || '.' || relname AS table,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND n_live_tup > 10000
ORDER BY seq_tup_read DESC;

Query Optimization Techniques

SELECT Optimization

-- BAD: Select all columns
SELECT * FROM orders WHERE user_id = 123;

-- GOOD: Select only needed columns
SELECT id, status, total, created_at FROM orders WHERE user_id = 123;

-- BAD: Subquery in SELECT
SELECT
  name,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count
FROM users;

-- GOOD: Use JOIN with GROUP BY
SELECT
  u.name,
  COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

JOIN Optimization

-- Ensure join columns are indexed
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Use appropriate join type
-- INNER JOIN: Only matching rows (faster)
-- LEFT JOIN: All from left, matching from right
-- Avoid implicit cartesian products

-- BAD: Cartesian product risk
SELECT * FROM users, orders WHERE users.id = orders.user_id;

-- GOOD: Explicit join
SELECT * FROM users
INNER JOIN orders ON users.id = orders.user_id;

WHERE Clause Optimization

-- BAD: Function on indexed column (prevents index use)
SELECT * FROM users WHERE LOWER(email) = '[e-mail]';

-- GOOD: Store normalized or use functional index
SELECT * FROM users WHERE email_lower = '[e-mail]';
-- Or: CREATE INDEX idx_email_lower ON users(LOWER(email));

-- BAD: Leading wildcard (no index)
SELECT * FROM products WHERE name LIKE '%phone%';

-- GOOD: Trailing wildcard only (uses index)
SELECT * FROM products WHERE name LIKE 'phone%';

-- BAD: OR on different columns
SELECT * FROM users WHERE email = 'a' OR phone = 'b';

-- GOOD: UNION of indexed queries
SELECT * FROM users WHERE email = 'a'
UNION
SELECT * FROM users WHERE phone = 'b';

Pagination

-- BAD: OFFSET for large datasets
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000;
-- Must scan and discard 10000 rows

-- GOOD: Keyset pagination (cursor-based)
SELECT * FROM products
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;
-- Directly seeks to position using index

-- For complex sorting, use composite cursor
SELECT * FROM products
WHERE (created_at, id) < (:last_date, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

ORM Best Practices

Prisma

// Eager loading
const users = await prisma.user.findMany({
  include: {
    posts: true,
    profile: true
  }
});

// Select specific fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    posts: {
      select: { id: true, title: true }
    }
  }
});

// Batch operations
await prisma.post.createMany({
  data: posts,
  skipDuplicates: true
});

// Raw query for complex cases
const result = await prisma.$queryRaw`
  SELECT u.*, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id
`;

TypeORM

// Eager loading with relations
const users = await userRepository.find({
  relations: ['posts', 'profile'],
  where: { status: 'active' }
});

// Query builder for complex queries
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .where('user.status = :status', { status: 'active' })
  .orderBy('user.createdAt', 'DESC')
  .getMany();

Query Performance Checklist

• No N+1 queries (use eager loading or batch fetching)
• Indexes on frequently filtered columns
• Composite indexes for multi-column queries
• SELECT only needed columns
• Appropriate JOIN types used
• No functions on indexed columns in WHERE
• Cursor-based pagination for large datasets
• Query plans analyzed for slow queries
• Connection pooling configured
• Database statistics up to date
• Slow query logging enabled
• Regular index maintenance scheduled

Monitoring Queries

// Prisma query logging
const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
    { emit: 'stdout', level: 'error' },
  ],
});

prisma.$on('query', (e) => {
  if (e.duration > 100) { // Log slow queries
    console.log('Slow query:', {
      query: e.query,
      duration: e.duration,
      params: e.params
    });
  }
});