{"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":"

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 function1<\/a><\/sup>, and return the execution plan (this is good when you care about a single query, and don’t want to watch the logs).<\/p>\n

This shows an example of how to do this2<\/a><\/sup>:<\/p>\n

\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
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 module3<\/a><\/sup>;<\/p>\n
\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
The biggest downside to this approach is that you’ll need to look at the Postgres logs to find the execution plans (this don’t show up the same as ‘notice’ blocks).<\/p>\n
Once you get past this, see this reference4<\/a><\/sup> for some great tuning tips.<\/p>\n
  1. http:\/\/stackoverflow.com\/questions\/10314963\/return-value-from-anonymous-function-postgresql [↩<\/a>]<\/span><\/li>
  2. http:\/\/stackoverflow.com\/questions\/22101229\/explain-analyze-within-pl-pgsql-gives-error-query-has-no-destination-for-resul [↩<\/a>]<\/span><\/li>
  3. http:\/\/dba.stackexchange.com\/questions\/84414\/explain-analyze-shows-no-details-for-queries-inside-a-plpgsql-function [↩<\/a>]<\/span><\/li>
  4. http:\/\/dba.stackexchange.com\/questions\/8119\/postgresql-stored-procedure-performance\/8189#8189 [↩<\/a>]<\/span><\/li><\/ol>","protected":false},"excerpt":{"rendered":"
    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,"footnotes":""},"categories":[12],"tags":[437],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t