Read PostgreSQL Plans to Find Slow Queries

Context

Slow queries in PostgreSQL often come from bad join order, missing indexes, or row estimates that are far off. Reading the execution plan helps you pinpoint where time is spent instead of guessing.

Core question

You are asked to explain how you would read a PostgreSQL execution plan to determine why a query is slow. Walk through what you look for first, how you interpret row estimates versus actual rows, and how you identify expensive nodes such as sequential scans, sorts, hash joins, and nested loops.

Scope guidance

Focus on practical plan-reading skills: what the key plan fields mean, how to spot the bottleneck, and how you would use EXPLAIN (ANALYZE, BUFFERS) to validate the diagnosis. Do not just define the terms — explain how they guide tuning decisions.