{"id":56599,"date":"2024-08-12T11:03:38","date_gmt":"2024-08-12T11:03:38","guid":{"rendered":"https:\/\/dbtut.com\/?p=56599"},"modified":"2024-08-12T11:03:38","modified_gmt":"2024-08-12T11:03:38","slug":"postgresql-execution-plan-explain","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/","title":{"rendered":"PostgreSQL Execution Plan (Explain)"},"content":{"rendered":"

In today’s article, we will be talking about how to use the EXPLAIN concept and its types to understand and optimize SQL queries.<\/p>\n

In Database Systems, each query has its own Execution Plan.<\/p>\n

We can also call the Execution Plan the life cycle of that query.<\/p>\n

The Explain command helps us a lot when examining our written queries.<\/p>\n

If we write explain at the beginning of the queries, it gives us the execution plan of that query.<\/p>\n

Some of the parameters related to Explain:<\/h4>\n

ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING, SUMMARY, FORMAT { TEXT | XML | JSON | YAML }<\/p>\n

ANALYZE:<\/strong> It presents information to the user such as the number of rows returned from the query, the total elapsed time and the running time.<\/p>\n

<\/p>\n

\n

VERBOSE:<\/strong> It is used to give more detailed information about the execution plan.<\/p>\n

COSTS:<\/strong> It is used to obtain the lengths and total costs of the affected rows at each stage of the execution plan.<\/p>\n

BUFFERS:<\/strong> Can be used with the ANALYZE parameter.<\/p>\n

TIMING:<\/strong> This parameter is used to show the start time, end time and how much time is spent on each node.<\/p>\n

SUMMARY:<\/strong> This parameter adds summary information, such as total timing, to the query plan. If ANALYZE is used, it includes summary information by default.<\/p>\n

FORMAT:<\/strong> The query plan is used to specify the output format. Output can be produced in TEXT, XML, JSON, YAML formats.<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

In today’s article, we will be talking about how to use the EXPLAIN concept and its types to understand and optimize SQL queries. In Database Systems, each query has its own Execution Plan. We can also call the Execution Plan the life cycle of that query. The Explain command helps us a lot when examining …<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":366,"featured_media":56601,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-56599","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-postgres"],"aioseo_notices":[],"yoast_head":"\nPostgreSQL Execution Plan (Explain) - Database Tutorials<\/title>\n<meta name=\"description\" content=\"In today's article, we will be talking about how to use the EXPLAIN concept and its types to understand and optimize SQL queries.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL Execution Plan (Explain) - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"In today's article, we will be talking about how to use the EXPLAIN concept and its types to understand and optimize SQL queries.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2024-08-12T11:03:38+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/08\/Ekran-goruntusu-2024-08-12-140003.png\" \/>\n\t<meta property=\"og:image:width\" content=\"706\" \/>\n\t<meta property=\"og:image:height\" content=\"326\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Faruk Erdem\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Faruk Erdem\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/\"},\"author\":{\"name\":\"Faruk Erdem\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb\"},\"headline\":\"PostgreSQL Execution Plan (Explain)\",\"datePublished\":\"2024-08-12T11:03:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/\"},\"wordCount\":233,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/08\/Ekran-goruntusu-2024-08-12-140003.png\",\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/\",\"name\":\"PostgreSQL Execution Plan (Explain) - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/08\/Ekran-goruntusu-2024-08-12-140003.png\",\"datePublished\":\"2024-08-12T11:03:38+00:00\",\"description\":\"In today's article, we will be talking about how to use the EXPLAIN concept and its types to understand and optimize SQL queries.\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/08\/Ekran-goruntusu-2024-08-12-140003.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/08\/Ekran-goruntusu-2024-08-12-140003.png\",\"width\":706,\"height\":326},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL Execution Plan (Explain)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb\",\"name\":\"Faruk Erdem\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g\",\"caption\":\"Faruk Erdem\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/farukerdem\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL Execution Plan (Explain) - Database Tutorials","description":"In today's article, we will be talking about how to use the EXPLAIN concept and its types to understand and optimize SQL queries.","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:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL Execution Plan (Explain) - Database Tutorials","og_description":"In today's article, we will be talking about how to use the EXPLAIN concept and its types to understand and optimize SQL queries.","og_url":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/","og_site_name":"Database Tutorials","article_published_time":"2024-08-12T11:03:38+00:00","og_image":[{"width":706,"height":326,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/08\/Ekran-goruntusu-2024-08-12-140003.png","type":"image\/png"}],"author":"Faruk Erdem","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Faruk Erdem","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/"},"author":{"name":"Faruk Erdem","@id":"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb"},"headline":"PostgreSQL Execution Plan (Explain)","datePublished":"2024-08-12T11:03:38+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/"},"wordCount":233,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/08\/Ekran-goruntusu-2024-08-12-140003.png","articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/","url":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/","name":"PostgreSQL Execution Plan (Explain) - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/08\/Ekran-goruntusu-2024-08-12-140003.png","datePublished":"2024-08-12T11:03:38+00:00","description":"In today's article, we will be talking about how to use the EXPLAIN concept and its types to understand and optimize SQL queries.","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/08\/Ekran-goruntusu-2024-08-12-140003.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2024\/08\/Ekran-goruntusu-2024-08-12-140003.png","width":706,"height":326},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2024\/08\/12\/postgresql-execution-plan-explain\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL Execution Plan (Explain)"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb","name":"Faruk Erdem","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g","caption":"Faruk Erdem"},"url":"https:\/\/dbtut.com\/index.php\/author\/farukerdem\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/56599","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/366"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=56599"}],"version-history":[{"count":1,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/56599\/revisions"}],"predecessor-version":[{"id":56602,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/56599\/revisions\/56602"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/56601"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=56599"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=56599"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=56599"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}