Postgres: select ‘explain’ output into a query

With Postgres, you can’t easily use the output of “explain” in a query. For instance, you might wish to write a query like this, but it is not possible syntactically:

SELECT * FROM (
  EXPLAIN 
    SELECT * FROM test_table
) DATA

However, you can execute explain as a function, from within another Postgres.

CREATE OR REPLACE FUNCTION explain_output(query text) RETURNS text AS
$BODY$
DECLARE
    query_explain  text;
    explanation    text;
BEGIN
  query_explain := e'EXPLAIN(FORMAT json) ' || query;
  EXECUTE query_explain INTO explanation;
  RETURN explanation;
END;
$BODY$
LANGUAGE plpgsql;
 
SELECT test2('select * from test_table')

One nice feature of this is you can chose a format that is easily parsed, like JSON or XML, so you get a result like this1:

[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Relation Name": "test_table",
      "Alias": "test_table",
      "Startup Cost": 0.00,
      "Total Cost": 9.87,
      "Plan Rows": 87,
      "Plan Width": 4899
    }
  }
]

The downside to looking at the execution plan is that it works best when there isn’t an index already, so in the future we’ll be looking for a way to address this.

Interested in Postgres? I send out weekly, personalized emails with articles and conference talks. Click here to see an example and subscribe.

Citations:
  1. http://stackoverflow.com/questions/7682102/putting-explain-results-into-a-table []
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 *