High CPU from Planner Spending Too Long Planning

Scenario

A SaaS analytics platform has a dynamic reporting query that joins 14 tables. Engineers notice that EXPLAIN ANALYZE shows Planning Time: 3800 ms versus Execution Time: 180 ms. CPU spikes occur at query start, not during execution. The query is generated by an ORM and cannot easily be simplified. The number of joins causes the planner to spend enormous effort evaluating join orderings.

How to Identify

Conditions:

• EXPLAIN (ANALYZE) output shows Planning Time: significantly greater than Execution Time:
• Queries involve 10+ table joins (views that join many tables count too)
• CPU spikes observed at query initiation, not sustained during execution
• geqo_threshold is reached (default 12 tables) — GEQO genetic algorithm takes over
• log_min_duration_statement catches the query but execution itself is fast

Analysis Steps

-- 1. Run EXPLAIN ANALYZE and check Planning Time vs Execution Time at the bottom
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT ...;
-- Look for:
--   Planning Time: XXXX ms
--   Execution Time: YYY ms
-- If Planning Time >> Execution Time, planning cost is the problem

-- 2. Check current join planning limits
SHOW join_collapse_limit;    -- default 8: planner reorders up to 8 tables
SHOW from_collapse_limit;    -- default 8: subquery collapse limit
SHOW geqo_threshold;         -- default 12: above this, GEQO genetic planner kicks in
SHOW geqo_effort;            -- GEQO quality/cost tradeoff (1-10, default 5)

-- 3. Count how many tables the query actually joins (including via views)
-- Expand any views to count real table references:
SELECT viewname, definition FROM pg_views
WHERE viewname = 'your_view_name';

-- 4. Check pg_stat_statements for high planning time overhead
SELECT query, calls,
       total_plan_time::BIGINT    AS total_plan_ms,
       mean_plan_time::BIGINT     AS mean_plan_ms,
       total_exec_time::BIGINT    AS total_exec_ms,
       mean_exec_time::BIGINT     AS mean_exec_ms
FROM   pg_stat_statements
WHERE  mean_plan_time > 500
ORDER  BY total_plan_time DESC
LIMIT  10;

-- 5. Use prepared statements to amortize planning cost over multiple executions
PREPARE report_query (INT, DATE) AS
SELECT ... WHERE tenant_id = $1 AND report_date = $2;

Pitfalls

• Reducing join_collapse_limit below the number of joins can produce worse plans — the planner loses the ability to reorder joins optimally; test before applying globally
• GEQO (Genetic Query Optimizer) is faster than exhaustive search but not guaranteed to find the optimal join order — set geqo_effort appropriately
• Complex views that are used in queries create implicit join complexity — a view joining 6 tables used in a query that already joins 7 tables results in 13-table planning
• Prepared statements eliminate per-execution planning cost but use a generic plan after 5 executions (controlled by plan_cache_mode); generic plans may not be optimal for skewed data
• Setting join_collapse_limit = 1 forces explicit JOIN order from the query text — useful for controlling plans but requires query rewriting

Resolution Approach

Excessive planning time on multi-join queries has several remedies, applied in order of invasiveness: