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)