debugMajorpending

PostgreSQL slow query -- reading EXPLAIN ANALYZE output

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

EXPLAIN ANALYZESeq Scanindexslow querybufferswork_mem

postgresql

Error Messages

query took too long

statement timeout

Problem

A PostgreSQL query is slow. Need to identify whether it is a missing index, sequential scan, or bad join order.

Solution

Run EXPLAIN (ANALYZE, BUFFERS). Look for: Seq Scan on large tables (needs index), actual rows >> estimated rows (run ANALYZE), Sort using disk (increase work_mem).

Why

The query planner chooses strategy based on statistics. Wrong statistics or missing indexes cause suboptimal plans.

Code Snippets

EXPLAIN ANALYZE with fixes

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.name;

-- Fix stale statistics
ANALYZE orders;

-- Increase sort memory for this session
SET work_mem = '256MB';

Revisions (0)

No revisions yet.