Optimize database queries, schemas, and configurations for performance. Analyze execution plans, index strategies, and tuning techniques.
This skill covers database performance optimization including query tuning, indexing strategies, schema design, and configuration tuning for relational and NoSQL databases.
Use this skill when you need to:
Analyze Execution Plans
-- SQL Server
SET SHOWPLAN_XML ON;
GO
SELECT * FROM orders WHERE customer_id = 123;
GO
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 123;
-- MySQL
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE customer_id = 123;
Common Query Anti-Patterns
Optimization Techniques
Index Types
Index Design Principles
-- Covering index example
CREATE INDEX idx_orders_covering
ON orders (customer_id, status, created_at)
INCLUDE (total_amount, shipping_address);
-- Partial index example
CREATE INDEX idx_active_users
ON users (last_login)
WHERE is_active = TRUE;
Index Maintenance
Normalization vs Denormalization
Data Types
Partitioning
-- PostgreSQL range partitioning
CREATE TABLE events (
id bigint,
event_time timestamp,
data jsonb
) PARTITION BY RANGE (event_time);
CREATE TABLE events_2024_q1 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
PostgreSQL Key Parameters
shared_buffers = 25% of RAM
effective_cache_size = 75% of RAM
work_mem = available RAM / max_connections / 2
maintenance_work_mem = 512MB
max_parallel_workers_per_gather = 4
random_page_cost = 1.1 # For SSD
MySQL Key Parameters
innodb_buffer_pool_size = 70-80% of RAM
innodb_log_file_size = 1-2GB
innodb_flush_log_at_trx_commit = 2 # Balance safety/performance
max_connections = 500
query_cache_type = 1 # For read-heavy workloads
Identify Slow Queries
-- PostgreSQL pg_stat_statements
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
-- SQL Server Query Store
SELECT qst.query_text_id, qst.query_sql_text,
qsrs.avg_duration, qsrs.count_executions
FROM sys.query_store_query_text qst
JOIN sys.query_store_runtime_stats qsrs
ON qst.query_text_id = qsrs.query_id
ORDER BY qsrs.avg_duration DESC;
Performance Metrics
Query Result Caching
Connection Pooling
See the examples/ directory for:
query-optimization.sql - Before/after query examplesindex-strategies.sql - Various indexing patternspartitioning-setup.sql - Table partitioning examplesmonitoring-queries.sql - Performance monitoring scripts