PostgreSQL Query Planning Settings Query Guide

PostgreSQL Query Planning Settings Query Guide

This PostgreSQL query retrieves all query tuning and statistics collection settings from the pg_settings system view. These parameters control how the query planner selects execution strategies, estimates costs, and collects runtime statistics.

Purpose and Overview

The PostgreSQL query planner decides how to execute every SQL statement — which indexes to use, whether to hash-join or merge-join, whether to use parallel workers, and more. The settings exposed by this query directly influence those decisions.

Database administrators tune these parameters to improve query performance, fix plans that have gone wrong, or verify that statistics collection is enabled for monitoring views like pg_stat_activity and pg_stat_statements.

SQL Script

 1SELECT
 2    name,
 3    setting,
 4    -- category,
 5    vartype,
 6    short_desc,
 7    -- enumvals,
 8    source,
 9    min_val,
10    max_val,
11    boot_val,
12    reset_val,
13    -- not available on PostgreSQL < 9.5
14    pending_restart
15FROM
16    pg_settings
17WHERE
18    category ILIKE '%Query%';

Code Breakdown

Filter Condition

1WHERE category ILIKE '%Query%'

The broad %Query% pattern intentionally captures two category groups:

• Query Tuning — planner method configuration, cost constants, genetic query optimizer, and other planner options
• Statistics / Query and Index Statistics Collector — settings like track_activities and track_counts whose category name contains "Query"

A narrower filter of %Query Tuning% would miss the statistics collector settings, so the wider pattern is used deliberately.

Selected Columns

name: The configuration parameter name such as enable_indexscan or work_mem.

setting: The current active value of the parameter.

vartype: The data type (bool, integer, real, string, enum).

short_desc: A brief description of what the parameter controls.

source: Where the value originates — default, configuration file, or session-level SET command.

min_val / max_val: Allowed range for numeric parameters.

boot_val: Compiled-in default value.

reset_val: Value used if the parameter is reset.

pending_restart: Whether a server restart is needed for a pending change.

Key Query Planning Parameters

Planner Method Configuration

enable_indexscan — Enables or disables index scans. Set to off to force sequential scans when debugging a bad plan.

enable_indexonlyscan — Enables index-only scans, which read directly from the index without visiting the heap.

enable_hashjoin — Controls whether the planner considers hash joins. Hash joins are fast for large unsorted datasets.

enable_mergejoin — Controls whether merge joins are considered. Merge joins work well on pre-sorted data.

enable_nestloop — Controls nested loop joins. Disabling this forces the planner to use hash or merge joins.

enable_seqscan — Controls sequential table scans. Setting to off encourages index use, useful during performance testing.

enable_sort — Controls explicit sort operations. Related to whether the planner prefers pre-sorted index paths.

enable_partitionwise_join — Allows joins of partitioned tables to be performed partition by partition (PostgreSQL 11+).

Planner Cost Constants

seq_page_cost — Estimated cost of reading a single page sequentially. The baseline cost constant (default 1.0).

random_page_cost — Estimated cost of a random page read. Lower values favor index scans over sequential scans. Often reduced to 1.1–2.0 on SSDs.

cpu_tuple_cost — Estimated cost of processing one row.

cpu_index_tuple_cost — Estimated cost of processing one index entry.

cpu_operator_cost — Estimated cost of processing one operator or function call.

effective_cache_size — The planner's estimate of how much memory is available for caching data (both PostgreSQL and OS cache). Affects whether index scans are estimated as cheap or expensive.

parallel_tuple_cost — Cost of transferring one row between worker processes during parallel query.

parallel_setup_cost — Fixed overhead cost of launching parallel worker processes.

JIT Compilation

jit — Enables or disables JIT compilation for queries (PostgreSQL 11+). JIT can accelerate CPU-bound queries but adds compilation overhead.

jit_above_cost — Queries with a plan cost above this threshold are considered for JIT compilation.

jit_optimize_above_cost — Queries above this cost trigger more aggressive JIT optimization.

Statistics Collection

track_activities — Enables tracking of current query text and state in pg_stat_activity. Required for most connection monitoring queries.

track_counts — Enables row-level statistics collection used by autovacuum and pg_stat_user_tables.

track_io_timing — Enables timing of I/O operations. Adds overhead but provides data for pg_stat_statements I/O columns.

track_functions — Controls whether function call counts and durations are tracked.

Practical Applications

Fixing Bad Query Plans

When a query is running slower than expected, check enable_* settings and cost constants. A misconfigured random_page_cost too high on an SSD can cause the planner to prefer sequential scans over available indexes.

Verifying Monitoring Prerequisites

Many monitoring views depend on track_activities and track_counts being enabled. This query confirms those settings are active before relying on pg_stat_activity or autovacuum statistics.

JIT Tuning

On analytical workloads, verify JIT is enabled and the cost thresholds are appropriate. On OLTP workloads with many short queries, disabling JIT eliminates compilation overhead.

Parallel Query Configuration

Check parallel query settings such as max_parallel_workers_per_gather and parallel_setup_cost to understand how aggressively PostgreSQL will use parallel execution.

Version Compatibility

Requires PostgreSQL 9.5 or later due to the pending_restart column. JIT settings require PostgreSQL 11 or later. Tested on PostgreSQL 9.5, 9.6, 10.x, 11.x, 12.x, and 13.0.

Best Practices

Never disable planner methods in production permanently: Turning off enable_indexscan or similar settings is a diagnostic tool, not a long-term fix. Find the root cause of the bad plan instead.

Tune random_page_cost for your storage: The default of 4.0 assumes spinning disks. For SSDs, values between 1.1 and 2.0 give the planner more accurate cost estimates.

Keep track_activities on: Disabling it saves a tiny amount of overhead but blinds all connection monitoring queries.

Document cost constant changes: Any change to cost constants should be documented with before/after query plan comparisons to justify the change.

References

• PostgreSQL Documentation - Query Planning - Official reference for all query tuning configuration parameters
• PostgreSQL Documentation - Statistics Collector - Reference for statistics collection settings
• PostgreSQL Documentation - JIT Compilation - Guide to JIT compilation in PostgreSQL
• Source SQL Script - postgres_settings_query_planning.sql - Original script by Hari Sekhon