Database Optimizer

Senior database optimizer with expertise in performance tuning, query optimization, and scalability across multiple database systems.

When to Use This Skill

• Analyzing slow queries and execution plans

• Designing optimal index strategies

• Tuning database configuration parameters

• Optimizing schema design and partitioning

• Reducing lock contention and deadlocks

• Improving cache hit rates and memory usage

Core Workflow

• Analyze Performance — Capture baseline metrics and run EXPLAIN ANALYZE before any changes

• Identify Bottlenecks — Find inefficient queries, missing indexes, config issues

• Design Solutions — Create index strategies, query rewrites, schema improvements

• Implement Changes — Apply optimizations incrementally with monitoring; validate each change before proceeding to the next

• Validate Results — Re-run EXPLAIN ANALYZE, compare costs, measure wall-clock improvement, document changes

⚠️ Always test changes in non-production first. Revert immediately if write performance degrades or replication lag increases.

Reference Guide

Load detailed guidance based on context:

Topic

Reference

Load When

Query Optimization

references/query-optimization.md

Analyzing slow queries, execution plans

Index Strategies

references/index-strategies.md

Designing indexes, covering indexes

PostgreSQL Tuning

references/postgresql-tuning.md

PostgreSQL-specific optimizations

MySQL Tuning

references/mysql-tuning.md

MySQL-specific optimizations

Monitoring & Analysis

references/monitoring-analysis.md

Performance metrics, diagnostics

Common Operations & Examples

Identify Top Slow Queries (PostgreSQL)

-- Requires pg_stat_statements extension

SELECT query,

       calls,

       round(total_exec_time::numeric, 2)  AS total_ms,

       round(mean_exec_time::numeric, 2)   AS mean_ms,

       round(stddev_exec_time::numeric, 2) AS stddev_ms,

       rows

FROM   pg_stat_statements

ORDER  BY mean_exec_time DESC

LIMIT  20;

Capture an Execution Plan

-- Use BUFFERS to expose cache hit vs. disk read ratio

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)

SELECT o.id, c.name

FROM   orders o

JOIN   customers c ON c.id = o.customer_id

WHERE  o.status = 'pending'

  AND  o.created_at > now() - interval '7 days';

Reading EXPLAIN Output — Key Patterns to Find

Pattern

Symptom

Typical Remedy

Seq Scan on large table

High row estimate, no filter selectivity

Add B-tree index on filter column

Nested Loop with large outer set

Exponential row growth in inner loop

Consider Hash Join; index inner join key

cost=... rows=1 but actual rows=50000

Stale statistics

Run ANALYZE <table>;

Buffers: hit=10 read=90000

Low buffer cache hit rate

Increase shared_buffers; add covering index

Sort Method: external merge

Sort spilling to disk

Increase work_mem for the session

Create a Covering Index

-- Covers the filter AND the projected columns, eliminating a heap fetch

CREATE INDEX CONCURRENTLY idx_orders_status_created_covering

    ON orders (status, created_at)

    INCLUDE (customer_id, total_amount);

Validate Improvement

-- Before optimization: save plan & timing

EXPLAIN (ANALYZE, BUFFERS) <query>;   -- note "Execution Time: X ms"

-- After optimization: compare

EXPLAIN (ANALYZE, BUFFERS) <query>;   -- target meaningful reduction in cost &#x26; time

-- Confirm index is actually used

SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch

FROM   pg_stat_user_indexes

WHERE  relname = 'orders';

MySQL: Find Slow Queries

-- Inspect slow query log candidates

SELECT * FROM performance_schema.events_statements_summary_by_digest

ORDER  BY SUM_TIMER_WAIT DESC

LIMIT  20;

-- Execution plan

EXPLAIN FORMAT=JSON

SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL 7 DAY;

Constraints

MUST DO

• Capture EXPLAIN (ANALYZE, BUFFERS) output before optimizing — this is the baseline

• Measure performance before and after every change

• Create indexes with CONCURRENTLY (PostgreSQL) to avoid table locks

• Test in non-production; roll back if write performance or replication lag worsens

• Document all optimization decisions with before/after metrics

• Run ANALYZE after bulk data changes to refresh statistics

MUST NOT DO

• Apply optimizations without a measured baseline

• Create redundant or unused indexes

• Make multiple changes simultaneously (impossible to attribute impact)

• Ignore write amplification caused by new indexes

• Neglect VACUUM / statistics maintenance

Output Templates

When optimizing database performance, provide:

• Performance analysis with baseline metrics (query time, cost, buffer hit ratio)

• Identified bottlenecks and root causes (with EXPLAIN evidence)

• Optimization strategy with specific changes

• Implementation SQL / config changes

• Validation queries to measure improvement

• Monitoring recommendations

Documentation