datadz.sql

drop table if exists datadz;
    
create table if not exists datadz (
    datestmp date,
    user_id integer,
    stat double precision
);

insert into datadz
select date(datestmp),
       user_id,
       random() * 100 as stat
  from generate_series(now() - INTERVAL '2 years', now(), INTERVAL '5 minutes') as datestmp,
       generate_series(1,100) user_id;
       
create index idx_datadz_datestmp on datadz (
    datestmp
);

vacuum analyze datadz;

explain (analyze, buffers)
select count(*)
from datadz
where datestmp >= current_date - interval '7 days';

explain (analyze, buffers)
select count(*)
from datadz
where datestmp >= current_date - interval '30 days';

explain (analyze, buffers)
select count(*)
from datadz
where datestmp between '1 JAN 2021' and '31 DEC 2021'

select pg_size_pretty (pg_relation_size('datadz'));

select pg_size_pretty (pg_total_relation_size ('datadz'));