Query Tuning - Performance Tuning | OneNoughtOne

0/278

Database Management SystemsPerformance Tuning

Performance Tuning

LevelAdvanced

Duration90 mins

TopicPerformance Tuning

1 / 5

Query Tuning

The Art of Query Optimization

A single poorly-written query can bring a production database to its knees. In high-traffic systems, the difference between a well-tuned query and a naive one can translate to seconds versus milliseconds, thousands of dollars in compute costs, or the difference between a responsive application and frustrated users abandoning your product.

Query tuning is the systematic practice of analyzing, understanding, and optimizing SQL queries to achieve maximum performance. Unlike other performance optimizations that require infrastructure changes, query tuning often delivers dramatic improvements with nothing more than rewriting a few lines of SQL.

This is not merely a technical skill—it's a discipline that separates database engineers who use databases from those who truly master them.

What You Will Learn

By the end of this page, you will understand how to read and interpret execution plans, identify common query anti-patterns, apply proven optimization techniques, and develop the systematic mindset needed to tune queries in any database system. These skills are essential for every DBA and indispensable for senior engineers working with data at scale.

Understanding Query Performance

Before optimizing anything, we must understand what makes queries slow. Query performance is determined by the interplay of several factors:

The Query Processing Pipeline:

When you submit a query, the database engine performs a series of operations:

Parsing — Syntactic and semantic validation of the SQL statement
Optimization — Generating and evaluating possible execution strategies
Execution — Running the selected plan against the actual data
Result delivery — Returning rows to the client

Performance problems can emerge at any stage, but the vast majority stem from the execution phase—specifically, from the optimizer choosing a suboptimal plan or from the query requiring inherently expensive operations.

Key Performance Metrics

•Logical I/O (Buffer Gets) — Number of data blocks read from the buffer cache. Even when data is cached in memory, reading too many blocks indicates inefficiency.
•Physical I/O (Disk Reads) — Number of blocks read from disk storage. Physical reads are orders of magnitude slower than logical reads.
•CPU Time — Processing time consumed by the query. Complex calculations, sorting, and hashing consume significant CPU resources.
•Elapsed Time — Wall-clock time from query submission to result completion. Includes all waits and delays.
•Rows Processed vs Rows Returned — A query that examines millions of rows to return ten has a serious efficiency problem.
•Memory Consumption — Amount of RAM required for sorts, hash tables, and intermediate results.

The Golden Ratio

A well-tuned query should process a number of rows roughly proportional to the rows it returns. If you're returning 10 rows but examining 10 million, something is fundamentally wrong. This ratio—rows examined to rows returned—is often the single most revealing metric for query efficiency.

Understanding Response Time Breakdown:

Total query response time consists of:

Response Time = Service Time + Wait Time

Service Time: Time spent actively doing work (CPU, I/O operations)
Wait Time: Time spent waiting for resources (locks, latches, I/O completion)

Effective tuning addresses both. A query might be fast on its own but slow due to lock contention. Conversely, a query with no waits can still be slow due to inefficient algorithms.

The Cost of Full Table Scans:

Consider a table with 100 million rows. A full table scan reads every single row. Even with modern storage:

At 100 bytes per row = 10 GB of data
SSD read speed ~500 MB/s = 20 seconds minimum
Network overhead to return results = additional seconds

With proper indexing, the same query might access only 1,000 rows:

100 KB of data = essentially instantaneous

This is why understanding access patterns is fundamental to query tuning.

Reading Execution Plans

Execution plans are the roadmap of query performance. Every database system provides tools to reveal how queries are executed. Without understanding execution plans, you're tuning blindly.

An execution plan shows:

What tables and indexes are accessed
In what order operations occur
What join methods are used
How many rows the optimizer estimates at each step
The estimated and actual costs of operations

execution_plan_examples.sql

-- PostgreSQL: EXPLAIN and EXPLAIN ANALYZE
-- EXPLAIN shows the estimated plan without executing
EXPLAIN SELECT c.customer_name, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date > '2024-01-01'
  AND o.total_amount > 1000;
 
-- EXPLAIN ANALYZE actually executes and shows real statistics
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.customer_name, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date > '2024-01-01'
  AND o.total_amount > 1000;
 
/* Sample Output:
Hash Join  (cost=15.00..35.00 rows=100 width=48) 
           (actual time=0.5..2.3 rows=87 loops=1)
  Hash Cond: (o.customer_id = c.customer_id)
  Buffers: shared hit=45
  ->  Index Scan using idx_orders_date on orders o 
         (cost=0.42..18.00 rows=100 width=24)
         (actual time=0.1..0.8 rows=87 loops=1)
        Index Cond: (order_date > '2024-01-01')
        Filter: (total_amount > 1000)
        Rows Removed by Filter: 23
        Buffers: shared hit=12
  ->  Hash  (cost=10.00..10.00 rows=500 width=28)
         (actual time=0.3..0.3 rows=450 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 32kB
        Buffers: shared hit=8
        ->  Seq Scan on customers c  (cost=0.00..10.00 rows=500 width=28)
               (actual time=0.01..0.2 rows=450 loops=1)
               Buffers: shared hit=8
Planning Time: 0.2 ms
Execution Time: 2.5 ms
*/

Common Execution Plan Operations
Operation	Description	Performance Implications
Table Scan / Seq Scan	Reads entire table row by row	⚠️ Expensive for large tables; acceptable for small tables or when most rows needed
Index Scan	Uses index to locate rows, then fetches from table	✅ Efficient when retrieving subset of rows
Index Only Scan	Satisfies query entirely from index	✅ Optimal when all needed columns are in index (covering index)
Nested Loop Join	For each row in outer table, scan inner table	⚠️ Fast for small datasets; disastrous for large ones (O(n×m))
Hash Join	Build hash table on smaller table, probe with larger	✅ Excellent for equality joins; requires memory
Merge Join	Merge two sorted datasets	✅ Efficient when data is already sorted or sort cost is low
Sort	Order rows by specified columns	⚠️ Memory-intensive; may spill to disk for large datasets
Aggregate	Compute GROUP BY or aggregation functions	Varies; watch for hash vs. stream aggregation choices

Estimates vs. Actuals

Always compare estimated row counts with actual row counts. Large discrepancies indicate stale statistics or complex predicates the optimizer can't analyze. When the optimizer thinks a query will return 10 rows but actually returns 10,000, it may choose a completely inappropriate plan (like nested loops instead of hash join).

Common Query Anti-Patterns

Certain query patterns consistently cause performance problems. Recognizing these anti-patterns lets you fix issues systematically. Below are the most common offenders and their solutions.

SARGable (Search ARGument able) predicates can use indexes. Non-SARGable predicates force full scans by preventing index usage.

Problem: Functions on indexed columns

sargable_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- ❌ NON-SARGABLE: Function on indexed column
-- Forces full table scan even with index on order_date
SELECT * FROM orders 
WHERE YEAR(order_date) = 2024;
 
SELECT * FROM orders 
WHERE DATE(order_date) = '2024-01-15';
 
SELECT * FROM customers 
WHERE UPPER(email) = 'USER@EXAMPLE.COM';
 
SELECT * FROM products 
WHERE price + tax > 100;
 
-- ✅ SARGABLE: Rewrite to allow index usage
-- Use range on the indexed column directly
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';
 
SELECT * FROM orders 
WHERE order_date >= '2024-01-15' 
  AND order_date < '2024-01-16';
 
-- Store normalized values or use computed columns
SELECT * FROM customers 
WHERE email_lower = 'user@example.com';
 
-- Rewrite arithmetic to isolate the column
SELECT * FROM products 
WHERE price > 100 - tax;  -- If tax is constant
-- Or create a computed/generated column for price + tax

The SARGability Rule

Never apply functions, arithmetic, or type conversions to indexed columns in WHERE clauses. Always keep the indexed column 'naked' on one side of the comparison.

Query Rewriting Techniques

Beyond avoiding anti-patterns, proactive query rewriting can transform slow queries into fast ones. These techniques exploit database engine capabilities and mathematical query equivalences.

query_rewriting.sql

-- TECHNIQUE 1: Reduce result set before joining
-- ❌ Filter after joining (processes unnecessary rows)
SELECT c.customer_name, o.order_date, o.total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 10000 AND o.order_date > '2024-01-01';
 
-- ✅ Pre-filter with CTE or subquery (reduces join input)
WITH high_value_orders AS (
    SELECT order_id, customer_id, order_date, total
    FROM orders
    WHERE total > 10000 AND order_date > '2024-01-01'
)
SELECT c.customer_name, hvo.order_date, hvo.total
FROM customers c
JOIN high_value_orders hvo ON c.customer_id = hvo.customer_id;
 
-- TECHNIQUE 2: Choose optimal join order
-- The optimizer usually handles this, but hints can help
-- Put the most selective table first when using nested loops
 
-- TECHNIQUE 3: Use semi-joins for existence checks
-- ❌ Full join when only checking existence
SELECT * FROM products p
WHERE p.product_id IN (
    SELECT DISTINCT product_id FROM order_items
);
 
-- ✅ EXISTS for semi-join (no duplicate handling needed)
SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi 
    WHERE oi.product_id = p.product_id
);

Test Rewrites with Production Data

Query performance can vary dramatically based on data distribution. A rewrite that helps with one dataset might hurt with another. Always test optimizations against realistic data volumes and distributions, ideally using a production-like copy.

Optimizer Hints and Directives

When the optimizer makes poor choices despite good queries and statistics, hints let you directly influence plan selection. Use hints judiciously—they override the optimizer's judgment and may become counterproductive as data changes.

optimizer_hints.sql

-- Oracle Hints (specified in comments)
 
-- Force index usage
SELECT /*+ INDEX(orders idx_order_date) */ *
FROM orders 
WHERE order_date > DATE '2024-01-01';
 
-- Force full table scan (when optimizer wrongly uses index)  
SELECT /*+ FULL(orders) */ *
FROM orders
WHERE status = 'active';  -- 90% of rows
 
-- Join order control
SELECT /*+ LEADING(c o oi) */ *
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id;
 
-- Join method hints
SELECT /*+ USE_HASH(o) */ *
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
 
SELECT /*+ USE_NL(o) */ *  -- Nested loops
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = 12345;
 
-- Parallel execution
SELECT /*+ PARALLEL(4) */ *
FROM orders
WHERE total > 10000;
 
-- Optimizer mode
SELECT /*+ FIRST_ROWS(10) */ *  -- Optimize for first 10 rows
FROM orders
ORDER BY order_date DESC;

Hint Maintenance Burden

Hints are a double-edged sword. They solve today's problem but create tomorrow's maintenance burden. As data grows and distributions change, hints that once helped may become harmful. Document why each hint was added, and revisit them periodically. The best long-term solution is usually fixing statistics or schema rather than forcing plans.

Systematic Query Tuning Process

Ad-hoc tuning leads to inconsistent results. A systematic process ensures you address root causes rather than symptoms and document your work for future reference.

The Query Tuning Workflow

•Identify the Problem Query — Use slow query logs, APM tools, or monitoring dashboards to find queries consuming excessive resources or time.
•Establish Baseline Metrics — Record current execution time, I/O, CPU usage, and rows processed. You can't measure improvement without a baseline.
•Understand the Business Context — Why does this query exist? How often does it run? What's the acceptable performance? A nightly batch job has different requirements than a customer-facing API call.
•Analyze the Execution Plan — Read the plan carefully. Identify the most expensive operations. Note estimated vs. actual row counts.
•Check Statistics and Indexes — Are statistics current? Do appropriate indexes exist? Are they being used?
•Identify Anti-Patterns — Look for non-SARGable predicates, type mismatches, unnecessary sorts, or correlated subqueries.
•Apply Targeted Fixes — Address one issue at a time. Re-measure after each change to isolate the effect.
•Validate Under Load — Test with production-like data and concurrent users. Some issues only appear under contention.
•Document Changes — Record what was changed, why, and the performance impact. Future DBAs will thank you.
•Monitor Post-Deployment — Verify improvements persist in production. Watch for regressions over time.

Query Tuning Decision Matrix
Symptom	Likely Cause	Investigation Steps
High I/O, many rows processed	Missing or unused index	Check execution plan for table scans; verify WHERE clause uses indexed columns
Estimates far from actuals	Stale or missing statistics	Update statistics; check for complex predicates optimizer can't estimate
CPU-bound query	Complex expressions, sorting, hashing	Simplify calculations; add indexes for ORDER BY; check for implicit conversions
Lock waits	Contention with other queries	Review locking strategy; consider isolation levels; check for long transactions
Slow first execution, fast repeats	Plan compilation time	Use parameterized queries; consider plan caching strategies
Slow regardless of data volume	Algorithmic issue (O(n²))	Look for correlated subqueries, cross joins, or nested loops on large sets

tuning_checklist.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- QUERY TUNING CHECKLIST
 
/* 1. BASELINE MEASUREMENT
   Record these metrics before any changes:
   - Execution time (elapsed and CPU)
   - Logical reads / Buffer gets
   - Physical reads
   - Rows processed vs rows returned
*/
 
-- PostgreSQL: Enable timing and get baseline
\timing on
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT /* your query here */;
 
-- MySQL: Enable profiling
SET profiling = 1;
SELECT /* your query here */;
SHOW PROFILE FOR QUERY 1;
 
-- SQL Server: Statistics
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT /* your query here */;
 
/* 2. STATISTICS CHECK
   Verify statistics are current
*/
 
-- PostgreSQL
SELECT relname, last_analyze, last_autoanalyze,
       n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';
 
ANALYZE orders;  -- Update statistics
 
-- MySQL
SHOW INDEX FROM orders;
ANALYZE TABLE orders;
 
-- SQL Server
DBCC SHOW_STATISTICS('orders', 'idx_order_date');
UPDATE STATISTICS orders;
 
/* 3. INDEX USAGE CHECK
   Verify indexes exist and are used
*/
 
-- PostgreSQL  
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders';
 
-- MySQL
SELECT index_name, column_name, cardinality
FROM information_schema.statistics
WHERE table_name = 'orders';
 
/* 4. AFTER EACH CHANGE
   Re-run EXPLAIN ANALYZE
   Compare metrics to baseline
   Document the change and result
*/

The 80/20 Rule of Query Tuning

In most systems, 20% of queries consume 80% of resources. Focus your tuning efforts on the top resource consumers first. A small improvement to a query running 10,000 times per hour has more impact than a large improvement to a query running once per day.

Summary: Query Tuning Mastery

Query tuning is a foundational skill that directly impacts application performance, infrastructure costs, and user experience. Let's consolidate what we've learned:

Key Takeaways

•Understand before optimizing — Learn to read execution plans and identify the actual bottlenecks before making changes.
•Monitor key metrics — Track logical I/O, physical I/O, CPU time, and the ratio of rows processed to rows returned.
•Avoid anti-patterns — Non-SARGable predicates, SELECT *, type mismatches, and N+1 queries are common performance killers.
•Rewrite strategically — Filter early, choose optimal join methods, and use set operations instead of row-by-row processing.
•Use hints sparingly — Optimizer hints are powerful but create maintenance burden; prefer fixing root causes.
•Follow a systematic process — Baseline, analyze, change one thing, measure, document, repeat.

What's Next:

Query tuning is closely linked to index tuning—even the best-written query performs poorly without appropriate indexes. In the next page, we'll explore Index Tuning: how to design indexes that support your queries, identify missing indexes, and maintain index health without over-indexing.

Page Complete

You now understand the principles and practices of query tuning—from reading execution plans to applying systematic optimization techniques. These skills form the foundation for diagnosing and resolving performance issues in any database system. Next, we'll examine how proper indexing amplifies query performance.