Query Optimize

SQL Query Optimization

This skill provides procedural guidance for optimizing SQL queries effectively, with emphasis on proper benchmarking, query plan analysis, and iterative refinement.

Core Optimization Workflow

1. Establish Performance Baselines

Before making any changes, establish clear performance metrics:

• Identify the target: Determine what "optimal" performance looks like. Look for reference implementations, documented benchmarks, or theoretical analysis of query complexity.
• Measure the original query: Record execution time with multiple runs to account for caching effects.
• Document the goal: Define success as "as fast as possible" not just "faster than the original."

2. Analyze Query Plans

Always use query plan analysis before and after optimization:

-- SQLite
EXPLAIN QUERY PLAN <query>;

-- PostgreSQL
EXPLAIN ANALYZE <query>;

-- MySQL
EXPLAIN <query>;

Key indicators to look for in query plans:

• Full table scans (SCAN TABLE) vs index usage (SEARCH TABLE USING INDEX)
• Correlated subqueries executing per-row
• Temporary B-tree creation for sorting/grouping
• Materialization of intermediate results
• Join order and join types

3. Identify Common Performance Issues

Correlated Subqueries: Subqueries that reference outer query columns execute once per row. Transform to JOINs or CTEs when possible.

Repeated Computations: The same subquery appearing multiple times should be factored out into a CTE or subquery.

Missing Indexes: Check for indexes on columns used in WHERE, JOIN, and ORDER BY clauses.

Inefficient Aggregations: GROUP BY on large result sets before filtering. Consider filtering earlier with WHERE.

4. Apply Optimization Techniques

Consider multiple approaches rather than stopping at the first working solution:

Approach 1: CTEs (Common Table Expressions)

• Pre-compute values used multiple times
• Improve readability
• Note: Some databases materialize CTEs which may hurt performance

Approach 2: Window Functions

• Efficient for ranking, running totals, and row numbering
• Avoid when simpler alternatives exist

Approach 3: Subquery Restructuring

• Convert correlated subqueries to JOINs
• Push predicates into subqueries to reduce intermediate result sizes

Approach 4: Join Optimization

• Reorder joins to filter early
• Use appropriate join types (INNER vs LEFT)
• Consider join hints if available

5. Database-Specific Considerations

SQLite:

• No parallel query execution
• CTEs may be materialized (can hurt performance)
• Limited optimizer compared to enterprise databases
• Consider using covering indexes

PostgreSQL:

• Supports parallel execution
• Advanced optimizer with cost-based decisions
• CTEs are optimization barriers in older versions (< 12)

MySQL:

• Derived table materialization can be forced or avoided
• Index hints available when optimizer makes poor choices

Verification Strategy

Correctness Verification

1. Full result comparison: Compare all rows and columns against the original query output
2. Edge case testing: Test with NULL values, empty results, ties in sorting/ranking
3. Sample verification is insufficient: If the full result set is too large, use checksums or row counts with spot checks

Performance Verification

1. Multiple runs: Execute at least 3-5 times and use median time
2. Cold vs warm cache: Test both scenarios if relevant
3. Compare against optimal: If a reference solution exists, compare against it, not just the original slow query
4. Incremental profiling: Measure each CTE or subquery independently to identify bottlenecks

Common Pitfalls

Satisficing vs Optimizing

• Problem: Stopping optimization when query is "faster than before" rather than "as fast as possible"
• Prevention: Always establish what optimal performance looks like before starting. Compare against known-good implementations when available.

Skipping Query Plan Analysis

• Problem: Making changes without understanding why the query is slow
• Prevention: Always run EXPLAIN before and after changes. Understand the query plan before proposing solutions.

Premature CTE Usage

• Problem: Assuming CTEs always improve performance. Some databases materialize CTEs, adding overhead.
• Prevention: Test both CTE and non-CTE versions. Profile each CTE independently.

Over-reliance on Window Functions

• Problem: Using ROW_NUMBER() or similar when simpler approaches work
• Prevention: Consider if a GROUP BY with MIN/MAX or a simple correlated subquery might be more efficient.

Incomplete Testing

• Problem: Verifying only a sample of rows due to timeouts
• Prevention: Use checksums, row counts, or hash comparisons for full validation. Test with smaller data subsets first.

Single-Approach Optimization

• Problem: Implementing one optimization approach without exploring alternatives
• Prevention: Always test at least 2-3 different approaches before selecting the best one.

Iterative Refinement Process

1. Analyze the original query and its plan
2. Identify the primary bottleneck
3. Propose 2-3 alternative approaches
4. Implement and benchmark each approach
5. Select the best performing approach
6. Verify correctness with full result comparison
7. Document the optimization rationale

Checklist Before Declaring Success

• Query plan analyzed and understood
• Multiple optimization approaches considered
• Performance compared against optimal/reference (not just original)
• Full result correctness verified (not just samples)
• Edge cases tested (NULLs, ties, empty results)
• Performance measured across multiple runs
• Database-specific optimizations considered

Overview

This skill provides practical guidance for optimizing slow SQL queries and improving database performance. It focuses on establishing baselines, analyzing query plans, applying multiple optimization techniques, and verifying both correctness and performance. Use it to produce repeatable, benchmarked improvements and clear documentation of why changes help.

How this skill works

The skill guides you through a structured workflow: measure the original query, inspect EXPLAIN/EXPLAIN ANALYZE plans, identify bottlenecks (full scans, correlated subqueries, missing indexes), and propose several alternative rewrites. It covers techniques such as CTEs, window functions, join reordering, and database-specific adjustments for SQLite, PostgreSQL, and MySQL. Final verification uses full-result comparisons, multi-run timing, and cold vs warm cache checks.

When to use it

A query is consistently slow or causing timeouts

You need to reduce resource consumption or latency on a critical report

Refactoring queries after a schema or data-volume change

Before adding hardware: check if SQL-level improvements exist

Validating that a new query produces identical results with better performance

Best practices

Establish clear performance goals and measure baseline with multiple runs

Always run EXPLAIN (or EXPLAIN ANALYZE) before and after changes

Test at least 2–3 alternative approaches rather than accepting the first improvement

Verify full correctness via row counts, checksums, or full-result comparisons

Test both cold and warm cache and use median times from multiple runs

Account for database-specific behaviors (CTE materialization, parallelism, optimizer hints)

Example use cases

Convert a correlated subquery to a JOIN or CTE and measure latency reduction

Add or adjust indexes for slow WHERE, JOIN, or ORDER BY columns and validate plan changes

Rewrite a GROUP BY aggregation to filter earlier and reduce intermediate rows

Use window functions to replace inefficient row-by-row logic for ranking or running totals

Benchmark and choose between a materialized CTE, derived table, or inline expression on a specific DBMS

FAQ

How many runs should I