Query Tuning / Other Planner Options

constraint_exclusion

• What it does: Enables the query planner to use table constraints to eliminate unnecessary table scans from query execution plans.
• Why it matters: This parameter significantly improves query performance for partitioned tables or tables with CHECK constraints by allowing the planner to skip entire table segments that cannot possibly contain relevant data based on their constraints. When working with partitioned tables, constraint exclusion can avoid scanning entire partitions that don't satisfy the query conditions, dramatically reducing I/O and execution time.
• Ideal value & Best Practice: Default partition is recommended for most environments as it only applies to partitioned tables. Set to on if you have extensive CHECK constraints that could benefit from exclusion. Use off only if you experience planning overhead or incorrect results, though this is rare.

cursor_tuple_fraction

• What it does: Sets the planner's estimate of what fraction of rows from a cursor will actually be retrieved by the application.
• Why it matters: This parameter helps the planner choose optimal plans for cursor-based queries by anticipating how many rows the application will actually fetch. When applications typically fetch only a subset of rows (common in web applications with pagination), this prevents over-optimization for full result retrieval and can lead to better initial response times.
• Ideal value & Best Practice: Default 0.1 (10%) is reasonable for many applications. Increase to 0.5-0.8 for applications that typically fetch most cursor rows. Decrease to 0.01-0.05 for applications that rarely fetch beyond the first few rows. Adjust based on your application's actual cursor usage patterns.

default_statistics_target

• What it does: Sets the default amount of statistics collected for table columns, affecting the planner's ability to estimate query selectivity accurately.
• Why it matters: Higher statistics targets provide more detailed distribution information, helping the planner choose better execution plans for queries with complex WHERE clauses or joins. However, increased targets also mean longer ANALYZE times and greater statistics storage requirements.
• Ideal value & Best Practice: Default 100 is sufficient for most workloads. Increase to 200-500 for data warehouses with complex analytical queries. For columns with unusual data distributions, use ALTER TABLE SET STATISTICS per-column instead of increasing the global default. Monitor ANALYZE performance after increasing this value.

from_collapse_limit

• What it does: Controls when the planner will merge subqueries into the main query based on the number of resulting FROM items.
• Why it matters: This parameter affects how the planner handles subqueries and view expansion. Lower values preserve subquery structure, which can be beneficial for optimization barriers or when subqueries contain LIMIT clauses. Higher values allow more aggressive merging, which can enable better join optimization across query boundaries.
• Ideal value & Best Practice: Default 8 provides a good balance. Increase to 12-16 for data warehouse workloads with complex queries to enable more cross-subquery optimization. Decrease to 4-6 if you experience planning regressions with complex subqueries.

jit

• What it does: Enables or disables Just-In-Time (JIT) compilation of queries for potentially faster execution.
• Why it matters: JIT compilation can significantly speed up execution of complex queries, especially those involving expressions, aggregates, and multiple WHERE conditions, by compiling parts of the query into native machine code. However, JIT adds planning overhead and consumes additional memory, making it less beneficial for simple queries or OLTP workloads.
• Ideal value & Best Practice: Default on is reasonable for most systems. For OLTP workloads with simple queries, consider setting to off to avoid JIT overhead. For data warehouses with complex analytical queries, ensure JIT is enabled and consider increasing related JIT parameters for optimal performance.

join_collapse_limit

• What it does: Controls when the planner will flatten explicit JOIN syntax into simple FROM lists based on the number of resulting items.
• Why it matters: This parameter affects how the planner handles explicit JOIN syntax. Lower values preserve the JOIN order specified in the query, which can be useful when the programmer has specific knowledge about optimal join orders. Higher values give the planner more flexibility to reorder joins for optimal performance.
• Ideal value & Best Practice: Default 8 is generally effective. Set to 1 if you want to force the planner to use the exact JOIN order specified in your queries. Increase to 12-20 for complex queries to give the planner more optimization flexibility. Use explicit JOIN syntax when you need to control join order precisely.

plan_cache_mode

• What it does: Controls whether the planner prefers custom or generic plans for prepared statements.
• Why it matters: Prepared statements can use either custom plans (optimized for specific parameter values) or generic plans (works for any parameters). This setting helps balance between planning time and execution performance, especially for prepared statements with parameter values that significantly affect optimal plan choice.
• Ideal value & Best Practice: Default auto lets the planner choose based on cost estimates. Use force_custom_plan if parameter values significantly affect optimal plans. Use force_generic_plan for OLTP workloads with stable plan shapes to avoid planning overhead. Monitor plan quality for prepared statements after changing this setting.

recursive_worktable_factor

• What it does: Sets the planner's estimate of the average size of a recursive query's working table relative to the initial non-recursive term.
• Why it matters: This parameter helps the planner estimate the cost of recursive queries (Common Table Expressions with RECURSIVE), which is otherwise difficult because the actual size of intermediate results isn't known during planning. Accurate estimates lead to better plan choices for recursive operations.
• Ideal value & Best Practice: Default 10.0 assumes the recursive part will produce about 10 times more rows than the non-recursive part. Increase for queries that typically generate large recursive results. Decrease for queries with limited recursion. Monitor actual versus estimated row counts for your recursive queries and adjust accordingly.

---

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