PostgreSQL Query Optimization Deep Dive: EXPLAIN ANALYZE, Index Strategies, and Real-World Tuning

数据库

The Query That Brought Down Production

2:47 AM. Pager duty. An API endpoint that normally responds in 12ms is timing out at 30 seconds. The database CPU is pegged at 100%. Investigation reveals a query that used to scan 500 rows is now scanning 47 million. An ORDER BY created_at DESC LIMIT 20 on a table with 800 million rows — and the index that was supposed to support it had been dropped during a migration three days earlier. Nobody noticed until the table crossed a tipping point.

PostgreSQL query optimization isn't about memorizing a checklist. It's about understanding how the query planner thinks, why it makes certain decisions, and how to give it the right information. This article covers the principles, the tools, and five real optimization cases from production systems.

Reading EXPLAIN ANALYZE: The Only Skill That Matters

Every PostgreSQL optimization starts with EXPLAIN ANALYZE. Not EXPLAIN — EXPLAIN ANALYZE. The difference: EXPLAIN estimates costs. EXPLAIN ANALYZE actually executes the query and reports real timing.

The Anatomy of an EXPLAIN Output

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

Limit  (cost=12543.28..12543.31 rows=10 width=44)
       (actual time=342.112..342.115 rows=10 loops=1)
  ->  Sort  (cost=12543.28..12554.78 rows=4600 width=44)
            (actual time=342.110..342.112 rows=10 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  HashAggregate  (cost=12320.50..12366.50 rows=4600 width=44)
                  (actual time=338.450..340.120 rows=4523 loops=1)
              Group Key: u.id
              Batches: 1  Memory Usage: 1169kB
              ->  Hash Right Join  (cost=4560.20..11980.30 rows=68040 width=40)
                        (actual time=125.340..310.280 rows=67200 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o  (cost=0.00..6540.00 rows=500000 width=8)
                              (actual time=0.015..85.400 rows=500000 loops=1)
                    ->  Hash  (cost=4520.20..4520.20 rows=3200 width=36)
                            (actual time=125.110..125.112 rows=3200 loops=1)
                          Buckets: 4096  Batches: 1  Memory Usage: 253kB
                          ->  Seq Scan on users u
                                    (cost=0.00..4520.20 rows=3200 width=36)
                                    (actual time=0.020..124.500 rows=3200 loops=1)
                                Filter: (created_at > '2025-01-01'::date)
                                Rows Removed by Filter: 96800
Planning Time: 1.234 ms
Execution Time: 342.356 ms

What Each Number Means

Field Meaning What to Look For
cost=x..y Startup cost..Total cost (arbitrary units) Big gap between estimated and actual rows
actual time=x..y ms to return first row..ms to return all rows High startup time = slow to begin emitting
rows=N (estimated) Planner's row estimate If far from actual, statistics are stale
rows=N (actual) Real rows processed Compare with estimated — mismatch >10x = problem
loops=N Times this node executed Nested Loop inner: high loops with high per-loop cost
Buffers: shared hit=N Pages read from buffer cache High = memory pressure?
Buffers: shared read=N Pages read from disk High = missing indexes or cold cache
Rows Removed by Filter Rows scanned but rejected High = missing or ineffective index

The Three Numbers That Matter Most

  1. Estimated vs Actual rows ratio: If the planner estimates 100 rows but gets 50,000, every downstream node makes wrong decisions. This is the root cause of most bad query plans.
  2. Rows Removed by Filter: Every row "removed by filter" was read from disk, processed, and discarded. If this number is in the millions and actual rows is in the hundreds, you need an index.
  3. Buffers: shared read: Disk reads. In a well-tuned system, most reads should be shared hit (from cache). High read on a repeated query means shared_buffers is too small or the working set exceeds memory.

Index Strategy: Knowing What Postgres Has

PostgreSQL has more index types than most developers realize. Choosing wrong means the planner ignores your index.

Index Type Decision Matrix

Index Type Best For Storage Overhead Write Penalty Limitations
B-tree Equality, range, sorting, LIKE 'abc%' Moderate (~2x indexed columns) Low Fails with leading wildcard LIKE '%abc'
Hash Equality only (=) Low Very low No range queries, no sorting, pre-v10 not WAL-logged
GIN Full-text search, arrays, JSONB containment High (inverted index) High Slow for exact-match, heavy write penalty
GiST Geometric data, full-text, range types Moderate Moderate Slower reads than B-tree for simple types
BRIN Very large tables with physical correlation Minimal Minimal Only works when data is physically ordered
SP-GiST Non-balanced tree structures (IP, points) Moderate Moderate Niche — don't use unless you know you need it

When Each Index Type Shines

B-tree — The 90% solution:

-- Standard B-tree: works for =, <, >, BETWEEN, ORDER BY, LIKE 'prefix%'
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Composite index: column order matters
-- Query: WHERE status = 'active' AND created_at > '2025-01-01'
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- status first (equality), then created_at (range) — optimal

-- Covering index (INCLUDE): index-only scans
CREATE INDEX idx_orders_user_status ON orders(user_id, status)
  INCLUDE (total_amount, created_at);
-- Query: SELECT user_id, status, total_amount FROM orders WHERE user_id = 123
-- Can answer entirely from index — no heap lookup

GIN — JSONB and full-text search:

-- JSONB indexing: @> (contains), ? (key exists), ?| (any key)
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);

-- Full-text search
CREATE INDEX idx_posts_search ON posts
  USING GIN (to_tsvector('english', title || ' ' || body));

-- Query that uses it
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('postgresql & optimization');

BRIN — The billion-row table savior:

-- BRIN: Block Range INdex — stores min/max per block range
-- Tiny index (often <1% of table size) for huge, append-mostly tables

CREATE INDEX idx_events_timestamp_brin ON events
  USING BRIN (created_at)
  WITH (pages_per_range = 32);

-- Works because data is inserted in timestamp order
-- 1TB table → 50MB BRIN index → sub-second range queries
SELECT * FROM events
WHERE created_at BETWEEN '2025-06-01' AND '2025-06-02';

Partial indexes — Index only what you query:

-- Only index unprocessed orders (5% of table)
CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

-- 95% smaller index, faster writes, faster scans
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 50;

Case Study 1: The N+1 Generator

The Symptom

An ORM-generated query was taking 8.2 seconds. The developer couldn't understand why — "it's just fetching 100 articles with their authors."

The Diagnosis

-- What the ORM actually generated (simplified)
-- 1 query for articles, then 100 individual queries for authors
SELECT * FROM articles ORDER BY published_at DESC LIMIT 100;
-- For each article:
SELECT * FROM users WHERE id = $1;  -- executed 100 times

The EXPLAIN ANALYZE showed 101 total queries. The 100 individual author queries, while fast individually (2ms each), added up to 200ms of network round-trips plus planning overhead.

The Fix

-- Replace 101 queries with 2
SELECT * FROM articles ORDER BY published_at DESC LIMIT 100;

-- Batch the author lookup
SELECT * FROM users WHERE id = ANY($1);
-- $1 = [author_id_1, author_id_2, ..., author_id_100]

After: 42ms total. The ORM's eager loading or preload feature was the answer — the developer just hadn't been using it. Always check what SQL your ORM generates. TypeORM logging, Prisma logging, ActiveRecord logging — turn it on in development.

Case Study 2: The OFFSET Trap

The Symptom

Pagination was fast on page 1 (20ms), slower on page 10 (200ms), and timing out on page 500 (30s+).

The Diagnosis

-- The problematic query
SELECT * FROM events
WHERE user_id = 456
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

EXPLAIN ANALYZE revealed: PostgreSQL was scanning 10,020 rows and discarding the first 10,000. Every page deeper meant scanning and discarding more rows. The index helped with ordering but couldn't skip the offset.

The Fix: Keyset Pagination

-- Page 1
SELECT * FROM events
WHERE user_id = 456
ORDER BY created_at DESC, id DESC
LIMIT 21; -- Fetch 21 to know if there's a next page

-- Last row: created_at = '2025-03-15 14:23:01', id = 88234

-- Page 2 (uses the cursor from the last row of page 1)
SELECT * FROM events
WHERE user_id = 456
  AND (created_at, id) < ('2025-03-15 14:23:01', 88234)
ORDER BY created_at DESC, id DESC
LIMIT 21;

-- Composite index to support the cursor
CREATE INDEX idx_events_user_cursor ON events(user_id, created_at DESC, id DESC);

After: Consistent 3-5ms per page, regardless of page depth. The index does the heavy lifting — it seeks directly to the cursor position and reads only the needed rows.

Case Study 3: The Function-On-Column Blocker

The Symptom

A dating app's "find nearby users" query was scanning the entire 50-million-row table despite having an index on location.

The Diagnosis

-- The query
SELECT * FROM profiles
WHERE ST_DWithin(
  location,
  ST_MakePoint($1, $2)::geography,
  10000  -- 10km
);

There was a B-tree index on location, but ST_DWithin() is a GiST operation. B-tree indexes can't accelerate spatial queries. The planner had no choice but to sequential scan.

The Fix

-- Replace B-tree with GiST spatial index
CREATE INDEX idx_profiles_location_gist ON profiles
  USING GIST (location);

-- Query now uses index
EXPLAIN ANALYZE SELECT * FROM profiles
WHERE ST_DWithin(location, ST_MakePoint(139.65, 35.67)::geography, 10000);

-- Index Scan using idx_profiles_location_gist
-- Rows: 1,247 (was 50,000,000 sequential scan)

After: 12ms (was 4.2 seconds). When using PostgreSQL extensions (PostGIS, pg_trgm, ltree), make sure the index type matches the operator.

Case Study 4: Time-Range Queries on a Billion-Row Table

The Symptom

An analytics dashboard's "recent 7 days" query on an 800-million-row events table was taking 45 seconds.

The Diagnosis

-- Current query
SELECT event_type, COUNT(*), AVG(duration_ms)
FROM analytics_events
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY event_type;

-- EXPLAIN showed: Parallel Seq Scan on analytics_events
-- Rows Removed by Filter: 790,000,000

A regular B-tree index existed on created_at, but the table was 450GB. The index itself was 38GB — too large to fit in memory. Every query had to read index pages from disk. The planner determined a sequential scan was cheaper than random index reads.

The Fix: Partitioning + BRIN

-- Step 1: Partition by month
CREATE TABLE analytics_events (
  id BIGSERIAL,
  event_type TEXT NOT NULL,
  duration_ms INTEGER,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE analytics_events_2025_07
  PARTITION OF analytics_events
  FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');

-- Step 2: BRIN index on each partition (tiny, fast)
CREATE INDEX idx_events_brin_created
  ON analytics_events USING BRIN (created_at)
  WITH (pages_per_range = 32);

-- Step 3: Enable partition pruning
SET enable_partition_pruning = on; -- default in PG 12+

After: 800ms (was 45 seconds). Partition pruning eliminated 23 of 24 monthly partitions immediately. The BRIN index on the relevant partition was 2MB instead of 38GB — fully cached in memory.

Partitioning Decision Guide

Table Size Partition By Why
<10M rows Don't partition Overhead > benefit
10M-100M May not need it Index tuning usually sufficient
100M-1B Month or week Partition pruning + manageable index size
>1B Day (if high ingest) Keep partitions <50M rows each

Case Study 5: The Statistics Blind Spot

The Symptom

A query that ran perfectly in staging (20ms) took 8 seconds in production. Same schema, same indexes, same query.

The Diagnosis

SELECT * FROM shipments
WHERE status = 'delivered'
  AND warehouse_id = 42
ORDER BY created_at DESC
LIMIT 50;

-- Staging: 10K rows, 9K are 'delivered'
-- Production: 50M rows, 48M are 'delivered'

-- The planner in staging: "status = 'delivered' eliminates most rows,
--   use the index on status"
-- Index Scan using idx_shipments_status → 20ms ✓

-- The planner in production: "status = 'delivered' matches 96% of rows,
--   index scan would be slower than sequential scan"
-- Seq Scan on shipments → 8 seconds ✗

PostgreSQL stores column statistics (most common values, histograms) in pg_stats. When distribution changes, statistics go stale. The planner in production didn't know warehouse_id = 42 narrowed things to 200 rows.

The Fix

-- Run ANALYZE to update statistics
ANALYZE shipments;

-- Or create extended statistics for correlated columns
CREATE STATISTICS shipments_status_warehouse (dependencies)
  ON status, warehouse_id FROM shipments;

ANALYZE shipments;

-- Even better: a composite index matching both conditions
CREATE INDEX idx_shipments_warehouse_status_created
  ON shipments(warehouse_id, status, created_at DESC);

After: 2ms (was 8 seconds). The composite index, combined with fresh statistics, let the planner choose an index-only scan.

When to ANALYZE

  • After bulk inserts (>5% of table size)
  • After bulk deletes
  • After creating new indexes
  • On a schedule: autovacuum_analyze_scale_factor = 0.05 means analyze triggers after 5% of rows change. For large tables (>10M rows), lower this to 0.01.

PostgreSQL Version Feature Improvements

Feature Version Impact
Parallel query execution PG 9.6+ Multiple workers for seq scans, hash joins
Parallel index scans (B-tree) PG 11+ Workers share index scan progress
Incremental sort PG 13+ Sort partially sorted data faster
Extended statistics PG 10+ (improved in 13+) Correlated column awareness
LZ4 compression for TOAST PG 14+ Faster decompression than PGLZ
Parallel hash joins PG 14+ Multi-worker hash table build
MERGE command PG 15+ Single-statement upsert
SQL/JSON constructor PG 15+ Native JSON construction in SQL
Parallel full & right hash joins PG 16+ More types of parallel joins
pg_stat_io PG 16+ I/O timing statistics
Maintenance_work_mem for VACUUM PG 17+ Separate memory for auto/manual vacuum

Connection Pooling: The Missing Performance Layer

A common pattern I see in production: max_connections = 500. This is almost always wrong.

PostgreSQL uses a process-per-connection model. Each connection consumes 5-10MB of memory and context-switches with every query. At 500 connections, that's 2.5-5GB just for connection overhead, plus crushing context-switching on the CPU.

PgBouncer Configuration

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# CRITICAL: Use transaction pooling
pool_mode = transaction

# Keep server connections low
default_pool_size = 25
max_client_conn = 500

# Reserve connections for critical operations
reserve_pool_size = 5
reserve_pool_timeout = 3
pool_mode Behavior Best For
session One server connection per client session Legacy apps that use session state
transaction Connection released after each transaction REST APIs, stateless services
statement Connection released after each statement Extreme scaling, no transactions

Rule of thumb: default_pool_size = (CPU cores * 2) for transaction pooling. For a 16-core server, pool_size of 25-32 is plenty. PostgreSQL handles 25 concurrent transactions more efficiently than 250.

The Optimization Debugging Toolkit

-- What's running right now?
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid != pg_backend_pid()
ORDER BY duration DESC;

-- Which queries are slow? (requires pg_stat_statements)
SELECT queryid, calls,
       mean_exec_time::numeric(10,1) AS avg_ms,
       total_exec_time::numeric(10,1) AS total_ms,
       rows,
       left(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Index usage statistics
SELECT schemaname, tablename, indexname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Unused indexes!
ORDER BY pg_relation_size(indexrelid) DESC;

-- Table bloat check
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Cache hit ratio
SELECT sum(heap_blks_read) AS heap_read,
       sum(heap_blks_hit) AS heap_hit,
       round(100.0 * sum(heap_blks_hit) /
         NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 1) AS cache_hit_ratio
FROM pg_statio_user_tables;
-- Aim for >99% cache hit ratio

Production Configuration Quick Reference

# postgresql.conf — changes from defaults for a 16GB/8-core server

# Memory
shared_buffers = 4GB              # 25% of RAM
effective_cache_size = 12GB       # 75% of RAM
work_mem = 64MB                   # Per-operation sort memory
maintenance_work_mem = 512MB      # VACUUM, CREATE INDEX
wal_buffers = 64MB                # Write-ahead log buffer

# Planner
random_page_cost = 1.1            # Default 4.0 assumes HDD. SSD = 1.0-1.5
effective_io_concurrency = 200    # SSD concurrent I/O depth
default_statistics_target = 500   # More samples for better plans

# Parallelism
max_parallel_workers_per_gather = 4  # Workers per parallel node
max_parallel_workers = 8             # Total parallel workers
max_worker_processes = 12            # Includes parallel + logical replication

# WAL & Checkpoints
max_wal_size = 8GB
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9   # Spread checkpoint writes

Summary: PostgreSQL query optimization is a cycle: measure with EXPLAIN ANALYZE, identify the bottleneck (disk I/O, row estimates, missing index, stale statistics), apply the minimal fix, measure again. The most impactful optimizations in order: add the right index → fix N+1 queries → update statistics → partition large tables → configure connection pooling → tune server parameters. For every optimization, prove it with before-and-after EXPLAIN ANALYZE (BUFFERS) output. Without measurement, you're just changing things.

Online Tools

  • JSON Formatter — Examine JSON query results during performance debugging
  • Hash Calculator — Generate hash indexes for large text columns when B-tree won't fit

Try these browser-local tools — no sign-up required →

#PostgreSQL#查询优化#EXPLAIN#索引#性能#数据库#2026