
DO $$
DECLARE
  i int;
  min int := 56;
  max int := 64;
  nloops int := 100;
  query_text text;
  explain_text text;
  explain_json json;
  agg_planning_time float := 0;
BEGIN
  -- Create tios table
  DROP TABLE IF EXISTS tios CASCADE;
  EXECUTE
    'CREATE TABLE tios (' ||
      string_agg('col' || (2 * n - 1)::text || ' int', ',') || ',' ||
      string_agg('col' || (2 * n)::text || ' bool', ',') ||
    ')'
    FROM generate_series(1,max) n;
  -- Add some partial indices to generate lots of plans
  FOR i IN 1..max LOOP
    EXECUTE
      'CREATE INDEX tios_col' || (2 * i - 1)::text || '_not_col' || (2 * i)::text || '_idx ON tios USING btree (col' || (2 * i - 1)::text || ') WHERE NOT col' || (2 * i)::text;
    EXECUTE
      'CREATE INDEX tios_col' || (2 * i)::text || '_min_col1_idx ON tios USING btree (col' || (2 * i)::text || ') WHERE col1 > ' || (2 * i - 1)::text;
  END LOOP;
  -- Generate big covering index
  EXECUTE
      'CREATE INDEX tios_cover_idx ON tios USING btree(' || string_agg('col' || (2 * (min + max - n))::text, ',') || ') WHERE ' ||
      string_agg('col1 > ' || (2 * (min + max - n) - 1), ' OR ')
      -- string_agg('col1 > ' || (2 * (min + max - n)) || ' OR '
                 -- 'col1 > ' || (2 * (min + max - n) - 1), ' OR ')
  FROM generate_series(min,max) n;
  -- Insert some data
  -- N.B. We actually don't want the data to be reaonable since otherwise index only scna won't be selected due to lack of vacuum.
  INSERT INTO tios(col2)
  SELECT NULL
  FROM generate_series(1,max) a CROSS JOIN generate_series(min,max) b CROSS JOIN generate_series(1,1000) c;
  -- Update statistics
  ANALYZE tios;
  -- Generate query
  SELECT 'SELECT ' ||
      string_agg('col' || (2 * (min + max - n))::text, ',') ||
      ' FROM tios WHERE ' ||
      string_agg('col1 > ' || (2 * (min + max - n)) || ' OR '
                 'col1 > ' || (2 * (min + max - n) - 1), ' OR ')
  FROM generate_series(min,max) n
    INTO query_text;
  RAISE NOTICE 'Query: %', query_text;
  EXECUTE 'EXPLAIN (ANALYZE,COSTS,VERBOSE,FORMAT TEXT) ' || query_text
  INTO explain_text;
  RAISE NOTICE 'Explain: %', explain_text;
  -- Run lots of explains
  FOR i IN 1..nloops LOOP
    EXECUTE 'EXPLAIN (COSTS,FORMAT JSON) ' || query_text
      INTO explain_json;
    SELECT agg_planning_time + (explain_json->0->>'Planning Time')::float
      INTO agg_planning_time;
  END LOOP;
  -- Show average
  RAISE NOTICE 'Average planning time (explain count = %): %ms', nloops, agg_planning_time / nloops;
END;
$$ LANGUAGE plpgsql;
