How to Debug Slow SQL Queries: A Step-by-Step Guide

-- PostgreSQL: log queries slower than 1 second
ALTER SYSTEM SET log_min_duration_statement = 1000;
SELECT pg_reload_conf();

-- MySQL: enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Enable the extension (add to postgresql.conf, then restart)
-- shared_preload_libraries = 'pg_stat_statements'

-- Top 10 queries by total execution time
SELECT
  substring(query, 1, 80) AS query_short,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round((total_exec_time / sum(total_exec_time) OVER ()) * 100, 2) AS pct_of_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Full diagnostic output
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 100;

-- For data-modifying statements: wrap and rollback
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'processed'
WHERE created_at < NOW() - INTERVAL '1 day';
ROLLBACK;

-- Refresh statistics on a table after bulk loads or large deletes
ANALYZE orders;

-- Plan node reference:
-- Seq Scan          → Full table scan (look for missing index)
-- Index Scan        → Uses index, but still fetches from table heap
-- Index Only Scan   → Fastest: index contains all needed columns
-- Hash Join         → Efficient for large row sets on both sides
-- Nested Loop       → Efficient when inner side is small and indexed
-- Rows Removed by Filter → Large number = index not filtering early enough

-- Create a composite index matching the query's filter and sort pattern
CREATE INDEX CONCURRENTLY idx_orders_status_date
ON orders(status, created_at DESC);

-- Confirm the planner uses it
EXPLAIN (ANALYZE)
SELECT id, total FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 100;
-- Expected: Index Scan using idx_orders_status_date

-- N+1 anti-pattern (bad): one query per order row
-- SELECT * FROM customers WHERE id = 1
-- SELECT * FROM customers WHERE id = 2  ... repeated 1000 times

-- Batch JOIN rewrite (good): one query total
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id = ANY(:order_ids);

-- Or with WHERE IN for a fixed list
SELECT * FROM customers
WHERE id IN (1, 2, 3, 4, 5);

-- Full index (covers all rows — large, slower for narrow filters)
CREATE INDEX idx_tickets_status ON support_tickets(status);

-- Partial index (covers only open tickets — much smaller and faster)
CREATE INDEX idx_tickets_open
ON support_tickets(created_at DESC)
WHERE status = 'open';

-- This query uses the partial index directly
SELECT * FROM support_tickets
WHERE status = 'open'
ORDER BY created_at DESC
LIMIT 50;

-- Index all foreign key columns used in JOINs
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Refresh statistics after bulk operations
ANALYZE orders;
ANALYZE order_items;

-- PostgreSQL 14+: improve estimates for correlated columns
CREATE STATISTICS orders_correlation(dependencies)
ON customer_id, status FROM orders;

-- Correlated subquery (slow): runs once per row in orders
SELECT o.id,
  (SELECT name FROM customers WHERE id = o.customer_id) AS customer_name
FROM orders o;

-- JOIN rewrite (fast): single pass over both tables
SELECT o.id, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- Functional index for case-insensitive email lookup
CREATE INDEX idx_users_email_ci ON users(LOWER(email));

-- This WHERE clause now uses the functional index
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- The original slow query
SELECT r.id, r.title, r.status, u.name AS author
FROM reports r
JOIN users u ON u.id = r.created_by
WHERE r.status = 'approved'
  AND r.created_at > NOW() - INTERVAL '30 days'
ORDER BY r.created_at DESC;

-- The fix: one composite index matching WHERE + ORDER BY
CREATE INDEX CONCURRENTLY idx_reports_status_date
ON reports(status, created_at DESC);

-- Result: Seq Scan → Index Scan
-- Query time: 4,200ms → 12ms (350x improvement)