Query Tuning / Planner Cost Constants

cpu_index_tuple_cost

• What it does: Sets the planner's estimated cost of processing each index entry during an index scan operation.
• Why it matters: This cost constant helps the query planner choose between index scans and other access methods by accounting for CPU overhead of index processing. Proper tuning ensures the planner makes optimal choices between index usage and sequential scans, especially for queries that return large result sets where index overhead might outweigh benefits.
• Ideal value & Best Practice: Default 0.005 is reasonable for most systems. Decrease to 0.0025 for faster CPUs or increase to 0.01 for slower CPUs. Monitor query plans for index scan versus sequential scan decisions and adjust if the planner consistently makes suboptimal choices.

cpu_operator_cost

• What it does: Sets the planner's estimated cost of processing each operator or function call within a query.
• Why it matters: This parameter affects the cost calculation of expressions, function calls, and complex WHERE clauses. Accurate tuning helps the planner properly estimate the overhead of computational operations, preventing underestimation of expensive function calls or overestimation of simple operations.
• Ideal value & Best Practice: Default 0.0025 works well for most workloads. Decrease for systems with very fast CPUs or increase for slower systems. Consider increasing if you use expensive user-defined functions that the planner might underestimate.

cpu_tuple_cost

• What it does: Sets the planner's estimated cost of processing each tuple (row) in query execution.
• Why it matters: This cost constant accounts for the CPU overhead of handling each row during query processing. It influences the planner's choice between plans that process many rows quickly versus plans that process fewer rows with more complex operations. Proper tuning ensures balanced consideration of CPU versus I/O costs.
• Ideal value & Best Practice: Default 0.01 is generally appropriate. Adjust based on your CPU performance relative to disk speed. Decrease for CPU-bound systems or increase for I/O-bound systems where CPU time is less significant compared to disk access.

effective_cache_size

• What it does: Sets the planner's assumption about the total size of disk caches available for PostgreSQL data (including both database shared buffers and operating system cache).
• Why it matters: This parameter doesn't allocate memory but informs the planner about available caching, influencing its choice between index and sequential scans. Underestimating may cause the planner to avoid index usage unnecessarily, while overestimating may lead to inappropriate index scan choices.
• Ideal value & Best Practice: Set to approximately 75% of total system RAM for dedicated database servers. For example, with 64GB RAM, set to 48000MB (64 _ 1024 _ 0.75 / 8). Adjust based on actual cache usage patterns observed in your workload.

jit_above_cost

• What it does: Sets the minimum total query cost threshold for considering JIT (Just-In-Time) compilation.
• Why it matters: Determines when the planner considers using JIT compilation based on query complexity. Queries with estimated costs below this threshold won't use JIT, avoiding overhead for simple queries. Higher values restrict JIT to more complex queries, while lower values enable it for simpler queries.
• Ideal value & Best Practice: Default 100000 is reasonable. Decrease to 50000 for systems with complex queries that benefit from JIT. Increase to 200000 or higher if JIT overhead is affecting simple query performance. Monitor JIT usage and performance impact.

jit_inline_above_cost

• What it does: Sets the cost threshold for inlining functions and expressions during JIT compilation.
• Why it matters: Function inlining can significantly improve performance by eliminating function call overhead but increases compilation time. This threshold balances the benefits of inlining against the additional compilation cost for complex queries.
• Ideal value & Best Practice: Default 500000 provides a good balance. Decrease to 300000 for systems where function call overhead is significant. Increase if compilation time becomes excessive for complex queries.

jit_optimize_above_cost

• What it does: Sets the cost threshold for applying advanced optimizations during JIT compilation.
• Why it matters: Advanced JIT optimizations can improve execution performance but require additional compilation time. This parameter controls when these optimizations are applied based on query complexity, ensuring they're only used for queries complex enough to benefit.
• Ideal value & Best Practice: Default 500000 is generally effective. Adjust based on your tolerance for compilation time versus execution performance. Lower values enable more aggressive optimization for simpler queries.

min_parallel_index_scan_size

• What it does: Sets the minimum amount of index data that must be scanned before considering a parallel index scan.
• Why it matters: Controls when the planner considers parallelizing index scans. Setting this too low may enable parallelism for small scans where overhead outweighs benefits. Setting too high may prevent parallel scans for operations that could benefit.
• Ideal value & Best Practice: Default 512kB is reasonable. Increase to 8MB or higher for systems with many CPUs where parallel overhead is justified for larger scans. Decrease if you have fast inter-process communication.

min_parallel_table_scan_size

• What it does: Sets the minimum amount of table data that must be scanned before considering a parallel table scan.
• Why it matters: Determines when parallel sequential scans are considered. This threshold prevents parallel overhead for small tables where single-process scanning is more efficient.
• Ideal value & Best Practice: Default 8MB works well for most systems. Increase to 64MB or higher for very large tables and many CPUs. Decrease for systems with very fast parallel processing capabilities.

parallel_setup_cost

• What it does: Sets the planner's estimated cost of starting up worker processes for parallel query execution.
• Why it matters: Accounts for the overhead of initializing parallel workers, including process creation and communication setup. This cost is weighed against the potential benefits of parallel execution when the planner chooses between parallel and serial plans.
• Ideal value & Best Practice: Default 1000.0 is reasonable. Decrease to 500.0 for systems with fast process creation or increase to 2000.0 for systems with slower parallel setup. Adjust based on actual parallel query performance.

parallel_tuple_cost

• What it does: Sets the planner's estimated cost of transferring each tuple from worker processes to the leader process.
• Why it matters: Accounts for the overhead of inter-process communication during parallel query execution. This cost influences whether the planner chooses parallel plans and how many workers it considers optimal.
• Ideal value & Best Practice: Default 0.1 is generally appropriate. Decrease to 0.01 for systems with very fast inter-process communication or shared memory. Increase to 0.5 for systems with slower communication mechanisms.

random_page_cost

• What it does: Sets the planner's estimated cost of a random (non-sequential) disk page access.
• Why it matters: This is one of the most important cost constants, as it significantly influences index versus sequential scan decisions. The ratio between random_page_cost and seq_page_cost determines how much the planner favors sequential versus random access patterns.
• Ideal value & Best Practice: For SSDs, set to 1.1 (down from default 4.0). For fast NVMe storage, consider 1.0. For RAID arrays, use 2.0-3.0. Always test with your specific workload and storage system.

seq_page_cost

• What it does: Sets the planner's estimated cost of a sequential disk page access.
• Why it matters: Represents the baseline cost for disk I/O operations. All other I/O costs are relative to this value. Proper setting ensures accurate cost estimation for various access patterns.
• Ideal value & Best Practice: Default 1.0 is appropriate as it establishes the baseline. Keep this constant and adjust other cost parameters relative to it. Only change if you have specific knowledge that your sequential I/O performance differs significantly from standard expectations.

---

Try pghealth Free Today 🚀

Start your journey toward a healthier PostgreSQL with pghealth.
You can explore all features immediately with a free trial — no installation required.

👉 Start Free Trial