Gary Sieling

Postgres: Explain + Notice

gary

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, 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 this2:

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

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 reference4 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 [↩]
Exit mobile version