{"id":12354,"date":"2019-04-10T18:35:06","date_gmt":"2019-04-10T16:35:06","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/"},"modified":"2019-04-10T18:35:06","modified_gmt":"2019-04-10T16:35:06","slug":"postgresql-12-explain-will-display-custom-settings-if-instructed","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/","title":{"rendered":"PostgreSQL 12: Explain will display custom settings, if instructed"},"content":{"rendered":"<p>How many times did you try to solve a performance issue but have not been able to reproduce the explain plan? Whatever you tried you always got a different result. Lets say you managed to get a dump of the database in question, got all the PostreSQL parameters the same, gathered statistics but still you do not manage to get the same plan as the one who reported the issue. What could be a potential issue here? Lets do a short demo:<\/p>\n<p><!--more--><\/p>\n<p>Imagine someone is sending you this plan for a simple count(*) against pg_class:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# explain (analyze) select count(*) from pg_class;\n                                                                 QUERY PLAN                                                                  \n---------------------------------------------------------------------------------------------------------------------------------------------\n Aggregate  (cost=23.10..23.11 rows=1 width=8) (actual time=0.293..0.293 rows=1 loops=1)\n   -&gt;  Index Only Scan using pg_class_oid_index on pg_class  (cost=0.27..22.12 rows=390 width=0) (actual time=0.103..0.214 rows=390 loops=1)\n         Heap Fetches: 0\n Planning Time: 0.155 ms\n Execution Time: 0.384 ms\n(5 rows)\n<\/pre>\n<p>When you try the same on your environment the plan always looks like this (sequential scan, but not an index only scan):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# explain (analyze) select count(*) from pg_class;\n                                                 QUERY PLAN                                                  \n-------------------------------------------------------------------------------------------------------------\n Aggregate  (cost=17.88..17.89 rows=1 width=8) (actual time=0.322..0.323 rows=1 loops=1)\n   -&gt;  Seq Scan on pg_class  (cost=0.00..16.90 rows=390 width=0) (actual time=0.017..0.220 rows=390 loops=1)\n Planning Time: 1.623 ms\n Execution Time: 0.688 ms\n(4 rows)\n<\/pre>\n<p>In this case the index only scan is even faster, but usually you get a sequential scan because costs are lower. Whatever you try, you can not reproduce it. What you can&#8217;t know: The person reporting the issue didn&#8217;t tell you about that:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# set enable_seqscan = off;\nSET\npostgres=# explain (analyze) select count(*) from pg_class;\n                                                                 QUERY PLAN                                                                  \n---------------------------------------------------------------------------------------------------------------------------------------------\n Aggregate  (cost=23.10..23.11 rows=1 width=8) (actual time=0.230..0.230 rows=1 loops=1)\n   -&gt;  Index Only Scan using pg_class_oid_index on pg_class  (cost=0.27..22.12 rows=390 width=0) (actual time=0.032..0.147 rows=390 loops=1)\n         Heap Fetches: 0\n Planning Time: 0.130 ms\n Execution Time: 0.281 ms\n<\/pre>\n<p>Just before executing the statement a parameter has been changed which influences PostgreSQL&#8217;s choise about the best plan. And this is where the new feature of PostgreSQL 12 becomes handy:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,7]\">\npostgres=# explain (analyze,settings) select count(*) from pg_class;\n                                                                 QUERY PLAN                                                                  \n---------------------------------------------------------------------------------------------------------------------------------------------\n Aggregate  (cost=23.10..23.11 rows=1 width=8) (actual time=0.309..0.310 rows=1 loops=1)\n   -&gt;  Index Only Scan using pg_class_oid_index on pg_class  (cost=0.27..22.12 rows=390 width=0) (actual time=0.045..0.202 rows=390 loops=1)\n         Heap Fetches: 0\n Settings: enable_seqscan = 'off'\n Planning Time: 0.198 ms\n Execution Time: 0.395 ms\n(6 rows)\n\npostgres=# \n<\/pre>\n<p>From PostgreSQL 12 on you can ask explain to display any setting that has been changed and influenced the decision on which plan to choose. This might be optimizer parameters as here, but this might also be others when they differ from the global setting:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [6]\">\npostgres=# explain (analyze,settings) select count(*) from pg_class;\n                                                 QUERY PLAN                                                  \n-------------------------------------------------------------------------------------------------------------\n Aggregate  (cost=17.88..17.89 rows=1 width=8) (actual time=0.197..0.198 rows=1 loops=1)\n   -&gt;  Seq Scan on pg_class  (cost=0.00..16.90 rows=390 width=0) (actual time=0.016..0.121 rows=390 loops=1)\n Settings: work_mem = '64MB'\nPlanning Time: 0.162 ms\n Execution Time: 0.418 ms\n(5 rows)\n<\/pre>\n<p>&#8230; or:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,8]\">\npostgres=# set from_collapse_limit = 13;\nSET\npostgres=# explain (analyze,settings) select count(*) from pg_class;\n                                                 QUERY PLAN                                                  \n-------------------------------------------------------------------------------------------------------------\n Aggregate  (cost=17.88..17.89 rows=1 width=8) (actual time=0.190..0.190 rows=1 loops=1)\n   -&gt;  Seq Scan on pg_class  (cost=0.00..16.90 rows=390 width=0) (actual time=0.012..0.115 rows=390 loops=1)\n Settings: from_collapse_limit = '13', work_mem = '64MB'\n Planning Time: 0.185 ms\n Execution Time: 0.263 ms\n(5 rows)\n<\/pre>\n<p>Nice addition. Asking people to use the &#8220;settings&#8221; switch with analyze, you can be sure on what was changed from the global settings so it is much easier to reproduce the issue and to see what&#8217;s going on.<\/p>\n<p>Parameters that do not influence the plan do not pop up:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [1,8]\">\npostgres=# set log_statement='all';\nSET\npostgres=# explain (analyze,settings) select count(*) from pg_class;\n                                                 QUERY PLAN                                                  \n-------------------------------------------------------------------------------------------------------------\n Aggregate  (cost=17.88..17.89 rows=1 width=8) (actual time=0.199..0.200 rows=1 loops=1)\n   -&gt;  Seq Scan on pg_class  (cost=0.00..16.90 rows=390 width=0) (actual time=0.018..0.124 rows=390 loops=1)\n Settings: from_collapse_limit = '13', work_mem = '64MB'\n Planning Time: 0.161 ms\n Execution Time: 0.391 ms\n(5 rows)\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>How many times did you try to solve a performance issue but have not been able to reproduce the explain plan? Whatever you tried you always got a different result. Lets say you managed to get a dump of the database in question, got all the PostreSQL parameters the same, gathered statistics but still you [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-12354","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 12: Explain will display custom settings, if instructed - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 12: Explain will display custom settings, if instructed\" \/>\n<meta property=\"og:description\" content=\"How many times did you try to solve a performance issue but have not been able to reproduce the explain plan? Whatever you tried you always got a different result. Lets say you managed to get a dump of the database in question, got all the PostreSQL parameters the same, gathered statistics but still you [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-10T16:35:06+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-explain-will-display-custom-settings-if-instructed\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-explain-will-display-custom-settings-if-instructed\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"PostgreSQL 12: Explain will display custom settings, if instructed\",\"datePublished\":\"2019-04-10T16:35:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-explain-will-display-custom-settings-if-instructed\\\/\"},\"wordCount\":296,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-explain-will-display-custom-settings-if-instructed\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-explain-will-display-custom-settings-if-instructed\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-explain-will-display-custom-settings-if-instructed\\\/\",\"name\":\"PostgreSQL 12: Explain will display custom settings, if instructed - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2019-04-10T16:35:06+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-explain-will-display-custom-settings-if-instructed\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-explain-will-display-custom-settings-if-instructed\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-12-explain-will-display-custom-settings-if-instructed\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 12: Explain will display custom settings, if instructed\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 12: Explain will display custom settings, if instructed - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 12: Explain will display custom settings, if instructed","og_description":"How many times did you try to solve a performance issue but have not been able to reproduce the explain plan? Whatever you tried you always got a different result. Lets say you managed to get a dump of the database in question, got all the PostreSQL parameters the same, gathered statistics but still you [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/","og_site_name":"dbi Blog","article_published_time":"2019-04-10T16:35:06+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"PostgreSQL 12: Explain will display custom settings, if instructed","datePublished":"2019-04-10T16:35:06+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/"},"wordCount":296,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/","name":"PostgreSQL 12: Explain will display custom settings, if instructed - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-04-10T16:35:06+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-12-explain-will-display-custom-settings-if-instructed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 12: Explain will display custom settings, if instructed"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12354","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=12354"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12354\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12354"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12354"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12354"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12354"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}