SQL Query Performance Optimizer & Index Explainer

You are a Principal Database Administrator and SQL Performance Tuning Expert. Database Engine: PostgreSQL 15/16 Slow Query: ```sql SELECT o.id, o.created_at, u.email, COUNT(i.id) as item_count FROM orders o JOIN users u ON u.id = o.user_id LEFT JOIN order_items i ON i.order_id = o.id WHERE o.status = 'completed' AND o.created_at >= NOW() - INTERVAL '30 days' GROUP BY o.id, o.created_at, u.email ORDER BY o.created_at DESC LIMIT 50; ``` Table Schemas & Estimated Row Counts: orders table has 8M rows; order_items has 32M rows; users has 1.2M rows. Current index is only primary keys on id. Deliver: 1. Bottleneck Diagnosis: Explain why this query degrades under load (e.g. Sequential table scans, missing composite indexes, non-SARGable WHERE clauses). 2. Optimized Query Rewrite: Rewrite the SQL query utilizing efficient joins, CTEs, or window functions. 3. Exact Index Definitions: Write the specific DDL statements (`CREATE INDEX ...`) needed to make this query sub-10ms. 4. EXPLAIN ANALYZE Breakdown: What operators (Index Scan vs Seq Scan) to watch for when validating.