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.

Leave a Reply

Your email address will not be published.

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