PostgreSQL / MySQL Query Optimization, Indexing & EXPLAIN ANALYZE

System Directive • Copy and Paste Below

Act as a senior Database Administrator and PostgreSQL / MySQL query optimization guru.

Analyze and optimize the following slow database query and execution plan.

### DATABASE CONTEXT:
- Database Engine & Version: [DATABASE_ENGINE_E_G_POSTGRESQL_16_OR_MYSQL_8]
- Approximate Table Size (row count): [TABLE_ROW_COUNTS]
- The Slow SQL Query:
```sql
[PASTE_SLOW_SQL_QUERY_HERE]
```
- EXPLAIN (ANALYZE, BUFFERS) Output (if available):
```
[PASTE_EXPLAIN_ANALYZE_OUTPUT_HERE]
```
- Current Table Schema & Existing Indexes:
```sql
[PASTE_TABLE_SCHEMA_AND_INDEXES]
```

### DELIVERABLES:
1. **Root Cause Bottleneck Diagnosis**:
   - Identifying why the planner chose sequential scans, nested loop joins, or external disk sorts.
2. **Rewritten & Optimized SQL Query**:
   - Refactored query using modern SQL techniques (CTEs, subquery flattening, window functions, conditional joins, or eliminating unnecessary subqueries).
3. **Targeted Indexing Strategy (DDL Statements)**:
   - Exact `CREATE INDEX CONCURRENTLY` statements (e.g. Composite B-Tree indexes with optimal column ordering, Partial indexes, or GIN/BRIN indexes for JSONB/timestamps).
4. **Maintenance & Configuration Tuning**:
   - Suggested `work_mem`, `random_page_cost`, or `effective_cache_size` recommendations relevant to this workload.
5. **Expected Performance Gain Explanation**:
   - Step-by-step mathematical explanation of how this changes algorithmic complexity from O(N) to O(log N).

More Prompts in Coding & Development