Stage 3 · Build

Database Reliability Engineering

Slow Query Remediation

Identifying, analyzing, and fixing slow queries without application downtime.

8 min readDatabases for SREBuild

Identifying Slow Queries

The first step is finding which queries are actually slow. pg_stat_statements provides aggregated statistics for every query executed. Focus on total impact (total time) rather than just average time.

Find slow queries


-- Top queries by total execution time (highest impact)
SELECT
    queryid,
    LEFT(query, 100) AS query,
    calls,
    ROUND(total_exec_time::numeric, 1) AS total_ms,
    ROUND(mean_exec_time::numeric, 1) AS avg_ms,
    ROUND(stddev_exec_time::numeric, 1) AS stddev_ms,
    rows,
    ROUND(100.0 * shared_blks_hit /
        GREATEST(shared_blks_hit + shared_blks_read, 1), 1) AS cache_hit_pct
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

-- Find queries with high I/O
SELECT
    LEFT(query, 100) AS query,
    calls,
    shared_blks_read AS disk_reads,
    shared_blks_hit AS cache_hits,
    ROUND(100.0 * shared_blks_hit /
        GREATEST(shared_blks_hit + shared_blks_read, 1), 1) AS cache_hit_pct
FROM pg_stat_statements
WHERE shared_blks_read > 100000
ORDER BY shared_blks_read DESC
LIMIT 10;

Focus on queries with high total_exec_time — they consume the most database resources overall. A query that runs 100K times at 10ms each (1000s total) is more impactful than one running 10 times at 1s each (10s total).

EXPLAIN ANALYZE Deep Dive

Analyze a slow query


-- Step 1: Get the actual execution plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at > '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 10;

-- Output analysis:
-- HashAggregate  (actual time=1234.567..1234.890 rows=500 loops=1)
--   Buffers: shared hit=45000 read=12000  <-- high read count
--   ->  Hash Join  (actual time=1230.123..1234.000 rows=50000 loops=1)
--         Hash Cond: (o.user_id = u.id)
--         Buffers: shared hit=40000 read=12000
--         ->  Seq Scan on orders o  (actual time=0.020..1200.000 rows=5000000 loops=1)
--               Filter: (created_at > '2024-01-01')
--               Rows Removed by Filter: 5000000  <-- full table scan!
--               Buffers: shared hit=30000 read=12000

Key red flags: Seq Scan on large tables, Rows Removed by Filter much larger than rows returned, high shared_blks_read (disk reads), and total time close to actual time (not estimated).

EXPLAIN ANALYZE modifies the query

EXPLAIN ANALYZE actually runs the query and returns real timing. For INSERT, UPDATE, or DELETE, wrap in a transaction and ROLLBACK to avoid actually modifying data. EXPLAIN (without ANALYZE) shows only the estimated plan.

Common Performance Patterns

Pattern	Symptom	Fix
Sequential scan on large table	Seq Scan with high cost	Add appropriate index
Nested loop on large datasets	Nested Loop with high row estimates	Use Hash Join or add index
SELECT * usage	More data transferred than needed	Select only required columns
Missing composite index	Index scan but high filter ratio	Create composite index matching WHERE clause
Cartesian product	Row count explodes at join	Check missing join condition

Fix: add missing index


-- Slow query: sequential scan on 10M rows
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';
-- Seq Scan on orders (actual time=1200..1200 rows=50 loops=1)

-- Fix: create composite index matching the WHERE clause
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);

-- Result: index scan instead of sequential scan
-- Index Scan using idx_orders_user_status (actual time=0.05..0.08 rows=50 loops=1)

The composite index matches both columns in the WHERE clause. Column order matters: user_id first (equality), then status (also equality). Both are covered by the index.

Query Rewrites

Rewrite patterns


-- BAD: correlated subquery (runs once per row)
SELECT u.name,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- GOOD: JOIN with GROUP BY
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- BAD: OR prevents index usage
SELECT * FROM users WHERE email = 'alice@example.com' OR name = 'Alice';

-- GOOD: UNION ALL with index-friendly queries
SELECT * FROM users WHERE email = 'alice@example.com'
UNION ALL
SELECT * FROM users WHERE name = 'Alice' AND email != 'alice@example.com';

-- BAD: NOT IN with subquery (slow, null-unsafe)
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

-- GOOD: LEFT JOIN with IS NULL (fast, null-safe)
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Rewrites often provide 10-100x performance improvements. The correlated subquery runs the inner query for every row in the outer query. The JOIN version scans each table once.

Online Fixes

Add index CONCURRENTLY — does not block reads or writes
Rewrite query in application code — deploy new code, not schema change
Update statistics — ANALYZE table to give planner better estimates
Tune planner settings — increase work_mem for specific queries
Add connection pooler — prevent query queuing from looking like slowness

Quick fixes without downtime


-- Update statistics (instant, no lock)
ANALYZE orders;

-- Increase work_mem for complex sorts (session-level only)
SET work_mem = '512MB';
-- Run the slow query
RESET work_mem;

-- Disable seqscan for debugging (do NOT leave this on)
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;  -- see what planner chooses without seqscan
SET enable_seqscan = on;

-- Force a specific plan with pg_hint (requires extension)
CREATE EXTENSION pg_hint_plan;
SET hint_plan.enable_hint = on;

Statistics updates are free and should be done after large data changes. Tuning work_mem per-session lets you optimize specific queries without affecting all connections.

Prevention Strategies

Require EXPLAIN ANALYZE in code reviews for new queries
Set statement_timeout to kill runaway queries automatically
Monitor pg_stat_statements trends for regression detection
Test queries against production-sized data in staging
Use query plan caching to detect plan regressions
Implement slow query logging with auto-explain extension

Auto-explain for production


-- Enable auto-explain for slow queries
ALTER SYSTEM SET shared_preload_libraries = 'auto_explain';
ALTER SYSTEM SET auto_explain.log_min_duration = '500ms';
ALTER SYSTEM SET auto_explain.log_analyze = true;
ALTER SYSTEM SET auto_explain.log_buffers = true;

-- Restart PostgreSQL (or use pg_reload_conf())
-- Now any query taking > 500ms automatically logs its execution plan
-- to the PostgreSQL log file

Auto-explain captures execution plans for slow queries without manual intervention. This is invaluable for catching regressions in production queries that were previously fast.

Create a slow query review process

Weekly, review the top 10 queries from pg_stat_statements by total time. Look for: queries with high stddev (unstable), low cache_hit_pct (I/O heavy), and growing mean_exec_time (regressing). Fix one query per week — compound improvements.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.