How to use EXPLAIN output in PostgreSQL?

Sometimes you want to process the EXPLAIN output in SQL. Unfortunately, EXPLAIN itself does not offer that option. However, there are tricks...

Wrapping EXPLAIN in a function

This is probably the simpler version. It requires to create a function:

CREATE OR REPLACE FUNCTION explain(
    p_q TEXT,
    p_mod TEXT[] DEFAULT '{ANALYZE,BUFFERS}'
) RETURNS SETOF JSON AS $$
BEGIN
    RETURN QUERY
    EXECUTE 'EXPLAIN ('
         || array_to_string(array_append(p_mod, 'FORMAT JSON'), ',')
         || ')'
         || p_q;
END
$$ LANGUAGE plpgsql;

That function then could be used like this:

postgres=# select explain('select count(*) from pg_class', '{}')->0;
                           ?column?                           
--------------------------------------------------------------
 {                                                           +
     "Plan": {                                               +
       "Node Type": "Aggregate",                             +
       "Strategy": "Plain",                                  +
       "Partial Mode": "Simple",                             +
       "Parallel Aware": false,                              +
       "Startup Cost": 13.80,                                +
       "Total Cost": 13.81,                                  +
       "Plan Rows": 1,                                       +
       "Plan Width": 8,                                      +
       "Plans": [                                            +
         {                                                   +
           "Node Type": "Index Only Scan",                   +
           "Parent Relationship": "Outer",                   +
           "Parallel Aware": false,                          +
           "Scan Direction": "Forward",                      +
           "Index Name": "pg_class_tblspc_relfilenode_index",+
           "Relation Name": "pg_class",                      +
           "Alias": "pg_class",                              +
           "Startup Cost": 0.27,                             +
           "Total Cost": 13.01,                              +
           "Plan Rows": 316,                                 +
           "Plan Width": 0                                   +
         }                                                   +
       ]                                                     +
     }                                                       +
   }
(1 row)

or like this:

postgres=# select (explain('select count(*) from pg_class')->0
postgres(#         ->>'Execution Time')::DOUBLE PRECISION;
 float8 
--------
  0.166
(1 row)

Using dblink

Sometimes you don't want to introduce a new function in a database. But maybe the database has the dblink extension installed. Or you have another database with dblink at hand.

postgres=# select (js->0->>'Execution Time')::DOUBLE PRECISION
postgres-#   from dblink('dbname=postgres',
postgres(#               'EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) ' ||
postgres(#               $$select count(*) from pg_class$$
postgres(#              ) t(js JSON);
 float8 
--------
   0.18
(1 row)

Note: This post has been ported from https://tech.binary.com/ (our old tech blog)