Testing Instructions Example¶

The RCA said "query is slow, consider adding indexes" but didn't
specify WHICH indexes or provide commands to create them.

For database performance issues:
1. Get EXPLAIN ANALYZE output for slow queries
2. Identify missing indexes using query plan
3. Calculate index selectivity
4. Provide specific CREATE INDEX CONCURRENTLY commands
5. Estimate performance improvement (old time → new time)

User: Validate this RCA instruction:

"For database performance issues:
1. Get EXPLAIN ANALYZE output for slow queries
2. Identify missing indexes using query plan
3. Calculate index selectivity
4. Provide specific CREATE INDEX CONCURRENTLY commands
5. Estimate performance improvement (old time → new time)"

Claude: ✓ Instruction validated successfully

Generated name: "Database Performance Analysis with Index Recommendations"
Type: RCA
Refined content: [AI-optimized version with added PostgreSQL-specific details]

Ready to test!

User: Show me database-related investigations from the last 30 days

Claude: Found 5 database investigations:

1. Slow Query - user_preferences table
   Session UUID: session-db-001
   Date: 2024-01-10
   Original RCA: Generic index recommendation

2. Connection Pool Exhaustion
   Session UUID: session-db-002
   ...

User: Apply this database performance instruction to session session-db-001

Claude: ✓ Instruction applied to test session session-db-001

The instruction has been added as a session-specific override.
It will ONLY affect this session when rerun, not other investigations.

Next: Rerun the session to see the impact.

User: Rerun session session-db-001 with the new instruction

Claude: 🔍 Rerunning investigation with new instruction...
⏳ Re-analyzing with database performance guidance... (30s)
⏳ Generating updated RCA... (15s)
✓ Investigation complete! (45s)

ROOT CAUSE:
Slow query on user_preferences table causing API latency.

CORRECTIVE ACTIONS:
- Consider adding database indexes
- Optimize query structure
- Review table size and growth

PREVENTIVE MEASURES:
- Monitor query performance
- Regular index maintenance

ROOT CAUSE:
Slow query on user_preferences table (850ms) caused by
full table scan due to missing index on user_id column.

EXPLAIN ANALYZE Output:
┌────────────────────────────────────────────────┐
│ Seq Scan on user_preferences                  │
│   Filter: (user_id = 12345)                   │
│   Rows: 500000 scanned, 1 returned            │
│   Time: 850.234 ms                            │
└────────────────────────────────────────────────┘

Missing Index Analysis:
- Column: user_id
- Selectivity: 0.0002% (1/500000)
- Cardinality: High (450K unique values)
- Recommendation: B-tree index

CORRECTIVE ACTIONS:
1. Add missing index (PRIMARY FIX)

   psql -h prod-db.rds.amazonaws.com -U admin -d production

   CREATE INDEX CONCURRENTLY idx_user_prefs_user_id
     ON user_preferences(user_id);

   Estimated improvement: 850ms → 2ms (99.8% faster)
   Index size: ~15MB
   Build time: ~30 seconds (CONCURRENTLY = no table lock)

2. Update application query to use prepared statement

3. Consider partitioning if table exceeds 1M rows

PREVENTIVE MEASURES:
- Add pg_stat_statements for query monitoring
- Set up alerts for queries >100ms
- Add index usage review to weekly maintenance
- Implement query plan analysis in code review

User: Apply this instruction to session session-api-latency-003
(an API latency incident NOT related to database)

User: Rerun session session-api-latency-003

User: Show me the RCA

User: The instruction improved database RCAs and doesn't affect
other investigation types. Add it to my Production project.

Claude: ✓ Created RCA instruction for Production project
Name: "Database Performance Analysis with Index Recommendations"
Status: Active

All future database performance investigations will now include:
- EXPLAIN ANALYZE output
- Specific index recommendations
- CREATE INDEX commands
- Performance improvement estimates

User: Show me database investigations from the last 7 days

Claude: Found 3 database investigations (all using new instruction):

1. Slow JOIN query - orders table
   RCA included: CREATE INDEX on orders.customer_id
   Estimated improvement: 1200ms → 8ms
   Quality score: 96/100

2. Missing composite index - payments table
   RCA included: CREATE INDEX on (merchant_id, created_at)
   Estimated improvement: 650ms → 3ms
   Quality score: 92/100

3. Inefficient subquery - analytics table
   RCA included: Materialized view recommendation
   Estimated improvement: 5s → 100ms
   Quality score: 89/100

Average quality score: 92.3/100 (vs 72/100 before)
Improvement: +20.3 points