PostgreSQL 12 EXPLAIN SETTINGSPostgreSQL 12 has a new feature that can be turned on in the
SETTINGS. This option provides some information about all and only those parameters that can affect an execution plan if and only if they are not at the default setting.
What does it mean in practice? Let’s see an old plain
the output is as follows:
digikamdb=> EXPLAIN (FORMAT YAML) SELECT * FROM digikam_images WHERE id IN ( SELECT id FROM digikam_images WHERE modificationdate = '2019-10-04' );
The output is quite long, as well as the query is intentionally stupid just to generate some kind of loop. Please note that I’m using
- Plan: + Node Type: "Nested Loop" + Parallel Aware: false + Join Type: "Inner" + Startup Cost: 0.29 + Total Cost: 1737.95 + Plan Rows: 17 + Plan Width: 87 + Inner Unique: true + Plans: + - Node Type: "Seq Scan" + Parent Relationship: "Outer" + Parallel Aware: false + Relation Name: "digikam_images" + Alias: "digikam_images_1" + Startup Cost: 0.00 + Total Cost: 1596.72 + Plan Rows: 17 + Plan Width: 8 + Filter: "(modificationdate = '2019-10-04'::date)"+ - Node Type: "Index Scan" + Parent Relationship: "Inner" + Parallel Aware: false + Scan Direction: "Forward" + Index Name: "idx_id" + Relation Name: "digikam_images" + Alias: "digikam_images" + Startup Cost: 0.29 + Total Cost: 8.31 + Plan Rows: 1 + Plan Width: 87 + Index Cond: "(id = digikam_images_1.id)"
yamlas an output format for better web impagination.
SETTINGSin action, so change the
that produces the very same output!
digikamdb=> EXPLAIN (FORMAT YAML, SETTINGS ON) SELECT * FROM digikam_images WHERE id IN ( SELECT id FROM digikam_images WHERE modificationdate = '2019-10-04' );
Why? Because nothing has changed, so nothing must be shown!
Now, let’s change a parameter or two:
and see again the
digikamdb=> SET seq_page_cost TO 3; digikamdb=> SET random_page_cost TO 1;
As you can see, there is another section at the end of the output, titled
digikamdb=> EXPLAIN (FORMAT YAML, SETTINGS ON) SELECT * FROM digikam_images WHERE id IN ( SELECT id FROM digikam_images WHERE modificationdate = '2019-10-04' ); ... - Plan: + Node Type: "Nested Loop" + Parallel Aware: false + Join Type: "Inner" + Startup Cost: 0.29 + Total Cost: 4353.95 + Plan Rows: 17 + Plan Width: 87 + Inner Unique: true + Plans: + - Node Type: "Seq Scan" + ... - Node Type: "Index Scan" + ... Settings: + random_page_cost: "1" + seq_page_cost: "4"
Settings, that reminds us what parameters have changed and to which value they are currently.
In this way, it is possible to get an idea of why a plan is as it is, or at least we can remember that the system is running with different parameters.
Are all parameters affected?Reading the documentation about
SETTINGSone could think that only those parameters that are part of an access method are going to be reported on the output of
However, even parameters that are not going to change the query plan are displayed. For example, in selection all the tuples, there is no need to know that the random page cost has changed, but it is displayed anyway:
SETTINGS Include information on configuration parameters. Specifically, include options affecting query planning with value different from the built-in default value. This parameter defaults to FALSE.
digikamdb=> RESET ALL; digikamdb=> SET seq_page_cost TO 2; digikamdb=> SET random_page_cost TO 1; digikamdb=> EXPLAIN (SETTINGS ON) SELECT * FROM digikam_images; QUERY PLAN ---------------------------------------------------------------------- Seq Scan on digikam_images (cost=0.00..2364.58 rows=55258 width=87) Settings: random_page_cost = '1', seq_page_cost = '2'
Which parameters?There are different parameters, other than the trivial costs, that can be reported by
SETTINGSsection. An example is
work_mem. Reading the commit ea569d64ac7174d3fe657e3e682d11053ecf1866 reveals that all the options marked in the source code with
GUC_EXPLAINare candidates to be printed.
So far, this resolves to the following long list, where I tried to mark as bold those that I usually configure (and I’ve seen touched by others):
SETTINGSaffects also the
auto_explaintuning and output, and in fact there is a new GUC named
auto_explain.log_settingsthat provides the same functionality as above for the
EXPLAIN (SETTINGS ON)new feature is something really cool in my opinion that pretty much every DBA should turn on when inspecting query execution plans.