-- Composite index for common filter + sort
CREATE INDEX idx_orders_user_date
ON orders (user_id, created_at DESC);
-- Partial index (only active records)
CREATE INDEX idx_users_active_email
ON users (email)
WHERE status = 'active';
-- Full-text search index (PostgreSQL)
CREATE INDEX idx_posts_search
ON posts
USING GIN (to_tsvector('english', title || ' ' || body));
-- Covering index (avoids table lookup)
CREATE INDEX idx_products_listing
ON products (category_id, price)
INCLUDE (name, slug, stock);
-- Check unused indexes
SELECT indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;