Optimize Database Query Performance

## Query Analysis The current query has several performance bottlenecks: - Missing indexes on `orders.user_id` and `orders.order_date` - Unnecessary LEFT JOIN when filtering on orders (excludes users with no orders anyway) - Inefficient GROUP BY with HAVING clause ## Optimized Query ```sql SELECT u.email, COUNT(*) as order_count, SUM(o.total) as total_spent FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.order_date >= '2023-01-01' GROUP BY u.id, u.email HAVING COUNT(*) > 5 ORDER BY total_spent DESC; ``` ## Indexing Recommendations 1. **Composite index**: `CREATE INDEX idx_orders_date_user ON orders(order_date, user_id, total);` 2. **Covering index**: Includes `total` to avoid table lookups ## Execution Plan Insights Look for: - Index scans instead of sequential scans - Efficient join algorithms (hash join preferred for large datasets) - Avoid nested loops with large result sets ## Alternative Approaches Consider materialized views for frequently accessed aggregations: ```sql CREATE MATERIALIZED VIEW user_order_stats AS ... ``` ## Scalability Considerations The composite index will maintain performance as data grows. Consider partitioning orders table by date for datasets exceeding 100M rows. ## Monitoring Track: execution time, index usage statistics, buffer cache hit ratio, and query plan stability.