Query Performance Basics - Learning Module

Loading content...

0/241

Resource Usage

The Four Pillars of Database Resources

Every SQL query, regardless of its simplicity or complexity, consumes a combination of four fundamental computational resources: CPU for processing, Memory for temporary data storage, I/O for persistent data access, and Network for result transmission. These resources are finite, shared among concurrent operations, and often interdependent in complex ways.

Mastering database performance requires understanding how queries consume each resource type, how to measure that consumption, and how to optimize for resource constraints. A query might be fast but memory-intensive, or economical with CPU but disk-bound. The optimal query balances these resources within available capacity—and achieving that balance requires deep knowledge of resource dynamics.

What You Will Learn

By the end of this page, you will understand the four primary resources consumed by database operations, how to measure and monitor each, how resource constraints manifest as performance problems, and the fundamental tradeoffs between resource types in query optimization.

CPU Resources

CPU (Central Processing Unit) resources are consumed during the computational phases of query execution. While databases are often characterized as "I/O-bound" workloads, modern systems with fast storage and adequate caching can shift significant load to CPU processing.

CPU-Intensive Operations

The following query operations are characteristically CPU-bound:

Expression Evaluation — Computing calculated columns, applying functions, evaluating CASE expressions
Comparison Operations — Evaluating WHERE predicates, JOIN conditions
Sorting (ORDER BY) — Comparison-based sorting of result sets
Hashing — Building hash tables for hash joins and hash aggregation
Aggregation — Computing SUM, AVG, COUNT, and other aggregate functions
String Operations — Pattern matching (LIKE), string concatenation, manipulation functions
Compression/Decompression — If data is stored compressed

CPU Consumption by Operation Type
Operation	CPU Intensity	Scaling Factor	Optimization Approach
Simple arithmetic	Very Low	O(1) per row	Rarely needs optimization
String comparison	Low-Medium	O(length) per comparison	Use prefix indexes, avoid LIKE '%...'
Regular expressions	High	Varies with pattern complexity	Simplify patterns, pre-filter data
Cryptographic functions	Very High	O(1) but high constant	Minimize usage in hot paths
JSON/XML parsing	High	O(document size)	Extract needed fields at write time
Sorting large sets	High	O(n log n)	Add covering indexes, reduce result size
Hash table building	Medium	O(n)	Ensure sufficient work_mem/sort_area_size

cpu-usage-monitoring.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
-- pg_stat_statements: Track CPU time per query
SELECT 
    calls,
    total_exec_time / 1000 AS total_seconds,
    mean_exec_time AS avg_ms,
    shared_blks_hit + shared_blks_read AS buffer_ops,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
 
-- High CPU queries often have high calls with moderate avg_ms
-- Example output:
--   calls: 1,450,000
--   total_seconds: 8,234
--   avg_ms: 5.67 ms
--   query: SELECT compute_hash(data) FROM transactions WHERE...
 
-- Identifying CPU-bound vs I/O-bound
-- CPU-bound: low blk_read_time relative to total_exec_time
SELECT 
    queryid,
    total_exec_time,
    blk_read_time,
    (total_exec_time - blk_read_time) AS cpu_time_approx,
    ROUND(100.0 * (total_exec_time - blk_read_time) / 
          NULLIF(total_exec_time, 0), 1) AS cpu_pct
FROM pg_stat_statements
WHERE total_exec_time > 1000  -- More than 1 second total
ORDER BY cpu_pct DESC;

Parallel Query Execution

Modern databases can parallelize CPU-intensive operations across multiple cores. Sorting, aggregation, and hash joins can execute on parallel worker processes. However, parallelism introduces coordination overhead and may not benefit small datasets. Monitor parallel worker usage to ensure parallelism is helping rather than hindering.

Memory Resources

Memory (RAM) serves multiple critical roles in database systems. Understanding memory allocation and consumption is essential because memory constraints directly impact both performance and system stability.

Types of Database Memory Usage

Buffer Pool / Buffer Cache — Caches data and index pages read from disk. The most significant memory consumer in most database systems. Larger buffer pools mean fewer disk reads.
Working Memory — Per-query memory for sorts, hash operations, and temporary results. Configured via settings like work_mem (PostgreSQL) or sort_buffer_size (MySQL).
Connection/Session Memory — Each database connection maintains state, prepared statements, and result buffers. Connection counts directly impact memory usage.
Plan Cache — Compiled query execution plans. Prevents repeated query compilation overhead.
Internal Structures — Lock tables, transaction logs, background process memory.

Sufficient Memory

•Working set fits in buffer pool
•Sorts complete in-memory
•Hash tables don't spill to disk
•No memory pressure from connections
•Cache hit ratio > 99%

Memory Constrained

•Frequent page eviction from cache
•Sort operations spill to disk (temp files)
•Hash joins become nested loops
•Connection refused due to OOM
•System starts swapping (critical!)

memory-monitoring.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
-- Buffer pool hit ratio (target: > 99%)
SELECT 
    sum(blks_hit) AS cache_hits,
    sum(blks_read) AS disk_reads,
    ROUND(100.0 * sum(blks_hit) / 
          NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) AS hit_ratio_pct
FROM pg_stat_database;
 
-- Memory settings review
SHOW shared_buffers;      -- Buffer pool size
SHOW work_mem;            -- Per-operation sort/hash memory
SHOW maintenance_work_mem; -- Memory for maintenance ops
 
-- Queries with temporary file usage (memory exceeded)
SELECT 
    temp_blks_read,
    temp_blks_written,
    query
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;
 
-- If temp_blks_written is high, consider:
-- 1. Increasing work_mem for that session/query
-- 2. Reducing result set size with better filtering
-- 3. Adding indexes to avoid large sorts

Memory Allocation is Per-Operation

The work_mem setting (PostgreSQL) applies per-operation, not per-query. A complex query with 10 sort operations could consume 10× work_mem. A busy server with 100 concurrent queries and 5 operations each could theoretically need 500× work_mem. Size these settings conservatively and increase for specific sessions when needed.

I/O Resources

I/O (Input/Output) resources refer to the reading and writing of data from/to persistent storage. Despite advances in SSD technology, I/O remains the primary bottleneck for most database workloads. Understanding I/O patterns and minimizing unnecessary disk access is central to performance optimization.

Types of Database I/O

Sequential I/O — Reading/writing contiguous pages. Efficient because storage devices optimize for sequential access. Examples: table scans, bulk loading.
Random I/O — Accessing non-contiguous pages across the storage device. Significantly slower than sequential, especially on spinning disks. Examples: index lookups, following pointers.
Read I/O — Fetching data from storage into memory. The majority of database I/O.
Write I/O — Persisting modified data and transaction logs. Includes synchronous writes for durability.

I/O Performance by Storage Type
Metric	HDD (7200 RPM)	SATA SSD	NVMe SSD
Sequential Read	~150 MB/s	~550 MB/s	~3,500 MB/s
Sequential Write	~150 MB/s	~520 MB/s	~3,000 MB/s
Random Read IOPS	~100-200	~50,000-100,000	~500,000-1,000,000
Random Write IOPS	~100-200	~50,000-90,000	~400,000-800,000
Random Read Latency	~8-12 ms	~0.1-0.2 ms	~0.02-0.05 ms

I/O Reduction Strategies

Minimizing I/O is often the highest-impact optimization:

Indexing — Reduces rows examined, converting full scans to targeted lookups
Covering Indexes — Include all needed columns in the index; no table access required
Column Selection — SELECT only needed columns; reduces page reads
Filtering Early — Push predicates down to reduce intermediate results
Buffer Pool Sizing — Larger cache = fewer disk reads
Partitioning — Eliminate entire data segments from scans
Compression — Trade CPU for reduced I/O volume

io-monitoring.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
-- Enable I/O timing for detailed analysis
SET track_io_timing = on;
 
-- Analyze query with I/O breakdown
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT * FROM large_orders WHERE customer_id = 12345;
 
/* Example output:
   Index Scan using idx_orders_customer on large_orders
     Index Cond: (customer_id = 12345)
     Buffers: shared hit=5 read=142
     I/O Timings: read=234.567
   Planning Time: 0.123 ms
   Execution Time: 236.789 ms
   
   Analysis:
   - 147 buffer accesses total
   - 5 from cache (3.4%), 142 from disk (96.6%)
   - 234ms of 237ms total was I/O wait (99%!)
   - This query is completely I/O bound
*/
 
-- Identify I/O-heavy tables
SELECT 
    schemaname, 
    tablename,
    heap_blks_read AS table_disk_reads,
    heap_blks_hit AS table_cache_hits,
    idx_blks_read AS index_disk_reads,
    idx_blks_hit AS index_cache_hits,
    ROUND(100.0 * heap_blks_hit / 
          NULLIF(heap_blks_hit + heap_blks_read, 0), 1) AS table_hit_pct
FROM pg_statio_user_tables
ORDER BY heap_blks_read + idx_blks_read DESC
LIMIT 10;

The I/O Amplification Problem

A single row lookup can require multiple I/O operations: traversing B-tree index levels (typically 3-4 reads) plus reading the data page. For range queries without covering indexes, each matching index entry requires a separate data page read. This 'random I/O amplification' is why covering indexes and careful index design matter so much.

Network Resources

Network resources are consumed when transmitting data between database clients and servers, as well as in distributed database architectures. Network considerations become increasingly important as applications scale and adopt distributed patterns.

Network Impact Scenarios

Client → Server Connection — Initial connection establishment, authentication, query submission
Result Set Transfer — Transmitting query results back to clients (often the largest volume)
Database Replication — Synchronizing data between primary and replica nodes
Distributed Queries — Cross-node communication in sharded or federated systems
Backup/Restore — Moving large data volumes to/from backup storage

Network Performance Factors

Latency — The round-trip time for a request-response cycle. Especially impactful for "chatty" applications that make many small database calls.

Same datacenter: 0.1-1 ms
Same region: 1-10 ms
Cross-region: 50-150 ms
Cross-continent: 100-300 ms

Bandwidth — The data volume capacity of the network connection. Becomes limiting when transferring large result sets.

Protocol Overhead — Database protocols add headers and metadata. Many small results have proportionally higher overhead than fewer large batches.

Network Transfer Time Examples (1 Gbps LAN)
Result Size	Rows (100 bytes/row)	Transfer Time	Notes
1 KB	10 rows	< 1 ms	Negligible
100 KB	1,000 rows	~1 ms	Negligible
10 MB	100,000 rows	~80 ms	Noticeable
100 MB	1,000,000 rows	~800 ms	Significant
1 GB	10,000,000 rows	~8 seconds	Problematic for interactive use

network-optimization.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
-- ANTI-PATTERN: Fetching all columns, filtering in application
-- Network impact: Transfers entire table to client
SELECT * FROM orders;  -- Returns 1M rows, 500MB
-- Application then filters for customer_id = 12345
 
-- OPTIMIZED: Filter at database, select needed columns only
-- Network impact: Transfers only matching rows, minimal columns
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 12345;  -- Returns 50 rows, ~5KB
 
-- ANTI-PATTERN: N+1 query pattern
-- Each iteration requires a network round-trip
FOR each customer_id IN customer_list:
    SELECT * FROM orders WHERE customer_id = ?;
-- 1000 customers = 1000 network round-trips
 
-- OPTIMIZED: Single query with batching
SELECT o.* FROM orders o
WHERE o.customer_id IN (?, ?, ?, ...);  -- Pass all IDs
-- Or JOIN:
SELECT o.* FROM orders o
INNER JOIN (VALUES (1),(2),(3)...) AS c(id) 
ON o.customer_id = c.id;
 
-- PAGINATION: Don't fetch more than needed
-- BAD: Fetch all, display first 20
SELECT * FROM products ORDER BY created_at DESC;
 
-- GOOD: Database-side pagination
SELECT * FROM products 
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;  -- Only 20 rows transferred

Network Optimization Strategies

•Minimize round-trips — Batch multiple operations into single calls; avoid chatty N+1 patterns
•Reduce payload size — SELECT only needed columns; filter at the database, not in the application
•Use pagination — LIMIT/OFFSET or keyset pagination to transfer only viewable data
•Connection pooling — Reuse connections to avoid repeated connection establishment overhead
•Compression — Enable protocol-level compression for large result sets
•Geographic locality — Deploy application servers close to database servers
•Read replicas — Serve read traffic from replicas geographically closer to users

The N+1 Query Problem

The N+1 pattern—executing one query to get a list, then N additional queries to get related data—is among the most common and costly network antipatterns. With 1ms round-trip latency, fetching 1,000 related items incurs 1 second of pure network wait, regardless of how fast each query executes. Always batch related data fetching.

Resource Tradeoffs

Database optimization rarely offers free improvements. Instead, we navigate tradeoffs—exchanging abundance of one resource for scarcity in another. Understanding these tradeoffs enables intelligent decisions aligned with system constraints.

The Fundamental Exchange Relationships

Most optimization techniques embody a core exchange:

Common Resource Tradeoffs
Technique	Reduces	Increases	When Appropriate
Adding indexes	Query I/O time	Storage space, write overhead	Read-heavy workloads, selective queries
Covering indexes	I/O (no table lookups)	Index size, write overhead	Hot queries with predictable columns
Caching/Materialization	CPU and I/O per query	Memory, staleness risk	Expensive recomputed results
Denormalization	Join I/O	Storage, update complexity	Read-heavy, stable structures
Compression	Storage I/O volume	CPU for compress/decompress	I/O-bound, CPU-available systems
Partitioning	I/O (partition pruning)	Query complexity, mgmt overhead	Large tables, time-based access
More work_mem	Disk I/O (temp files)	Per-query memory	Memory-abundant systems
Parallel queries	Elapsed time	CPU, coordination overhead	Large scans, multi-core CPUs

Context Determines Correctness

There is no universally "correct" optimization. The right choice depends on:

Workload Characteristics — Read/write ratio, query patterns, data distribution
Resource Availability — Which resources are scarce vs. abundant?
Performance Goals — Latency targets, throughput requirements, consistency needs
Operational Constraints — Maintenance windows, backup requirements, HA needs

I/O-Bound System

When I/O is the constraint: Add indexes liberally. Use covering indexes. Enable compression (trades CPU for less I/O). Increase buffer pool. Consider faster storage.

CPU-Bound System

When CPU is the constraint: Disable compression. Reduce expression complexity. Pre-compute expensive calculations. Add read replicas to distribute load. Consider hardware upgrade.

The Tradeoff That Isn't

One optimization has no meaningful downside: writing better SQL. Selecting only needed columns, filtering early, avoiding unnecessary computation—these improvements reduce ALL resources simultaneously. This is why SQL optimization should precede hardware solutions.

Monitoring Resource Consumption

Effective resource monitoring requires capturing usage across all four dimensions—CPU, memory, I/O, and network—and correlating this data to identify patterns, anomalies, and optimization opportunities.

Monitoring Levels

Query Level — Per-statement resource consumption. Essential for identifying expensive queries.
Session Level — Per-connection cumulative usage. Identifies problematic applications or users.
Database Level — Aggregate across all queries. Capacity planning and trend analysis.
System Level — OS-level metrics. Total resource picture including non-database processes.

comprehensive-monitoring.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
-- Enable comprehensive statistics collection
-- In postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = 'all'
-- track_io_timing = on
-- track_functions = 'all'
 
-- Top resource consumers (multi-dimensional)
SELECT 
    queryid,
    calls,
    -- Time breakdown
    ROUND(total_exec_time::numeric, 2) AS total_ms,
    ROUND(mean_exec_time::numeric, 2) AS avg_ms,
    -- I/O metrics
    shared_blks_hit AS cache_hits,
    shared_blks_read AS disk_reads,
    ROUND(100.0 * shared_blks_hit / 
          NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
    -- Sort/Hash spills (memory exceeded)
    temp_blks_read + temp_blks_written AS temp_blks,
    -- Actual query
    LEFT(query, 80) AS query_preview
FROM pg_stat_statements
WHERE calls > 100  -- Focus on frequently executed
ORDER BY total_exec_time DESC
LIMIT 20;
 
-- Real-time session monitoring
SELECT 
    pid,
    usename,
    state,
    EXTRACT(EPOCH FROM (now() - query_start)) AS running_seconds,
    wait_event_type,
    wait_event,
    LEFT(query, 60) AS query_preview
FROM pg_stat_activity
WHERE state = 'active' AND pid != pg_backend_pid()
ORDER BY query_start;

Key Monitoring Metrics

•CPU: Worker/execution time, CPU queue length, parallel worker utilization
•Memory: Buffer hit ratio, page life expectancy, memory grant waiting, temp file usage
•I/O: Physical reads/writes, read latency, throughput, wait events
•Network: Bytes sent/received per session, network wait time, round-trips
•Composite: Total elapsed time, statements per second, active sessions

Automate Monitoring

Manual inspection quickly becomes unsustainable. Implement automated monitoring with tools like Prometheus + Grafana, Datadog, or database-specific solutions (pganalyze, SolarWinds DPA). Set alerts for threshold violations and trend deviations. The goal is proactive detection, not reactive firefighting.

Resource Saturation Symptoms

When resources approach capacity, databases exhibit characteristic symptoms. Recognizing these patterns enables rapid diagnosis and targeted intervention.

Identifying the Constrained Resource

Each resource type produces distinct symptoms when saturated:

Resource Saturation Symptoms
Resource	Saturation Symptoms	Diagnostic Indicators	Immediate Actions
CPU	High latency for all queries, CPU >90%, query queue builds	OS CPU metrics, SOS_SCHEDULER_YIELD waits, high CPU time in query stats	Kill expensive queries, add replicas, optimize hot queries
Memory	Temp files, swapping, OOM errors, poor cache hit ratio	Page life expectancy <60s, temp_blks in pg_stat_statements, swap usage	Reduce connection count, lower work_mem, increase RAM
I/O	Slow queries with low CPU, high PAGEIO waits, disk queue	PAGEIO_LATCH waits, iostat %util >80%, high physical reads	Add indexes, increase buffer pool, upgrade storage
Network	Client timeout, slow result return for large queries	Network wait stats, bytes_sent/received anomalies	Reduce result size, check network infrastructure, add compression

The Cascading Effect

Resource saturation rarely stays contained. When one resource is exhausted:

Queries queue — Incoming work waits, consuming connection slots
Connections exhaust — New clients can't connect, errors propagate
Retry storms — Applications retry failed requests, amplifying load
Secondary saturation — Backlogged work overloads another resource

This cascade can transform a single slow query into a complete system outage. Early detection of saturation symptoms prevents escalation.

The Death Spiral

Memory exhaustion is particularly dangerous. When work_mem is exceeded, queries spill to disk (I/O). Disk I/O slows query completion (queries accumulate). More concurrent queries consume more memory (deepening shortage). Eventually, the system cannot make forward progress. Monitor memory proactively and set conservative limits.

Summary: Resource Usage

Understanding resource consumption transforms database performance work from guesswork into science. Let's consolidate the essential concepts:

Key Takeaways

•Four resources dominate: CPU, Memory, I/O, Network — Each has distinct consumption patterns, monitoring approaches, and optimization strategies
•I/O is typically the primary bottleneck — Even with SSDs, reducing unnecessary disk access yields the largest performance gains
•Memory serves as I/O prevention — Adequate buffer pools and working memory prevent expensive disk operations
•CPU becomes significant for cached data — When data fits in memory, compute-intensive operations surface as bottlenecks
•Network impact scales with result size and round-trips — Minimize transferred data and batch operations to reduce latency
•Tradeoffs are unavoidable — Optimization exchanges one resource for another; context determines the right choice
•Saturation cascades — Resource exhaustion in one dimension quickly spreads; monitor all resources proactively

What's Next

With resource fundamentals established, we're ready to tackle the diagnostic process. The next page explores Bottleneck Identification—systematic approaches to locating the specific queries, operations, and resource constraints that limit system performance.

Page Complete

You now understand the four pillars of database resources, how each is consumed and measured, the tradeoffs involved in optimization, and the symptoms of resource saturation. This knowledge enables you to comprehensively analyze database resource consumption. Next, we'll learn to systematically identify performance bottlenecks.