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