-- setup tables
do $an$
DECLARE
   chunks int[] := array[16, 64, 256, 1024, 4096];
   prefixes text[] := case when exists(select * from pg_extension where extname='timescaledb') then array['ts', 'p'] else array['p'] end;
   i int;
   chunk int;
   prefix text;
begin
   for prefix in select * from unnest(prefixes) LOOP
      for chunk in select * from unnest(chunks) LOOP
         if prefix = 'p' THEN
            execute format($$
 DROP TABLE IF EXISTS %s%s CASCADE;
 CREATE TABLE %s%s (
   a text,
   b text,
   c float8,
   d jsonb,
   updated_at timestamptz not null
) partition by range(updated_at);
create index on %s%s (a, updated_at desc);
            $$, prefix, chunk, prefix, chunk, prefix, chunk);
            for i in select * from generate_series(1, chunk) loop
               execute format($f$ create table %s%s_%s partition of %s%s for values from ('%s') to ('%s') $f$, prefix, chunk, i, prefix, chunk, '2015-04-04'::date - i, '2015-04-04'::date - i + 1);
            end loop;
         ELSE
            execute format($$
 DROP TABLE IF EXISTS %s%s CASCADE;
 CREATE TABLE %s%s (
   a text,
   b text,
   c float8,
   d jsonb,
   updated_at timestamptz not null
);
create index on %s%s (a, updated_at desc);
select create_hypertable('public.%s%s', 'updated_at', chunk_time_interval=>'1d'::interval, create_default_indexes=>false);
            $$, prefix, chunk, prefix, chunk, prefix, chunk, prefix, chunk);
         end if;
         execute format($$ insert into %s%s select 'ab', 'bc', 2, null, '2015-04-04'::date - v from generate_series(1, %s) v $$, prefix, chunk, chunk);
      end loop;
   end loop;
end
$an$;

do $an$
DECLARE
   chunks int[] := array[16, 64, 256, 1024, 4096];
   prefixes text[] := case when exists(select * from pg_extension where extname='timescaledb') then array['ts', 'p'] else array['p'] end;
   chunk int;
   prefix text;
begin
   for prefix in select * from unnest(prefixes) LOOP
      for chunk in select * from unnest(chunks) LOOP
         execute format($$
CREATE OR REPLACE FUNCTION %s%sat(_ts1 timestamptz, _ts2 timestamptz)
 RETURNS SETOF %s%s
 LANGUAGE sql
 STABLE PARALLEL SAFE ROWS 1
AS $function$
   SELECT * FROM %s%s WHERE updated_at BETWEEN _ts1 and _ts2 AND a='abc';
$function$
         $$, prefix, chunk, prefix, chunk, prefix, chunk);
         execute format($$
CREATE OR REPLACE FUNCTION %s%sat_ni(_ts1 timestamptz, _ts2 timestamptz)
 RETURNS SETOF %s%s
 LANGUAGE sql
 STABLE PARALLEL SAFE ROWS 1 STRICT
AS $function$
   SELECT * FROM %s%s WHERE updated_at BETWEEN _ts1 and _ts2 AND a='abc';
$function$
         $$, prefix, chunk, prefix, chunk, prefix, chunk);
         execute format($$
CREATE OR REPLACE FUNCTION %s%sat_ni_plpg(_ts1 timestamptz, _ts2 timestamptz)
 RETURNS SETOF %s%s
 LANGUAGE plpgsql
 STABLE PARALLEL SAFE ROWS 1 STRICT
AS $function$
begin
   RETURN QUERY SELECT * FROM %s%s WHERE updated_at BETWEEN _ts1 and _ts2 AND a='abc';
end;
$function$
         $$, prefix, chunk, prefix, chunk, prefix, chunk);
         execute format($$
CREATE OR REPLACE FUNCTION %s%s_limit_at(_ts1 timestamptz, _ts2 timestamptz)
 RETURNS SETOF %s%s
 LANGUAGE sql
 STABLE PARALLEL SAFE ROWS 1
AS $function$
   SELECT * FROM %s%s WHERE updated_at BETWEEN _ts1 and _ts2 AND a='abc'
   ORDER BY a, updated_at DESC LIMIT 1;
$function$
         $$, prefix, chunk, prefix, chunk, prefix, chunk);
      end loop;
   end loop;
end
$an$;

-- insert.sql
insert into :tbl select 'ab', 'bc', 2, null, '2015-04-03' from generate_series(1,1000) v;

-- select_static.sql
explain(analyze)
select * from :tbl where a='abc' and updated_at between '2015-03-25' and '2015-03-26'

-- select_static_limit.sql
explain(analyze)
select * from :tbl where a='abc' and updated_at between '2015-03-25' and '2015-03-26'
order by a, updated_at desc limit 1;

-- select_now.sql
explain(analyze)
select * from :tbl where a='abc' and updated_at between now() and now()+interval '1d';

-- select_now_limit.sql
explain(analyze)
select * from :tbl where a='abc' and updated_at between now() and now()+interval '1d'
order by a, updated_at desc limit 1;

-- select_static_fnc.sql
-- run for :fnc in:
--    {prefixes}{chunks}at -> regular, inlineable SQL function
--    {prefixes}{chunks}at_ni -> regular non-inlineable SQL function (accomplished in this test case by marking strict) (this *always* runs with a generic plan)
--    {prefixes}{chunks}at_ni_plpg -> plpgsql function (this only runs with a generic plan if it thinks it's faster after 5 tries)
--    {prefixes}{chunks}limit_at -> regular inlineable SQL function with ORDER BY a, updated_at DESC LIMIT 1 added
explain(analyze)
select * from :fnc('2015-03-25', '2015-03-26');

-- select_now_fnc.sql
explain(analyze)
select * from :fnc(now(), now()+interval '1d');

-- select_nested_loop.sql
-- any query where the join is on a dynamic value and the planner chooses a nested loop plan
-- note that nested loops happens very often for large-scale data where a suitable index is available
-- here we just need to make sure the table contains enough data for the planner to choose it - so run insert.sql for a while before this
set enable_seqscan=0; -- make it a bit more likely the planner doesn't choose a seq scan for this test case as it messes everything up
explain(analyze)
select * from (
   select * from :tbl where updated_at between '2015-04-03' and '2015-04-04' limit 10
) t1
cross join lateral ( -- looping 10 times over this, no chunk exclusion for TimescaleDb case
   select * from :tbl t2 where t1.a=t2.a and t2.updated_at=t1.updated_at + interval '1s' limit 10
) _;