Quick start (5 min)
Your API endpoint latency spikes from 45ms to 3.2s during peak traffic. Nine times out of ten, the culprit is a database query choking under load. While tools like MySQL or MongoDB offer query inspection, PostgreSQL gives you exceptional visibility into its planner decisions. You just need to know how to read EXPLAIN ANALYZE.
Prefix any SQL statement with EXPLAIN ANALYZE in psql or your database GUI to see how Postgres actually runs your query:
EXPLAIN ANALYZE
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE status = 'completed'
AND created_at >= '2026-01-01';
The database executes the query and returns a detailed execution tree:
Seq Scan on orders (cost=0.00..1845.00 rows=1420 width=36) (actual time=0.045..14.821 rows=1530 loops=1)
Filter: ((created_at >= '2026-01-01'::date) AND ((status)::text = 'completed'::text))
Rows Removed by Filter: 85470
Planning Time: 0.182 ms
Execution Time: 15.012 ms
Start at the bottom. Planning Time (0.182 ms) represents the time the optimizer spent calculating the cheapest execution path. Execution Time (15.012 ms) measures real disk I/O, row retrieval, and filter evaluation. Plain EXPLAIN only displays theoretical cost models. Adding ANALYZE runs the query, returning ground-truth runtimes alongside planner estimates.
Deep dive
Execution plans read from the inside out and bottom to top. Child nodes run first, passing their output rows upward to parent nodes.
Deconstructing the Node Metrics
Each plan node presents two distinct metric sets: estimated values followed by actual execution tallies.
- cost=0.00..1845.00: Arbitrary work units based on disk page fetches and CPU cycles.
0.00is startup cost (time until the node yields row one).1845.00is the estimated cost to process every matching row. - rows=1420: The optimizer’s row estimate, derived from statistical distributions stored in
pg_statistic. - width=36: Expected row footprint in bytes.
- actual time=0.045..14.821: Wall-clock duration in milliseconds. The first row materialized at 0.045 ms; all 1,530 rows completed at 14.821 ms.
- rows=1530: The actual count of rows returned. Compare this against estimated rows (1,420). Close numbers mean healthy statistics.
- loops=1: Iteration count. If an inner loop inside a nested join runs 50 times, multiply reported times and row counts by 50 to calculate total resource consumption.
Scan Strategies: Sequential vs Index
Postgres selects from four primary scan methods depending on table volume, index presence, and column cardinality:
- Seq Scan (Sequential Scan): Streams the full table from disk page by page. Notice
Rows Removed by Filter: 85470in our earlier plan. The engine inspected 87,000 physical rows just to retain 1,530—wasting disk bandwidth on an 98.2% discard rate. - Index Scan: Navigates a B-Tree index to locate specific tuple pointers (TIDs), then fetches individual table rows directly from heap pages.
- Bitmap Index Scan / Bitmap Heap Scan: Bridges sequential and index scans. The index scan first constructs a memory bitmap marking matching physical pages. Next, the heap scan reads those pages in physical storage order, preventing random seek thrashing.
- Index Only Scan: The fastest scan path. The index contains every requested column, so Postgres skips visiting the heap entirely whenever the table visibility map confirms pages are clean.
Advanced usage
Reading node costs gets you halfway there. Pinpointing production bottlenecks requires drilling into memory spills, buffer hits, and planner edge cases.
Tracking Memory and Disk I/O with BUFFERS
Always include the BUFFERS option when troubleshooting slow queries. It proves whether data lived warm in RAM or required physical storage reads:
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, count(o.id) AS total_orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country_code = 'US'
GROUP BY u.email
ORDER BY total_orders DESC
LIMIT 10;
The resulting plan reveals buffer metrics:
Buffers: shared hit=412 read=1895 dirtied=0 written=0
- shared hit (412): Pages pulled directly from PostgreSQL
shared_buffersin RAM. Fast and near-zero latency. - shared read (1895): Pages fetched from the OS cache or disk. At the default 8 kB per page, 1,895 reads translate to ~14.8 MB of disk or cache throughput. High read counts indicate cold cache issues or absent indexes.
Hunting Down Hidden Sequential Scans
You created an index on created_at, yet the planner still insists on a full table scan. Three common issues cause this behavior:
1. Type Mismatches and Implicit Casting
When an application driver passes parameters with mismatched types—such as passing a string literal to a bigint column or comparing differing text collations—Postgres applies a type conversion function around the column. B-Tree indexes cannot index expressions unless explicitly built as expression indexes.
-- Bad: Function wrapper prevents B-Tree index usage
EXPLAIN ANALYZE SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Good: Exact match leverages standard index on email
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';
-- Alternative: Create an expression index to support case-insensitive lookups
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
2. Column Transformations in the WHERE Clause
Wrapping timestamp columns in date functions strips away index eligibility. Avoid expressions like WHERE DATE(created_at) = '2026-03-01'. Use sargable boundary ranges instead:
-- Uses index range scan cleanly
EXPLAIN ANALYZE
SELECT id, total_amount
FROM orders
WHERE created_at >= '2026-03-01 00:00:00'
AND created_at < '2026-03-02 00:00:00';
3. Low Selectivity Thresholds
The query planner calculates whether random I/O overhead beats raw sequential throughput. If your predicate matches more than 10% to 20% of the entire table, sequential scanning is often genuinely faster than hopping back and forth across an index. On fast NVMe drives where random_page_cost is close to seq_page_cost (e.g., 1.1), this trade-off shifts, but sequential scans on large result sets remain expected behavior.
Detecting Memory Spills in Sorts and Hashes
Check the sort node details in your output:
Sort Method: external merge Disk: 18432kB
PostgreSQL sets work_mem to 4 MB by default. When sorts or hash tables outgrow this allocation, the engine writes temporary chunks to disk, cratering throughput. Raising work_mem for the active session resolves the spill without requiring a global database restart:
SET work_mem = '64MB';
-- Re-running the query now yields:
-- Sort Method: quicksort Memory: 4120kB
Practical tips
Safe production debugging requires sensible operational guardrails:
- Protect Live Data on Mutations:
EXPLAIN ANALYZEexecutes statements. If you pass aDELETEorUPDATE, rows are modified immediately. Wrap mutating queries in a transaction block and roll back:
BEGIN;
EXPLAIN ANALYZE
DELETE FROM sessions WHERE expired_at < NOW();
ROLLBACK;
- Refresh Stale Statistics: When estimated rows (10) diverge wildly from actual rows (50,000), the optimizer selects inefficient nested loop joins over hash joins. Rebuild column statistics using
ANALYZE orders;to restore accurate plan estimates. - Automate Slow Query Logging: Skip manual query capture. Load the
auto_explainmodule inpostgresql.confto automatically dump execution plans for any query crossing your performance SLA:
# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '250ms'
auto_explain.log_analyze = true
auto_explain.log_buffers = true
- Visualize Giant Execution Trees: Complex queries with 15+ joins generate plans hundreds of lines long. Export the plan with
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)and load it into visual diagnostic tools like PEV2 or Dalibo’s Explain. These highlight the costliest bottleneck nodes in seconds.
