-- some constants
\set num_samples 100
\set num_rows 100000
-- Create local tables (to be pointed by foreign tables)
DROP TABLE lt CASCADE;
CREATE TABLE lt(val int, val2 int);
CREATE INDEX i_lt_val ON lt(val);
INSERT INTO lt SELECT generate_series(1, :num_rows), generate_series(1, :num_rows);
-- Create a parent table to test inheritance case
DROP TABLE lpt CASCADE;
CREATE TABLE lpt (LIKE lt);

DROP EXTENSION postgres_fdw CASCADE;
create extension postgres_fdw;
CREATE SERVER pg1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres');
CREATE SERVER pg2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres');
CREATE USER MAPPING FOR CURRENT_USER SERVER pg1;
CREATE USER MAPPING FOR CURRENT_USER SERVER pg2;
CREATE FOREIGN TABLE ft1 () INHERITS (lpt) SERVER pg1 OPTIONS (table_name 'lt',
																use_remote_estimate 'true');
CREATE FOREIGN TABLE ft2 () INHERITS (lpt) SERVER pg2 OPTIONS (table_name 'lt',
																use_remote_estimate 'true');

DROP FUNCTION query_execution_stats(query text, num_samples int,
													OUT avg_exe_time float,
													OUT exec_time_dev float,
													OUT min_exe_time float,
													OUT max_exe_time float);

CREATE FUNCTION query_execution_stats(query text, num_samples int,
													OUT avg_exe_time float,
													OUT std_dev_exe_time float,
													OUT min_exe_time float,
													OUT max_exe_time float)
RETURNS record LANGUAGE plpgsql AS $$
DECLARE
	plan json;
BEGIN
	CREATE TEMPORARY TABLE query_exe_times(exe_time float); 

	-- Execute query a few times (5% of user specified runs) to warm the cache
	FOR i IN 1 .. num_samples/20 LOOP
		EXECUTE query;
	END LOOP;

	FOR i IN 1 .. num_samples LOOP
		EXECUTE 'EXPLAIN (analyze, format json) ' || query INTO plan;
		INSERT INTO query_exe_times VALUES ((plan->0->'Execution Time')::text::float);
	END LOOP;

	SELECT avg(exe_time), stddev(exe_time), min(exe_time), max(exe_time)
		INTO avg_exe_time, std_dev_exe_time, min_exe_time, max_exe_time
		FROM query_exe_times;

	DROP TABLE query_exe_times;
END;
$$;

ANALYZE ft1;

\set query 'SELECT * FROM ft1 ORDER BY val'
EXPLAIN VERBOSE :query ; 
EXPLAIN ANALYSE :query ;
SELECT avg_exe_time, std_dev_exe_time, min_exe_time, max_exe_time
		FROM query_execution_stats(:'query', :num_samples);

\set query 'SELECT sum(val2) FROM ft1 GROUP BY val'
EXPLAIN VERBOSE :query ; 
EXPLAIN ANALYSE :query ;
SELECT avg_exe_time, std_dev_exe_time, min_exe_time, max_exe_time
		FROM query_execution_stats(:'query', :num_samples);

-- Case with inheritance 
\set query 'SELECT * FROM lpt ORDER BY val'
EXPLAIN VERBOSE :query ; 
EXPLAIN ANALYSE :query ;
SELECT avg_exe_time, std_dev_exe_time, min_exe_time, max_exe_time
		FROM query_execution_stats(:'query', :num_samples);
