Query Tuning / Planner Method Configuration

enable_async_append

• What it does: Enables or disables the planner's use of async append plans, which allow concurrent execution of multiple subplans.
• Why it matters: Async append can significantly improve performance for queries involving multiple partitions or UNION operations by executing subplans concurrently rather than sequentially. This is particularly beneficial for distributed queries or partitioned tables where different subplans may have varying execution times.
• Ideal value & Best Practice: Default on is recommended. Disable only if you experience issues with async execution or for debugging purposes. Especially beneficial for partitioned tables and queries with multiple UNION branches.

enable_bitmapscan

• What it does: Controls whether the planner can use bitmap index scans, which combine multiple index conditions.
• Why it matters: Bitmap scans are crucial for queries with multiple WHERE conditions that can use different indexes. They allow combining multiple index results efficiently before accessing the actual table data, often providing the optimal balance between index usage and table access.
• Ideal value & Best Practice: Default on is strongly recommended. Disabling bitmap scans may force less efficient plans for multi-condition queries. Only disable for specific debugging scenarios.

enable_gathermerge

• What it does: Enables or disables gather merge plans, which are used for preserving ordering in parallel queries.
• Why it matters: Gather merge is essential for parallel queries that require sorted results, as it efficiently merges sorted results from multiple workers while maintaining the correct order. This provides better performance than gathering unsorted results and then sorting.
• Ideal value & Best Practice: Default on is recommended. Disable only if you encounter issues with parallel sorted queries. Particularly important for queries with ORDER BY and LIMIT clauses.

enable_group_by_reordering

• What it does: Allows the planner to reorder GROUP BY keys for more efficient aggregation.
• Why it matters: GROUP BY reordering can enable more efficient aggregation plans by leveraging index orderings or reducing sorting overhead. This can significantly improve performance for complex aggregation queries, especially when combined with appropriate indexes.
• Ideal value & Best Practice: Default on is recommended. Disable only if you experience incorrect results or specific compatibility issues. Generally provides better performance without downsides.

enable_hashagg

• What it does: Controls whether the planner can use hash aggregation for GROUP BY and DISTINCT operations.
• Why it matters: Hash aggregation can be much faster than sort-based aggregation for large datasets, as it avoids the overhead of sorting. However, it requires more memory and may not preserve ordering. The planner chooses between hash and sort aggregation based on cost estimates.
• Ideal value & Best Practice: Default on is recommended. Disable only for memory-constrained environments or when order preservation is critical. Monitor memory usage when using hash aggregation.

enable_hashjoin

• What it does: Enables or disables hash join operations, where one table is hashed and the other is probed against the hash table.
• Why it matters: Hash joins are typically the most efficient join method for large tables without pre-existing indexes. They're especially effective when one table can fit in memory. Disabling hash joins may force less efficient nested loop or merge joins.
• Ideal value & Best Practice: Default on is strongly recommended. Only disable for specific debugging or when memory constraints prevent effective hash join usage.

enable_incremental_sort

• What it does: Controls whether the planner can use incremental sort, which preserves and leverages existing ordering.
• Why it matters: Incremental sort can significantly reduce sorting overhead when the input data is partially ordered (e.g., from an index scan). This is particularly valuable for queries with multiple ORDER BY columns or window functions.
• Ideal value & Best Practice: Default on is recommended. Disable only if you experience planner issues. Especially beneficial when combined with appropriate indexes.

enable_indexonlyscan

• What it does: Enables or disables index-only scans, where the query can be satisfied entirely from index data.
• Why it matters: Index-only scans can provide dramatic performance improvements by avoiding table access entirely. This requires that all requested columns are included in the index and that visibility information is available.
• Ideal value & Best Practice: Default on is strongly recommended. Never disable unless troubleshooting specific issues. Encourages proper index design with covering indexes.

enable_indexscan

• What it does: Controls whether the planner can use index scans for data retrieval.
• Why it matters: Index scans are fundamental for efficient data access in most workloads. Disabling index scans would force sequential scans for all queries, which is only appropriate for very specific debugging scenarios.
• Ideal value & Best Practice: Default on is essential for production use. Only disable for testing or debugging purposes. Keeping this enabled is crucial for query performance.

enable_material

• What it does: Controls whether the planner can use materialization (storing intermediate results).
• Why it matters: Materialization can improve performance for complex queries with common subexpressions or when intermediate results are reused multiple times. However, it consumes additional memory and may not always be beneficial.
• Ideal value & Best Practice: Default on is recommended. The planner generally makes good decisions about when materialization is helpful. Disable only if you experience memory issues with specific queries.

enable_memoize

• What it does: Enables or disables memoization of parameterized nested loop joins.
• Why it matters: Memoization can significantly improve performance for queries with parameterized nested loops by caching inner side results for repeated parameter values. This is particularly beneficial for queries with correlated subqueries or certain join patterns.
• Ideal value & Best Practice: Default on is recommended. Disable only if you experience memory pressure or specific performance issues. Generally provides performance benefits.

enable_mergejoin

• What it does: Controls whether the planner can use merge joins, which require sorted input from both sides.
• Why it matters: Merge joins are efficient for large datasets when both inputs are already sorted or can be sorted cheaply. They're particularly useful for equality joins and when preserving order is important.
• Ideal value & Best Practice: Default on is recommended. Merge joins can be the optimal choice for large, sorted datasets. Disable only for specific debugging scenarios.

enable_nestloop

• What it does: Enables or disables nested loop joins, which iterate through one table for each row in another.
• Why it matters: Nested loop joins are efficient for small tables or when good indexes exist on the inner table. While often inefficient for large tables, they're essential for certain query patterns and correlated subqueries.
• Ideal value & Best Practice: Default on is recommended. The planner generally chooses nested loops appropriately for small datasets. Disabling may break certain query patterns.

enable_parallel_append

• What it does: Controls parallel append operations for partitioned tables or UNION queries.
• Why it matters: Parallel append enables concurrent execution across multiple partitions or UNION branches, significantly improving performance for partitioned tables and complex UNION queries.
• Ideal value & Best Practice: Default on is recommended. Especially beneficial for partitioned tables and large UNION queries. Disable only if you experience resource contention issues.

enable_parallel_hash

• What it does: Enables or disables parallel hash join operations.
• Why it matters: Parallel hash joins can significantly accelerate large join operations by distributing the work across multiple processes. This is particularly valuable for data warehouse workloads and large analytical queries.
• Ideal value & Best Practice: Default on is recommended. Ensure adequate memory is available for parallel hash operations. Disable only in memory-constrained environments.

enable_partition_pruning

• What it does: Enables elimination of partitions that cannot contain relevant data based on query constraints.
• Why it matters: Partition pruning is essential for partitioned table performance, as it avoids scanning irrelevant partitions. This can improve performance by orders of magnitude for partitioned tables with good constraint matching.
• Ideal value & Best Practice: Default on is crucial and should never be disabled in production. Disabling would eliminate most benefits of partitioning.

enable_partitionwise_aggregate

• What it does: Enables partition-wise aggregation, performing aggregates on individual partitions.
• Why it matters: Partition-wise aggregation can dramatically improve performance for aggregated queries on partitioned tables by pushing aggregation down to individual partitions and combining results.
• Ideal value & Best Practice: Default on is recommended. Especially beneficial for large partitioned tables with aggregation queries. Disable only for specific compatibility issues.

enable_partitionwise_join

• What it does: Enables partition-wise joins, joining matching partitions individually.
• Why it matters: Partition-wise joins can significantly improve join performance for partitioned tables by joining corresponding partitions separately, reducing the data volume for each join operation.
• Ideal value & Best Practice: Default on is recommended. Particularly valuable for large partitioned tables with partition-aligned join conditions.

enable_presorted_aggregate

• What it does: Enables use of presorted input for ORDER BY/DISTINCT aggregate functions.
• Why it matters: This optimization can eliminate redundant sorting when input data is already appropriately ordered, improving performance for ordered aggregations and DISTINCT operations.
• Ideal value & Best Practice: Default on is recommended. Disable only if you experience issues with specific aggregation patterns. Generally provides performance benefits.

enable_seqscan

• What it does: Enables or disables sequential table scans.
• Why it matters: While sequential scans are often less efficient than index scans, they're sometimes the optimal choice for full-table operations or when indexes aren't beneficial. Disabling sequential scans entirely is rarely appropriate.
• Ideal value & Best Practice: Default on is recommended. The planner generally chooses sequential scans appropriately. Disable only for specific testing scenarios, not in production.

enable_sort

• What it does: Controls whether the planner can use explicit sort operations.
• Why it matters: Sorting is essential for ORDER BY, DISTINCT, merge joins, and group aggregation. Disabling sorting would prevent many essential query operations from working correctly.
• Ideal value & Best Practice: Default on is essential. Only disable for specific debugging scenarios. Most queries require sorting capability.

enable_tidscan

• What it does: Enables or disables TID (tuple identifier) scans, which access rows directly by physical location.
• Why it matters: TID scans are used for certain internal operations and can be efficient for direct row access when the physical location is known. However, they're rarely used in typical application queries.
• Ideal value & Best Practice: Default on is recommended. Disabling may break certain internal operations and should generally be avoided.

---

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