My PostgreSQL query only returns one row and I need to include in the SELECT the amount of time it took to return.
How do I do this?
E.g. Table hello
SELECT COUNT(h.*) as "count", <insert query time calculation here> as "elapsed_ms"
FROM hello h;
Kind regards
You cannot do this in one single SQL statement because PostgreSQL does not expose system function or system column in SQL to retrieve current query execution time (function statement_timestamp() can be used for query start but there is no corresponding function for query end).
But you could try something like this in a function:
create function exec_count()
returns table (ccount int, ctime_microsec int)
language plpgsql
as
$$
declare
v_begin timestamp;
v_end timestamp;
v_s int;
v_mls int;
v_mcs int;
v_count int;
v_time int;
begin
v_begin = clock_timestamp();
select count(*) into v_count from hello h;
v_end = clock_timestamp();
select extract(microseconds from (v_end - v_begin)) into v_mcs;
select extract(milliseconds from (v_end - v_begin)) into v_mls;
select extract(second from v_end - v_begin) into v_s;
v_time = v_mcs + v_mls*1000 * v_s*1000*1000;
return query select v_count, v_time;
end;
$$;
You could call the function in SQL instead of the query:
select * from exec_count();
ccount | ctime_microsec
--------+----------------
457 | 407
(1 row)
This function computes execution time in microseconds and assumes the query does not take more than 60 seconds.
epoch instead of seconds from the interval to make it work for larger values
psqlterminal? Or inside a language? There are different approaches in different scenariosEXPLAIN ANALYZE, but afaik there's no way to get the actual result rows from that.