Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings.

On 3rd of April 2019, Tomas Vondra committed patch:

Add SETTINGS option to EXPLAIN, to print modified settings.
 
 
Query planning is affected by a number of configuration options, and it
may be crucial to know which of those options were set to non-default
values.  With this patch you can say EXPLAIN (SETTINGS ON) to include
that information in the query plan.  Only options affecting planning,
with values different from the built-in default are printed.
 
This patch also adds auto_explain.log_settings option, providing the
same capability in auto_explain module.
 
Author: Tomas Vondra
 
Discussion: https://postgr.es/m/e1791b4c-df9c-be02-edc5-7c8874944be0@2ndquadrant.com

This is pretty cool, though mostly when you're reaching out to someone ( like on irc ) to get help.

Let's imagine you have a situation where planner is choosing something really weird. Now, with this new SETTINGS option, you can include all non-standard, but important, settings in explain output.

How does it look like?

=$ EXPLAIN (settings ON) SELECT COUNT(*) FROM pg_class;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Aggregate  (cost=18.36..18.37 ROWS=1 width=8)
   ->  Seq Scan ON pg_class  (cost=0.00..17.29 ROWS=429 width=0)
(2 ROWS)

So far nothing new, but that's because I don't have any changes in this test Pg.

So, let's change something:

```
=$ SET enable_seqscan = FALSE;
```
```
SET
```
```
 
```

=$ EXPLAIN (settings ON) SELECT COUNT(*) FROM pg_class;

                                           QUERY PLAN

-------------------------------------------------------------------------------------------------

 Aggregate  (cost=35.36..35.37 ROWS=1 width=8)

   ->  INDEX ONLY Scan USING pg_class_oid_index ON pg_class  (cost=0.27..34.29 ROWS=429 width=0)

```
 Settings: enable_seqscan = 'off'
```
```
(3 ROWS)
```

Whoa. Take a look at line 8.

So, how will that look if I'll have more settings modified:

=$ SET cpu_tuple_cost = 0.02;
SET
 
=$ SET cpu_index_tuple_cost = 0.006;
SET
 
=$ SET enable_seqscan = FALSE;
SET
 
=$ EXPLAIN (settings ON) SELECT COUNT(*) FROM pg_class;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Aggregate  (cost=40.08..40.10 ROWS=1 width=8)
   ->  INDEX ONLY Scan USING pg_class_oid_index ON pg_class  (cost=0.27..39.00 ROWS=429 width=0)
 Settings: cpu_index_tuple_cost = '0.006', cpu_tuple_cost = '0.02', enable_seqscan = 'off'
(3 ROWS)

Pretty cool.

Please note that it can also get included in auto_explain plans, if you'd do:

=$ SET auto_explain.log_settings = TRUE;

I wasn't able to find a nicely accessible list of GUCs that will get printed, but after quick(ish) work with sources, it looks that these are the ones:

enable_bitmapscan : Enables the planner's use of bitmap-scan plans.
enable_gathermerge : Enables the planner's use of gather merge plans.
enable_hashagg : Enables the planner's use of hashed aggregation plans.
enable_hashjoin : Enables the planner's use of hash join plans.
enable_indexonlyscan : Enables the planner's use of index-only-scan plans.
enable_indexscan : Enables the planner's use of index-scan plans.
enable_material : Enables the planner's use of materialization.
enable_mergejoin : Enables the planner's use of merge join plans.
enable_nestloop : Enables the planner's use of nested-loop join plans.
enable_parallel_append : Enables the planner's use of parallel append plans.
enable_parallel_hash : Enables the planner's use of parallel hash plans.
enable_partitionwise_aggregate : Enables partitionwise aggregation and grouping.
enable_partitionwise_join : Enables partitionwise join.
enable_partition_pruning : Enable plan-time and run-time partition pruning.
enable_seqscan : Enables the planner's use of sequential-scan plans.
enable_sort : Enables the planner's use of explicit sort steps.
enable_tidscan : Enables the planner's use of TID scan plans.
geqo : Enables genetic query optimization.
jit : Allow JIT compilation.
parallel_leader_participation : Controls whether Gather and Gather Merge also run subplans.

This is really nice, thanks Tomas, and others involved.

2 thoughts on “Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings.”

There are quite some more GUCs reported by “EXPLAIN (settings ON)”. I compiled a list by (don’t have a grep -P on my system)

$ pcregrep -Mo1 '(?s){\s*"(\w+)"[^{}]*GUC_EXPLAIN' guc.c | sort
constraint_exclusion
cpu_index_tuple_cost
cpu_operator_cost
cpu_tuple_cost
cursor_tuple_fraction
effective_cache_size
effective_io_concurrency
enable_bitmapscan
enable_gathermerge
enable_hashagg
enable_hashjoin
enable_indexonlyscan
enable_indexscan
enable_material
enable_mergejoin
enable_nestloop
enable_parallel_append
enable_parallel_hash
enable_partition_pruning
enable_partitionwise_aggregate
enable_partitionwise_join
enable_seqscan
enable_sort
enable_tidscan
force_parallel_mode
from_collapse_limit
geqo
geqo_effort
geqo_generations
geqo_pool_size
geqo_seed
geqo_selection_bias
geqo_threshold
jit
jit_above_cost
jit_inline_above_cost
jit_optimize_above_cost
join_collapse_limit
max_parallel_workers
max_parallel_workers_per_gather
min_parallel_index_scan_size
min_parallel_table_scan_size
optimize_bounded_sort
parallel_leader_participation
parallel_setup_cost
parallel_tuple_cost
plan_cache_mode
random_page_cost
search_path
seq_page_cost
temp_buffers
work_mem

Oh, we should do this in psql!

# \SET guc TO `cat guc.c`
# SELECT category, name, short_desc FROM pg_settings NATURAL JOIN (SELECT (regexp_matches(:'guc', '{\s*"(\w+)"[^{}]*GUC_EXPLAIN', 'g'))[1] AS name) EXP ORDER BY 1,2;
┌─────────────────────────────────────────────────┬─────────────────────────────────┬────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                    category                     │              name               │                                             short_desc                                             │
├─────────────────────────────────────────────────┼─────────────────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Client Connection Defaults / Statement Behavior │ search_path                     │ Sets the schema SEARCH ORDER FOR names that are NOT schema-qualified.                              │
│ Query Tuning / Genetic Query Optimizer          │ geqo                            │ Enables genetic query optimization.                                                                │
│ Query Tuning / Genetic Query Optimizer          │ geqo_effort                     │ GEQO: effort IS used TO SET the DEFAULT FOR other GEQO parameters.                                 │
│ Query Tuning / Genetic Query Optimizer          │ geqo_generations                │ GEQO: NUMBER OF iterations OF the algorithm.                                                       │
│ Query Tuning / Genetic Query Optimizer          │ geqo_pool_size                  │ GEQO: NUMBER OF individuals IN the population.                                                     │
│ Query Tuning / Genetic Query Optimizer          │ geqo_seed                       │ GEQO: seed FOR random path selection.                                                              │
│ Query Tuning / Genetic Query Optimizer          │ geqo_selection_bias             │ GEQO: selective pressure WITHIN the population.                                                    │
│ Query Tuning / Genetic Query Optimizer          │ geqo_threshold                  │ Sets the threshold OF FROM items beyond which GEQO IS used.                                        │
│ Query Tuning / Other Planner Options            │ constraint_exclusion            │ Enables the planner TO USE constraints TO OPTIMIZE queries.                                        │
│ Query Tuning / Other Planner Options            │ cursor_tuple_fraction           │ Sets the planner's estimate of the fraction of a cursor's ROWS that will be retrieved.             │
│ Query Tuning / Other Planner Options            │ force_parallel_mode             │ Forces USE OF parallel query facilities.                                                           │
│ Query Tuning / Other Planner Options            │ from_collapse_limit             │ Sets the FROM-list SIZE beyond which subqueries are NOT collapsed.                                 │
│ Query Tuning / Other Planner Options            │ jit                             │ Allow JIT compilation.                                                                             │
│ Query Tuning / Other Planner Options            │ join_collapse_limit             │ Sets the FROM-list SIZE beyond which JOIN constructs are NOT flattened.                            │
│ Query Tuning / Other Planner Options            │ plan_cache_mode                 │ Controls the planner's selection of custom or generic plan.                                        │
│ Query Tuning / Planner Cost Constants           │ cpu_index_tuple_cost            │ Sets the planner's estimate OF the cost OF processing each INDEX entry during an INDEX scan.       │
│ Query Tuning / Planner Cost Constants           │ cpu_operator_cost               │ Sets the planner's estimate of the cost of processing each operator or function call.              │
│ Query Tuning / Planner Cost Constants           │ cpu_tuple_cost                  │ Sets the planner's estimate OF the cost OF processing each tuple (ROW).                            │
│ Query Tuning / Planner Cost Constants           │ effective_cache_size            │ Sets the planner's assumption about the total size of the data caches.                             │
│ Query Tuning / Planner Cost Constants           │ jit_above_cost                  │ Perform JIT compilation if query is more expensive.                                                │
│ Query Tuning / Planner Cost Constants           │ jit_inline_above_cost           │ Perform JIT inlining if query is more expensive.                                                   │
│ Query Tuning / Planner Cost Constants           │ jit_optimize_above_cost         │ Optimize JITed functions if query is more expensive.                                               │
│ Query Tuning / Planner Cost Constants           │ min_parallel_index_scan_size    │ Sets the minimum amount of index data for a parallel scan.                                         │
│ Query Tuning / Planner Cost Constants           │ min_parallel_table_scan_size    │ Sets the minimum amount of table data for a parallel scan.                                         │
│ Query Tuning / Planner Cost Constants           │ parallel_setup_cost             │ Sets the planner's estimate OF the cost OF starting up worker processes FOR parallel query.        │
│ Query Tuning / Planner Cost Constants           │ parallel_tuple_cost             │ Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend. │
│ Query Tuning / Planner Cost Constants           │ random_page_cost                │ Sets the planner's estimate OF the cost OF a nonsequentially fetched disk page.                    │
│ Query Tuning / Planner Cost Constants           │ seq_page_cost                   │ Sets the planner's estimate of the cost of a sequentially fetched disk page.                       │
│ Query Tuning / Planner Method Configuration     │ enable_bitmapscan               │ Enables the planner's USE OF bitmap-scan plans.                                                    │
│ Query Tuning / Planner Method Configuration     │ enable_gathermerge              │ Enables the planner's use of gather merge plans.                                                   │
│ Query Tuning / Planner Method Configuration     │ enable_hashagg                  │ Enables the planner's USE OF hashed aggregation plans.                                             │
│ Query Tuning / Planner Method Configuration     │ enable_hashjoin                 │ Enables the planner's use of hash join plans.                                                      │
│ Query Tuning / Planner Method Configuration     │ enable_indexonlyscan            │ Enables the planner's USE OF index-only-scan plans.                                                │
│ Query Tuning / Planner Method Configuration     │ enable_indexscan                │ Enables the planner's use of index-scan plans.                                                     │
│ Query Tuning / Planner Method Configuration     │ enable_material                 │ Enables the planner's USE OF materialization.                                                      │
│ Query Tuning / Planner Method Configuration     │ enable_mergejoin                │ Enables the planner's use of merge join plans.                                                     │
│ Query Tuning / Planner Method Configuration     │ enable_nestloop                 │ Enables the planner's USE OF nested-loop JOIN plans.                                               │
│ Query Tuning / Planner Method Configuration     │ enable_parallel_append          │ Enables the planner's use of parallel append plans.                                                │
│ Query Tuning / Planner Method Configuration     │ enable_parallel_hash            │ Enables the planner's USE OF parallel hash plans.                                                  │
│ Query Tuning / Planner Method Configuration     │ enable_partition_pruning        │ Enable plan-TIME AND run-TIME partition pruning.                                                   │
│ Query Tuning / Planner Method Configuration     │ enable_partitionwise_aggregate  │ Enables partitionwise aggregation AND GROUPING.                                                    │
│ Query Tuning / Planner Method Configuration     │ enable_partitionwise_join       │ Enables partitionwise JOIN.                                                                        │
│ Query Tuning / Planner Method Configuration     │ enable_seqscan                  │ Enables the planner's use of sequential-scan plans.                                                │
│ Query Tuning / Planner Method Configuration     │ enable_sort                     │ Enables the planner's USE OF explicit sort steps.                                                  │
│ Query Tuning / Planner Method Configuration     │ enable_tidscan                  │ Enables the planner's use of TID scan plans.                                                       │
│ Resource Usage / Asynchronous Behavior          │ effective_io_concurrency        │ Number of simultaneous requests that can be handled efficiently by the disk subsystem.             │
│ Resource Usage / Asynchronous Behavior          │ max_parallel_workers            │ Sets the maximum number of parallel workers that can be active at one time.                        │
│ Resource Usage / Asynchronous Behavior          │ max_parallel_workers_per_gather │ Sets the maximum number of parallel processes per executor node.                                   │
│ Resource Usage / Asynchronous Behavior          │ parallel_leader_participation   │ Controls whether Gather and Gather Merge also run subplans.                                        │
│ Resource Usage / Memory                         │ temp_buffers                    │ Sets the maximum number of temporary buffers used by each session.                                 │
│ Resource Usage / Memory                         │ work_mem                        │ Sets the maximum memory to be used for query workspaces.                                           │
└─────────────────────────────────────────────────┴─────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────┘
(51 rows)

Comments are closed.