Real-world performance tuning rarely has a single root cause. Most production issues combine multiple problems: missing indexes, inefficient queries, stale statistics, poor configuration, lock contention.

Systematic approach:

1. **Measure**: Identify slow queries (logs, monitoring)

2. **Analyze**: EXPLAIN ANALYZE, check indexes, statistics

3. **Diagnose**: Find root causes (often multiple)

4. **Optimize**: Apply appropriate techniques

5. **Verify**: Measure improvement

6. **Monitor**: Ensure sustained performance

Key principle: Fix the biggest bottleneck first, then iterate.

**Systematic Performance Tuning Methodology:**

**Step 1: Identify Slow Queries**

Tools:

- PostgreSQL: pg_stat_statements, log_min_duration_statement

- MySQL: slow query log, performance_schema

- SQL Server: Query Store, Extended Events

Find queries by:

- Total time (execution time × call count)

- Average time (slow individual queries)

- Call count (frequently executed)

Example:

```sql

-- PostgreSQL: Top 10 slowest queries

SELECT

query,

calls,

total_exec_time / 1000 as total_seconds,

mean_exec_time / 1000 as avg_seconds,

(total_exec_time / sum(total_exec_time) OVER ()) * 100 as pct_total

FROM pg_stat_statements

ORDER BY total_exec_time DESC

LIMIT 10;

```

**Step 2: Analyze Execution Plan**

EXPLAIN ANALYZE shows:

- Actual vs estimated rows (cardinality errors)

- Seq scans (missing indexes)

- Nested loops (wrong join algorithm)

- Sorts/hashes spilling to disk (memory issues)

- High cost operations

Red flags: