datatz.sql

drop table if exists datatz;
    
create table if not exists datatz (
    timestmp timestamp,
    user_id integer,
    stat double precision
);

insert into datatz
select timestmp,
       user_id,
       random() * 100 as stat
  from generate_series(now() - INTERVAL '2 years', now(), INTERVAL '5 minutes') as timestmp,
       generate_series(1,100) user_id;
       
create index idx_datatz_timestmp on datatz (
    timestmp
);       

vacuum analyze datatz;

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

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

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


select pg_size_pretty (pg_relation_size('datatz'));

select pg_size_pretty (pg_total_relation_size ('datatz'));