SQL Query Optimization — Fix Slow Queries Step by Step (2026)

Symptom: API response times are slow. EXPLAIN shows sequential scans. Production database is under load.

Step 1: Identify Slow Queries

PostgreSQL — Enable slow query logging:

-- In postgresql.conf or via ALTER SYSTEM:
ALTER SYSTEM SET log_min_duration_statement = '1000';  -- Log queries > 1 second
SELECT pg_reload_conf();

-- Check pg_stat_statements extension (most useful)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries:
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  calls,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  substring(query, 1, 120) AS query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

MySQL — slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1 second threshold
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Step 2: EXPLAIN ANALYZE — Understand the Query Plan

-- Always use EXPLAIN ANALYZE (not just EXPLAIN) for real execution stats
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

What to look for in the output:

• Seq Scan — Sequential scan (bad for large tables without WHERE on indexed column)
• Index Scan — Using an index (good)
• Hash Join vs Nested Loop — Hash join better for large datasets
• Actual Rows vs Estimated Rows — Large difference = stale statistics
• Loops — High loop count on nested loops = N+1 problem

Step 3: Add the Right Indexes

-- Single column index for frequent WHERE/ORDER BY
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite index (column order matters — most selective first)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index (index only relevant subset)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';  -- Only indexes active users

-- Covering index (includes all needed columns, avoids heap fetch)
CREATE INDEX idx_orders_covering ON orders(user_id)
INCLUDE (id, created_at, total_amount);

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Unused indexes (candidates for removal)
ORDER BY schemaname, tablename;

Step 4: Fix the N+1 Query Problem

N+1 is the most common performance killer in ORMs:

-- BAD: N+1 pattern (1 query for users + N queries for each user's orders)
SELECT * FROM users WHERE active = true;
-- Then for EACH user:
SELECT * FROM orders WHERE user_id = ?;

-- GOOD: Single JOIN query
SELECT
  u.id,
  u.name,
  u.email,
  COUNT(o.id) as order_count,
  MAX(o.created_at) as last_order
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.active = true
GROUP BY u.id, u.name, u.email;

-- GOOD: Batch load (when you need order details)
SELECT * FROM orders
WHERE user_id IN (1, 2, 3, 4, 5...);  -- Load all at once

Step 5: Rewrite Slow Query Patterns

Pattern 1: Avoid SELECT *

-- Bad: Fetches all columns, can't use covering index
SELECT * FROM orders WHERE user_id = 123;

-- Good: Fetch only needed columns
SELECT id, status, total_amount, created_at
FROM orders WHERE user_id = 123;

Pattern 2: Use EXISTS instead of COUNT for existence check

-- Bad: Counts all matching rows
SELECT COUNT(*) FROM orders WHERE user_id = 123;
IF count > 0 THEN ...

-- Good: Stops at first match
SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = 123);

Pattern 3: Avoid functions on indexed columns in WHERE

-- Bad: Function call prevents index use
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- Good: Use functional index or normalize data
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- OR store email always lowercase and query directly
SELECT * FROM users WHERE email = 'alice@example.com';

Pattern 4: Pagination with OFFSET on large tables

-- Bad: OFFSET 10000 scans and discards 10,000 rows
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- Good: Keyset pagination (cursor-based)
SELECT * FROM posts
WHERE created_at < '2026-05-01T12:00:00'  -- Last item from previous page
ORDER BY created_at DESC
LIMIT 20;

Step 6: Update Statistics and Vacuum

-- PostgreSQL: Update planner statistics (auto-runs but sometimes needs manual trigger)
ANALYZE users;
ANALYZE orders;

-- Vacuum to reclaim dead tuple space
VACUUM ANALYZE orders;

-- Check table bloat
SELECT
  schemaname, tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) -
    pg_relation_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Quick Wins Checklist

• Index all foreign key columns
• Index columns used in WHERE, JOIN ON, ORDER BY
• EXPLAIN ANALYZE before and after every change
• Avoid SELECT *
• Use EXISTS instead of COUNT for presence checks
• Replace OFFSET pagination with keyset pagination on large tables
• Check for N+1 patterns in ORM usage
• Run VACUUM ANALYZE on heavily-updated tables