PostgreSQL Query Optimization: From
Explain Plans to Index Design
Abstract
Effective query optimization in PostgreSQL requires understanding the query
planner, interpreting execution plans, and designing appropriate indexes. This
document covers the internals of PostgreSQL’s cost-based optimizer, practical
EXPLAIN analysis, and indexing strategies for common workload patterns.
1. The Query Planner
PostgreSQL uses a cost-based query optimizer that evaluates multiple execution
strategies and selects the plan with the lowest estimated total cost. The planning
process follows these stages:
1. Parsing: SQL text is converted to a parse tree
2. Rewriting: Rules and views are expanded
3. Planning/Optimization: The planner generates candidate plans and estimates
costs
4. Execution: The selected plan is executed by the executor
1.1 Cost Model
The planner assigns costs using two primary metrics:
startup cost: Resources needed before the first row can be returned
total cost: Resources needed to return all rows
Costs are expressed in arbitrary units calibrated to sequential page reads. Key cost
parameters:
SHOW seq_page_cost; -- 1.0 (baseline)
SHOW random_page_cost; -- 4.0 (default, lower for SSDs)
SHOW cpu_tuple_cost; -- 0.01
SHOW cpu_index_tuple_cost; -- 0.005
SHOW cpu_operator_cost; -- 0.0025
SHOW effective_cache_size; -- estimate of OS + PG cache
For SSD-based systems, setting random_page_cost to 1.1-1.5 more accurately
reflects the reduced penalty for random I/O.
1.2 Statistics
The planner relies on table statistics maintained by ANALYZE (run automatically by
autovacuum). Key statistics are stored in pg_statistic and accessible via pg_stats:
SELECT attname, null_frac, n_distinct, most_common_vals,
most_common_freqs, histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
n_distinct: Estimated number of distinct values (negative = fraction of rows)
correlation: Physical vs. logical ordering (-1 to 1), affects index scan cost
most_common_vals / freqs: Values with disproportionate frequency
Increase statistics granularity for skewed columns:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;
2. Reading EXPLAIN Output
2.1 Basic EXPLAIN
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT [Link], [Link], [Link]
FROM orders o
JOIN customers c ON [Link] = o.customer_id
WHERE o.created_at >= '2025-01-01'
AND [Link] = 'completed';
Sample output:
Hash Join (cost=45.20..1823.47 rows=312 width=52)
(actual time=0.892..12.341 rows=287 loops=1)
Hash Cond: (o.customer_id = [Link])
Buffers: shared hit=412 read=23
-> Bitmap Heap Scan on orders o
(cost=12.45..1785.23 rows=312 width=28)
(actual time=0.234..11.567 rows=287 loops=1)
Recheck Cond: (created_at >= '2025-01-01')
Filter: (status = 'completed')
Rows Removed by Filter: 1843
Heap Blocks: exact=198
Buffers: shared hit=203 read=23
-> Bitmap Index Scan on idx_orders_created_at
(cost=0.00..12.37 rows=2155 width=0)
(actual time=0.112..0.112 rows=2130 loops=1)
Buffers: shared hit=5 read=3
-> Hash (cost=22.50..22.50 rows=820 width=32)
(actual time=0.631..0.632 rows=820 loops=1)
Buffers: shared hit=12
-> Seq Scan on customers c
(cost=0.00..22.50 rows=820 width=32)
(actual time=0.008..0.298 rows=820 loops=1)
Buffers: shared hit=12
Planning Time: 0.245 ms
Execution Time: 12.567 ms
2.2 Key Indicators of Problems
Symptom Likely Cause Solution
Rows Removed by Index not selective enough Composite index with
Filter is high filter column
actual rows >> Stale statistics or correlation ANALYZE; increase
estimated rows statistics target
Seq Scan on large Create appropriate
table Missing index index
Nested Loop with Missing index on inner table Index on join column
high loops
Sort with external Increase work_mem or
merge work_mem too low add index
Cold cache or undersized Warm cache or
Buffers read >> hit shared_buffers increase
shared_buffers
3. Index Types and Strategies
3.1 B-tree (Default)
Best for equality and range queries. Supports <, <=, =, >=, >, BETWEEN, IN, IS NULL,
and pattern matching with fixed prefixes.
-- Composite index for the query above
CREATE INDEX idx_orders_status_created
ON orders (status, created_at)
WHERE status = 'completed'; -- partial index
Column ordering in composite indexes matters: place equality columns first,
then range columns.
-- Good: equality first, then range
CREATE INDEX idx_lookup ON events (type, created_at);
-- Query benefits fully:
SELECT * FROM events WHERE type = 'click' AND created_at > '2025-01-01';
-- This ordering would be less efficient:
CREATE INDEX idx_lookup_bad ON events (created_at, type);
3.2 GIN (Generalized Inverted Index)
Optimized for composite values: arrays, JSONB, full-text search.
-- JSONB containment queries
CREATE INDEX idx_metadata ON products USING GIN (metadata jsonb_path_ops);
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
-- Full-text search
CREATE INDEX idx_fts ON articles USING GIN (to_tsvector('english', body));
SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('database & optimization');
3.3 GiST (Generalized Search Tree)
Supports geometric data, range types, and nearest-neighbor searches.
-- Range overlap queries
CREATE INDEX idx_booking_period ON bookings USING GIST (
tstzrange(check_in, check_out)
);
SELECT * FROM bookings
WHERE tstzrange(check_in, check_out) && tstzrange('2025-06-01', '2025-06-15');
3.4 BRIN (Block Range Index)
Extremely compact indexes for physically ordered data. Stores min/max values per
block range.
-- Time-series data that's inserted in order
CREATE INDEX idx_logs_ts ON logs USING BRIN (created_at)
WITH (pages_per_range = 32);
-- Size comparison on 100M rows:
-- B-tree: ~2.1 GB
-- BRIN: ~48 KB
4. Common Optimization Patterns
4.1 Covering Indexes (Index-Only Scans)
Include all columns needed by the query to avoid heap access:
CREATE INDEX idx_orders_covering ON orders (customer_id)
INCLUDE (total, status);
-- This can now be an index-only scan:
SELECT total, status FROM orders WHERE customer_id = 42;
4.2 Partial Indexes
Index only the rows that matter:
-- Only 2% of orders are 'pending', but queries filter on it constantly
CREATE INDEX idx_pending_orders ON orders (created_at)
WHERE status = 'pending';
4.3 Expression Indexes
Index computed values:
CREATE INDEX idx_email_lower ON users (lower(email));
-- Now uses the index:
SELECT * FROM users WHERE lower(email) = 'user@[Link]';
4.4 Pagination with Keyset
Replace OFFSET with keyset pagination for consistent performance:
-- Slow at high offsets:
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100000;
-- Fast at any depth:
SELECT * FROM products WHERE id > 100000 ORDER BY id LIMIT 20;
5. Monitoring and Identifying Slow Queries
Enable pg_stat_statements for query-level metrics:
SELECT query, calls, mean_exec_time, stddev_exec_time,
rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
Identify missing indexes via sequential scan statistics:
SELECT relname, seq_scan, seq_tup_read,
idx_scan, idx_tup_fetch,
seq_tup_read / NULLIF(seq_scan, 0) AS avg_seq_tuples
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_tup_read DESC;
6. Conclusion
PostgreSQL query optimization is an iterative process: profile with EXPLAIN
ANALYZE, identify bottlenecks, apply targeted indexes, and verify improvement.
Understanding the cost model, statistics system, and available index types gives
you the tools to handle most performance challenges systematically.
References
1. PostgreSQL Documentation: Query Planning, Indexes
2. “The Art of PostgreSQL” by Dimitri Fontaine
3. pgMustard EXPLAIN Glossary
4. Citus Data: PostgreSQL Index Types Reference