SQL Query Performance Tuning

The AI would provide a comprehensive guide:

-- Before: Sequential scan on 2M rows
EXPLAIN ANALYZE SELECT * FROM orders 
WHERE created_at > '2024-01-01';

-- After: Index-only scan (100x faster)
CREATE INDEX CONCURRENTLY idx_orders_created 
ON orders (created_at DESC);

Key strategies covered:
1. B-tree vs GIN vs GiST index selection
2. Partial indexes for filtered queries
3. Covering indexes to avoid heap fetches
4. Connection pooling with PgBouncer
5. Autovacuum tuning for write-heavy tables