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:<\/p>\n
\nselect * from (\n explain \n select * from test_table\n) data\n<\/pre>\nHowever, you can execute explain as a function, from within another Postgres.<\/p>\n
\nCREATE OR REPLACE FUNCTION explain_output(query text) returns text AS\n$BODY$\nDECLARE\n query_explain text;\n explanation text;\nBEGIN\n query_explain := e'EXPLAIN(FORMAT json) ' || query;\n execute query_explain INTO explanation;\n return explanation;\nEND;\n$BODY$\nLANGUAGE plpgsql;\n\nselect test2('select * from test_table')\n<\/pre>\nOne 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<\/a><\/sup>:<\/p>\n\n[\n {\n \"Plan\": {\n \"Node Type\": \"Seq Scan\",\n \"Relation Name\": \"test_table\",\n \"Alias\": \"test_table\",\n \"Startup Cost\": 0.00,\n \"Total Cost\": 9.87,\n \"Plan Rows\": 87,\n \"Plan Width\": 4899\n }\n }\n]\n<\/pre>\nThe 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.<\/p>\n