Overview of PostgreSQL Planner Configuration Parameters for Better Query Performance

PostgreSQL is known for its powerful and intelligent query planner. However, what many database administrators and developers don’t realize is that PostgreSQL allows fine-grained control over the planner’s behavior using a set of internal parameters. These settings can be especially useful when debugging slow queries or understanding why the planner chose one execution path over another.

In this blog, we’ll explore the purpose of these enable_* planner method parameters found in the postgresql.conf file. We’ll explain what each one does, how it impacts query execution, and provide examples so even beginners can start using them effectively.

What is the PostgreSQL Query Planner?

When you run a SQL query in PostgreSQL, it doesn’t just start scanning tables or using indexes blindly. Instead, it uses a query planner that evaluates multiple possible strategies and chooses the one it estimates to be the most efficient.

These strategies include:

• Sequential scans
• Index scans
• Hash joins
• Merge joins
• Parallelism
• Bitmap scans, and more.

The enable_* settings in postgresql.conf or via SQL session commands allow you to enable or disable specific planner strategies, giving you control to test and compare execution plans.

Where Are These Settings Located?

You can find them in your postgresql.conf file under the section:

#------------------------------------------------------------------------------

# QUERY TUNING

#------------------------------------------------------------------------------

# - Planner Method Configuration -

You can also set them during a session using SQL:

SET enable_seqscan TO off;

Let’s now go through each setting, its purpose, and an example.

Planner Method Settings Explained

1. enable_seqscan

• Description: Enables use of sequential table scans.
• Default: on
• When to disable: To force PostgreSQL to use an index (for testing purposes).

Example:

SET enable_seqscan TO off;
EXPLAIN SELECT * FROM employees WHERE id = 100;

2. enable_indexscan

• Description: Allows index-based scans on base tables.
• Disabling this forces the planner to avoid index scans even if indexes exist.

Example:

SET enable_indexscan TO off;

3. enable_indexonlyscan

• Description: Enables index-only scans (only possible when all required columns are in the index).
• Tip: Index-only scans are faster since they avoid accessing the main table.

SET enable_indexonlyscan TO off;

4. enable_bitmapscan

• Description: Bitmap scans are used when multiple index matches are expected, combining results efficiently.

SET enable_bitmapscan TO off;

5. enable_tidscan

• Description: Enables scans based on tuple IDs (ctid). Useful for very specific row lookups.

Example:

SELECT * FROM employees WHERE ctid = '(10,1)';

6. enable_hashjoin

• Description: Allows use of hash join algorithms (good for large joins with no ordering).

SET enable_hashjoin TO off;

7. enable_mergejoin

• Description: Enables merge join operations (efficient for pre-sorted data).
• Tip: Often preferred for queries involving large sorted datasets.

SET enable_mergejoin TO off;

8. enable_nestloop

• Description: Allows nested loop joins (great for small tables or indexes).
• Tip: Useful in OLTP scenarios where lookups are very selective.

SET enable_nestloop TO off;

Tip: If this is disabled, you can simulate how a large join would perform with other join types.

9. enable_material

• Description: Controls use of materialization (temporary storage of subquery results).
• Why it matters: Materialization can reduce recomputation of expensive subqueries.

10. enable_sort

• Description: Allows planner to use sort nodes.
• Use case: Disabling can help test index-only scans or avoid temp files from sorting.

Useful when testing sort performance or encouraging use of indexes for sorted results.

11. enable_gathermerge

• Description: Enables use of Gather Merge operations in parallel query plans.
• When used: For merging sorted results from multiple workers.

12. enable_parallel_append

• Description: Allows partitioned data or UNION ALL queries to be executed in parallel.
• Why it matters: Improves performance on partitioned or sharded tables.

13. enable_parallel_hash

• Description: Enables use of parallel hash joins.
• When helpful: On large joins where full parallelism can reduce total latency.

14. enable_async_append

• Description: Enables asynchronous append plans, allowing more efficient query execution over multiple partitions or child tables.
• Benefit: Reduces wait times by fetching data from multiple sources concurrently.

15. enable_partition_pruning

• Description: Enables run-time pruning of partitions in queries (helps with performance).
• Use case: Essential for performance in large partitioned tables with WHERE clause filtering.

16. enable_partitionwise_join, enable_partitionwise_aggregate

• Description: Controls if joins or aggregates are executed separately for each partition. Good for large partitioned tables.

SET enable_partitionwise_join TO on;
SET enable_partitionwise_aggregate TO on;

Viewing Effects with EXPLAIN

Here’s a real example showing how disabling sequential scan affects the plan:

-- Step 1: Allow only index scan
SET enable_seqscan TO off;
-- Step 2: See the plan
EXPLAIN SELECT * FROM employees WHERE id = 123;

If there's an index on id, PostgreSQL will now use it—even if the planner originally thought a sequential scan was cheaper.

Conclusion

The enable_* planner method parameters in postgresql.conf are among PostgreSQL’s most insightful yet underutilized features. They don’t just control whether a specific scan or join method is allowed—they provide a window into the decision-making process of the PostgreSQL planner itself.

By experimenting with these parameters—such as enable_seqscan, enable_indexscan, enable_nestloop, and the various parallel or partitioning options—you can observe how different plans affect query performance, resource usage, and execution time. This allows database administrators and developers to validate indexing strategies, compare alternative execution paths, and pinpoint performance regressions.

Moreover, parameters like enable_parallel_append, enable_incremental_sort, and enable_memoize highlight PostgreSQL’s evolution into a highly parallel and memory-efficient engine. The ability to toggle these features makes it possible to test cutting-edge planner optimizations in a controlled manner.