{"id":4762,"date":"2016-07-23T18:09:28","date_gmt":"2016-07-23T18:09:28","guid":{"rendered":"http:\/\/www.garysieling.com\/blog\/?p=4762"},"modified":"2016-07-23T18:09:28","modified_gmt":"2016-07-23T18:09:28","slug":"postgres-explain-notice","status":"publish","type":"post","link":"https:\/\/www.garysieling.com\/blog\/postgres-explain-notice\/","title":{"rendered":"Postgres: Explain + Notice"},"content":{"rendered":"<p>If you want to print the execution plan for a query from an anonymous block of plpgsql code, you have a couple options. You can turn it into a real function<sup><a href=\"#footnote_0_4762\" id=\"identifier_0_4762\" class=\"footnote-link footnote-identifier-link\" title=\"http:\/\/stackoverflow.com\/questions\/10314963\/return-value-from-anonymous-function-postgresql\">1<\/a><\/sup>, and return the execution plan (this is good when you care about a single query, and don&#8217;t want to watch the logs).<\/p>\n<p>This shows an example of how to do this<sup><a href=\"#footnote_1_4762\" id=\"identifier_1_4762\" class=\"footnote-link footnote-identifier-link\" title=\"http:\/\/stackoverflow.com\/questions\/22101229\/explain-analyze-within-pl-pgsql-gives-error-query-has-no-destination-for-resul\">2<\/a><\/sup>:<\/p>\n<pre lang=\"sql\">\nCREATE OR REPLACE FUNCTION get_plan()\n  RETURNS SETOF text AS\n$func$\nBEGIN\n  -- several queries here...\n  RETURN QUERY EXPLAIN ANALYZE --insert your query here--\nEND \n$$ LANGUAGE plpgsql;\n<\/pre>\n<p>This is really useful if you need to run a batch of queries in a single transaction. If you are a superuser, you can log the plan for every query as well using the auto_explain module<sup><a href=\"#footnote_2_4762\" id=\"identifier_2_4762\" class=\"footnote-link footnote-identifier-link\" title=\"http:\/\/dba.stackexchange.com\/questions\/84414\/explain-analyze-shows-no-details-for-queries-inside-a-plpgsql-function\">3<\/a><\/sup>;<\/p>\n<pre lang=\"sql\">\nDO $$\nBEGIN\n  LOAD 'auto_explain';\n  SET auto_explain.log_nested_statements = ON;\n  SET auto_explain.log_min_duration=0; \n  SET auto_explain.log_nested_statements=ON; \n  SET auto_explain.log_analyze = true; \n\n  ...queries here...\nEND \n$$ LANGUAGE plpgsql;\n<\/pre>\n<p>The biggest downside to this approach is that you&#8217;ll need to look at the Postgres logs to find the execution plans (this don&#8217;t show up the same as &#8216;notice&#8217; blocks).<\/p>\n<p>Once you get past this, see this reference<sup><a href=\"#footnote_3_4762\" id=\"identifier_3_4762\" class=\"footnote-link footnote-identifier-link\" title=\"http:\/\/dba.stackexchange.com\/questions\/8119\/postgresql-stored-procedure-performance\/8189#8189\">4<\/a><\/sup> for some great tuning tips.<\/p>\n<ol class=\"footnotes\"><li id=\"footnote_0_4762\" class=\"footnote\">http:\/\/stackoverflow.com\/questions\/10314963\/return-value-from-anonymous-function-postgresql<span class=\"footnote-back-link-wrapper\"> [<a href=\"#identifier_0_4762\" class=\"footnote-link footnote-back-link\">&#8617;<\/a>]<\/span><\/li><li id=\"footnote_1_4762\" class=\"footnote\">http:\/\/stackoverflow.com\/questions\/22101229\/explain-analyze-within-pl-pgsql-gives-error-query-has-no-destination-for-resul<span class=\"footnote-back-link-wrapper\"> [<a href=\"#identifier_1_4762\" class=\"footnote-link footnote-back-link\">&#8617;<\/a>]<\/span><\/li><li id=\"footnote_2_4762\" class=\"footnote\">http:\/\/dba.stackexchange.com\/questions\/84414\/explain-analyze-shows-no-details-for-queries-inside-a-plpgsql-function<span class=\"footnote-back-link-wrapper\"> [<a href=\"#identifier_2_4762\" class=\"footnote-link footnote-back-link\">&#8617;<\/a>]<\/span><\/li><li id=\"footnote_3_4762\" class=\"footnote\">http:\/\/dba.stackexchange.com\/questions\/8119\/postgresql-stored-procedure-performance\/8189#8189<span class=\"footnote-back-link-wrapper\"> [<a href=\"#identifier_3_4762\" class=\"footnote-link footnote-back-link\">&#8617;<\/a>]<\/span><\/li><\/ol>","protected":false},"excerpt":{"rendered":"<p>Logging execution plans for queries in a Postgres code block or stored procedure<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","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,"footnotes":""},"categories":[12],"tags":[437],"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/4762"}],"collection":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/comments?post=4762"}],"version-history":[{"count":0,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/posts\/4762\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/media?parent=4762"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/categories?post=4762"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.garysieling.com\/blog\/wp-json\/wp\/v2\/tags?post=4762"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}