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)