PostgreSQL Query Optimization: EXPLAIN ANALYZE, Indexes, and Planner

PostgreSQL Query Optimization: EXPLAIN ANALYZE, Indexes, and Planner

Slow queries in PostgreSQL are a headache for any DevOps engineer. A query that used to take half a second with 100k rows starts hanging for 8 seconds at 2 million — and everyone looks at the developer. The query itself is correct, the schema is normal, the hardware hasn't changed. The issue is the PostgreSQL planner, which chose one plan for a small table and switched to another as data grew. Or that ANALYZE hasn't run in a month, and statistics are stale. Or that an index exists, but the planner doesn't use it due to a wrong statistics target. We've encountered these problems dozens of times and developed a systematic approach to optimization. Our engineers with 10+ years of experience guarantee a reduction in database response time of up to 50% and decreased server load. We'll evaluate your project: contact us for a consultation.

How to Read EXPLAIN ANALYZE Correctly?

EXPLAIN shows the plan without executing the query. EXPLAIN ANALYZE actually executes and shows actual vs. estimated rows. The difference between them is key to diagnosis.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.email, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > CURRENT_DATE - INTERVAL '1 year'
GROUP BY u.email;

The BUFFERS flag adds information about hits in shared_buffers and disk accesses — critical for understanding how much the query stresses I/O. Read the plan bottom-up: lower nodes execute first.

What to look for first:

Rows estimation mismatch. If the planner expected 10 rows (rows=10) but got 50000 (actual rows=50000), statistics are stale or statistics_target is too low. This forces the planner to choose Nested Loop where Hash Join is needed.

-- view statistics and increase granularity
SELECT n_distinct, correlation, most_common_vals
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Sequential Scan on a large table. Not always bad — if a query returns >5-10% of table rows, Seq Scan is cheaper than Index Scan. But if returning rows are few and Seq Scan is still chosen, the index likely does not exist or does not cover the condition.

Hash Join with large Batches. If Batches > 1 in a Hash Join, the hash table did not fit in work_mem and spilled to disk. Solution: SET work_mem = '64MB' for heavy analytical queries (not globally — that would consume all memory with concurrent sessions).

Why Does the Planner Choose a Wrong Plan?

PostgreSQL uses a cost-based optimizer. Operation costs are set by parameters in postgresql.conf. The most common case of an incorrect plan is outdated cost parameters for SSD:

# For SSD, lower random_page_cost (default=4.0) random_page_cost = 1.1 effective_cache_size = 12GB # ~75% of server RAM 

With random_page_cost = 4.0, the planner considers random disk access expensive and prefers Seq Scan even where Index Scan makes more sense. On SSD, this leads to wrong decisions. PostgreSQL Documentation: random_page_cost should be lowered for SSD.

If after gathering statistics and fixing parameters the query still picks a bad plan, you can force it via pg_hint_plan (extension) or session parameters:

-- temporarily disable Nested Loop for current session
SET enable_nestloop = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_nestloop = on;

This is a diagnostic tool, not a permanent solution. Permanently disabling a planner operator breaks other queries.

Indexes: Which, When, and Why

The standard B-tree index works for equality and ranges on regular types. For specific tasks, other types are needed:

-- partial index: index only active orders
CREATE INDEX idx_orders_active ON orders(created_at) WHERE status = 'active';

-- composite index: column order matters
-- works for WHERE user_id = ? AND status = ?
-- and for WHERE user_id = ? (first column)
-- does NOT work for WHERE status = ? (only second column)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- covering index: include needed columns, avoid heap fetch
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total_amount, created_at);

-- GIN for JSONB
CREATE INDEX idx_events_payload ON events USING GIN(payload jsonb_path_ops);

Covering indexes (with INCLUDE) are especially useful for frequently executed queries that read 2-3 specific fields. The planner performs an Index Only Scan — data is taken directly from the index, without accessing the heap.

Bloated indexes are a separate problem. After mass UPDATE/DELETE, indexes accumulate dead pages. pgstatindex shows bloat:

SELECT * FROM pgstatindex('idx_orders_user_status'); -- leaf_fragmentation > 30% → time for REINDEX CONCURRENTLY 

REINDEX CONCURRENTLY does not block reads and writes — unlike regular REINDEX.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending'; 

SELECT * FROM products WHERE category_id = 5 AND is_active = true ORDER BY created_at DESC LIMIT 20 OFFSET 10000;