Database Query Optimization — EXPLAIN ANALYZE, Indexes, and Rewriting Slow Queries

-- Simple query with index usage
EXPLAIN ANALYZE
SELECT u.id, u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
ORDER BY post_count DESC
LIMIT 10;

-- Output interpretation:
-- Limit  (cost=1000.00..1000.50 rows=10 width=40) (actual time=120.456..120.480 rows=10 loops=1)
--   -> Sort  (cost=1000.00..1000.50 rows=10) (actual time=120.430..120.450 rows=10 loops=1)
--     Sort Key: (count(p.id)) DESC
--     -> HashAggregate  (cost=950.00..975.00 rows=1000 width=40) (actual time=110.200..115.300 rows=2450 loops=1)
--       -> Hash Left Join  (cost=500.00..900.00 rows=10000 width=32) (actual time=10.200..50.400 rows=24500 loops=1)
--         Hash Cond: (p.user_id = u.id)
--         -> Seq Scan on posts p  (cost=0.00..300.00 rows=10000 width=16) (actual time=0.100..5.300 rows=10000 loops=1)
--         -> Hash  (cost=400.00..400.00 rows=2000 width=16) (actual time=9.200..9.300 rows=2000 loops=1)
--           -> Seq Scan on users u  (cost=0.00..400.00 rows=2000 width=16) (actual time=0.050..2.100 rows=2000 loops=1)
--             Filter: (created_at > '2025-01-01'::timestamp without time zone)
--             Rows Removed by Filter: 8000

import { Client } from 'pg';

interface QueryPlan {
  planType: string;
  estimatedCost: [number, number];
  actualTime: [number, number];
  estimatedRows: number;
  actualRows: number;
  loopCount: number;
  relationName?: string;
  filter?: string;
}

async function analyzeQuery(client: Client, sql: string): Promise<QueryPlan[]> {
  const result = await client.query(`EXPLAIN (ANALYZE, FORMAT JSON) ${sql}`);

  const plans: QueryPlan[] = [];

  function extractPlan(plan: any): void {
    plans.push({
      planType: plan['Node Type'],
      estimatedCost: [plan['Plan Rows'], plan['Total Cost']],
      actualTime: [plan['Actual Startup Time'], plan['Actual Total Time']],
      estimatedRows: plan['Plan Rows'],
      actualRows: plan['Actual Rows'],
      loopCount: plan['Actual Loops'] || 1,
      relationName: plan['Relation Name'],
      filter: plan['Filter'],
    });

    if (plan['Plans']) {
      plan['Plans'].forEach(extractPlan);
    }
  }

  result.rows[0][0][0]?.forEach(extractPlan);

  return plans;
}

async function diagnoseSlowQuery(client: Client, sql: string): Promise<string[]> {
  const plans = await analyzeQuery(client, sql);
  const issues: string[] = [];

  for (const plan of plans) {
    const rowRatio = plan.actualRows / plan.estimatedRows;
    if (rowRatio > 10 || rowRatio < 0.1) {
      issues.push(`Row estimation off by ${rowRatio.toFixed(2)}x for ${plan.planType}`);
    }

    if (plan.planType === 'Seq Scan' && plan.actualRows > 100000) {
      issues.push(`Sequential scan on large table returning ${plan.actualRows} rows`);
    }

    if (plan.loopCount > 10) {
      issues.push(`${plan.planType} executed ${plan.loopCount} times (possible N+1)`);
    }
  }

  return issues;
}

-- B-tree index (default, for equality and range queries)
CREATE INDEX idx_users_email ON users(email);

-- Multi-column B-tree (compound key)
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- GIN index (for arrays/JSON)
CREATE INDEX idx_tags_gin ON posts USING gin(tags);

-- GiST index (for geometric/range data)
CREATE INDEX idx_spatial_gist ON locations USING gist(geometry);

-- BRIN index (for large sequential tables)
CREATE INDEX idx_events_time_brin ON events USING brin(created_at);

-- Partial index (only index WHERE condition matches)
CREATE INDEX idx_published_posts ON posts(id) WHERE published = true;

-- Expression index (index on computed values)
CREATE INDEX idx_users_name_lower ON users(LOWER(name));

-- UNIQUE index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

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

-- With INCLUDE: All data in index, no table access needed
CREATE INDEX idx_published_posts_cover ON posts(published) INCLUDE (id, title);

EXPLAIN ANALYZE
SELECT id, title FROM posts WHERE published = true LIMIT 10;
-- Index Scan using idx_published_posts_cover (cost=...)
-- Index Cond: (published = true)
-- (Notice: no "Heap Fetch" line, meaning table wasn't accessed)

-- Covering index for common queries
CREATE INDEX idx_user_posts_cover ON posts(user_id, published) INCLUDE (id, title, created_at);

-- Partial index: only published posts
CREATE INDEX idx_published_posts ON posts(created_at) WHERE published = true;

-- Query must include WHERE published = true to use this index
EXPLAIN ANALYZE
SELECT * FROM posts WHERE published = true ORDER BY created_at DESC LIMIT 10;
-- Uses index ✓

-- Common pattern: soft deletes (deleted_at IS NULL)
CREATE INDEX idx_active_users ON users(id) WHERE deleted_at IS NULL;

-- All queries must include deleted_at IS NULL
WHERE deleted_at IS NULL AND id = 123 -- Uses index
WHERE id = 123 -- Seq scan, slow

-- Pattern 1: EXISTS vs IN for subqueries
-- EXISTS: Stops after first match (faster with LIMIT)
SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id LIMIT 1);

-- IN: Collects all matching IDs, then joins
SELECT u.* FROM users u
WHERE u.id IN (SELECT DISTINCT user_id FROM posts);

-- Pattern 2: CTE vs subquery (depends on Postgres version)
-- CTE (WITH clause): Often materializes (evaluates once)
WITH active_users AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT au.id, COUNT(p.id)
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.id;

-- Pattern 3: LATERAL join for row-wise limits
-- Get each user's 3 most recent posts
SELECT u.id, p.id, p.title
FROM users u
JOIN LATERAL (
  SELECT id, title FROM posts
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) p ON true;

-- Pattern 4: ORDER BY with LIMIT optimization
-- Bad: Sort entire result set, take first 10
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;
-- Cost: Sort (100,000 rows) -> return 10

-- Good: Use index on (created_at) and take first 10
CREATE INDEX idx_posts_created_desc ON posts(created_at DESC);
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;
-- With index: Return 10 rows directly (no sort)

-- Problem: Fetch 5 most recent comments per post
-- Naive approach: Load all comments, filter in app (slow)
SELECT p.id, c.id, c.text
FROM posts p
JOIN comments c ON p.id = c.post_id
ORDER BY p.id, c.created_at DESC;

-- Solution: LATERAL join with per-row subquery
SELECT p.id, c.id, c.text
FROM posts p
JOIN LATERAL (
  SELECT id, text FROM comments
  WHERE post_id = p.id
  ORDER BY created_at DESC
  LIMIT 5
) c ON true
ORDER BY p.id, c.created_at DESC;

-- Execution:
-- 1. Scan posts (1000 rows)
-- 2. For each post, execute subquery (get 5 comments)
-- 3. Total: efficient index seeks, not table scan

-- Gotcha 1: Stale statistics (planner uses outdated row counts)
-- Fix: ANALYZE your tables regularly
ANALYZE posts;
ANALYZE users;

-- Gotcha 2: Correlated columns not recognized
-- Problem: WHERE created_at > '2025-01-01' AND status = 'active'
-- Planner doesn't know 'active' records are mostly old (correlated)
-- Fix: Partial index or manually increase planner selectivity estimate
ALTER TABLE posts ALTER COLUMN status SET STATISTICS 100;
ANALYZE posts;

-- Gotcha 3: Hash join vs nested loop
-- Hash join: Fast with large result sets (materialize smaller side in hash)
-- Nested loop: Fast with small result sets (index lookups)
-- Planner sometimes guesses wrong
SET enable_nestloop = false; -- Force hash join
RESET enable_nestloop;

-- Gotcha 4: OR conditions prevent index usage
-- Problem: WHERE id = 1 OR id = 2 (can't use simple index)
-- Better: WHERE id IN (1, 2) or use UNION
SELECT * FROM posts WHERE id = 1
UNION ALL
SELECT * FROM posts WHERE id = 2;

-- Gotcha 5: LIKE patterns don't use indexes
-- Problem: WHERE name LIKE '%john%' (full table scan)
-- Better: WHERE name LIKE 'john%' (uses index)
-- Even better: Use full-text search for text contains

-- Gotcha 6: Function calls prevent index usage
-- Problem: WHERE YEAR(created_at) = 2025 (can't use index on created_at)
-- Better: WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

import { Client } from 'pg';

interface QueryMetrics {
  query: string;
  duration: number;
  rowsAffected: number;
  timestamp: Date;
}

class QueryMonitor {
  private client: Client;
  private slowQueryThreshold = 100; // milliseconds

  async monitorQuery(query: string, params: any[]): Promise<any> {
    const start = performance.now();

    try {
      const result = await this.client.query(query, params);
      const duration = performance.now() - start;

      if (duration > this.slowQueryThreshold) {
        this.logSlowQuery({
          query,
          duration,
          rowsAffected: result.rowCount || 0,
          timestamp: new Date(),
        });
      }

      return result;
    } catch (error) {
      const duration = performance.now() - start;
      console.error(`Query failed after ${duration}ms:`, query);
      throw error;
    }
  }

  private logSlowQuery(metrics: QueryMetrics): void {
    console.warn(`Slow query (${metrics.duration.toFixed(2)}ms):`, metrics.query);

    // Send to monitoring system
    sendMetric({
      name: 'slow_query',
      duration: metrics.duration,
      timestamp: metrics.timestamp,
      query: metrics.query,
    });
  }
}