Query Performance

Learn to diagnose and fix slow queries using PostgreSQL's powerful analysis tools.

The Golden Rule

Measure, don't guess. Always use EXPLAIN ANALYZE before optimizing.

EXPLAIN ANALYZE

Basic Usage

EXPLAIN ANALYZE
SELECT u.name, COUNT(b.id) as bookings
FROM users u
LEFT JOIN bookings b ON b.user_id = u.id
GROUP BY u.id, u.name;

Reading the Output

HashAggregate  (cost=245.00..247.50 rows=200 width=40) (actual time=5.234..5.312 rows=150 loops=1)
   Group Key: u.id
   ->  Hash Left Join  (cost=10.50..220.00 rows=5000 width=36) (actual time=0.123..3.456 rows=5000 loops=1)
         Hash Cond: (b.user_id = u.id)
         ->  Seq Scan on bookings b  (cost=0.00..155.00 rows=5000 width=8) (actual time=0.012..1.234 rows=5000 loops=1)
         ->  Hash  (cost=8.00..8.00 rows=200 width=32) (actual time=0.089..0.090 rows=200 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 15kB
               ->  Seq Scan on users u  (cost=0.00..8.00 rows=200 width=32) (actual time=0.005..0.045 rows=200 loops=1)
Planning Time: 0.234 ms
Execution Time: 5.456 ms

Key metrics: - cost=start..total — Estimated cost (arbitrary units) - rows=N — Estimated rows returned - actual time=start..end — Real milliseconds - rows=N — Actual rows returned - loops=N — How many times this step ran

EXPLAIN Options

-- Include buffers (I/O stats)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM large_table WHERE id = 123;

-- Format as JSON (for programmatic analysis)
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM users WHERE email = 'test@example.com';

-- Show all settings
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT * FROM users LIMIT 10;

Buffer Output

Buffers: shared hit=100 read=50

• shared hit — Pages found in cache (fast)
• read — Pages read from disk (slow)
• written — Pages written during query

Common Query Plan Nodes

Scan Types

Node	Description	When Used	Speed
Seq Scan	Read entire table	No usable index, small table	Slow for large tables
Index Scan	Read index, fetch rows	Selective queries	Fast
Index Only Scan	Read only index	All columns in index	Fastest
Bitmap Index Scan	Build bitmap, then scan	Multiple conditions	Medium

Join Types

Node	Description	When Used
Nested Loop	For each left row, scan right	Small tables, indexed lookups
Hash Join	Build hash of smaller table	Medium tables, equality joins
Merge Join	Sort both, merge	Pre-sorted data, large tables

Aggregation

Node	Description
HashAggregate	Hash-based GROUP BY
GroupAggregate	Sort-based GROUP BY (preserves order)
Sort	Sort rows (expensive if no index)

Problem Patterns

Sequential Scan on Large Table

Problem:

Seq Scan on bookings  (cost=0.00..15000.00 rows=1000000 width=100)
   Filter: (user_id = 123)
   Rows Removed by Filter: 999900

Solution: Add an index.

CREATE INDEX idx_bookings_user_id ON bookings(user_id);

Index Not Used

PostgreSQL may ignore indexes when:

1. Small table — Seq scan is faster
2. Low selectivity — Query returns most rows anyway
3. Type mismatch — WHERE int_col = '123' (string)
4. Function on column — WHERE LOWER(email) = 'test'

Fix function-on-column:

-- Bad: can't use index
WHERE LOWER(email) = 'test@example.com'

-- Good: create expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

Nested Loop with Many Iterations

Problem:

Nested Loop  (actual time=0.1..5000.0 rows=100000 loops=1)
   ->  Seq Scan on orders  (rows=10000)
   ->  Index Scan on order_items  (rows=10 loops=10000)  -- 10000 loops!

Solution: PostgreSQL chose nested loop, but hash join might be better.

-- Hint via CTE (forces materialization)
WITH order_batch AS MATERIALIZED (
    SELECT * FROM orders WHERE date > '2024-01-01'
)
SELECT * FROM order_batch o
JOIN order_items i ON i.order_id = o.id;

-- Or adjust work_mem for the session
SET work_mem = '256MB';

Sort Spilling to Disk

Problem:

Sort  (cost=10000.00..10500.00)
   Sort Key: created_at
   Sort Method: external merge  Disk: 50000kB  -- BAD

Solution: Increase work_mem or add index.

-- For this query
SET work_mem = '256MB';

-- Or create index for the sort
CREATE INDEX idx_bookings_created ON bookings(created_at);

Hash Join Batching

Problem:

Hash Join
   Hash  Batches: 16  Memory Usage: 4096kB  -- Multiple batches = disk

Solution: Increase work_mem.

pg_stat_statements

Track query statistics across all executions.

Enable Extension

-- In postgresql.conf or via SQL
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Find Slow Queries

-- Top 10 by total time
SELECT
    substring(query, 1, 100) AS query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with high variability
SELECT
    substring(query, 1, 100) AS query,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms
FROM pg_stat_statements
WHERE calls > 100
ORDER BY stddev_exec_time DESC
LIMIT 10;

Reset Statistics

SELECT pg_stat_statements_reset();

Query Optimization Workflow

1. Identify Slow Queries

-- From pg_stat_statements
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100  -- > 100ms
ORDER BY total_exec_time DESC;

-- From slow query log (if enabled)
-- Check PostgreSQL log files

2. Analyze the Query

EXPLAIN (ANALYZE, BUFFERS)
<your slow query here>;

3. Check for Missing Indexes

-- Tables with seq scans
SELECT
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND seq_tup_read > 10000
ORDER BY seq_tup_read DESC;

4. Verify Index Usage

-- Unused indexes (candidates for removal)
SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

5. Apply Fixes

• Add missing indexes
• Rewrite query to use indexes
• Adjust work_mem if needed
• Consider denormalization for hot paths

6. Verify Improvement

-- Re-run EXPLAIN ANALYZE
-- Check pg_stat_statements after some traffic

Common Optimizations

Use Covering Indexes

-- Query only needs user_id and created_at
SELECT user_id, created_at FROM bookings WHERE status = 'active';

-- Covering index includes all needed columns
CREATE INDEX idx_bookings_status_covering
ON bookings(status) INCLUDE (user_id, created_at);
-- Result: Index Only Scan (no table access)

Partial Indexes

-- Only index active records
CREATE INDEX idx_users_active_email
ON users(email)
WHERE status = 'active';

-- Much smaller than full index, faster to maintain

BRIN for Time-Series Data

-- For append-only time-series tables
CREATE INDEX idx_events_time_brin
ON events USING BRIN (created_at);
-- Tiny index, great for range queries on ordered data

Optimize JOINs

-- Ensure join columns are indexed
CREATE INDEX idx_bookings_user_id ON bookings(user_id);

-- For multi-column joins, index in join order
CREATE INDEX idx_order_items_order_product
ON order_items(order_id, product_id);

Batch Operations

-- Bad: N+1 queries
for user_id in user_ids:
    SELECT * FROM bookings WHERE user_id = ?

-- Good: Single query
SELECT * FROM bookings
WHERE user_id = ANY(ARRAY[1, 2, 3, 4, 5]);

Configuration Tuning

Key Parameters

-- Memory for sorts/hashes (per operation)
SET work_mem = '64MB';  -- Default 4MB is often too low

-- Planner cost estimates
SET random_page_cost = 1.1;  -- Lower for SSDs (default 4.0)
SET effective_cache_size = '4GB';  -- ~75% of RAM

-- Connection count
SET max_connections = 100;  -- Match your pool size

Check Current Settings

SHOW work_mem;
SHOW random_page_cost;
SHOW effective_cache_size;

-- All settings
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('work_mem', 'random_page_cost', 'effective_cache_size');

Monitoring Query Performance

Live Queries

-- Currently running queries
SELECT
    pid,
    now() - query_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;

-- Kill a long-running query
SELECT pg_cancel_backend(pid);  -- Graceful
SELECT pg_terminate_backend(pid);  -- Force

Table Statistics

-- When was the table last analyzed?
SELECT
    schemaname,
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables;

-- Force statistics update
ANALYZE users;
ANALYZE VERBOSE users;  -- With details

Best Practices

1. Enable pg_stat_statements in all environments
2. Run EXPLAIN ANALYZE before and after changes
3. Check for Seq Scans on large tables
4. Monitor index usage and remove unused indexes
5. Keep statistics current with regular ANALYZE
6. Test with production-like data — Query plans change with data size