Indexes

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

-- Composite (multi-column) index
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

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

-- Partial index (only indexes subset of rows)
CREATE INDEX idx_orders_pending ON orders(user_id)
WHERE status = 'pending';

-- Expression/functional index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Drop index
DROP INDEX idx_users_email;

-- List indexes
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

-- When to index:
-- WHERE clauses: WHERE user_id = 42
-- JOIN columns: ON orders.user_id = users.id
-- ORDER BY: ORDER BY created_at DESC
-- GROUP BY with heavy aggregations

-- Index every foreign key (critical for JOIN performance)
CREATE INDEX idx_orders_user_id      ON orders(user_id);
CREATE INDEX idx_order_items_order   ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_products_category   ON products(category_id);

-- Index common search/filter columns
CREATE INDEX idx_products_active_price ON products(price) WHERE is_active = TRUE;
CREATE INDEX idx_orders_status_date    ON orders(status, created_at DESC);

-- Index for text search (avoid using LIKE '%search%' on large tables)
CREATE INDEX idx_products_name_search ON products USING gin(to_tsvector('english', name));

-- Composite index matches queries like:
-- WHERE user_id = 42 AND status = 'pending'
-- WHERE user_id = 42 (leftmost prefix — still uses index!)
-- NOT: WHERE status = 'pending' alone (rightmost column — no index benefit)
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);

-- Check index usage
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
-- Look for "Index Scan" (good) vs "Seq Scan" (bad for large tables)