PostgreSQL 12 EXPLAIN SETTINGS

EXPLAIN

SETTINGS

EXPLAIN

digikamdb => EXPLAIN ( FORMAT YAML ) 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 : 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)"

yaml

SETTINGS

EXPLAIN

digikamdb => EXPLAIN ( FORMAT YAML , SETTINGS ON ) SELECT * FROM digikam_images WHERE id IN ( SELECT id FROM digikam_images WHERE modificationdate = '2019-10-04' );

digikamdb => SET seq_page_cost TO 3 ; digikamdb => SET random_page_cost TO 1 ;

EXPLAIN

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

Are all parameters affected?

EXPLAIN

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?

SETTINGS

work_mem

GUC_EXPLAIN

enable_seqscan , enable_indexscan enable_indexonlyscan , enable_bitmapscan ;

; temp_buffers , work_mem ;

; max_parallel_workers_per_gather , max_parallel_workers , enable_gathermerge ;

; effective_cache_size ;

; min_parallel_table_scan_size , min_parallel_index_scan_size ;

; enable_parallel_append , enable_parallel_hash , enable_partition_pruning ;

; enable_nestloop , enable_mergejoin , enable_hashjoin ;

; enable_tidscan ;

; enable_sort ;

; enable_hashagg ;

; enable_material ;

; enable_partitionwise_join ;

; enable_partitionwise_aggregate ;

; geqo ;

; optimize_bounded_sort ;

; parallel_leader_participation ;

; jit ;

; from_collapse_limit ;

; join_collapse_limit ;

; geqo_threshold ;

; geqo_effort ;

; geqo_pool_size ;

; geqo_generations ;

; effective_io_concurrency ;

What about auto_explain ?

SETTINGS

auto_explain

auto_explain

Conclusions

EXPLAIN (SETTINGS ON)