Indexes and Performance

Without index: Scan ALL rows → O(n)
With index:    B-tree lookup  → O(log n)

For 1 million rows:
  Full scan:  ~1,000,000 operations
  Index:      ~20 operations

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Composite index (multiple columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Partial index (only index some rows)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- ✅ CREATE INDEX on columns that are:
-- 1. Used in WHERE clauses frequently
SELECT * FROM orders WHERE user_id = 123;     -- Index on user_id
SELECT * FROM users WHERE email = 'a@m.com';  -- Index on email

-- 2. Used in JOIN conditions
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;  -- Index on user_id

-- 3. Used in ORDER BY
SELECT * FROM orders ORDER BY created_at DESC;  -- Index on created_at

-- 4. Used in GROUP BY
SELECT department, COUNT(*) FROM employees GROUP BY department;

-- ❌ DON'T index:
-- - Small tables (< 1000 rows)
-- - Columns with few unique values (gender, boolean)
-- - Columns rarely used in queries
-- - Frequently updated columns (index maintenance cost)

-- See how PostgreSQL executes a query
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- With actual execution statistics
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- Example output:
-- Index Scan using idx_orders_user_id on orders  (cost=0.42..8.44 rows=1 width=...)
--   Index Cond: (user_id = 123)
-- Planning Time: 0.1 ms
-- Execution Time: 0.05 ms

-- ❌ BAD: Selecting all columns
SELECT * FROM orders WHERE user_id = 123;

-- ✅ GOOD: Select only what you need
SELECT id, product, amount, created_at FROM orders WHERE user_id = 123;

-- ❌ BAD: Using functions on indexed columns
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- ✅ GOOD: Use an expression index instead
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- ❌ BAD: Leading wildcard (can't use index)
SELECT * FROM users WHERE name LIKE '%son';

-- ✅ GOOD: Trailing wildcard (can use index)
SELECT * FROM users WHERE name LIKE 'John%';

-- ❌ BAD: OR with different columns
SELECT * FROM users WHERE email = 'a@m.com' OR city = 'NYC';

-- ✅ GOOD: UNION ALL
SELECT * FROM users WHERE email = 'a@m.com'
UNION ALL
SELECT * FROM users WHERE city = 'NYC' AND email != 'a@m.com';

-- ❌ BAD: NOT IN with subquery
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);

-- ✅ GOOD: NOT EXISTS
SELECT * FROM users u WHERE NOT EXISTS (
    SELECT 1 FROM banned_users b WHERE b.user_id = u.id
);

-- ❌ BAD: N+1 queries (application code)
-- SELECT * FROM users;
-- Then for each user: SELECT * FROM orders WHERE user_id = ?

-- ✅ GOOD: JOIN
SELECT u.*, o.*
FROM users u
JOIN orders o ON u.id = o.user_id;

-- CTEs make complex queries readable
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', created_at)
),
growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_month,
        ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) /
              LAG(revenue) OVER (ORDER BY month) * 100, 1) AS growth_pct
    FROM monthly_revenue
)
SELECT * FROM growth WHERE growth_pct > 10;

-- Update statistics for query planner
ANALYZE users;

-- Remove dead tuples and update statistics
VACUUM ANALYZE users;

-- Check table bloat
SELECT
    schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;