Average total time and total cost of a given SQL query run 10k times.

benchmark.sql

CREATE OR REPLACE FUNCTION benchmark(
  IN  query      text,
  OUT total_cost double precision,
  OUT runtime    double precision
)
  RETURNS record
LANGUAGE plpgsql
STRICT AS
$$DECLARE
  j json;
BEGIN
  total_cost := 0.0;
  runtime := 0.0;
  FOR i IN 1 .. 10000 LOOP
    EXECUTE 'EXPLAIN (ANALYZE, FORMAT JSON) ' || query INTO j;
    total_cost := total_cost + (j -> 0 -> 'Plan' ->> 'Total Cost') :: double precision;
    runtime := runtime + (j -> 0 -> 'Plan' ->> 'Actual Total Time') :: double precision;
  END LOOP;
  total_cost := total_cost / 10000.0;
  runtime := runtime / 10000.0;
  RETURN;
END;$$;
-- SELECT * FROM benchmark($$ select * from alpacas $$);