This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.
PostgreSQL Query Optimization: From 2 Seconds to 2 Milliseconds
A slow PostgreSQL query is the most common performance bottleneck in web applications — and the most fixable. This guide covers the systematic approach to identifying slow queries, reading EXPLAIN ANALYZE output, and applying the optimizations that have the highest ROI: indexing, query rewriting, and configuration tuning. From 2 seconds to 2 milliseconds.
The Optimization Workflow
- Identify: Enable pg_stat_statements, find the top 10 slowest/most frequent queries
- Analyze: Run EXPLAIN (ANALYZE, BUFFERS) on the slow query
- Diagnose: Is it a missing index? A bad query plan? Lock contention? Hardware?
- Fix: Apply the specific optimization, measure the improvement
- Prevent: Add an index, adjust work_mem, or rewrite the query permanently
Reading EXPLAIN ANALYZE Output
| Node Type | What It Means | Good or Bad? | Action |
|---|---|---|---|
| Seq Scan | Scanning every row in the table | Bad for large tables (>10K rows) | Add an index |
| Index Scan | Using index, reads index + heap | Good for selective queries, bad for large result sets | Usually fine; Index Only Scan is better |
| Index Only Scan | Index covers all needed columns | Excellent — no heap access needed | Keep; consider covering indexes |
| Bitmap Index Scan → Bitmap Heap Scan | Combines multiple indexes, then reads heap | OK for AND/OR conditions on indexed columns | Fine unless rows are very large |
| Nested Loop | For each row in A, look up B | Good if A is small, B is indexed | Bad for large tables without index |
| Hash Join | Build hash of one table, probe with other | Good for joining two large tables | Usually fine; ensure work_mem is sufficient |
| Merge Join | Both inputs sorted, merge like zipper | Good for pre-sorted inputs (from indexes) | Excellent if both are index scans |
Index Types and When to Use Them
| Index Type | Best For | Example | Size Overhead |
|---|---|---|---|
| B-Tree (default) | Equality, range, ORDER BY, <, >, BETWEEN | WHERE user_id = 123 / WHERE created_at > now() - interval '7 days' | ~40% of table size |
| Partial Index | Queries on a subset of rows | WHERE status = 'active' AND created_at > '2024-01-01' (index WHERE status = 'active') | Small |
| Covering Index (INCLUDE) | Index-Only Scans for specific columns | INDEX ON users (email) INCLUDE (name, avatar_url) | Medium |
| GIN (Generalized Inverted) | Full-text search, arrays, JSONB | WHERE document @@ to_tsquery('search & query') | Large |
| GiST | Geometric data, full-text search | WHERE location <@ ST_MakeEnvelope(...) | Medium-Large |
| BRIN (Block Range) | Very large tables, naturally sorted data | WHERE created_at BETWEEN ... (on 1B+ row tables) | Very Small (<0.1%) |
Common Optimizations by Root Cause
| Symptom | Root Cause | Fix | Speedup |
|---|---|---|---|
| Seq Scan on large table | Missing index | CREATE INDEX ON table (filter_column) | 100-10,000x |
| Nested Loop with large inner table | Missing JOIN index | CREATE INDEX ON inner_table (join_key) | 50-500x |
| Sort using external merge (disk) | work_mem too low | SET work_mem = '256MB' for this query | 5-20x |
| N+1 queries (ORM) | Lazy loading in application | Use eager loading (includes/joins) | 10-100x |
| Slow COUNT(*) | MVCC visibility checks | Use estimate: SELECT reltuples FROM pg_class WHERE relname = 'table' | 100-1000x |
| Table bloat | Dead tuples from UPDATE/DELETE | VACUUM ANALYZE; adjust autovacuum settings | 2-10x |
Key PostgreSQL Configuration Tuning
-- Check current settings
SHOW shared_buffers; -- Default: 128MB. Set to 25% of RAM.
SHOW work_mem; -- Default: 4MB. Increase to 64-256MB for reporting queries.
SHOW maintenance_work_mem; -- Default: 64MB. Set to 10% of RAM for VACUUM/INDEX speed.
SHOW effective_cache_size; -- Default: 4GB. Set to 75% of RAM (hint for planner).
SHOW random_page_cost; -- Default: 4.0. Set to 1.1 for SSD (encourages index use).
-- Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = 1000; -- Log queries > 1s
SELECT pg_reload_conf();
Bottom line: 90% of PostgreSQL performance problems are solved by adding the right index and adjusting work_mem. Before adding indexes, run EXPLAIN (ANALYZE, BUFFERS) on the slow query. If you see Seq Scan on a large table, add an index. If you see external merge on disk, increase work_mem. These two fixes alone resolve the vast majority of performance issues. See also: Full-Text Search Comparison and Database Migrations Guide.
Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.
Found this useful? Check out more developer guides and tool comparisons on AI Study Room.
Top comments (0)