Finding the Real Bottleneck in a Slow PostgreSQL Query

Your application is slow. The dashboard shows query times spiking. You open pgAdmin, run EXPLAIN ANALYZE, and stare at a wall of text. Where do you even start?

This article presents a systematic methodology for PostgreSQL performance tuning that goes beyond "add an index" to actually understanding what's happening.

The Wrong Approach

Most developers do this:

1. See slow query
2. Add index on WHERE column
3. Query still slow
4. Add more indexes
5. Write performance gets worse
6. Give up, blame PostgreSQL

The Right Approach: Systematic Analysis

Step 1: Get the Actual Execution Plan

-- Always use ANALYZE and BUFFERS
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.email, o.total, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 20;

Key options:

• ANALYZE — Actually executes, shows actual vs estimated rows
• BUFFERS — Shows shared memory hits/reads/writes
• FORMAT TEXT — Readable output (JSON for tooling)

Step 2: Read the Plan Bottom-Up

PostgreSQL executes plans bottom-up. The indented nodes are children that feed into parents.

Limit (cost=1234.56..1234.58 rows=20 width=120) (actual time=45.2..45.3 rows=20 loops=1)
  ->  Sort (cost=1234.56..1234.58 rows=20 width=120) (actual time=45.2..45.3 rows=20 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Nested Loop (cost=0.56..1234.54 rows=20 width=120) (actual time=0.8..44.9 rows=20 loops=1)
              ->  Index Scan using idx_users_created on users u (cost=0.28..12.30 rows=100 width=72) (actual time=0.04..0.1 rows=100 loops=1)
                    Index Cond: (created_at > '2024-01-01'::date)
              ->  Index Scan using idx_orders_user on orders o (cost=0.28..12.20 rows=1 width=48) (actual time=0.1..0.4 rows=0 loops=100)
                    Index Cond: (user_id = u.id)
                    Filter: (status = 'completed')
                    Rows Removed by Filter: 50

Read order: Start at the deepest indentation (Index Scan on users) and work up.

Step 3: Check Estimates vs Actuals

The most important signal: rows=X vs actual rows=Y

Index Scan using idx_orders_user on orders o
  (cost=0.28..12.20 rows=1 width=48)  <-- ESTIMATED: 1 row
  (actual time=0.1..0.4 rows=0 loops=100)  <-- ACTUAL: 0 rows x 100 loops = 0 total

Red flags:

• Estimate 1, actual 1000+ (missing statistics)
• Estimate 1000, actual 1 (overestimation, maybe stale stats)
• loops=N where N is large (nested loop with many iterations)

Step 4: Analyze Buffer Usage

Buffers: shared hit=150 read=50 dirtied=2 written=1

• hit — Found in shared_buffers (good)
• read — Had to read from disk (slow)
• dirtied — Modified in memory
• written — Flushed to disk

High read values indicate:

• Working set doesn't fit in memory
• Missing indexes causing seq scans
• Poor cache utilization

Common Plan Patterns and Fixes

Sequential Scan (Seq Scan)

->  Seq Scan on orders (cost=0.00..5000.00 rows=100000 width=200)
      Filter: (status = 'completed')
      Rows Removed by Filter: 900000

Diagnosis: No usable index for the filter condition.

Fix: Create partial index:

CREATE INDEX idx_orders_completed 
ON orders (created_at DESC) 
WHERE status = 'completed';

Nested Loop with Many Loops

->  Nested Loop (cost=0.56..50000.00 rows=1000 width=200)
      ->  Index Scan on users (100 rows)
      ->  Index Scan on orders (loops=100, rows=10 each)

Diagnosis: For each user, scanning orders. OK for small outer, bad for large.

Fix: Hash join or merge join:

-- Force hash join (if statistics are accurate)
SET enable_nestloop = off;
-- Or restructure query to allow hash join

Sort Spilling to Disk

Sort Method: external merge  Disk: 15000kB

Diagnosis: work_mem too small for sort operation.

Fix: Increase work_mem for this query:

SET LOCAL work_mem = '256MB';
-- Or optimize to avoid sort (index on ORDER BY column)

The Statistics Problem

PostgreSQL's planner relies on statistics collected by ANALYZE.

Check Statistics Freshness

SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public';

Manual Statistics Update

-- For specific table
ANALYZE orders;

-- With higher detail for large tables
ANALYZE (options (prefetch_pages = 100)) orders;

Correlation Statistics

-- Check column correlation (affects index scan vs seq scan decisions)
SELECT tablename, attname, correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders';

Low correlation (near 0) = index scans less effective.

Advanced: Join Strategy Selection

PostgreSQL chooses between three join algorithms:

| Algorithm | Best For | Memory | |-----------|----------|--------| | Nested Loop | Small outer, indexed inner | Low | | Hash Join | Large unsorted inputs | Medium (hash table) | | Merge Join | Pre-sorted inputs | Low |

Forcing Join Strategy

-- Session level (for testing)
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_nestloop = on;

-- Query level (using CTEs to influence)
WITH filtered_orders AS (
  SELECT * FROM orders WHERE status = 'completed'
)
SELECT * FROM users u
JOIN filtered_orders o ON u.id = o.user_id;

Real-World Case Study

The Query

SELECT p.name, SUM(oi.quantity) as total_sold
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.created_at >= '2024-01-01'
  AND o.status = 'completed'
GROUP BY p.id, p.name
ORDER BY total_sold DESC
LIMIT 10;

Initial Plan (2.3s)

Limit (actual time=2300..2300 rows=10)
  ->  Sort (actual time=2300..2300 rows=10)
        Sort Key: (SUM(oi.quantity)) DESC
        ->  Hash Aggregate (actual time=2200..2250 rows=5000)
              Group Key: p.id, p.name
              ->  Hash Join (actual time=50..1800 rows=500000)
                    Hash Cond: (oi.product_id = p.id)
                    ->  Hash Join (actual time=30..1200 rows=500000)
                          Hash Cond: (oi.order_id = o.id)
                          ->  Seq Scan on order_items oi (rows=2M)
                          ->  Hash (actual time=25..25 rows=50000)
                                ->  Index Scan on orders (status filter)
                    ->  Hash (actual time=5..5 rows=10000)
                          ->  Seq Scan on products

Problems Identified

1. Seq Scan on order_items (2M rows) — No index on order_id or product_id
2. Hash Join building large hash tables — Spilling to disk
3. Late aggregation — Aggregating 500K rows before LIMIT 10

Optimizations Applied

-- 1. Composite indexes for join columns
CREATE INDEX idx_order_items_order_product 
ON order_items (order_id, product_id);

CREATE INDEX idx_order_items_product_order 
ON order_items (product_id, order_id);

-- 2. Partial index for filtered orders
CREATE INDEX idx_orders_completed_recent 
ON orders (id) 
WHERE status = 'completed' AND created_at >= '2024-01-01';

-- 3. Covering index for aggregation
CREATE INDEX idx_order_items_covering 
ON order_items (product_id, order_id) 
INCLUDE (quantity);

Optimized Plan (45ms)

Limit (actual time=45..45 rows=10)
  ->  Sort (actual time=45..45 rows=10)
        Sort Key: (SUM(oi.quantity)) DESC
        ->  Hash Aggregate (actual time=35..40 rows=1000)
              Group Key: p.id, p.name
              ->  Nested Loop (actual time=2..30 rows=50000)
                    ->  Index Scan using idx_products_pkey on products p
                    ->  Index Only Scan using idx_order_items_covering on order_items oi
                          Index Cond: (product_id = p.id)
                          Filter: (order_id IN (SELECT id FROM orders WHERE ...))

Key Changes

| Metric | Before | After | |--------|--------|-------| | Execution Time | 2,300ms | 45ms | | Rows Processed | 2,000,000 | 50,000 | | Disk I/O | High | Minimal | | Memory | 150MB | 5MB |

Monitoring Query Performance

pg_stat_statements

-- Enable in postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

-- Query top slow queries
SELECT query, calls, mean_exec_time, total_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Auto-Explain

-- Log slow queries automatically
auto_explain.log_min_duration = 1000  -- 1 second
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_format = 'json'

Checklist for Query Tuning

• [ ] Run EXPLAIN (ANALYZE, BUFFERS)
• [ ] Compare estimated vs actual rows at each node
• [ ] Check for Seq Scans on large tables
• [ ] Look for high loops in Nested Loops
• [ ] Verify buffer usage (hit vs read)
• [ ] Check pg_stat_statements for patterns
• [ ] Update statistics with ANALYZE
• [ ] Consider partial indexes for filtered queries
• [ ] Test with production-like data volumes
• [ ] Monitor after deployment

When NOT to Optimize

• Query runs < 10ms and is called < 100x/minute
• Optimization would add significant complexity
• The query is already using appropriate indexes
• The bottleneck is actually network/application layer

---

Next: Building Maintainable FastAPI Services — applying similar systematic thinking to API design.