-- 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;