jcn-costing-test.sql

create unlogged table iot (
    id bigint generated by default as identity primary key,
    num double precision not null,
    create_dt timestamptz not null,
    stuff text generated always as (md5(id::text)) stored
)
with (fillfactor = 95);

insert into iot (num, create_dt)
select random(), x
from generate_series(
  '2020-01-01 0:00'::timestamptz,
  '2020-01-01 0:00'::timestamptz +'20000000 seconds'::interval,
  '2 seconds'::interval) x;


create index cd_multi on iot using brin(create_dt timestamptz_minmax_multi_ops);
vacuum analyze iot;

explain analyze select *
from iot
where create_dt >= '2020-02-01 0:00' and create_dt < '2020-03-01 0:00';

create index cd_single on iot using brin(create_dt);
vacuum analyze iot;

explain analyze select *
from iot
where create_dt >= '2020-02-01 0:00' and create_dt < '2020-03-01 0:00';

drop index cd_single;


-- delete first month and hi/lo values to create some holes in the table
delete from iot
where create_dt < '2020-02-01 0:00'::timestamptz;

delete from iot
where num < 0.05
or num > 0.95;

vacuum iot;

-- add add back first month, but with double density (1s step rather
-- than 2s) so it spills over into other parts of the table, causing more
-- block ranges to have a lower bound with this month.
insert into iot (num, create_dt)
select random(), x
from generate_series(
  '2020-01-01 0:00'::timestamptz,
  '2020-01-31 23:59'::timestamptz,
  '1 second'::interval) x;

vacuum analyze iot;

explain analyze select *
from iot
where create_dt >= '2020-02-01 0:00' and create_dt < '2020-03-01 0:00';

create index cd_single on iot using brin(create_dt);
vacuum analyze iot;

explain analyze select *
from iot
where create_dt >= '2020-02-01 0:00' and create_dt < '2020-03-01 0:00';