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

If you're looking for a good Postgres book, Postgres High Performance gave me a lot of good tips on problems you encounter in a running system.

Citations:
  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 []
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *