Diagnose and fix a slow SQL query using EXPLAIN ANALYZE like a DBA

Act as a database performance engineer (Postgres unless I say otherwise). I'll give you a slow query, the table schemas, and optionally the EXPLAIN ANALYZE output.

Before proposing fixes, scan for these specific anti-patterns:
- N+1 queries disguised as correlated subqueries or lateral joins — suggest batch/CTE alternatives.
- Missing COMPOSITE indexes (queries filtering on A + ordering by B need a composite (A, B) index, not two single-column indexes).
- Function calls on indexed columns that prevent index usage (e.g., WHERE LOWER(email) = ... needs a functional index or generated column).
- Implicit type casting that silently kills index scans (e.g., comparing varchar to int).
- SELECT * pulling columns not needed by the query, bloating I/O.
- Large IN() lists that should be rewritten as joins against a VALUES list or temp table.
- ORDER BY + LIMIT without a supporting index (causes full sort then discard).

Then do this:
1. Read the plan (or infer one) and name the ACTUAL bottleneck — seq scan, nested loop on unindexed join, sort spill to disk, hash join with bad estimate, etc. Explain WHY it's slow in one sentence a junior could understand.
2. Propose the smallest set of changes — most impactful first. For each:
   - Exact CREATE INDEX statement (include CONCURRENTLY for production).
   - Query rewrite (if needed) with a one-line explanation of what changed.
   - Schema tweak (if needed) with migration safety note.
3. Show the fully rewritten query.
4. Draw the expected query plan BEFORE and AFTER as a simple text diagram:
   ```
   BEFORE: Seq Scan on orders (cost=0..45000)
     → Filter: status = 'active' (removes 95% of rows)
   
   AFTER:  Index Scan using idx_orders_status on orders (cost=0..150)
     → Index Cond: status = 'active'
   ```
5. State tradeoffs honestly: write amplification, index storage cost, lock duration for migrations.
6. Flag anything that needs real data distribution (selectivity, cardinality) to decide — don't guess.

Query: [PASTE]
Schema: [PASTE]
EXPLAIN ANALYZE: [PASTE OR OMIT]