-- Planning time of a row-value IN list against a two-column index.
-- Self-contained: creates its own table, runs EXPLAIN (SUMMARY ON) only, executes none of the measured queries.
-- usage: psql -X -f repro.sql
\set ON_ERROR_STOP on
\pset pager off
\timing off

select version();

drop table if exists orproof_t;
create table orproof_t (a text not null, b timestamp not null, c int);
insert into orproof_t
select 'K' || lpad(i::text, 7, '0'), timestamp '2026-01-01' + (i % 10) * interval '1 day', i
from generate_series(1, 500000) i;
create index orproof_t_a_b on orproof_t (a, b);
vacuum analyze orproof_t;

-- Returns the top plan node, whether any node carries a Filter, and the planning time of one EXPLAIN.
create or replace function orproof_plan(q text, out plan text, out has_filter boolean, out planning_ms numeric)
language plpgsql as $$
declare
  l text;
  first boolean := true;
begin
  has_filter := false;
  for l in execute 'explain (summary on) ' || q loop
    if first then
      plan := split_part(l, '  (', 1);
      first := false;
    end if;
    if l ~ '^\s*Filter:' then
      has_filter := true;
    end if;
    if l like 'Planning Time:%' then
      planning_ms := substring(l from '([0-9.]+) ms')::numeric;
    end if;
  end loop;
end $$;

create temp table orproof_result (
  form text, setting text, n int, run int, plan text, has_filter boolean, planning_ms numeric);

do $$
declare
  n int;
  step int;
  form text;
  tuples text;
  params text;
  args text;
  a_list text;
  b_list text;
  q text;
  run int;
  r record;
begin
  foreach n in array array[250, 500, 1000, 2000] loop
    -- step 1: b varies across the tuples; step 10: all tuples share one value of b
    foreach step in array array[1, 10] loop
      form := case step when 1 then 'row IN, b varies' else 'row IN, b shared' end;
      select string_agg(format('(%L,%L)', a, b), ',' order by k),
             string_agg(format('($%s,$%s)', 2 * k - 1, 2 * k), ',' order by k),
             string_agg(format('%L,%L', a, b), ',' order by k),
             string_agg(format('%L', a), ',' order by k),
             string_agg(format('%L', b), ',' order by k)
        into tuples, params, args, a_list, b_list
        from (select k,
                     'K' || lpad((k * step)::text, 7, '0') as a,
                     (timestamp '2026-01-01' + ((k * step) % 10) * interval '1 day')::text as b
                from generate_series(1, n) k) s;

      q := 'select * from orproof_t where (a, b) in (' || tuples || ')';
      execute 'prepare orproof_p as select * from orproof_t where (a, b) in (' || params || ')';

      for run in 1..3 loop
        perform set_config('enable_bitmapscan', 'on', false);
        select * into r from orproof_plan(q);
        insert into orproof_result values (form, 'literals', n, run, r.plan, r.has_filter, r.planning_ms);

        perform set_config('enable_bitmapscan', 'off', false);
        select * into r from orproof_plan(q);
        insert into orproof_result values (form, 'literals, enable_bitmapscan = off', n, run, r.plan, r.has_filter, r.planning_ms);
        perform set_config('enable_bitmapscan', 'on', false);

        perform set_config('plan_cache_mode', 'force_custom_plan', false);
        select * into r from orproof_plan('execute orproof_p(' || args || ')');
        insert into orproof_result values (form, 'prepared, custom plan', n, run, r.plan, r.has_filter, r.planning_ms);
      end loop;

      -- A generic plan is cached after the first EXECUTE, so each run needs a fresh PREPARE.
      execute 'deallocate orproof_p';
      perform set_config('plan_cache_mode', 'force_generic_plan', false);
      for run in 1..3 loop
        execute 'prepare orproof_p as select * from orproof_t where (a, b) in (' || params || ')';
        select * into r from orproof_plan('execute orproof_p(' || args || ')');
        insert into orproof_result values (form, 'prepared, generic plan', n, run, r.plan, r.has_filter, r.planning_ms);
        execute 'deallocate orproof_p';
      end loop;
      perform set_config('plan_cache_mode', 'auto', false);

      for run in 1..3 loop
        select * into r from orproof_plan(
          'select t.* from orproof_t t join unnest(array[' || a_list || ']::text[], array[' || b_list
          || ']::timestamp[]) u(a, b) on t.a = u.a and t.b = u.b');
        insert into orproof_result values (replace(form, 'row IN', 'join unnest'), 'literals', n, run, r.plan, r.has_filter, r.planning_ms);
      end loop;
    end loop;

    for run in 1..3 loop
      select * into r from orproof_plan('select * from orproof_t where a in (' || a_list || ')');
      insert into orproof_result values ('single-column IN', 'literals', n, run, r.plan, r.has_filter, r.planning_ms);
    end loop;
  end loop;
end $$;

select form, setting, n,
       min(plan) as top_plan_node,
       bool_or(has_filter) as filter,
       min(planning_ms) as min_ms,
       (array_agg(planning_ms order by planning_ms))[2] as median_ms,
       max(planning_ms) as max_ms
  from orproof_result
 group by form, setting, n
 order by form, setting, n;

drop table orproof_t;
drop function orproof_plan(text);