EXPLAIN MEMORY and Planning Buffers #

A point lookup executes in 60 microseconds and takes 37 milliseconds end to end. Nothing in the plan tree explains the gap, because the time is not in the tree: it is in producing it. Planning a query over a heavily partitioned table reads thousands of catalog pages and allocates hundreds of megabytes, and two options make that visible.

The wider option set is covered in EXPLAIN options and output formats. This page is about measuring the planner itself.

The Condition #

Two pieces of output describe planning rather than execution:

Planning: Buffers: — the shared and local buffers the planner read, almost all of them catalog pages: relations, indexes, statistics, constraints. It appears when BUFFERS is requested.
Planning: Memory: used=… allocated=… — the memory the planner allocated while building the plan. Available from PostgreSQL 17 with the MEMORY option.

Together with Planning Time, they turn “planning is slow” into a measurement with a cause. Planning cost grows with:

the number of relations, including partitions, inheritance children and tables reached through views;
the number of indexes on each relation, since every index is a candidate path;
the number of join orders searched, which grows steeply with relations in a single join list — see join order and collapse limits;
partitionwise join and aggregate planning, which multiplies path building across partitions;
extended statistics objects, which are loaded and evaluated for the relevant clause sets.

A query planned once and executed for a long time need not care. A query planned thousands of times a second, or one planned repeatedly because its statements are not prepared, pays this cost every time.

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS, MEMORY, SUMMARY)
SELECT id, payload FROM events WHERE occurred_at = '2026-09-16 10:00:00+00' AND device_id = 4821;

Append  (actual time=0.03..0.04 rows=1 loops=1)
  ->  Index Scan using events_2026_09_16_device_idx on events_2026_09_16 events_1  (actual rows=1 loops=1)
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=18402
  Memory: used=148204kB  allocated=152064kB
Planning Time: 37.104 ms
Execution Time: 0.061 ms
-- 18,402 catalog buffers and 148 MB of planner memory for a four-buffer query
-- planning is 600× the execution cost

A complex join, where the join search dominates:

Planning:
  Buffers: shared hit=2104
  Memory: used=41204kB  allocated=42496kB
Planning Time: 612.408 ms
Execution Time: 2170.902 ms
-- 14 relations in one join list: the search itself is expensive
-- see whether geqo_threshold applies at this relation count

And a healthy ratio:

Planning:
  Buffers: shared hit=112
  Memory: used=1204kB  allocated=1280kB
Planning Time: 0.412 ms
Execution Time: 84.206 ms
-- planning is 0.5% of the statement: nothing to do

Step-by-Step Resolution #

Capture with SUMMARY and BUFFERS — and MEMORY on PostgreSQL 17+ — for the workload’s most frequent statements, not only its slowest.
Compute the ratio. Planning above about 10% of execution deserves attention for frequently executed statements; above 100% it is the problem.
Identify the driver from the buffers and memory: thousands of catalog buffers point at relation count (partitions, views expanding to many tables); high memory with moderate buffers points at the join search.
For partition-driven planning cost, reduce the partition count or reuse plans, following planning overhead with many partitions.
For join-search cost, check the relation count against geqo_threshold and the collapse limits; raising limits improves plans but increases planning time, and the trade-off is measured exactly here.
Prune unused indexes. Every index on a referenced relation is a candidate path considered during planning, so unused indexes cost planning time as well as write time.
Reuse plans with prepared statements where the workload allows, so the cost is paid once per session rather than per execution.

Before and After #

-- BEFORE: 1,460 partitions, planned on every execution
Planning: Buffers: shared hit=18402  Memory: used=148204kB   Planning Time: 37.104 ms

-- AFTER: 52 partitions, prepared statement reused
Planning: Buffers: shared hit=412    Memory: used=3204kB     Planning Time: 0.402 ms (first execution only)

Planning cost in aggregate #

A single statement’s planning time is rarely alarming; the workload’s total is. pg_stat_statements tracks planning separately when pg_stat_statements.track_planning is enabled, which makes it possible to rank statements by total planning time across the workload:

SELECT calls, round(total_plan_time) AS plan_ms, round(total_exec_time) AS exec_ms, left(query, 60)
FROM pg_stat_statements
WHERE total_plan_time > 0
ORDER BY total_plan_time DESC LIMIT 10;

That ranking often surprises. A statement planned in 3 ms and executed in 1 ms, called 200 times a second, spends more CPU planning than a nightly report spends executing. Enabling the tracking has a small overhead of its own, so it is usually turned on temporarily for an investigation rather than left on permanently.

The remedy list is short and ordered: fewer relations and indexes per query, plan reuse, and — only when neither is possible — accepting the cost and provisioning CPU for it.

Planner memory as a capacity signal #

The MEMORY figures are worth watching for a reason beyond speed. Planner memory is allocated per backend, per statement, and freed when the statement completes — but while a hundred backends each plan a query over a thousand partitions, a hundred allocations of a hundred megabytes exist simultaneously. That is enough to exhaust a machine whose memory budget was calculated from shared_buffers and work_mem alone, and the failure looks like an out-of-memory event with no obviously large query.

Two habits keep it visible. Capture MEMORY for the statements with the largest relation counts, not only the slowest, and multiply the figure by realistic concurrency when sizing the machine. And treat a planner allocation above a few tens of megabytes as a design signal: it almost always means a query is being planned over far more relations than it needs, which is a schema question rather than a memory one.

Common Pitfalls #

Optimising the plan tree when planning dominates. The tree is already fast. Diagnostic signal: execution time in microseconds, planning in milliseconds. Fix: reduce planning work.

Ignoring frequently executed statements. Planning cost scales with call rate. Diagnostic signal: high total planning time for a fast statement. Fix: rank by total, not per call.

Adding indexes without counting the cost. Each is a candidate path at planning time. Diagnostic signal: planning time rising after index additions. Fix: remove unused indexes.

Assuming prepared statements always help. They do not when plans are invalidated frequently or when custom plans are forced. Diagnostic signal: planning time unchanged despite preparation. Fix: check pg_prepared_statements counters.

Frequently Asked Questions #

How do I measure planning cost in PostgreSQL? #

Use EXPLAIN with SUMMARY for Planning Time, BUFFERS for the planner’s catalog reads, and — from PostgreSQL 17 — MEMORY for the memory the planner allocated. Compare planning time with execution time for the statement’s real call rate.

What makes planning slow? #

Many relations (including partitions and tables reached through views), many indexes per relation, large join searches, partitionwise planning and extended statistics. Each adds candidate paths or catalog lookups.

Does preparing statements remove planning cost? #

It amortises it: a cached generic plan is built once per session and reused, so frequently executed statements stop paying per execution. Plans are re-planned when invalidated, so the saving depends on how stable the schema and statistics are.

EXPLAIN Options and Output Formats — parent guide: all options
Planning Overhead with Many Partitions — the most common cause
GEQO for Queries Joining Many Tables — planning cost from the join search

Up: EXPLAIN Options and Output Formats