Diagnosing Slow Postgres Queries: Planning Time vs Execution Time

When a Postgres query runs slow, EXPLAIN ANALYZE prints two numbers—Planning Time and Execution Time—that demand completely different fixes. Confusing them is expensive: throwing an index at a planning bottleneck just adds overhead, while treating an execution bottleneck as a planning issue leaves the real cost untouched.

On a table partitioned into 500 daily chunks holding 2.1 billion rows, a real trace showed planning taking nine times longer than execution (62.9ms vs 7.2ms) and touching 21x more buffer pages, because Postgres must lock and price every partition before pruning discards most of them. No amount of indexing fixes that; the answer is plan caching and fewer partitions.

A second trace on a single partition showed the opposite pattern: planning under a millisecond, execution over 1.6 seconds, with disk spills from an undersized work_mem and a filter that scanned 4.2 million rows to keep half of them—despite the planner's row estimates being accurate. A third case shows how correlated columns like region and rack mislead cardinality estimates, and how CREATE STATISTICS (dependencies) fixes it.

Reading the buffer counts in EXPLAIN (ANALYZE, BUFFERS) against each phase is the fastest way to know whether memory, storage layout, or partition pruning is the actual fix, before spending weeks optimizing the wrong phase.