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:

  1. =$ SET enable_seqscan = FALSE;
  2. SET
  3.  
  4. =$ EXPLAIN (settings ON) SELECT COUNT(*) FROM pg_class;
  5.                                            QUERY PLAN                                            
  6. -------------------------------------------------------------------------------------------------
  7.  Aggregate  (cost=35.36..35.37 ROWS=1 width=8)
  8.    ->  INDEX ONLY Scan USING pg_class_oid_index ON pg_class  (cost=0.27..34.29 ROWS=429 width=0)
  9.  Settings: enable_seqscan = 'off'
  10. (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:

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.”

  1. 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
  2. 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)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.