This is an excerpt from the official PostgreSQL 18 documentation (Server Configuration, Section 19.7 Query Planning). It describes planner method configuration parameters (enable_* switches for scan, join, and aggregation plan types), planner cost constants (seq_page_cost, random_page_cost, cpu_* costs, parallel costs, effective_cache_size, JIT cost thresholds), the Genetic Query Optimizer (GEQO) settings for complex joins, and other planner options such as default_statistics_target and constraint_exclusion. The text is reference documentation rather than tutorial content.
main points
unique insights
practical applications
key topics
key insights
learning outcomes
• main points
1
Authoritative, precise reference for every planner-related configuration parameter in PostgreSQL
2
Explains the trade-offs behind each setting, such as how random_page_cost should change for SSD, network storage, or fully cached databases
3
Provides a concrete example of constraint exclusion with inheritance-based partitioning
• unique insights
1
Cost constants are relative; scaling them all by the same factor does not change planner decisions, and the documentation warns that tuning from a few experiments is risky
2
random_page_cost may be set lower relative to seq_page_cost when data is fully cached, and lowering CPU costs is also advisable in that case
• practical applications
Highly valuable as a reference for DBAs and developers diagnosing suboptimal query plans, tuning planner behavior for specific hardware, and understanding the consequences of enabling partitionwise joins or aggregation on memory usage. It is not a step-by-step learning guide, so its value is mainly as a lookup resource.
• key topics
1
Planner method enable/disable parameters for scans, joins, and aggregation
2
Planner cost constants and tuning guidance for storage and caching environments
3
Genetic Query Optimizer (GEQO) configuration for complex multi-table queries
• key insights
1
Authoritative source documentation directly from the PostgreSQL project
2
Explicit guidance on how storage type and cache size should influence cost tuning
3
Clear warnings about memory and planning-time costs of partitionwise operations
• learning outcomes
1
Understand how each planner enable_* parameter influences the choice of scan, join, and aggregation strategies
2
Learn how to adjust planner cost constants to reflect storage hardware and caching conditions
3
Recognize when GEQO is engaged and how its parameters trade planning time against plan quality
The PostgreSQL query optimizer decides how each SQL statement will be executed, choosing among scan methods, join strategies, and aggregation approaches. Usually its default choices are good, but occasionally a plan is suboptimal for a particular workload. PostgreSQL provides a set of server configuration parameters that let administrators influence these decisions, along with cost constants that describe the relative expense of operations. Understanding these tools helps database engineers diagnose slow queries and apply targeted corrections, while recognizing that the most durable improvements usually come from accurate statistics and sound schema design rather than from forcing particular plans.
“ Planner Method Configuration Parameters
PostgreSQL offers a family of enable_* parameters that switch individual plan types on or off. Examples include enable_indexscan, enable_indexonlyscan, enable_bitmapscan, enable_seqscan, enable_hashjoin, enable_mergejoin, enable_nestloop, enable_hashagg, enable_sort, and enable_memoize. All of them default to on. Disabling a parameter does not forbid the plan type entirely; it discourages the planner from choosing it when alternatives exist. Parallel-related options such as enable_parallel_append, enable_parallel_hash, and enable_gathermerge also belong to this group. Because these settings are blunt instruments, they are best used for diagnosis, for testing whether an alternative plan performs better, or as a temporary workaround while the root cause is addressed. Newer options such as enable_incremental_sort, enable_presorted_aggregate, and enable_self_join_elimination allow the planner to exploit specific optimizations, and enable_partitionwise_join and enable_partitionwise_aggregate control per-partition processing with a noticeable increase in planning and memory cost.
“ Understanding Planner Cost Constants
The planner estimates the cost of each candidate plan using numeric constants measured on an arbitrary scale. Only relative magnitudes matter, so scaling all constants by the same factor leaves plan choices unchanged. By convention, seq_page_cost is set to 1.0, and other constants are expressed relative to it. The most important constants include cpu_tuple_cost (default 0.01), which covers processing each row; cpu_index_tuple_cost (default 0.005), covering each index entry; and cpu_operator_cost (default 0.0025), covering each operator or function call. Because these values are averages across an entire workload, adjusting them based on a handful of experiments can easily produce worse behavior elsewhere, so changes should be validated broadly.
“ Tuning Random and Sequential Page Costs
The default random_page_cost of 4.0 reflects the assumption that many random reads hit the cache. Lowering it relative to seq_page_cost encourages index scans, which suits databases whose working set fits in memory. Raising it makes index scans look more expensive, which can be appropriate for slower magnetic storage. Setting random_page_cost lower than seq_page_cost is not physically meaningful, though equal values can make sense for fully cached databases. The effective_cache_size parameter, defaulting to 4GB, tells the planner how much disk cache a single query can expect to use; it affects estimates only and does not allocate memory. Both page cost settings can be overridden per tablespace, offering finer control where storage hardware differs.
“ Parallel Query and Cost Settings
Parallel execution introduces its own cost parameters. parallel_setup_cost (default 1000) estimates the expense of launching worker processes, and parallel_tuple_cost (default 0.1) estimates the cost of passing each tuple between processes. The thresholds min_parallel_table_scan_size (default 8MB) and min_parallel_index_scan_size (default 512kB) determine how much data a scan must touch before parallel plans are considered. Small tables and lightly used indexes therefore remain serial, avoiding coordination overhead that would outweigh any benefit.
“ Just-in-Time Compilation Cost Thresholds
When JIT compilation is enabled, three thresholds govern its use. jit_above_cost (default 100000) sets the query cost at which JIT compilation begins; jit_inline_above_cost (default 500000) enables inlining of functions and operators; and jit_optimize_above_cost (default 500000) enables expensive optimizations. JIT adds planning time but can accelerate long-running queries. Setting a threshold to -1 disables the corresponding feature. Because the expensive optimizations only pay off for very costly queries, the defaults keep JIT overhead away from short transactions.
“ The Genetic Query Optimizer (GEQO)
For queries joining many relations, the exhaustive search performed by the standard planner can take prohibitively long. The Genetic Query Optimizer uses heuristic search to find a good join order quickly, at the risk of sometimes missing the best plan. The geqo parameter enables or disables it, and geqo_threshold (default 12) sets the number of FROM items at which it takes over. The geqo_effort setting, an integer from 1 to 10 with a default of 5, balances planning time against plan quality and computes defaults for pool size, generations, and selection bias. These can also be set manually: geqo_pool_size, geqo_generations, geqo_selection_bias (1.50 to 2.00, default 2.00), and geqo_seed (between zero and one) allow fine control over the search. Chapter 61 of the documentation describes the algorithm in detail.
“ Statistics and Other Planner Options
Accurate row estimates are the foundation of good plans. The default_statistics_target parameter, defaulting to 100, sets the depth of statistics gathered by ANALYZE for columns without an explicit target. Increasing it improves estimates for skewed data at the cost of longer ANALYZE runs. Per-column targets can be set with ALTER TABLE SET STATISTICS, which is often more efficient than raising the global default. Regularly running ANALYZE keeps these statistics current, and this maintenance usually yields larger gains than manipulating cost constants.
“ Constraint Exclusion and Partition Pruning
The constraint_exclusion parameter controls whether the planner compares query conditions with table CHECK constraints to skip irrelevant tables. It accepts on, off, or partition, with partition as the default, which limits examination to inheritance children and UNION ALL subqueries. For example, when a parent table's children each cover a distinct key range, a query filtering on a specific key can avoid scanning every child entirely. This mechanism is essential for traditional inheritance-based partitioning and complements the declarative partitioning features described elsewhere in the documentation.
“ Best Practices for Safe Planner Tuning
Planner tuning should follow a disciplined approach. Begin by examining EXPLAIN ANALYZE output to identify where estimates diverge from actual row counts. Refresh statistics with ANALYZE and consider raising statistics targets for problematic columns. Only after these steps should cost constants be considered, and changes should be tested across representative workloads rather than a single query. Use enable_* parameters for experimentation rather than permanent configuration, and document any changes so they can be revisited as data volumes and hardware evolve. Done carefully, these adjustments let PostgreSQL deliver consistently efficient query execution.
We use cookies that are essential for our site to work. To improve our site, we would like to use additional cookies to help us understand how visitors use it, measure traffic to our site from social media platforms and to personalise your experience. Some of the cookies that we use are provided by third parties. To accept all cookies click ‘Accept’. To reject all optional cookies click ‘Reject’.
Comment(0)