Query Performance Analysis

Introduction

Performance work is methodical: find slow queries, understand their plan, fix with indexes/rewrites, verify with measurements. Repeat.

Understanding the topic

Core concepts to understand:

• Postgres: pg_stat_statements ranks slowest queries by total time.
• MySQL: slow query log + EXPLAIN FORMAT=JSON.
• APMs: New Relic, Datadog DBM, pganalyze.
• p95/p99 latency matters more than averages.
• Reproduce on a copy of prod data, never on prod.

Syntax reference

Visual workflow / architecture:

Workflow
[prod metrics] ─▶ [find top-N slow]
                       │
                       ▼
                 EXPLAIN ANALYZE
                       │
                       ▼
            add index / rewrite / cache
                       │
                       ▼
                  re-measure p95

Real-world use

Stripe, Shopify and every fintech run weekly 'top slow queries' reviews powered by pg_stat_statements + APM dashboards.

Best practices

• Always reproduce on prod-sized data.
• Track p95/p99, not averages.
• Document the fix and the before/after numbers.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. How do you find slow queries in production?
• Q2. p95 vs average latency — why care?
• Q3. Tools you've used.
• Q4. Risk of 'optimizing on dev data'.
• Q5. Document before/after.