Overview
This page is the atomic definition. EXPLAIN output interpretation lives at query-plan and explain.
Definition
The query planner (or query optimizer) is the component inside a relational database that transforms a SQL statement into an execution plan. Given a query, the planner enumerates candidate plans (different join orderings, index choices, scan methods), estimates the cost of each using table statistics stored in pg_statistic, and picks the cheapest. In PostgreSQL the planner is cost-based: it assigns numerical cost units to disk reads, CPU operations, and row processing. The planner uses column statistics gathered by ANALYZE (run automatically by autovacuum) to estimate row counts and selectivity. If statistics are stale or misleading, the planner chooses suboptimal plans, such as a sequential-scan over a large table when an index would be faster. EXPLAIN ANALYZE reveals the plan the planner chose alongside actual versus estimated row counts. Large row count discrepancies signal that ANALYZE needs to run or that extended statistics (CREATE STATISTICS) are needed for correlated columns. Planner configuration parameters (enable_seqscan, enable_hashjoin) can disable specific plan types for debugging but should not be used in production to paper over missing indexes.
When it applies
Understand the query planner when a query is slower than expected. Run EXPLAIN (ANALYZE, BUFFERS) to compare estimated and actual costs. Check for sequential scans on large tables, nested loop joins with high estimated rows, and large discrepancies between planned and actual row counts.
Example
A query on a 10 M-row table returns in 3 ms with an index scan. After a large data import, pg_statistic is stale; the planner estimates 100 rows and chooses a nested loop that now takes 4 seconds. Running ANALYZE refreshes statistics; the planner returns to the index scan.
Related concepts
- query-plan - the output the planner produces; readable via EXPLAIN.
- sequential-scan - one scan type the planner may choose over index scans.
- btree-index - the most common index type the planner uses for equality and range queries.
- materialized-view - pre-computed results the planner can use to avoid expensive joins.
- postgres - PostgreSQL’s planner documentation and configuration reference.
Citing this term
See Query Planner (llmbestpractices.com/glossary/query-planner).