Query Optimization and EXPLAIN¶

EXPLAIN reveals how the database plans to execute a query. EXPLAIN ANALYZE executes it and shows actual metrics. Understanding plan operators and cost model is essential for diagnosing slow queries.

EXPLAIN Output¶

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE user_id = 42;

Key output fields: - cost: startup_cost..total_cost in arbitrary units (NOT milliseconds) - rows: estimated rows returned (based on table statistics) - width: average row size in bytes - actual time: milliseconds (only in ANALYZE), first_row..total - loops: how many times this node executed - Buffers: shared hit (cache) vs read (disk)

Cost Units (PostgreSQL)¶

seq_page_cost = 1.0       -- baseline: sequential page read
random_page_cost = 4.0    -- random page read (lower to 1.1-2.0 for SSD)
cpu_tuple_cost = 0.01     -- processing one row
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025

Plan Node Types¶

Operator	Description	Performance Notes
Seq Scan	Full table scan	Cost proportional to table size
Index Scan	B+Tree lookup + heap fetch	2+ I/Os per row (index + heap)
Index Only Scan	All data from index, no heap	Fastest - requires covering index + clean visibility map
Bitmap Index Scan	Build bitmap, read pages sequentially	Good for moderate selectivity
Nested Loop Join	For each outer row, scan inner	Good when inner has index or is small
Hash Join	Build hash from smaller table, probe	Good for large equality joins
Merge Join	Both sorted, merge-sort traversal	Good when inputs already sorted
Sort	External sort if > work_mem	Creates temp files when memory insufficient
Aggregate	GROUP BY processing	Hash or sort-based
Gather	Parallel query coordination

Visualization Tools¶

https://explain.tensor.ru - interactive plan visualization
https://tatiyants.com/pev - plan visualizer
https://explain.depesz.com - detailed analysis

Query Optimization Checklist¶

SQL Level¶

Use UNION ALL instead of UNION when duplicates acceptable
Select only needed columns (avoid SELECT *)
Avoid DISTINCT when possible (often signals bad JOIN)
For composite indexes, include the first column in queries
Use EXISTS/IN where applicable
Small tables: full scan is faster than index scan (fits in one page)
Functions on indexed columns disable index usage

Database Level¶

Run ANALYZE regularly for fresh statistics
Tune work_mem for complex queries with sorts/hashes
Set random_page_cost = 1.1-2.0 for SSD storage
Increase default_statistics_target for better cardinality estimates
Enable pg_stat_statements to find slow/frequent queries

Architecture Level¶

Add connection pooler when max_connections > 100-200
Consider read replicas for read-heavy workloads
Materialized views for repeated heavy aggregations
Partition large tables by date/range
Batch large DML operations

Patterns¶

Diagnosing Stale Statistics¶

-- Compare estimated vs actual rows - large discrepancy = stale stats
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'active';
-- If estimated: 100, actual: 50000 -> run ANALYZE
ANALYZE orders;

Forcing Index Use (Testing Only)¶

-- PostgreSQL: disable seq scan to test if index would help
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
RESET enable_seqscan;

-- MySQL: force specific index
SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 42;

Monitoring Extensions¶

-- pg_stat_statements: find slowest/most frequent queries
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- auto_explain: automatically log slow query plans
ALTER SYSTEM SET auto_explain.log_min_duration = '1s';

Gotchas¶

EXPLAIN ANALYZE actually executes the query (including DML!) - wrap in transaction and ROLLBACK
Plans for 1000-row tables don't extrapolate to millions - planner costs are non-linear
Single-page tables always get sequential scan regardless of indexes
EXPLAIN ANALYZE timing excludes network transfer and format conversion
High join_collapse_limit value improves plan but slows planning for many-table JOINs
After VACUUM, visibility map updated -> Index Only Scan may replace Seq Scan