Postgres: select ‘explain’ output into a query

gary

8 years ago

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 this¹:

[
  {
    "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.

http://stackoverflow.com/questions/7682102/putting-explain-results-into-a-table [↩]