SQL Query Optimization Techniques

Rewriting queries for performance, avoiding common pitfalls, using statistics

21 min read

#databases, #sql

Introduction

Query optimization is the process of improving SQL query performance through systematic analysis and modification. A poorly written query can scan millions of unnecessary rows, hold locks for extended periods, and consume excessive CPU resources. Conversely, a well-optimized query retrieves only the data it needs, uses appropriate indexes, and completes in milliseconds instead of seconds.

The difference between a slow query and a fast query often comes down to understanding how the database query optimizer works and structuring your queries to leverage its capabilities. This article covers the fundamental concepts, common pitfalls, and practical techniques for writing efficient SQL queries.

Why Query Optimization Matters

Performance issues in databases typically stem from inefficient queries rather than hardware limitations. A single poorly optimized query can:

Consume excessive CPU and memory resources
Hold locks that block other queries
Exhaust connection pool limits
Increase cloud infrastructure costs
Degrade user experience across the entire application

In modern cloud environments, query performance directly impacts operational costs. A query that takes 10 seconds instead of 0.1 seconds doesn't just affect one user - it consumes 100x more CPU time, which translates to higher cloud bills and reduced system capacity.

Most query optimization follows predictable patterns. Understanding these patterns allows you to identify and fix performance issues before they reach production.

Understanding Query Execution

When you execute a SQL query, the database processes it through several distinct phases:

The Query Execution Pipeline

Parse - Validates syntax and checks that tables and columns exist
Rewrite - Applies view definitions and query rewrite rules
Plan - Generates multiple possible execution strategies
Optimize - Evaluates costs and selects the best execution plan
Execute - Runs the selected plan and returns results

The optimization phase is critical. The query optimizer is a cost-based decision engine that examines available indexes, table statistics, and query structure to choose the most efficient execution strategy. It may evaluate dozens or hundreds of possible plans before selecting one.

The optimizer's effectiveness depends entirely on the quality of available statistics. Without accurate statistics, the optimizer makes uninformed decisions that lead to poor performance.

Database Statistics: The Foundation

Database statistics are metadata about your data distribution. They provide information such as:

Total number of rows in each table
Distribution of values within columns
Number of distinct values per column
Percentage of NULL values
Correlation between columns

How Statistics Influence Optimization

Consider this query:

SELECT * FROM orders
WHERE status = 'pending'
AND customer_id = 12345;

The optimizer must decide which index to use. The correct choice depends on data distribution:

If 90% of orders have status 'pending' but customer 12345 has only 5 orders, the customer_id index is optimal
If customer 12345 has 1 million orders but only 10 orders have status 'pending', the status index is optimal

Without statistics, the optimizer cannot make an informed decision. With current statistics, it calculates the estimated cost of each approach and selects the most efficient one.

Maintaining Statistics

Most databases automatically update statistics, but manual updates are necessary after significant data changes:

-- PostgreSQL
ANALYZE table_name;
ANALYZE; -- All tables

-- MySQL
ANALYZE TABLE table_name;

-- SQL Server
UPDATE STATISTICS table_name;

-- Oracle
EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name');

Outdated statistics are the most common cause of sudden query performance degradation. When a previously fast query becomes slow without code changes, update statistics first.

Reading Execution Plans

Execution plans show exactly how the database executes a query. They reveal which indexes are used, which tables are scanned, and how tables are joined. Understanding execution plans is essential for query optimization.

Obtaining Execution Plans

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345;

-- MySQL
EXPLAIN
SELECT * FROM orders WHERE customer_id = 12345;

-- SQL Server
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM orders WHERE customer_id = 12345;
GO

-- Oracle
EXPLAIN PLAN FOR
SELECT * FROM orders WHERE customer_id = 12345;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Key Execution Plan Elements

Execution plans are typically read from innermost to outermost operations (or bottom to top in tree format). Look for these indicators of performance problems:

Sequential Scans on Large Tables

Seq Scan on orders  (cost=0.00..50000.00 rows=1000000)

Sequential scans read every row in a table. On large tables, this indicates missing or unused indexes.

High Cost Estimates

Hash Join  (cost=25000.00..75000.00 rows=100000)

Cost values are relative. High costs compared to other operations indicate expensive steps that warrant investigation.

Inaccurate Row Estimates

Hash Join  (cost=100..200 rows=10 actual rows=500000)

Large discrepancies between estimated and actual row counts indicate stale statistics or optimizer miscalculations.

Nested Loop Joins with Large Datasets

Nested loop joins are efficient for small datasets but become exponentially slower with large datasets. Nested loops joining 100,000+ rows typically indicate a problem.

Common Query Pitfalls

Pitfall 1: Selecting Unnecessary Columns

-- Inefficient: Fetches all columns
SELECT * FROM orders WHERE status = 'pending';

-- Efficient: Fetches only needed columns
SELECT order_id, customer_id, total
FROM orders WHERE status = 'pending';

Impact: SELECT * retrieves unnecessary data, increases I/O operations, prevents covering index usage, and increases network transfer time.

Pitfall 2: Functions on Indexed Columns

-- Inefficient: Function prevents index usage
SELECT * FROM orders
WHERE YEAR(order_date) = 2024;

-- Efficient: Direct column comparison uses index
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date < '2025-01-01';

Impact: When a column is wrapped in a function, the database cannot use indexes on that column. The function must be evaluated for every row in the table.

Pitfall 3: OR Conditions on Multiple Columns

-- Inefficient: Prevents efficient index usage
SELECT * FROM products
WHERE category = 'electronics'
   OR manufacturer = 'Sony';

-- Efficient: Allows separate index usage
SELECT * FROM products WHERE category = 'electronics'
UNION
SELECT * FROM products WHERE manufacturer = 'Sony';

Impact: OR conditions across different columns often force full table scans. UNION allows the optimizer to use separate indexes for each condition.

Pitfall 4: Implicit Type Conversions

-- Inefficient: customer_id is VARCHAR, comparing to INT
SELECT * FROM orders WHERE customer_id = 12345;

-- Efficient: Type matches column definition
SELECT * FROM orders WHERE customer_id = '12345';

Impact: Implicit type conversions prevent index usage and add computational overhead to every row comparison.

Pitfall 5: NOT IN with Subqueries

-- Inefficient: NOT IN scans all values and handles NULLs poorly
SELECT * FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
);

-- Efficient: NOT EXISTS can short-circuit
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

Impact: NOT IN must evaluate every value in the subquery and has problematic NULL handling semantics. NOT EXISTS can terminate as soon as a match is found.

Query Rewriting Techniques

Technique 1: Eliminate Correlated Subqueries

Correlated subqueries execute once for each outer row, resulting in N+1 query problems.

-- Inefficient: Subquery executes once per customer
SELECT c.customer_id, c.name,
    (SELECT COUNT(*) FROM orders o
     WHERE o.customer_id = c.customer_id) as order_count
FROM customers c;

-- Efficient: Single join with aggregation
SELECT c.customer_id, c.name, COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

Technique 2: Use JOINs Instead of Subqueries

Modern query optimizers handle JOINs more efficiently than subqueries in WHERE clauses.

-- Less efficient: Subquery in WHERE
SELECT * FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM customers
    WHERE country = 'USA'
);

-- More efficient: JOIN
SELECT o.* FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA';

Technique 3: Filter Early, Join Late

Reduce dataset size before expensive operations.

-- Less efficient: Join everything, then filter
SELECT o.*, p.product_name
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
  AND p.category = 'electronics';

-- More efficient: Filter first, then join smaller datasets
SELECT o.*, p.product_name
FROM (
    SELECT * FROM orders
    WHERE order_date >= '2024-01-01'
) o
JOIN (
    SELECT * FROM products
    WHERE category = 'electronics'
) p ON o.product_id = p.product_id;

Technique 4: Use EXISTS for Existence Checks

When checking for existence, don't count or retrieve unnecessary data.

-- Inefficient: Counts all matching rows
SELECT customer_id
FROM customers
WHERE (SELECT COUNT(*) FROM orders
       WHERE customer_id = customers.customer_id) > 0;

-- Efficient: Stops at first match
SELECT customer_id
FROM customers
WHERE EXISTS (
    SELECT 1 FROM orders
    WHERE customer_id = customers.customer_id
);

Technique 5: Leverage Covering Indexes

Covering indexes contain all columns needed by a query, eliminating table access.

-- Frequent query pattern:
SELECT order_id, customer_id, order_date, status
FROM orders
WHERE customer_id = 12345
  AND status = 'pending';

-- Covering index for this query:
CREATE INDEX idx_orders_covering
ON orders (customer_id, status, order_date, order_id);

The database satisfies the entire query from the index without accessing the table, significantly improving performance.

Indexing Strategies

Index Fundamentals

Indexes are data structures that allow rapid row lookup without scanning entire tables. They work similarly to book indexes - providing direct access to specific content without reading every page.

Multi-Column Index Ordering

Column order in multi-column indexes significantly affects performance:

-- Index definition
CREATE INDEX idx_orders ON orders (customer_id, order_date, status);

-- Efficient: Matches index column order
SELECT * FROM orders
WHERE customer_id = 12345
  AND order_date >= '2024-01-01';

-- Inefficient: Does not match index leading columns
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
  AND status = 'pending';

Optimization rule: Place the most selective columns (those that filter the most rows) first. Place equality conditions before range conditions.

Partial Indexes

Partial indexes contain only rows matching a specific condition, reducing index size and maintenance overhead:

-- Index only active orders
CREATE INDEX idx_active_orders
ON orders (customer_id, order_date)
WHERE status = 'active';

Partial indexes are smaller, faster to update, and more likely to fit in memory.

When to Avoid Indexes

Indexes consume storage space and slow down INSERT, UPDATE, and DELETE operations. Avoid indexes on:

Low-selectivity columns (columns with few distinct values)
Tables with high write-to-read ratios
Small tables that fit entirely in memory
Columns never used in WHERE, JOIN, ORDER BY, or GROUP BY clauses

Systematic Optimization Workflow

Step 1: Measure Current Performance

Always measure before optimizing. Use EXPLAIN ANALYZE to capture actual execution times and row counts:

EXPLAIN ANALYZE
SELECT c.customer_id, c.name, COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.registration_date >= '2024-01-01'
GROUP BY c.customer_id, c.name;

Step 2: Identify Bottlenecks

Analyze the execution plan for:

Operations with high execution times
Large row counts being processed
Sequential scans on large tables
Nested loops with high iteration counts
Significant differences between estimated and actual rows

Step 3: Update Statistics

ANALYZE customers;
ANALYZE orders;

Run EXPLAIN ANALYZE again. If the execution plan improved and performance increased, the issue was stale statistics. If not, continue optimization.

Step 4: Add or Modify Indexes

Create indexes for columns used in:

WHERE clause conditions
JOIN conditions
ORDER BY clauses
GROUP BY clauses

Focus on columns that appear frequently in slow queries.

Step 5: Rewrite the Query

Apply the rewriting techniques discussed earlier. Test each variation with EXPLAIN ANALYZE to verify improvements.

Step 6: Consider Query Hints (Last Resort)

Query hints override the optimizer's decisions. Use them sparingly:

-- PostgreSQL: Force index usage
SELECT * FROM orders
WHERE customer_id = 12345
/*+ IndexScan(orders idx_customer_date) */;

-- SQL Server: Force join type
SELECT * FROM orders o
INNER HASH JOIN customers c ON o.customer_id = c.customer_id;

-- MySQL: Force index
SELECT * FROM orders USE INDEX (idx_customer_date)
WHERE customer_id = 12345;

Hints lock queries into specific execution strategies that may become suboptimal as data changes. Use them only when the optimizer consistently makes poor decisions despite correct statistics and indexing.

Practical Optimization Example

Original Query (30 seconds)

SELECT
    c.customer_id,
    c.name,
    c.email,
    COUNT(DISTINCT o.order_id) as total_orders,
    SUM(o.total_amount) as total_spent,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE YEAR(c.registration_date) = 2024
  AND (o.status = 'completed' OR o.status IS NULL)
GROUP BY c.customer_id, c.name, c.email
ORDER BY total_spent DESC
LIMIT 100;

Issues Identified

YEAR(c.registration_date) prevents index usage on registration_date
OR condition with NULL check prevents efficient index usage on status
Missing indexes on join and filter columns
COUNT(DISTINCT) and SUM in the same aggregation can be inefficient

Optimized Query (0.3 seconds)

-- Create necessary indexes
CREATE INDEX idx_customers_reg_date
ON customers (registration_date, customer_id, name, email);

CREATE INDEX idx_orders_customer_completed
ON orders (customer_id, order_date, total_amount)
WHERE status = 'completed';

-- Optimized query
SELECT
    c.customer_id,
    c.name,
    c.email,
    COALESCE(o.total_orders, 0) as total_orders,
    COALESCE(o.total_spent, 0) as total_spent,
    o.last_order_date
FROM customers c
LEFT JOIN (
    SELECT
        customer_id,
        COUNT(*) as total_orders,
        SUM(total_amount) as total_spent,
        MAX(order_date) as last_order_date
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
) o ON o.customer_id = c.customer_id
WHERE c.registration_date >= '2024-01-01'
  AND c.registration_date < '2025-01-01'
ORDER BY COALESCE(o.total_spent, 0) DESC
LIMIT 100;

Optimizations Applied

Removed function from WHERE clause: Changed YEAR(registration_date) = 2024 to range comparison
Eliminated OR with NULL: Filtered orders in subquery instead of checking status in outer query
Added covering indexes: Created indexes containing all columns needed by query
Improved aggregation: Moved aggregation to subquery for cleaner execution
Used COALESCE for NULL handling: Replaced OR conditions with explicit NULL handling

Result: 100x performance improvement (30 seconds to 0.3 seconds)

Optimization Best Practices

Query Structure Checklist

Select only required columns, never use SELECT * unless necessary
Avoid functions on indexed columns in WHERE clauses
Replace correlated subqueries with JOINs
Use EXISTS instead of IN for subquery existence checks
Make type conversions explicit to match column types

Indexing Checklist

Create indexes on all JOIN columns
Create indexes on frequently filtered columns
Order multi-column indexes by selectivity (most selective first)
Use covering indexes for frequently executed queries
Create partial indexes for commonly filtered subsets

Maintenance Checklist

Keep statistics current, especially after bulk operations
Verify EXPLAIN ANALYZE shows accurate row estimates
Ensure no sequential scans on large tables
Verify execution plans remain stable across multiple runs
Monitor index usage and remove unused indexes

Testing Checklist

Test with production-scale data volumes
Test with cold cache (restart database between tests)
Measure actual execution time, not just row count
Verify query results remain correct after optimization

Performance Impact

Query optimization has multiplicative effects on system capacity. Optimizing 10 queries that each save 2 seconds saves 20 seconds per execution cycle. If these queries execute 1000 times per hour, that's 20,000 seconds (5.5 hours) of saved CPU time per hour - meaning the database can handle significantly more load.

Optimization increases database capacity without hardware upgrades or infrastructure changes. In cloud environments, this directly reduces operational costs.

Summary

Effective query optimization requires understanding how query optimizers work and structuring queries to leverage their capabilities. The key principles are:

Maintain accurate statistics - The optimizer needs current data distribution information
Use indexes strategically - Index frequently filtered and joined columns
Write optimizer-friendly queries - Avoid functions on indexed columns, use appropriate join types
Measure and verify - Use EXPLAIN ANALYZE to validate optimization efforts
Focus on impact - Optimize queries that consume the most resources

Query optimization is a systematic process, not guesswork. By following the techniques and practices outlined in this article, you can identify and resolve performance issues efficiently.

Epilogue: Advanced Optimization Techniques

Materialized Views for Expensive Aggregations

Materialized views store pre-computed query results, shifting computation from query time to refresh time:

-- PostgreSQL
CREATE MATERIALIZED VIEW customer_summary AS
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as lifetime_value,
    MAX(order_date) as last_order_date
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

-- Refresh on schedule or as needed
REFRESH MATERIALIZED VIEW customer_summary;

-- Index the materialized view
CREATE INDEX idx_customer_summary_ltv
ON customer_summary (lifetime_value DESC);

Materialized views are particularly effective for dashboards and reports where slightly stale data is acceptable.

Table Partitioning for Large Datasets

Partitioning divides large tables into smaller physical segments, allowing the optimizer to scan only relevant partitions:

-- PostgreSQL range partitioning
CREATE TABLE orders (
    order_id BIGSERIAL,
    customer_id INT,
    order_date DATE,
    total_amount NUMERIC
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

Queries with date filters scan only relevant partitions, reducing I/O significantly.

Query Result Caching

Application-level caching stores query results for reuse:

import redis
import hashlib
import json

def cached_query(query, ttl=300):
    cache = redis.Redis()
    cache_key = f"query:{hashlib.md5(query.encode()).hexdigest()}"

    # Check cache
    cached = cache.get(cache_key)
    if cached:
        return json.loads(cached)

    # Execute and cache
    result = execute_query(query)
    cache.setex(cache_key, ttl, json.dumps(result))
    return result

Caching is effective for expensive queries with infrequent data changes.

Batch Operations

Batching reduces network round-trips and allows better optimization:

-- Instead of 1000 individual queries
SELECT * FROM products WHERE product_id = 1;
SELECT * FROM products WHERE product_id = 2;
-- ... 998 more

-- Use a single batched query
SELECT * FROM products
WHERE product_id IN (1, 2, 3, ..., 1000);

-- Or use a temporary table
CREATE TEMP TABLE needed_ids (product_id INT);
INSERT INTO needed_ids VALUES (1), (2), (3), ..., (1000);
SELECT p.* FROM products p
INNER JOIN needed_ids n ON p.product_id = n.product_id;

Parallel Query Execution

Modern databases can execute queries in parallel across multiple CPU cores:

-- PostgreSQL: Configure parallel workers
SET max_parallel_workers_per_gather = 4;

-- SQL Server: Use MAXDOP hint
SELECT * FROM large_table
WHERE complex_condition
OPTION (MAXDOP 4);

-- Verify parallel execution in EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM massive_table;
-- Look for "Parallel Seq Scan" or "Parallel Hash Join"

Parallel execution provides 2-4x speedup on analytical queries at the cost of higher resource utilization.

Index-Only Scans

Structure indexes to contain all columns needed by a query, eliminating table access:

-- Query pattern
SELECT user_id, login_count
FROM user_stats
WHERE last_login > '2024-01-01';

-- Covering index for index-only scan
CREATE INDEX idx_covering
ON user_stats (last_login, user_id, login_count);

Index-only scans are 5-10x faster than regular index scans because they eliminate random I/O to the table.

Bloom Filter Indexes

PostgreSQL supports Bloom filter indexes for queries with multiple equality conditions:

CREATE INDEX bloom_idx ON products
USING bloom (category, manufacturer, color)
WITH (length=80, col1=2, col2=2, col3=4);

-- Efficient for multi-condition queries
SELECT * FROM products
WHERE category = 'electronics'
  AND manufacturer = 'Sony'
  AND color = 'black';

Bloom filters use less space than traditional multi-column indexes for specific query patterns.

Statistical Sampling

For approximate analytics on massive datasets, use sampling:

-- PostgreSQL: TABLESAMPLE
SELECT AVG(total_amount)
FROM orders
TABLESAMPLE SYSTEM (1); -- 1% sample

-- Estimate with confidence intervals
SELECT
    AVG(total_amount) as avg_estimate,
    STDDEV(total_amount) * 1.96 / SQRT(COUNT(*)) as margin_of_error
FROM orders
TABLESAMPLE SYSTEM (5); -- 5% sample

Sampling reduces query time by 10-100x for exploratory analysis where exact values aren't required.

Execution Plan Forcing

Force specific execution plans when the optimizer consistently makes suboptimal choices:

-- SQL Server: Plan guides
EXEC sp_create_plan_guide
    @name = N'PlanGuide1',
    @stmt = N'SELECT * FROM orders WHERE customer_id = @cust_id',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@cust_id INT',
    @hints = N'OPTION (HASH JOIN, MAXDOP 4)';

-- PostgreSQL: pg_hint_plan extension
/*+ HashJoin(orders customers) */
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Use plan forcing sparingly. Forced plans can become suboptimal as data distributions change.

Strategic Denormalization

In read-heavy workloads, denormalization can eliminate expensive joins:

-- Denormalize frequently joined data
ALTER TABLE orders
ADD COLUMN customer_name VARCHAR(255),
ADD COLUMN customer_email VARCHAR(255);

-- Maintain consistency with triggers
CREATE TRIGGER sync_customer_data
AFTER UPDATE ON customers
FOR EACH ROW
UPDATE orders
SET customer_name = NEW.name,
    customer_email = NEW.email
WHERE customer_id = NEW.customer_id;

Denormalization trades write complexity and storage space for read performance. Apply only when read performance is critical and write complexity is manageable.

Advanced Optimization Priorities

Of these advanced techniques, three deliver the most impact:

Materialized views for expensive aggregations and reports
Partitioning for time-series and large historical datasets
Covering indexes for frequently executed queries

These three techniques solve the majority of advanced performance problems.

Knowing When to Stop

Optimization has diminishing returns. If a query executes in under 100ms, consumes less than 1% of database resources, and doesn't cause user-facing problems, further optimization is unnecessary. Focus optimization efforts on queries with the highest impact on system performance and user experience.

Query optimization is about impact, not perfection. Prioritize the queries that matter most to your users and business objectives.