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
- http:\/\/stackoverflow.com\/questions\/7682102\/putting-explain-results-into-a-table [↩<\/a>]<\/span><\/li><\/ol>","protected":false},"excerpt":{"rendered":"
How to get the results of a Postgres ‘explain’ into a useable SQL data structure<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[12],"tags":[437,523],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t