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 (
    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
    query_explain  text;
    explanation    text;
  query_explain := e'EXPLAIN(FORMAT json) ' || query;
  execute query_explain INTO explanation;
  return explanation;
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.

  1. []