Postgres: Explain + Notice

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

This shows an example of how to do this²:

CREATE OR REPLACE FUNCTION get_plan()
  RETURNS SETOF text AS
$func$
BEGIN
  -- several queries here...
  RETURN QUERY EXPLAIN ANALYZE --insert your query here--
END 
$$ LANGUAGE plpgsql;

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³;

DO $$
BEGIN
  LOAD 'auto_explain';
  SET auto_explain.log_nested_statements = ON;
  SET auto_explain.log_min_duration=0; 
  SET auto_explain.log_nested_statements=ON; 
  SET auto_explain.log_analyze = true; 

  ...queries here...
END 
$$ LANGUAGE plpgsql;

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).

Once you get past this, see this reference⁴ for some great tuning tips.

1. http://stackoverflow.com/questions/10314963/return-value-from-anonymous-function-postgresql [↩]
2. http://stackoverflow.com/questions/22101229/explain-analyze-within-pl-pgsql-gives-error-query-has-no-destination-for-resul [↩]
3. http://dba.stackexchange.com/questions/84414/explain-analyze-shows-no-details-for-queries-inside-a-plpgsql-function [↩]
4. http://dba.stackexchange.com/questions/8119/postgresql-stored-procedure-performance/8189#8189 [↩]