In PostgreSQL, when I run:
x
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
Advertisement
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.