-- Planning time of "c = X AND (a, b) IN (...)" against an index on (c, a, b).
-- The arms of the OR stay two-column ANDs, so the OR-to-SAOP transformation of PostgreSQL 18 does not apply.
-- Self-contained: creates its own table, runs EXPLAIN (SUMMARY ON) only, executes none of the measured queries.
-- usage: psql -X -f repro2.sql
\set ON_ERROR_STOP on
\pset pager off
\timing off

select version();

drop table if exists orproof_t;
create table orproof_t (c int not null, a text not null, b timestamp not null, d int);
insert into orproof_t
select i % 5, '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_c_a_b on orproof_t (c, a, b);
vacuum analyze orproof_t;

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;
  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
    -- rows with i = 5k + 1 all have c = 1, and b varies across them
    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((5 * k + 1)::text, 7, '0') as a,
                   (timestamp '2026-01-01' + ((5 * k + 1) % 10) * interval '1 day')::text as b
              from generate_series(1, n) k) s;

    q := 'select * from orproof_t where c = 1 and (a, b) in (' || tuples || ')';

    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 ('c = 1 and (a, b) IN', '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 ('c = 1 and (a, b) IN', 'literals, enable_bitmapscan = off', n, run, r.plan, r.has_filter, r.planning_ms);
      perform set_config('enable_bitmapscan', 'on', false);
    end loop;

    -- A generic plan is cached after the first EXECUTE, so each run needs a fresh PREPARE.
    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 c = 1 and (a, b) in (' || params || ')';
      select * into r from orproof_plan('execute orproof_p(' || args || ')');
      insert into orproof_result values ('c = 1 and (a, b) IN', '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 where t.c = 1');
      insert into orproof_result values ('c = 1 and join unnest', '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);