SQL Query Optimization: Indexes, Execution Plans, and Performance Tuning

-- Without index: Full table scan (slow)
SELECT * FROM users WHERE email = 'john@example.com';
-- Scans all 1,000,000 rows: ~500ms

-- Create index
CREATE INDEX idx_users_email ON users(email);

-- With index: Index seek (fast)
SELECT * FROM users WHERE email = 'john@example.com';
-- Uses index: ~5ms (100x faster)

-- Index types:

-- 1. Single-column index
CREATE INDEX idx_users_status ON users(status);

-- 2. Composite index (order matters!)
CREATE INDEX idx_users_status_created 
ON users(status, created_at);
-- Good for: WHERE status = 'active' AND created_at > '2024-01-01'
-- Good for: WHERE status = 'active'
-- ❌ Bad for: WHERE created_at > '2024-01-01' (doesn't use index)

-- 3. Covering index (includes all needed columns)
CREATE INDEX idx_users_covering 
ON users(status) 
INCLUDE (email, name);
-- Query doesn't need to access table at all!

-- 4. Unique index (enforces uniqueness + performance)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 5. Partial index (smaller, faster)
CREATE INDEX idx_active_users 
ON users(created_at) 
WHERE status = 'active';
-- Only indexes active users, saves space

-- When NOT to use indexes:
-- - Small tables (<1000 rows)
-- - Columns with low cardinality (few distinct values)
-- - Columns frequently updated (indexes slow writes)
-- - Tables with high insert/update volume

-- 1. Use EXPLAIN to see execution plan
EXPLAIN ANALYZE
SELECT u.name, o.total 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
  AND o.created_at > '2024-01-01';

-- ❌ Bad: SELECT *
SELECT * FROM users WHERE status = 'active';
-- Fetches unnecessary data, slower

-- ✅ Good: Select only needed columns
SELECT id, name, email FROM users WHERE status = 'active';

-- ❌ Bad: OR in WHERE (doesn't use indexes well)
SELECT * FROM users 
WHERE status = 'active' OR status = 'premium';

-- ✅ Good: Use IN
SELECT * FROM users 
WHERE status IN ('active', 'premium');

-- ❌ Bad: Function on indexed column
SELECT * FROM users 
WHERE LOWER(email) = 'john@example.com';
-- Doesn't use index on email

-- ✅ Good: Store lowercase, or use expression index
CREATE INDEX idx_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- ❌ Bad: LIKE with leading wildcard
SELECT * FROM users WHERE name LIKE '%John%';
-- Full table scan, can't use index

-- ✅ Good: LIKE without leading wildcard
SELECT * FROM users WHERE name LIKE 'John%';
-- Can use index

-- ❌ Bad: Implicit type conversion
SELECT * FROM users WHERE phone = 1234567890;
-- phone is VARCHAR, converts every row

-- ✅ Good: Match data types
SELECT * FROM users WHERE phone = '1234567890';

-- ❌ Bad: Joining large tables without filters
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;
-- Joins millions of rows

-- ✅ Good: Filter before joining
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
  AND o.created_at > '2024-01-01';
-- Reduces rows before join

-- ❌ Bad: Subquery in SELECT
SELECT 
  u.name,
  (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u;
-- Executes subquery for each user (N+1 problem)

-- ✅ Good: Use JOIN
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;

-- ✅ Better: WITH clause for readability
WITH active_users AS (
  SELECT id, name FROM users WHERE status = 'active'
),
recent_orders AS (
  SELECT user_id, SUM(total) as total_spent
  FROM orders
  WHERE created_at > '2024-01-01'
  GROUP BY user_id
)
SELECT au.name, ro.total_spent
FROM active_users au
JOIN recent_orders ro ON au.id = ro.user_id;

-- Join order matters (smaller table first)
-- ❌ Bad: Large table first
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'premium';  -- Only 1000 premium users

-- ✅ Good: Small table first
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'premium';

-- ❌ Bad: OFFSET (slow for large offsets)
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;
-- Scans first 10,020 rows and discards 10,000

-- ✅ Good: Cursor-based pagination
-- First page:
SELECT * FROM posts
ORDER BY id DESC
LIMIT 20;

-- Next page (use last ID from previous page):
SELECT * FROM posts
WHERE id < 9980  -- Last ID from previous page
ORDER BY id DESC
LIMIT 20;

-- ✅ Even better: Composite cursor for stable sorting
SELECT * FROM posts
WHERE (created_at, id) < ('2024-01-15 10:30:00', 9980)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Index for cursor pagination:
CREATE INDEX idx_posts_cursor ON posts(created_at DESC, id DESC);

-- ❌ Bad: COUNT(*) on large table
SELECT COUNT(*) FROM orders;
-- Full table scan: ~5 seconds

-- ✅ Good: Approximate count (PostgreSQL)
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'orders';
-- ~1ms, good enough for dashboards

-- ❌ Bad: Multiple aggregates with repeated scans
SELECT 
  (SELECT COUNT(*) FROM orders WHERE status = 'pending'),
  (SELECT COUNT(*) FROM orders WHERE status = 'completed'),
  (SELECT COUNT(*) FROM orders WHERE status = 'cancelled');
-- Scans table 3 times

-- ✅ Good: Single scan with CASE
SELECT 
  COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending,
  COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled
FROM orders;

-- ✅ Alternative: GROUP BY
SELECT status, COUNT(*) as count
FROM orders
GROUP BY status;

-- Heavy aggregations: Use materialized views
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
  DATE(created_at) as sale_date,
  COUNT(*) as order_count,
  SUM(total) as revenue
FROM orders
GROUP BY DATE(created_at);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

-- Query is now instant:
SELECT * FROM daily_sales_summary 
WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days';