How do I process execution plan in PostgreSQL?

In PostgreSQL, when I run:

EXPLAIN (FORMAT JSON) SELECT * FROM ACCOUNT WHERE TYPE > 'CHK'

I get:

[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "account",
      "Alias": "account",
      "Startup Cost": 0.00,
      "Total Cost": 13.25,
      "Plan Rows": 87,
      "Plan Width": 276,
      "Filter": "((type)::text > 'CHK'::text)"
    }
  }
]

How can I get the “Total Cost” from there? I tried:

SELECT P -> 'Total Cost' FROM (
  EXPLAIN (FORMAT JSON)
  SELECT * FROM ACCOUNT WHERE TYPE > 'CHK'
) X (P)

But I get the error:

ERROR: syntax error at or near "JSON"
  Position: 51
SQLState:  42601

Answer

explain always needs to go before a statement, you can’t put it in the middle of one.

You can do what you want by wrapping this into a function:

create or replace function get_plan_cost(p_sql text)
  returns text
as
$$
declare
  l_result jsonb;
begin  
  execute 'explain (format json) ' || p_sql
    into l_result;
  return l_result #>> '{0,Plan,"Total Cost"}';
end;
$$
language plpgsql;

select get_plan_cost($$ SELECT * FROM ACCOUNT WHERE TYPE > 'CHK' $$);

---

If you can’t use a function, you can use an anonymous block with a RAISE statement:

set client_min_messages=notice;
do
$$
declare
  l_sql text := $$ SELECT * FROM ACCOUNT WHERE TYPE > 'CHK' $$;
  l_result jsonb;
begin  
  execute 'explain (format json) ' || l_sql
    into l_result;
  raise notice 'Cost: %', l_result #>> '{0,Plan,"Total Cost"}';
end;
$$
;

But that seems overly complicated compare to using:

explain (format text)
SELECT * FROM ACCOUNT WHERE TYPE > 'CHK';

And then simply looking at the first line.