\set num_samples 100
drop table t1 cascade;
drop table t2 cascade;
create table t1 (a int, b varchar) partition by range(a);
create table t1_p1 partition of t1 for values start (1) end (10000) inclusive;
create table t1_p2 partition of t1 for values start (10001) end (20000) inclusive;
create table t1_p3 partition of t1 for values start (20001) end (30000) inclusive;
create table t1_p4 partition of t1 for values start (30001) end (40000) inclusive;
create table t1_p5 partition of t1 for values start (40001) end (50000) inclusive;
insert into t1 select i, to_char(i, 'FM000000') from generate_series(1, 50000) i;
create table t2 (a int, b varchar) partition by range(a);
create table t2_p1 partition of t2 for values start (1) end (10000) inclusive;
create table t2_p2 partition of t2 for values start (10001) end (20000) inclusive;
create table t2_p3 partition of t2 for values start (20001) end (30000) inclusive;
create table t2_p4 partition of t2 for values start (30001) end (40000) inclusive;
create table t2_p5 partition of t2 for values start (40001) end (50000) inclusive;
insert into t2 select i, to_char(i, 'FM000000') from generate_series(1, 50000) i;

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);
		RAISE NOTICE 'completed % samples', i;
	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 t1;
analyze t2;
analyze t1_p1;
analyze t1_p2;
analyze t1_p3;
analyze t1_p4;
analyze t1_p5;
analyze t2_p1;
analyze t2_p2;
analyze t2_p3;
analyze t2_p4;
analyze t2_p5;

-- join between two partitioned relations
\set query 'select * from t1, t2 where t1.a = t2.a'

-- partition-wise join
set enable_partition_wise_join to true;
select avg_exe_time, std_dev_exe_time, min_exe_time, max_exe_time
		from query_execution_stats(:'query', :num_samples);
explain (verbose, analyze) :query;

-- partition-wise join off
set enable_partition_wise_join to false;
select avg_exe_time, std_dev_exe_time, min_exe_time, max_exe_time
		from query_execution_stats(:'query', :num_samples);
explain (verbose, analyze) :query;