Indexes — B-tree, Partial, Composite

-- Simple B-tree index (the default)
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Composite — fast for queries that filter by user_id THEN sort by created_at
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
-- The leading column matters: a query filtering only on created_at can't use this.

-- UNIQUE index — enforces uniqueness AND serves as a query index
CREATE UNIQUE INDEX idx_users_email ON users (LOWER(email));
-- Functional index — LOWER(email) lets case-insensitive lookups use it

-- Partial index — only over rows that satisfy a predicate
-- HUGE savings when ~90% of the table doesn't match
CREATE INDEX idx_active_orders ON orders (created_at)
WHERE status = 'pending';

-- Covering / include columns (PostgreSQL 11+) — index-only scans
CREATE INDEX idx_orders_user_covering ON orders (user_id)
INCLUDE (amount, status);

-- Drop indexes you don\'t need — every index slows writes
DROP INDEX IF EXISTS idx_orders_old;