Database Query Optimization: Indexes, EXPLAIN, and N+1 Queries

-- PostgreSQL: enable slow query logging
-- In postgresql.conf:
log_min_duration_statement = 1000  -- log queries > 1 second

-- Or use pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the slowest queries
SELECT
    query,
    calls,
    total_exec_time / calls AS avg_ms,
    rows / calls AS avg_rows,
    total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

// Node.js: log slow queries with Prisma
const prisma = new PrismaClient({
    log: [
        { emit: 'event', level: 'query' },
    ],
});

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

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 10;

-- Index on a frequently filtered column
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Verify it's being used
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- Should show: Index Scan using idx_users_email

-- For queries like: WHERE user_id = ? AND status = ? ORDER BY created_at DESC
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at DESC);

-- This index supports:
-- WHERE user_id = 42
-- WHERE user_id = 42 AND status = 'pending'
-- WHERE user_id = 42 AND status = 'pending' ORDER BY created_at DESC

-- This index does NOT support:
-- WHERE status = 'pending'  (skips user_id — leftmost prefix rule)

-- Only index active users (much smaller, faster)
CREATE INDEX idx_users_email_active ON users(email)
WHERE deleted_at IS NULL;

-- Only index recent orders
CREATE INDEX idx_orders_recent ON orders(user_id, created_at)
WHERE created_at > NOW() - INTERVAL '90 days';

-- Query: SELECT id, name, email FROM users WHERE email = ?
-- Without INCLUDE: index lookup → table fetch for name
-- With INCLUDE: answered entirely from index
CREATE INDEX idx_users_email_cover ON users(email)
INCLUDE (id, name);

-- EXPLAIN should show: Index Only Scan

-- Tables with many sequential scans (candidates for indexing)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND n_live_tup > 10000
ORDER BY seq_tup_read DESC;

-- Unused indexes (wasting write performance)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE 'pg_%'
ORDER BY schemaname, tablename;

// BAD: N+1 — 1 query for users + N queries for posts
const users = await User.findAll();  // 1 query
for (const user of users) {
    user.posts = await Post.findAll({ where: { userId: user.id } });  // N queries
}
// Total: 1 + N queries

// GOOD: 2 queries with JOIN
const users = await User.findAll({
    include: [{ model: Post }]  // Sequelize eager loading
});
// Total: 2 queries (users + posts with IN clause)

// Prisma: use include for eager loading
const users = await prisma.user.findMany({
    include: {
        posts: {
            select: { id: true, title: true, createdAt: true }
        }
    }
});

// TypeORM: use relations
const users = await userRepository.find({
    relations: ['posts'],
});

-- Raw SQL: single query with JOIN
SELECT
    u.id,
    u.name,
    u.email,
    COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name, u.email;

// Log all queries and look for patterns
// With Prisma:
const prisma = new PrismaClient({
    log: ['query'],
});

// With Sequelize:
const sequelize = new Sequelize(url, {
    logging: (sql) => console.log(sql),
});

// Look for repeated queries like:
// SELECT * FROM posts WHERE user_id = 1
// SELECT * FROM posts WHERE user_id = 2
// SELECT * FROM posts WHERE user_id = 3
// → This is N+1

-- Page 1
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Page 100 (scans 2000 rows to skip them)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 1980;

-- First page
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: use last item's values as cursor
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-03-01 10:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

// API implementation
async function getPosts(cursor, limit = 20) {
    const where = cursor
        ? {
            OR: [
                { createdAt: { lt: cursor.createdAt } },
                { createdAt: cursor.createdAt, id: { lt: cursor.id } }
            ]
          }
        : {};

    const posts = await prisma.post.findMany({
        where,
        orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],
        take: limit + 1,  // fetch one extra to check if there's a next page
    });

    const hasNextPage = posts.length > limit;
    const items = hasNextPage ? posts.slice(0, -1) : posts;
    const nextCursor = hasNextPage
        ? { createdAt: items[items.length - 1].createdAt, id: items[items.length - 1].id }
        : null;

    return { items, nextCursor, hasNextPage };
}

-- BAD: fetches all columns including large text/blob fields
SELECT * FROM articles WHERE id = 42;

-- GOOD: fetch only needed columns
SELECT id, title, excerpt, author_id, created_at
FROM articles
WHERE id = 42;

// BAD: separate queries — inconsistent if one fails
await db.query('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
await db.query('UPDATE accounts SET balance = balance + 100 WHERE id = 2');

// GOOD: atomic transaction
await db.transaction(async (trx) => {
    await trx('accounts').where('id', 1).decrement('balance', 100);
    await trx('accounts').where('id', 2).increment('balance', 100);
});

-- BAD: N separate INSERT statements
INSERT INTO tags (name) VALUES ('javascript');
INSERT INTO tags (name) VALUES ('typescript');
INSERT INTO tags (name) VALUES ('react');

-- GOOD: single batch INSERT
INSERT INTO tags (name) VALUES
    ('javascript'),
    ('typescript'),
    ('react');

// Prisma batch insert
await prisma.tag.createMany({
    data: [
        { name: 'javascript' },
        { name: 'typescript' },
        { name: 'react' },
    ],
    skipDuplicates: true,
});

// Create indexes
db.users.createIndex({ email: 1 }, { unique: true });
db.posts.createIndex({ userId: 1, createdAt: -1 });
db.posts.createIndex({ tags: 1 });  // multikey index for arrays

// Explain a query
db.posts.find({ userId: ObjectId("...") }).explain("executionStats");
// Look for: IXSCAN (good) vs COLLSCAN (bad)

// Projection: only fetch needed fields
db.posts.find(
    { userId: ObjectId("...") },
    { title: 1, createdAt: 1, _id: 0 }  // include title, createdAt; exclude _id
);

// Aggregation pipeline optimization
db.orders.aggregate([
    { $match: { status: "pending" } },  // filter early (uses index)
    { $sort: { createdAt: -1 } },       // sort before limit
    { $limit: 20 },                      // limit before expensive operations
    { $lookup: { ... } },               // join after reducing dataset
]);