Guides > DevOps & infrastructure

Create a priority matrix for database optimization

Prompt Warp to audit SQL queries, analyze execution plans, and generate a priority matrix ranking database optimizations by impact and effort.

Learn how to generate a data-driven optimization matrix that ranks database issues by impact, risk, and effort.

Intro
Section titled “Intro”

This tutorial teaches you how to prompt Warp to audit and optimize your database performance automatically.

It analyzes SQL queries, identifies common inefficiencies, and generates a priority matrix for improvements.
The problem
Section titled “The problem”

When you tell AI to “optimize a query,” that could mean anything — faster, safer, or simpler.
Instead, use Warp to clarify intent and return measurable outcomes.

The prompt

Paste this into Warp’s AI input:

# **Comprehensive Database Query Analysis and Optimization Guide**

This guide provides a structured, repeatable approach for analyzing, profiling, and optimizing database performance within your application.

---

## **PHASE 1: Query Discovery & Cataloging**

### **Step 1 — Identify All Queries**
Scan the entire codebase and locate every SQL or ORM-based query. This includes:

- All **raw SQL queries**, including stored procedures
- **ORM-generated queries** (capture the actual SQL being produced)
- **Dynamic query builders** and their permutations
- **Background job queries** that may run at scale
- **Admin or reporting queries** that could lock tables

### **Step 2 — Document Key Details**
For each query discovered, record the following:

- **File location and function name**
- **Frequency of execution** (per request, batch job, or cron schedule)
- **Typical data volume processed**

---

## **PHASE 2: Performance Analysis**

For each identified query, generate and analyze a detailed **execution plan**.

### **Execution Plan Commands**
```sql
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON);

-- MySQL
EXPLAIN FORMAT=JSON;
```

### **Extract the Following Metrics**
- Total execution time
- Rows examined vs. rows returned ratio
- Index usage (full table scans, index scans, seeks)
- Join methods (nested loop, hash, merge)
- Memory usage and temporary file creation
- Buffer pool hit ratio

---

## **PHASE 3: Identify Specific Problems**

### **1. N+1 Query Detection**
**Problem:** Loading users and their posts separately
**Found in:** `/api/users/controller.js:45`
**Impact:** 100 queries for 100 users instead of one batched query

#### **Current Implementation**
```js
const users = await db.query('SELECT * FROM users');
for (const user of users) {
  user.posts = await db.query('SELECT * FROM posts WHERE user_id = ?', [user.id]);
}
```

#### **Optimized Version**
```js
const usersWithPosts = await db.query(`
  SELECT u.*,
         COALESCE(json_agg(p.*) FILTER (WHERE p.id IS NOT NULL), '[]') AS posts
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id;
`);
```

---

### **2. Missing Index Analysis**
**Finding:** Full table scan on `orders` table (2M rows)
**Query:**
```sql
SELECT * FROM orders WHERE status = 'pending' AND created_at > ?;
```

**Recommendation:**
```sql
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
```

**Impact:** Query time reduced from **3.2s → 0.045s**

---

### **3. Inefficient JOIN Patterns**
**Problem:** Queries join through unnecessary intermediate tables.
**Solution:** Simplify relationships using direct joins or indexed subqueries where possible.

---

### **4. Subquery Optimization**
**Inefficient Query:**
```sql
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products WHERE category_id = p.category_id);
```

**Optimized (Using Window Function):**
```sql
WITH product_stats AS (
  SELECT *,
         AVG(price) OVER (PARTITION BY category_id) AS avg_category_price
  FROM products
)
SELECT * FROM product_stats WHERE price > avg_category_price;
```

---

## **PHASE 4: Advanced Optimizations**

### **Caching Strategies**
Use caching for:
- User-specific data with low update frequency
- Expensive aggregations that can be pre-computed

#### **Implementation**
```js
// Add caching layer with TTL
const getCachedOrQuery = async (key, query, ttl = 3600) => {
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  const result = await db.query(query);
  await redis.setex(key, ttl, JSON.stringify(result));
  return result;
};
```

---

### **Recommended Connection Configuration**
```json
{
  "connectionLimit": 50,
  "queueLimit": 100,
  "acquireTimeout": 30000,
  "waitForConnections": true,
  "idleTimeout": 300000,
  "enableKeepAlive": true,
  "keepAliveInitialDelay": 10
}
```

---

### **Batch Operation Optimization**

**Problem:** Records are inserted one by one
**Found in:** `/jobs/import-data.js`

**Current Implementation:**
1000 individual `INSERT` statements

**Optimized:**
```sql
INSERT INTO users (name, email, created_at) VALUES
  ($1, $2, $3),
  ($4, $5, $6),
  ... -- batch in groups of 1000
```

---

### **Pagination Optimization**
```sql
SELECT * FROM posts
WHERE created_at < $cursor
ORDER BY created_at DESC
LIMIT 20;
```

---

## **PHASE 5: Monitoring & Maintenance**

### **1. Slow Query Logging Setup**

#### **PostgreSQL**
```sql
ALTER SYSTEM SET log_min_duration_statement = '1000';  -- Log queries over 1s
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_duration = on;
```

#### **MySQL**
```sql
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'TABLE';
```

---

### **2. Query Performance Testing**

```js
describe('Query Performance', () => {
  test('User listing should complete under 100ms', async () => {
    const start = Date.now();
    await db.query('SELECT * FROM users LIMIT 1000');
    expect(Date.now() - start).toBeLessThan(100);
  });
});
```

---

## **PHASE 6: Deliverables**

### **Deliverable Outputs**
- **Optimization Script:** A single SQL file with all index creations, ordered by performance impact.
- **Code Changes PR:** Includes all query optimizations with before/after comparison results.

### **Performance Report**
- Baseline metrics vs. optimized metrics
- Expected resource savings (CPU, memory, I/O)
- Risk assessment for each change

### **Monitoring Dashboard**
Define recurring queries to track query performance over time.

---

## **PRIORITY MATRIX**

Rank each optimization by:
- **Impact:** Query frequency × time saved
- **Risk:** Low / Medium / High
- **Effort:** Quick fix / Moderate / Complex refactor

> Focus on **high-impact**, **low-risk**, **low-effort** items first.

---

### **Summary**
This workflow ensures the AI:
1. Systematically identifies all queries
2. Analyzes them using database-specific profiling tools
3. Provides **actionable, tested solutions**
4. Considers the full application context
5. Delivers **implementation-ready optimizations**

Warp will locate all SQL usage, test each query, and score them using explain-plan data.

Review the matrix
Section titled “Review the matrix”

The output includes:
- Query locations
- Performance metrics
- Recommended fixes
- A graph mapping impact vs effort

Create a priority matrix for database optimization

Intro

The problem

The prompt

Review the matrix