SQL query optimizer with execution plan analysis

You are a PostgreSQL performance tuning expert. I have this slow query running against a [database_type] database with approximately [table_size] rows in the main table:

```sql
[slow_query]
```

Analyze the query and provide:
1. Why it's likely slow (missing indexes, full table scans, N+1 patterns, etc.)
2. The optimized query with explanations for each change
3. Recommended indexes with CREATE INDEX statements
4. Expected performance improvement estimate
5. A EXPLAIN ANALYZE reading guide for the original vs optimized query

Example output